ecomp-portal-BE-common/src/main/webapp/WEB-INF/fusion/orm/EP.hbm.xml

   1 <?xml version="1.0"?>
   2 <!--
   3   ============LICENSE_START==========================================
   4   ONAP Portal
   5   ===================================================================
   6   Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
   7   ===================================================================
   8
   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
  13
  14               http://www.apache.org/licenses/LICENSE-2.0
  15
  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.
  21
  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
  26
  27               https://creativecommons.org/licenses/by/4.0/
  28
  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.
  34
  35   ============LICENSE_END============================================
  36
  37
  38   -->
  39 <!DOCTYPE hibernate-mapping PUBLIC
  40         "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  41         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
  42
  43 <!-- Publishes mappings and queries specific to the ONAP Portal application. -->
  44 <hibernate-mapping package="org.onap.portalapp.portal.domain">
  45
  46         <!-- Widget class mapping details -->
  47         <class name="Widget" table="FN_WIDGET">
  48                 <id name="id" column="WIDGET_ID">
  49                         <generator class="native">
  50                                 <param name="sequence">seq_fn_widget</param>
  51                         </generator>
  52                 </id>
  53                 <property name="width" type="integer">
  54                         <column name="WDG_WIDTH" not-null="true" default="0"></column>
  55                 </property>
  56                 <property name="height" type="integer">
  57                         <column name="WDG_HEIGHT" not-null="true" default="0"></column>
  58                 </property>
  59                 <property name="url" type="string">
  60                         <column name="WDG_URL" not-null="true" default="?"></column>
  61                 </property>
  62                 <property name="name" type="string">
  63                         <column name="WDG_NAME" not-null="true" default="?"></column>
  64                 </property>
  65
  66                 <property name="appId" type="long">
  67                         <column name="APP_ID" not-null="true"></column>
  68                 </property>
  69
  70         </class>
  71
  72         <class name="EPEndpoint" table="EP_ENDPOINTS">
  73                 <id name="id" column="id">
  74                         <generator class="native">
  75                                 <param name="sequence">seq_ep_endpoint</param>
  76                         </generator>
  77                 </id>
  78                 <property name="name" type="string">
  79                         <column name="url" not-null="true"></column>
  80                 </property>
  81         </class>
  82
  83
  84         <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
  85                 <id name="id" column="id">
  86                         <generator class="native">
  87                                 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
  88                         </generator>
  89                 </id>
  90                 <property name="ep_id" type="long">
  91                         <column name="ep_id"></column>
  92                 </property>
  93                 <property name="account_id" type="long">
  94                         <column name="account_id"></column>
  95                 </property>
  96         </class>
  97
  98         <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
  99                 <id name="id" column="id">
 100                         <generator class="native">
 101                                 <param name="sequence">seq_ep_widget_catalog_parameter</param>
 102                         </generator>
 103                 </id>
 104                 <property name="widgetId" type="long">
 105                         <column name="widget_id"></column>
 106                 </property>
 107                 <property name="userId" type="long">
 108                         <column name="user_id" not-null="true"></column>
 109                 </property>
 110                 <property name="paramId" type="long">
 111                         <column name="param_id" not-null="true"></column>
 112                 </property>
 113
 114                 <property name="user_value" type="string">
 115                         <column name="user_value" not-null="true"></column>
 116                 </property>
 117
 118         </class>
 119
 120         <class name="BasicAuthCredentials" table="ep_basic_auth_account">
 121         <id name="id" column="id">
 122             <generator class="native">
 123                                 <param name="sequence">seq_ep_basic_auth_account</param>
 124                         </generator>
 125         </id>
 126                 <property name="applicationName" type="string">
 127                         <column name="ext_app_name" not-null="true" default="?"></column>
 128                 </property>
 129                 <property name="username" type="string">
 130                         <column name="username"></column>
 131                 </property>
 132                 <property name="password" type="string">
 133                         <column name="password"></column>
 134                 </property>
 135                 <property name="isActive" type="string">
 136                         <column name="active_yn" not-null="true" default="Y"></column>
 137                 </property>
 138     </class>
 139
 140         <!-- EPUserNotification class mapping details -->
 141         <class name="EPUserNotification" table="ep_user_notification">
 142                 <id name="id" column="id">
 143                         <generator class="native">
 144                                 <param name="sequence">seq_ep_user_notification</param>
 145                         </generator>
 146                 </id>
 147                 <property name="userId" column="User_ID" />
 148                 <property name="notificationId" column="notification_ID" />
 149                 <property name="viewed" column="is_viewed" />
 150                 <property name="updateTime" column="updated_time" />
 151         </class>
 152
 153
 154         <!-- User class mapping details -->
 155         <class name="EPUser" table="FN_USER">
 156                 <id name="id" column="user_id">
 157                         <generator class="native">
 158                                 <param name="sequence">seq_fn_user</param>
 159                         </generator>
 160                 </id>
 161
 162                 <property name="orgId" column="org_id" />
 163                 <property name="managerId" column="manager_id" />
 164                 <property name="firstName" column="first_name" />
 165                 <property name="middleInitial" column="middle_name" />
 166                 <property name="lastName" column="last_name" />
 167                 <property name="phone" column="phone" />
 168                 <property name="fax" column="fax" />
 169                 <property name="cellular" column="cellular" />
 170                 <property name="email" column="email" />
 171                 <property name="addressId" column="address_id" />
 172                 <property name="alertMethodCd" column="alert_method_cd" />
 173
 174                 <property name="address1" column="address_line_1" />
 175                 <property name="address2" column="address_line_2" />
 176                 <property name="city" column="city" />
 177                 <property name="state" column="state_cd" />
 178                 <property name="zipCode" column="zip_code" />
 179                 <property name="country" column="country_cd" />
 180
 181                 <property name="hrid" column="hrid" />
 182                 <property name="orgUserId" column="org_user_id" />
 183                 <property name="orgCode" column="org_code" />
 184                 <property name="loginId" column="login_id" />
 185                 <property name="loginPwd" column="login_pwd" />
 186                 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
 187
 188                 <property name="locationClli" column="location_clli" />
 189                 <property name="orgManagerUserId" column="org_manager_userid" />
 190                 <property name="company" column="company" />
 191                 <property name="department" column="department" />
 192                 <property name="departmentName" column="department_name" />
 193                 <property name="businessUnit" column="business_unit" />
 194                 <property name="businessUnitName" column="business_unit_name" />
 195                 <property name="jobTitle" column="job_title" />
 196                 <property name="siloStatus" column="silo_status" />
 197                 <property name="financialLocCode" column="fin_loc_code" />
 198                 <property name="costCenter" column="cost_center" />
 199
 200                 <property name="active" column="active_yn" type="yes_no" />
 201                 <property name="internal" column="is_internal_yn" type="yes_no" />
 202
 203                 <property name="created" type="timestamp" column="created_date" />
 204                 <property name="modified" type="timestamp" column="modified_date" />
 205
 206                 <property name="createdId" column="created_id" />
 207                 <property name="modifiedId" column="modified_id" />
 208                 <property name="timeZoneId" column="timezone" />
 209
 210                 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
 211                         inverse="true">
 212                         <key column="user_id" />
 213                         <one-to-many class="org.onap.portalapp.portal.domain.EPUserApp" />
 214                 </set>
 215
 216         </class>
 217
 218         <class name="MicroserviceData" table="ep_microservice">
 219                 <id name="id" column="id">
 220                         <generator class="native">
 221                                 <param name="sequence">seq_ep_microservice</param>
 222                         </generator>
 223                 </id>
 224
 225                 <property name="name" type="string">
 226                         <column name="name" not-null="true" default="?"></column>
 227                 </property>
 228
 229                 <property name="desc" type="string">
 230                         <column name="description" not-null="true" default="?"></column>
 231                 </property>
 232
 233                 <property name="appId" type="long">
 234                         <column name="appId" not-null="true"></column>
 235                 </property>
 236
 237                 <property name="url" type="string">
 238                         <column name="endpoint_url" not-null="true" default="?"></column>
 239                 </property>
 240
 241                 <property name="securityType" type="string">
 242                         <column name="security_type" not-null="true" default="?"></column>
 243                 </property>
 244
 245
 246                 <property name="username" type="string">
 247                         <column name="username" not-null="true" default="?"></column>
 248                 </property>
 249
 250                 <property name="password" type="string">
 251                         <column name="password" not-null="true" default="?"></column>
 252                 </property>
 253
 254                 <property name="active" type="string">
 255                         <column name="active" not-null="true" default="Y"></column>
 256                 </property>
 257         </class>
 258
 259         <class name="MicroserviceParameter" table="ep_microservice_parameter">
 260                 <id name="id" column="id">
 261                         <generator class="native">
 262                                 <param name="sequence">seq_ep_microservice_parameter</param>
 263                         </generator>
 264                 </id>
 265
 266                 <property name="serviceId" type="long">
 267                         <column name="service_id" not-null="true"></column>
 268                 </property>
 269
 270                 <property name="para_key" type="string">
 271                         <column name="para_key" not-null="true" default="?"></column>
 272                 </property>
 273
 274                 <property name="para_value" type="string">
 275                         <column name="para_value" not-null="true" default="?"></column>
 276                 </property>
 277         </class>
 278
 279         <!-- UserApp class mapping details -->
 280         <class name="EPUserApp" table="fn_user_role">
 281                 <composite-id>
 282                         <key-property name="userId" type="long">
 283                                 <column name="user_id" precision="11" scale="0" />
 284                         </key-property>
 285                         <key-many-to-one name="app"
 286                                 class="org.onap.portalapp.portal.domain.EPApp" column="app_id" />
 287                         <key-many-to-one name="role"
 288                                 class="org.onap.portalapp.portal.domain.EPRole" column="role_id" />
 289                 </composite-id>
 290                 <property name="priority" type="java.lang.Short">
 291                         <column name="priority" precision="4" scale="0" />
 292                 </property>
 293         </class>
 294
 295         <!-- User App class mapping details -->
 296         <class name="EPApp" table="fn_app">
 297                 <id name="id" column="app_id">
 298                         <generator class="native">
 299                                 <param name="sequence">seq_fn_app</param>
 300                         </generator>
 301                 </id>
 302                 <property name="name" type="string">
 303                         <column name="app_name" not-null="true" default="?"></column>
 304                 </property>
 305                 <property name="imageUrl" column="app_image_url" />
 306                 <property name="description" column="app_description" />
 307                 <property name="notes" column="app_notes" />
 308                 <property name="url" column="app_url" />
 309                 <property name="alternateUrl" column="app_alternate_url" />
 310                 <property name="appRestEndpoint" column="app_rest_endpoint" />
 311                 <property name="mlAppName" type="string">
 312                         <column name="ml_app_name" not-null="true" default="?"></column>
 313                 </property>
 314                 <property name="mlAppAdminId" type="string">
 315                         <column name="ml_app_admin_id" not-null="true" default="?"></column>
 316                 </property>
 317                 <property name="motsId" column="mots_id" />
 318                 <property name="appPassword" type="string">
 319                         <column name="app_password" not-null="true" default="?"></column>
 320                 </property>
 321                 <property name="thumbnail" column="thumbnail" />
 322                 <property name="username" type="string">
 323                         <column name="app_username" not-null="true" default="?"></column>
 324                 </property>
 325                 <property name="open" type="yes_no">
 326                         <column name="open" not-null="true" default="Y"></column>
 327                 </property>
 328                 <property name="enabled" type="yes_no">
 329                         <column name="enabled" not-null="true" default="N"></column>
 330                 </property>
 331                 <property name="uebTopicName" type="string">
 332                         <column name="ueb_topic_name"></column>
 333                 </property>
 334                 <property name="uebKey" type="string">
 335                         <column name="ueb_key"></column>
 336                 </property>
 337                 <property name="uebSecret" type="string">
 338                         <column name="ueb_secret"></column>
 339                 </property>
 340                 <property name="appType" type="integer">
 341                         <column name="app_type"></column>
 342                 </property>
 343                 <property name="centralAuth" type="yes_no">
 344                         <column name="auth_central"></column>
 345                 </property>
 346                 <property name="nameSpace" type="string">
 347                         <column name="auth_namespace"></column>
 348                 </property>
 349                 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
 350
 351         </class>
 352
 353         <!-- EPUserAppsSortPreference class mapping details -->
 354         <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
 355                 <id name="id" column="ID">
 356                         <generator class="native">
 357                                 <param name="sequence">seq_ep_usrappsrtprf</param>
 358                         </generator>
 359                 </id>
 360                 <property name="userId" column="USER_ID" />
 361                 <property name="sortPref" column="SORT_PREF" />
 362         </class>
 363
 364         <!-- EPUserAppsManualSortPreference class mapping details -->
 365         <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
 366                 <id name="id" column="ID">
 367                         <generator class="native">
 368                                 <param name="sequence">seq_ep_usrmanappsrtprf</param>
 369                         </generator>
 370                 </id>
 371                 <property name="userId" column="user_id"
 372                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 373                 <property name="appId" column="app_id"
 374                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 375                 <property name="appManualSortOrder" column="sort_order" type="int" />
 376         </class>
 377
 378         <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
 379                 <id name="id" column="ID">
 380                         <generator class="native">
 381                                 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
 382                         </generator>
 383                 </id>
 384                 <property name="userId" column="user_id" />
 385                 <property name="widgetId" column="widget_id" />
 386                 <property name="widgetRow" column="x" />
 387                 <property name="widgetCol" column="y" />
 388                 <property name="widgetHeight" column="height" />
 389                 <property name="widgetWidth" column="width" />
 390         </class>
 391
 392         <!-- User Role class mapping details -->
 393         <class name="EPRole" table="FN_ROLE" >
 394                 <id name="id" column="role_id">
 395                         <generator class="native">
 396                                 <param name="sequence">seq_fn_role</param>
 397                         </generator>
 398                 </id>
 399
 400                 <property name="name" column="role_name" />
 401                 <property name="priority" column="priority" />
 402                 <property name="active" column="active_yn" type="yes_no" />
 403                 <!-- 2 lines below may be removed! -->
 404                 <property name="appId" column="APP_ID" />
 405                 <property name="appRoleId" column="APP_ROLE_ID" />
 406                 <!-- 2 lines above may be removed -->
 407
 408                 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
 409                         sort="natural">
 410                         <key column="role_id" />
 411                         <many-to-many column="function_cd"
 412                                 class="org.onap.portalsdk.core.domain.RoleFunction" />
 413                 </set>
 414
 415                 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 416                         sort="natural">
 417                         <key column="parent_role_id" />
 418                         <many-to-many column="child_role_id"
 419                                 class="org.onap.portalapp.portal.domain.EPRole" />
 420                 </set>
 421
 422                 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 423                         sort="natural">
 424                         <key column="child_role_id" />
 425                         <many-to-many column="parent_role_id"
 426                                 class="org.onap.portalapp.portal.domain.EPRole" />
 427                 </set>
 428
 429         </class>
 430
 431         <!-- User App class mapping details -->
 432         <class name="AppContactUs" table="fn_app_contact_us">
 433                 <id name="id" column="app_id">
 434                         <generator class="foreign">
 435                                 <param name="property">app</param>
 436                         </generator>
 437                 </id>
 438                 <property name="url" column="url" />
 439                 <property name="description" column="description" />
 440                 <property name="contactName" column="contact_name" />
 441                 <property name="contactEmail" column="contact_email" />
 442                 <property name="activeYN" column="active_yn" />
 443
 444                 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
 445
 446         </class>
 447
 448         <!-- Personalization of user app selections -->
 449         <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
 450                 <id name="id" column="id">
 451                         <generator class="native">
 452                                 <param name="property">seq_fn_pers_user_app_sel</param>
 453                         </generator>
 454                 </id>
 455                 <property name="userId" column="user_id" />
 456                 <property name="appId" column="app_id" />
 457                 <property name="statusCode" column="status_cd" />
 458         </class>
 459
 460         <!-- EPAppRoleFunction class mapping details -->
 461         <class name="EPAppRoleFunction" table="ep_app_role_function">
 462                 <id name="id" column="id">
 463                         <generator class="native">
 464                                 <param name="property">seq_epp_app_role_func</param>
 465                         </generator>
 466                 </id>
 467                 <property name="roleId" column="role_id"></property>
 468                 <property name="appId" column="app_id"></property>
 469                 <property name="code" column="function_cd"></property>
 470                 <property name="roleAppId" column="role_app_id"></property>
 471         </class>
 472
 473         <!-- CentralRoleFunction class mapping details -->
 474         <class name="CentralV2RoleFunction" table="ep_app_function">
 475                 <composite-id>
 476                         <key-property name="appId"
 477                                 column="app_id" />
 478                         <key-property name="code"
 479                                 column="function_cd" />
 480                 </composite-id>
 481                 <property name="name" column="function_name" />
 482         </class>
 483
 484         <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
 485                 <id name="id" column="id">
 486                         <generator class="native">
 487                                 <param name="property">seq_ep_pers_user_widget_sel</param>
 488                         </generator>
 489                 </id>
 490                 <property name="userId" column="user_id" />
 491                 <property name="widgetId" column="widget_id" />
 492                 <property name="statusCode" column="status_cd" />
 493         </class>
 494
 495         <!-- EPUserAppRolesRequest class mapping details -->
 496         <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
 497                 <id name="id" column="req_id">
 498                         <generator class="native">
 499                                 <param name="sequence">seq_ep_user_role_request</param>
 500                         </generator>
 501                 </id>
 502                 <property name="userId" column="user_id" />
 503                 <property name="appId" column="app_id" />
 504                 <property name="createdDate" column="created_date" type="timestamp" />
 505                 <property name="updatedDate" column="updated_date" type="timestamp" />
 506                 <property name="requestStatus" column="request_status" />
 507
 508                 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
 509                         lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
 510                         <key column="req_id" />
 511                         <one-to-many
 512                                 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
 513                 </set>
 514         </class>
 515
 516         <!-- EPUserAppRolesRequestDetail class mapping details -->
 517         <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
 518                 <id name="id" column="id">
 519                         <generator class="native">
 520                                 <param name="sequence">seq_ep_user_role_request_det</param>
 521                         </generator>
 522                 </id>
 523                 <property name="reqRoleId" column="requested_role_id" />
 524                 <property name="reqType" column="request_type" />
 525                 <many-to-one name="epRequestIdData" fetch="select"
 526                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest">
 527                         <column name="req_id" not-null="true" />
 528                 </many-to-one>
 529         </class>
 530
 531         <!-- show the current user plus related users -->
 532         <sql-query name="relatedUsers">
 533                 <return-scalar column="org_user_id" type="java.lang.String" />
 534                 <![CDATA[
 535                         select t.org_user_id from (
 536                                 select distinct c.org_user_id org_user_id, c.last_login_date from
 537                                         (select c.org_user_id, c.last_login_date from
 538                                                 fn_user_role a,
 539                                                 (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,
 540                                                 fn_user c
 541                                                 where a.ROLE_ID = b.role_id
 542                                                 and a.APP_ID = b.app_id
 543                                                 and a.USER_ID = c.user_id
 544                                         union
 545                                         select org_user_id , last_login_date from fn_user where org_user_id=:userId
 546                                         ) c
 547                                 order by c.last_login_date desc limit 10
 548                         ) t
 549                 ]]>
 550         </sql-query>
 551
 552         <!-- requires values for named parameters :searchQuery and :userId -->
 553         <sql-query name="searchPortal">
 554                 <return alias="searchResult"
 555                         class="org.onap.portalapp.portal.ecomp.model.SearchResultItem" />
 556                 <![CDATA[
 557                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 558                                 ( (
 559                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
 560                                 from fn_user where
 561                                 upper( :searchQuery ) != ''
 562                                 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
 563                                 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
 564                                 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%'))
 565                                 or
 566                         ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
 567                         AND
 568                        last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
 569                         OR
 570                        (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
 571                          AND
 572                        first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
 573                                 and  upper(active_yn) = 'Y'
 574                                 )
 575                                 union
 576                                 (
 577                                 select distinct 'Application' CATEGORY, b1.app_name NAME,
 578                                 if (b1.app_type = 2,'false','true') UUID,
 579                                 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
 580                                 from
 581                                 (
 582                                 select c.*
 583                                 from fn_user_role a, fn_user b, fn_app c
 584                                 where a.USER_ID = b.user_id
 585                                 and upper(b.org_user_id) = upper(  :userId )
 586                                 and a.app_id = c.app_id
 587                                 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 588                                 and upper(c.ENABLED) = 'Y'
 589                                 ) a1 right outer join
 590                                 (select * from fn_app where
 591                                 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 592                                 and upper(ENABLED) = 'Y') b1
 593                                 on a1.APP_ID = b1.app_id
 594                                 )
 595                                 union
 596                                 (
 597                                 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
 598                                 if (b1.app_type = 2,'false','true') UUID,
 599                                 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
 600                                 from
 601                                 (select distinct  d.*, e.app_type from fn_user a,
 602                                 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
 603                                 where a.USER_ID = b.user_id
 604                                 and b.role_id = c.role_id
 605                                 and c.menu_id = d.menu_id
 606                                 and upper(a.org_user_id) = upper( :userId )
 607                                 and c.APP_ID = e.app_id
 608                                 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 609                                 and upper(d.active_yn) = 'Y'
 610                                 and upper(e.enabled) = 'Y'
 611                                 ) a1 right outer join
 612                                 (
 613                                 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
 614                                 where active_yn = 'Y'
 615                                 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 616                                 and a.menu_id = b.menu_id
 617                                 and b.app_id = c.app_id
 618                                 and c.enabled = 'Y'
 619                                 ) b1
 620                                 on a1.menu_id = b1.menu_id
 621                                 )
 622                                 union
 623                                 select distinct CATEGORY, NAME, UUID, TARGET
 624                                 from
 625                                 (
 626                                 (
 627                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 628                                 d.widget_id UUID,
 629                                 d.wdg_desc TARGET
 630                                 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
 631                                 where upper(a.org_user_id) = upper( :userId )
 632                                 and a.user_id = b.user_id
 633                                 and b.role_id = c.role_id
 634                                 and c.widget_id = d.widget_id
 635                                 and d.all_user_flag<>'Y'
 636                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 637                                 )
 638                                 union
 639                                 (
 640                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 641                                 d.widget_id UUID,
 642                                 d.wdg_desc TARGET
 643                                 from ep_widget_catalog d
 644                                 where d.all_user_flag='Y'
 645                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 646                                 )
 647                                 union
 648                                 (
 649                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 650                                 d.widget_id UUID,
 651                                 d.wdg_desc TARGET
 652                                 from fn_user a, fn_user_role b, ep_widget_catalog d
 653                                 where upper(a.org_user_id) = upper( :userId )
 654                                 and a.user_id = b.user_id
 655                                 and b.role_id = 1
 656                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 657                                 )
 658                                 ) y
 659                                 ) t,
 660                                 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
 661                                                                         ;
 662
 663                 ]]>
 664         </sql-query>
 665
 666         <!-- requires values for named parameters :searchQuery and :userId -->
 667         <sql-query name="getNotifications">
 668                 <return alias="notificationResult"
 669                         class="org.onap.portalapp.portal.transport.EpNotificationItem" />
 670                 <![CDATA[
 671                         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
 672                         (
 673
 674                         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
 675                         from
 676                                 (
 677                                 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
 678                                 from
 679                                 (
 680                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 681                                         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
 682                                 from ep_notification a, ep_role_notification b
 683                                 where a.notification_id = b.notification_id
 684                                 and (end_time is null ||  SYSDATE() <= end_time )
 685                                 and (start_time is null ||  SYSDATE() >= start_time)
 686                                 and a.is_for_all_roles = 'N'
 687                                 ) a,
 688                                 (
 689                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 690                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 691                                 where COALESCE(c.app_id,1) = d.app_id
 692                         and a.user_id = b.user_id
 693                                 and a.user_id = :user_id
 694                                 and b.role_id = c.role_id
 695                         and (d.enabled='Y' or d.app_id=1)
 696                                 )b
 697                                 where
 698                                 (
 699                                 a.role_id = b.role_id
 700                                 )
 701                                 union
 702                                 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
 703                                 from
 704                                 (
 705                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 706                                         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
 707                                 from ep_notification a, ep_role_notification b
 708                                 where a.notification_id = b.notification_id
 709                                 and (end_time is null ||  SYSDATE() <= end_time )
 710                                 and (start_time is null ||  SYSDATE() >= start_time)
 711                                 and a.is_for_all_roles = 'N'
 712                                 ) a
 713                                 where
 714                                 (
 715                                 a.recv_user_id=:user_id
 716                                 )
 717                                 union
 718                                 (
 719                                 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
 720                                 from ep_notification a
 721                                 where a.notification_id
 722                                 and (end_time is null ||  SYSDATE() <= end_time )
 723                                 and (start_time is null ||  SYSDATE() >= start_time)
 724                                 and a.is_for_all_roles = 'Y'
 725                                 )
 726                                 ) a
 727                                 where
 728                                         active_YN = 'Y'
 729                                 and
 730                                         not exists
 731                                 (
 732                                 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'
 733                                 )
 734                                 order by priority desc, created_date desc,start_Time desc
 735
 736
 737                          ) t,
 738                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 739                         ;
 740                 ]]>
 741
 742         </sql-query>
 743                 <sql-query name="getUsersByOrgIdsNotifications">
 744                 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
 745                 <![CDATA[
 746                         SELECT * from fn_user where org_user_id in (:OrgIds)
 747                 ]]>
 748
 749         </sql-query>
 750
 751
 752         <!-- Gets all Admin notification history; accepts no parameters -->
 753         <sql-query name="getAdminNotificationHistoryVO">
 754                 <return alias="adminNotificationHistoryVOResult"
 755                         class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
 756                 <![CDATA[
 757                 SELECT * from (
 758 SELECT
 759                         n.notification_ID AS notificationId,
 760                         n.is_for_online_users AS isForOnlineUsers,
 761                         n.is_for_all_roles AS isForAllRoles,
 762                         n.active_YN AS activeYn,
 763                         n.msg_header AS msgHeader,
 764                         n.msg_description AS msgDescription,
 765                         n.msg_source AS msgSource,
 766                         n.start_Time AS startTime,
 767                         n.end_time AS endTime,
 768                         n.priority,
 769                         n.creator_ID AS creatorId,
 770                         n.created_date AS createdDate,
 771                         n.notification_hyperlink AS notificationHyperlink,
 772                         u.org_user_id AS loginId
 773                 FROM
 774                         ep_notification n
 775                 LEFT JOIN
 776                         fn_user u on u.user_id = n.creator_id
 777                 WHERE
 778                         n.active_YN='Y' and msg_source='EP'
 779
 780       UNION
 781
 782       SELECT
 783                         n.notification_ID AS notificationId,
 784                         n.is_for_online_users AS isForOnlineUsers,
 785                         n.is_for_all_roles AS isForAllRoles,
 786                         n.active_YN AS activeYn,
 787                         n.msg_header AS msgHeader,
 788                         n.msg_description AS msgDescription,
 789                         n.msg_source AS msgSource,
 790                         n.start_Time AS startTime,
 791                         n.end_time AS endTime,
 792                         n.priority,
 793                         n.creator_ID AS creatorId,
 794                         n.created_date AS createdDate,
 795                         n.notification_hyperlink AS notificationHyperlink,
 796                         u.org_user_id AS loginId
 797                 FROM
 798                         ep_notification n
 799                 LEFT JOIN
 800                         fn_user u on u.user_id = n.creator_id
 801     JOIN
 802                         ep_role_notification r on r.notification_ID=n.notification_ID
 803                 WHERE
 804                         n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
 805       )n order by n.startTime desc
 806                 ]]>
 807         </sql-query>
 808
 809         <sql-query name="getEpNotificationAppRoles">
 810                 <return alias="notificationAppRoles"
 811                         class="org.onap.portalapp.portal.domain.EcompAppRole" />
 812                 <!-- This query requires no parameters. -->
 813                 <![CDATA[
 814
 815 select  a.app_id, a.app_name, b.role_id, b.role_name from
 816 (select * from fn_app where app_id = 1) a,
 817 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
 818 union
 819 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
 820 from fn_app, fn_role
 821 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
 822
 823
 824
 825                 ]]>
 826         </sql-query>
 827
 828         <!-- Gets all notification history -->
 829         <sql-query name="getNotificationHistoryVO">
 830                 <return alias="notificationHistoryVOResult"
 831                         class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
 832                 <![CDATA[
 833                 SELECT
 834                         notificationId, isForOnlineUsers, isForAllRoles, msgHeader,     msgDescription,msgSource,
 835                         startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId,     activeYn
 836                 FROM
 837                         (
 838                                         select distinct
 839                                         a.notification_ID AS notificationId,
 840                                         is_for_online_users AS isForOnlineUsers,
 841                                         is_for_all_roles AS isForAllRoles,
 842                                         msg_header AS msgHeader,
 843                                         msg_description AS msgDescription,
 844                                         msg_source AS msgSource,
 845                                         start_Time AS startTime,
 846                                         end_time AS endTime,
 847                                         priority,
 848                                         created_date AS createdDate,
 849                                         creator_ID AS creatorId,
 850                                         notification_hyperlink AS notificationHyperlink,
 851                                         login_id AS loginId,
 852                                         active_YN AS activeYn,
 853                                         if (is_viewed is null, 'N', is_viewed)
 854                         from
 855                         (
 856                                 select
 857                                         user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 858                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 859                                         creator_ID,notification_hyperlink,active_YN
 860                                 from
 861                                 (
 862                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 863                                         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,
 864                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 865
 866                    ELSE NULL
 867               END  AS login_id,b.recv_user_id
 868                                 from ep_notification a, ep_role_notification b,fn_user u
 869                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 870                                 and a.is_for_all_roles = 'N'
 871                                 and (
 872                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 873                                 or
 874                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 875                                 or
 876                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 877                                 or
 878                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 879                                 )
 880                                 ) a,
 881                                 (
 882                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 883                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 884                                 where COALESCE(c.app_id,1) = d.app_id
 885                         and a.user_id = b.user_id
 886                                 and a.user_id = :user_id
 887                                 and b.role_id = c.role_id
 888                         and (d.enabled='Y' or d.app_id=1)
 889                                 ) b
 890                                 where
 891                                 (
 892                                 a.role_id = b.role_id
 893                                 )
 894                                    UNION
 895                         select
 896                                         :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 897                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 898                                         creator_ID,notification_hyperlink,active_YN
 899                                 from
 900                                 (
 901                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 902                                         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,
 903                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 904
 905                    ELSE NULL
 906               END  AS login_id,b.recv_user_id
 907                                 from ep_notification a, ep_role_notification b,fn_user u
 908                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 909                                 and a.is_for_all_roles = 'N'
 910                                 and (
 911                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 912                                 or
 913                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 914                                 or
 915                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 916                                 or
 917                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 918                                 )
 919                                 ) a
 920                         where
 921                         (
 922                         a.recv_user_id=:user_id
 923                         )
 924                                 union
 925                                 (
 926                                 select
 927                                         :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
 928                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
 929                                         creator_ID, a.notification_hyperlink,a.active_YN
 930                                 from ep_notification a  JOIN fn_user b on b.user_id=a.creator_ID
 931                                 where a.notification_id
 932                                 and a.is_for_all_roles = 'Y'
 933                                 and (
 934                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 935                                 or
 936                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 937                                 or
 938                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 939                                 or
 940                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 941                                 )
 942                                 )
 943                                 ) a left outer join (
 944                                 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
 945                                 ) m
 946                                 on  a.notification_id = m.notification_ID
 947                                 where
 948                                 active_YN = 'Y'
 949
 950                                 order by  start_Time desc,end_time desc
 951                         ) t,
 952                      (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
 953                 ]]>
 954
 955         </sql-query>
 956
 957                 <!-- shows the received recipient to whom the notification is delivered from external system -->
 958         <sql-query name="messageRecipients">
 959                 <return-scalar column="org_user_id" type="java.lang.String" />
 960                 <![CDATA[
 961                         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
 962                 ]]>
 963         </sql-query>
 964
 965
 966         <!-- Gets role details for a specified notification -->
 967         <sql-query name="getNotificationRoles">
 968                 <return alias="notificationRolesResult"
 969                         class="org.onap.portalapp.portal.transport.EpRoleNotificationItem" />
 970                 <![CDATA[
 971                         SELECT *
 972                         FROM
 973                                 ep_role_notification
 974                         WHERE
 975                                 notification_Id = :notificationId
 976                 ]]>
 977         </sql-query>
 978
 979         <!-- Gets all applications, possibly with contact information -->
 980         <sql-query name="getAppsAndContacts">
 981                 <return alias="appContactUs"
 982                         class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
 983                 <!-- This query requires no parameters. -->
 984                 <![CDATA[
 985                         select
 986                                 a.app_id as appId, a.app_name as appName,
 987                                 c.contact_name as contactName,
 988                                 c.contact_email as contactEmail, c.url, c.description,
 989                                 c.active_yn as activeYN
 990                         from
 991                                 fn_app a
 992                         left join
 993                                 fn_app_contact_us c
 994                                 on a.app_id = c.app_id
 995                         where
 996                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 997                         ;
 998                 ]]>
 999         </sql-query>
1000
1001         <!-- Gets all rows from the app-contact-us table, extended with app information -->
1002         <sql-query name="getAppContactUsItems">
1003                 <return alias="appContactUs"
1004                         class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1005                 <!-- This query requires no parameters. -->
1006                 <![CDATA[
1007                         select
1008                                 c.app_id as appId, c.contact_name as contactName,
1009                                 c.contact_email as contactEmail, c.url, c.description,
1010                                 c.active_yn as activeYN, a.app_name as appName
1011                         from
1012                                 fn_app_contact_us c
1013                         left join
1014                                 fn_app a
1015                                 on a.app_id = c.app_id
1016                         where
1017                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1018                         ;
1019                 ]]>
1020         </sql-query>
1021
1022         <!-- Gets one row for each app with the category and catenated functions. -->
1023         <sql-query name="getAppCategoryFunctions">
1024                 <return alias="appCategoryFunctions"
1025                         class="org.onap.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1026                 <!-- This query requires no parameters. -->
1027                 <![CDATA[
1028                         SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1029                                 r.category as category, r.app_id as appId, r.app_name as application,
1030                                 group_concat(function_text separator ', ') as functions
1031                         FROM (
1032                                 SELECT
1033                                 app_id, function_text, app_name,
1034                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1035                                 FROM (
1036                                         SELECT
1037                                         j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1038                                         k.text parent_menu, k.parent_menu_id parent_menu_id
1039                                         FROM (
1040                                                 SELECT distinct
1041                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1042                                                 FROM
1043                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1044                                                 WHERE
1045                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1046                                                 ) j,
1047                                         fn_menu_functional k
1048                                         WHERE j.parent_menu_id = k.menu_id
1049                                         ) fn,
1050                                 fn_menu_functional a
1051                                 WHERE fn.parent_menu_id = a.menu_id
1052                         ) r,
1053                         (SELECT @rn /*'*/:=/*'*/ 0) t2
1054                         group by r.category, r.app_id, r.app_name
1055                         order by category, app_name
1056                         ;
1057                 ]]>
1058         </sql-query>
1059
1060         <!-- Gets one row for each function-application-role combination. -->
1061         <sql-query name="getAppAccessFunctionRole">
1062                 <return alias="appAccessFunctionRole"
1063                         class="org.onap.portalapp.portal.domain.GetAccessResult" />
1064                 <!-- This query requires one parameters. -->
1065                 <![CDATA[
1066                         select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1067                         (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,
1068                         o.requested_role_id,o.request_type
1069                         from
1070                         (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1071                         from
1072                         (
1073                         select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1074                         from
1075                         FN_ROLE C, FN_APP D
1076                         where COALESCE(c.app_id,1) = d.app_id
1077                         and (d.enabled='Y' or d.app_id=1)
1078                         and c.active_yn = 'Y'
1079                         ) a left outer join
1080                         (
1081                         select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1082                         WHERE
1083                         A.MENU_ID = B.MENU_ID
1084                         and active_yn = 'Y'
1085                         )b
1086                         on a.role_id = b.role_id) t left outer join
1087                         (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1088                         where er.req_id=ed.req_id
1089                         and upper(ed.request_type)='P'
1090                         and er.user_id =:userId) o
1091                         on t.app_id=o.app_id
1092                         and t.role_id = o.requested_role_id
1093                         JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1094                         ;
1095                 ]]>
1096         </sql-query>
1097
1098         <query name="getCommonWidgetItem">
1099                 from CommonWidget where category = :cat order by
1100                 sortOrder, title
1101         </query>
1102
1103         <sql-query name="getGuestLastLogin">
1104                 <return-scalar column="audit_date" type="java.util.Date" />
1105                 <![CDATA[
1106                         select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1107                 ]]>
1108         </sql-query>
1109
1110         <sql-query name="getActiveUsers">
1111                 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1112                 <![CDATA[
1113                 SELECT * FROM FN_USER WHERE active_yn='Y'
1114                 ]]>
1115         </sql-query>
1116
1117         <sql-query name="getAppsAdmins">
1118                 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1119         <![CDATA[
1120         SELECT apps.APP_NAME, apps.APP_ID, user.USER_ID, user.FIRST_NAME, user.LAST_NAME, user.org_user_id FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_app apps ON apps.APP_ID = userrole.APP_ID  WHERE user.active_yn='Y' AND userrole.ROLE_ID = :accountAdminRoleId AND (apps.ENABLED = 'Y' OR apps.APP_ID=1)
1121         ]]>
1122         </sql-query>
1123
1124         <sql-query name="getPortalAdmins">
1125                 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1126         <![CDATA[
1127                 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
1128         ]]>
1129         </sql-query>
1130
1131         <!-- Gets personalized list of enabled and accessible apps for regular user -->
1132         <sql-query name="getPersUserApps">
1133                 <return alias="persUserApps" class="org.onap.portalapp.portal.domain.EPApp" />
1134                 <!-- This query requires one parameter: userId (number) -->
1135                 <![CDATA[
1136                     select
1137                                 distinct  -- multiple roles yield multiple rows
1138                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1139                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1140                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1141                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1142                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL
1143                     from FN_APP a
1144                     -- Portal assigns role 999 to app administrator
1145                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1146                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1147                         where a.ENABLED = 'Y'
1148                     and (
1149                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1150                         or
1151                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1152                         )
1153                         order by app_name
1154                     ;
1155                 ]]>
1156         </sql-query>
1157
1158         <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1159                 admin -->
1160         <sql-query name="getPersAdminApps">
1161                 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1162                 <!-- This query requires one parameter: userId (number) -->
1163                 <![CDATA[
1164                     select
1165                                 distinct  -- multiple roles yield multiple rows
1166                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1167                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1168                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1169                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1170                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE      , a.AUTH_CENTRAL ,
1171                                 a.AUTH_NAMESPACE
1172                     from FN_APP a
1173                     -- Portal assigns role 999 to app administrator
1174                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1175                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1176                         where a.ENABLED = 'Y'
1177                     and (
1178                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1179                         or
1180                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1181                                 or
1182                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1183                         )
1184                         order by app_name
1185                     ;
1186                 ]]>
1187         </sql-query>
1188
1189         <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1190         <sql-query name="getPersAdminAppsOrderByName">
1191                 <return alias="AdminAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1192                 <!-- This query requires one parameter: userId (number) -->
1193                 <![CDATA[
1194                  select
1195                                 distinct  -- multiple roles yield multiple rows
1196                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1197                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1198                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1199                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1200                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1201                                 a.AUTH_NAMESPACE
1202                     from FN_APP a
1203                     -- Portal assigns role 999 to app administrator
1204                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1205                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1206                         where a.ENABLED = 'Y'
1207                     and (
1208                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1209                         or
1210                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1211                                 or
1212                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1213                         )
1214                         union
1215                 select distinct  -- multiple roles yield multiple rows
1216                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1217                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1218                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1219                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1220           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1221           b.AUTH_NAMESPACE
1222           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1223           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
1224                   order by app_name
1225                   ;
1226
1227                 ]]>
1228         </sql-query>
1229
1230         <!--Gets personalized list of enabled and accessible User apps by name -->
1231         <sql-query name="getPersUserAppsOrderByName">
1232                 <return alias="UserAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1233                 <!-- This query requires one parameter: userId (number) -->
1234                 <![CDATA[
1235                 select
1236                                 distinct  -- multiple roles yield multiple rows
1237                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1238                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1239                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1240                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1241                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1242                                 a.AUTH_NAMESPACE
1243                     from FN_APP a
1244                     -- Portal assigns role 999 to app administrator
1245                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1246                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1247                         where a.ENABLED = 'Y'
1248                     and (
1249                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1250                         or
1251                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1252                         )
1253                         union
1254                 select distinct  -- multiple roles yield multiple rows
1255                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1256                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1257                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1258                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1259           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1260           b.AUTH_NAMESPACE
1261           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1262           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
1263                 order by app_name
1264                  ;
1265
1266                 ]]>
1267         </sql-query>
1268
1269
1270         <!--Gets personalized list of enabled and accessible Super Admin apps by
1271                 LastUsed -->
1272         <sql-query name="getAdminAppsOrderByLastUsed">
1273                 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1274                 <![CDATA[
1275                     select
1276                         *
1277                         from (select
1278                                 distinct  -- multiple roles yield multiple rows
1279                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1280                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1281                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1282                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1283                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1284                                 a.AUTH_NAMESPACE
1285                     from FN_APP a
1286                     -- Portal assigns role 999 to app administrator
1287                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1288                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1289                         where a.ENABLED = 'Y'
1290                     and (
1291                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1292                         or
1293                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1294                                 or
1295                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1296                         )
1297                         union
1298                   select distinct  -- multiple roles yield multiple rows
1299                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1300                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1301                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1302                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1303           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1304           b.AUTH_NAMESPACE
1305           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1306           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
1307                         ) A
1308                 left outer join
1309                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1310                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1311                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1312                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1313                         and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1314                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1315                          )B
1316                         on A.app_id = B.Affected_record_id
1317                         order by AUDIT_DATE DESC;
1318                         ]]>
1319         </sql-query>
1320
1321         <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1322         <sql-query name="getUserAppsOrderByLastUsed">
1323                 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1324                 <![CDATA[
1325                         select * from (select
1326                                 distinct  -- multiple roles yield multiple rows
1327                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1328                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1329                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1330                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1331                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1332                                 a.AUTH_NAMESPACE
1333                     from FN_APP a
1334                     -- Portal assigns role 999 to app administrator
1335                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1336                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1337                         where a.ENABLED = 'Y'
1338                     and (
1339                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1340                         or
1341                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1342                         )union
1343                 select distinct  -- multiple roles yield multiple rows
1344                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1345                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1346                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1347                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1348           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1349           b.AUTH_NAMESPACE
1350           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1351           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
1352
1353                         ) A
1354                 left outer join
1355                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1356                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1357                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1358                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1359                         and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1360                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1361                          )B
1362                         on A.app_id = B.Affected_record_id
1363                         order by AUDIT_DATE DESC;
1364
1365
1366                 ]]>
1367
1368         </sql-query>
1369
1370         <!--Gets personalized list of enabled and accessible Super Admin apps by
1371                 Most Used -->
1372         <sql-query name="getAdminAppsOrderByMostUsed">
1373                 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1374                 <![CDATA[
1375                         select
1376                         *
1377                         from (select
1378                                 distinct  -- multiple roles yield multiple rows
1379                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1380                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1381                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1382                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1383                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1384                                 a.AUTH_NAMESPACE
1385                     from FN_APP a
1386                     -- Portal assigns role 999 to app administrator
1387                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1388                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1389                         where a.ENABLED = 'Y'
1390                     and (
1391                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1392                         or
1393                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1394                                 or
1395                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1396                         )union
1397                    select distinct  -- multiple roles yield multiple rows
1398                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1399                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1400                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1401                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1402           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1403           b.AUTH_NAMESPACE
1404           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1405           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
1406                         ) A
1407                         left outer join
1408                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1409                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1410                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1411                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1412                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1413                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1414                          )B
1415                         on A.app_id = B.Affected_record_id
1416                         order by total_visits DESC;
1417                 ]]>
1418
1419         </sql-query>
1420
1421         <!--Gets personalized list of enabled and accessible NON Super Admin User
1422                 apps by Most Used -->
1423         <sql-query name="getUserAppsOrderByMostUsed">
1424                 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1425                 <![CDATA[
1426                         select * from
1427                         (select
1428                                 distinct  -- multiple roles yield multiple rows
1429                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1430                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1431                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1432                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1433                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE             , a.AUTH_CENTRAL ,
1434                                 a.AUTH_NAMESPACE
1435                     from FN_APP a
1436                     -- Portal assigns role 999 to app administrator
1437                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1438                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1439                         where a.ENABLED = 'Y'
1440                     and (
1441                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1442                         or
1443                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1444                         )union
1445                   select distinct  -- multiple roles yield multiple rows
1446                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1447                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1448                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1449                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1450           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1451           b.AUTH_NAMESPACE
1452           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1453           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
1454                         )A
1455                         left outer join
1456                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1457                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1458                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1459                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1460                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1461                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1462                          )B
1463                         on A.app_id = B.Affected_record_id
1464                         order by total_visits DESC;
1465                 ]]>
1466         </sql-query>
1467
1468         <!--Gets personalized list of enabled and accessible Super Admin apps by
1469                 Manual -->
1470         <sql-query name="getAdminAppsOrderByManual">
1471                 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1472                 <![CDATA[
1473                         select * from
1474                         (select
1475                                 distinct  -- multiple roles yield multiple rows
1476                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1477                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1478                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1479                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1480                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1481                                 a.AUTH_NAMESPACE
1482                     from FN_APP a
1483                     -- Portal assigns role 999 to app administrator
1484                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1485                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1486                         where a.ENABLED = 'Y'
1487                     and (
1488                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1489                         or
1490                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1491                                 or
1492                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1493                         )union
1494                 select distinct  -- multiple roles yield multiple rows
1495                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1496                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1497                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1498                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1499           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1500           b.AUTH_NAMESPACE
1501           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1502           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
1503                         ) A
1504                         left outer join
1505                          (select m.app_id, m.sort_order
1506                         from ep_pers_user_app_man_sort m
1507                         where USER_ID = :userId
1508                         ) B
1509                         on A.APP_ID = B.app_id
1510                         order by sort_order ASC
1511                     ;
1512                 ]]>
1513
1514         </sql-query>
1515
1516         <!--Gets personalized list of enabled and accessible NON Super admin User
1517                 apps by Manual -->
1518         <sql-query name="getUserAppsOrderByManual">
1519                 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1520                 <![CDATA[
1521                                                         select * from   (select
1522                                 distinct  -- multiple roles yield multiple rows
1523                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1524                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1525                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1526                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1527                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1528                                 a.AUTH_NAMESPACE
1529                     from FN_APP a
1530                     -- Portal assigns role 999 to app administrator
1531                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1532                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1533                         where a.ENABLED = 'Y'
1534                     and (
1535                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1536                         or
1537                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1538                         )union
1539                 select distinct  -- multiple roles yield multiple rows
1540                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1541                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1542                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1543                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1544           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1545           b.AUTH_NAMESPACE
1546           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1547           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
1548                         ) A
1549                         left outer join
1550                          (select m.app_id, m.sort_order
1551                         from ep_pers_user_app_man_sort m
1552                         where USER_ID = :userId
1553                         ) B
1554                         on A.APP_ID = B.app_id
1555                         order by sort_order ASC
1556                     ;
1557                 ]]>
1558         </sql-query>
1559
1560         <!-- Gets regular user's list of enabled apps including accessible and select
1561                 statuses -->
1562         <sql-query name="getUserAppCatalog">
1563                 <return alias="userAppCatalog"
1564                         class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1565                 <!-- This query requires one parameter: userId (number) -->
1566                 <![CDATA[
1567                                                 select
1568                                 distinct  -- multiple roles yield multiple rows
1569                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1570                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1571                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1572                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1573                             IF(a.open = 'Y', TRUE, FALSE) as open,
1574                 -- ACCESS(-ible) means user has a defined role OR the application is open
1575                                 case
1576                                         -- regular app and user has a role
1577                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1578                     -- open app
1579                     when a.OPEN = 'Y' then TRUE
1580                     else FALSE
1581                 end as 'access' ,
1582                 -- SELECT(-ed) indicates user personalization
1583                 case
1584                                         -- regular app, user has a role, no personalization
1585                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1586                     -- open app and has personalization
1587                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1588                     else FALSE
1589                 end as 'select' ,
1590                 -- PENDING indicates user checked a box
1591                 case
1592                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1593                         else FALSE
1594                 end as 'pending'
1595                     from FN_APP a
1596                     -- Portal assigns role 999 to app administrator
1597             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1598                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1599       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'
1600       where a.ENABLED = 'Y'
1601
1602                         -- Show accessible apps first, then the rest; sort by name within each set.
1603             order by access desc, app_name asc
1604                     ;
1605                 ]]>
1606         </sql-query>
1607
1608         <sql-query name="getMyloginAppDetails">
1609                 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1610                 <![CDATA[
1611                 SELECT * FROM fn_app  where ml_app_name =:appName
1612                 ;
1613                 ]]>
1614         </sql-query>
1615
1616         <sql-query name="getAppDetailsByAppName">
1617                 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1618                 <![CDATA[
1619                 SELECT * FROM fn_app  where app_name =:appName
1620                 ;
1621                 ]]>
1622         </sql-query>
1623
1624                 <sql-query name="getMyAppDetailsByUebKey">
1625                 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1626                 <![CDATA[
1627                 SELECT * FROM fn_app  where ueb_key =:appKey
1628                 ;
1629                 ]]>
1630         </sql-query>
1631
1632
1633         <sql-query name="deleteAccountEndpoint">
1634                 <![CDATA[
1635                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1636                 ;
1637                 ]]>
1638         </sql-query>
1639
1640         <sql-query name="deleteAccountEndpointRecord">
1641                 <![CDATA[
1642                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1643                 ;
1644                 ]]>
1645         </sql-query>
1646
1647         <sql-query name="deleteEPEndpoint">
1648                 <![CDATA[
1649                 DELETE FROM ep_endpoints WHERE id =:epId
1650                 ;
1651                 ]]>
1652         </sql-query>
1653
1654         <sql-query name="deleteBasicAuthAccount">
1655                 <![CDATA[
1656                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1657                 ;
1658                 ]]>
1659         </sql-query>
1660
1661         <sql-query name="getAppRoles">
1662                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1663                 <![CDATA[
1664                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1665                 ;
1666                 ]]>
1667         </sql-query>
1668
1669         <sql-query name="getPartnerAppRolesList">
1670                 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1671                 <![CDATA[
1672                 SELECT * FROM fn_role where app_id =:appId
1673                 ;
1674                 ]]>
1675         </sql-query>
1676
1677         <sql-query name="getPortalAppRolesList">
1678                 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1679                 <![CDATA[
1680                 SELECT * FROM fn_role where app_id is null
1681                 ;
1682                 ]]>
1683         </sql-query>
1684
1685         <sql-query name="getPortalAppRoleByRoleId">
1686                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1687                 <![CDATA[
1688                 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1689                 ;
1690                 ]]>
1691         </sql-query>
1692
1693         <sql-query name="getPartnerAppRoleByRoleId">
1694                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1695                 <![CDATA[
1696                 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1697                 ;
1698                 ]]>
1699         </sql-query>
1700
1701         <sql-query name="getPartnerAppRoleById">
1702                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1703                 <![CDATA[
1704                 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1705                 ;
1706                 ]]>
1707         </sql-query>
1708
1709         <sql-query name="getPortalAppRoles">
1710                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1711                 <![CDATA[
1712                 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1713                 ;
1714                 ]]>
1715         </sql-query>
1716
1717         <sql-query name="deleteMicroserviceParameter">
1718                 <![CDATA[
1719                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1720                 ;
1721                 ]]>
1722         </sql-query>
1723
1724         <sql-query name="deleteMicroserviceParameterById">
1725                 <![CDATA[
1726                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1727                 ;
1728                 ]]>
1729         </sql-query>
1730
1731         <sql-query name="deleteWidgetCatalogParameter">
1732                 <![CDATA[
1733                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1734                 ;
1735                 ]]>
1736         </sql-query>
1737
1738         <sql-query name="deleteMicroservice">
1739                 <![CDATA[
1740                 DELETE FROM ep_microservice WHERE id =:serviceId
1741                 ;
1742                 ]]>
1743         </sql-query>
1744
1745         <sql-query name="epUserAppId">
1746                 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1747                 <![CDATA[
1748                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1749                 ;
1750                 ]]>
1751         </sql-query>
1752
1753         <sql-query name="userAppsSortPreferenceQuery">
1754                 <return alias="appsSortPreferenceQuery"
1755                         class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1756                 <![CDATA[
1757                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1758
1759                 ]]>
1760         </sql-query>
1761
1762         <sql-query name="userAppsManualSortPrfQuery">
1763                 <return alias="AppsManualSortPrfQuery"
1764                         class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1765                 <![CDATA[
1766                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1767
1768                 ]]>
1769         </sql-query>
1770
1771         <sql-query name="userWidgetManualSortPrfQuery">
1772                 <return alias="widgetManualSortPrfQuery"
1773                         class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1774                 <![CDATA[
1775                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1776                 ]]>
1777         </sql-query>
1778
1779         <sql-query name="appRoles">
1780                 <return alias="rolesForApp"
1781                         class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1782                 <![CDATA[
1783                         SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1784                         ;
1785                 ]]>
1786         </sql-query>
1787
1788         <sql-query name="userAppRolesRequestList">
1789                 <return alias="appRolesRequestList"
1790                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1791                 <![CDATA[
1792                         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'
1793                         ;
1794                 ]]>
1795         </sql-query>
1796
1797         <sql-query name="userAppRolesRequestDetailList">
1798                 <return alias="appRolesRequestDetailList"
1799                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1800                 <![CDATA[
1801
1802                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1803                         ;
1804                 ]]>
1805         </sql-query>
1806
1807         <!-- Gets list of enabled apps including accessible and select statuses -->
1808         <sql-query name="getAdminAppCatalog">
1809                 <return alias="adminAppCatalog"
1810                         class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1811                 <!-- This query requires one parameter: userId (number) -->
1812                 <![CDATA[
1813                         select
1814                                 distinct  -- multiple roles yield multiple rows
1815                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1816                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1817                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1818                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1819                             IF(a.open = 'Y', TRUE, FALSE) as open,
1820                 -- ACCESS(-ible) means user has a defined role OR the application is open
1821                                 case
1822                                         -- regular app and user has a role
1823                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1824                     -- open app
1825                     when a.OPEN = 'Y' then TRUE
1826                     else FALSE
1827                 end as 'access' ,
1828                 -- SELECT(-ed) indicates user personalization
1829                 case
1830                                         -- regular app, user has a role, no personalization
1831                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1832                                         -- regular app, user has role, admin forced a personalization
1833                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1834                                         -- regular app, user has no role, admin forced a personalization
1835                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1836                     -- open app and has personalization
1837                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1838                     else FALSE
1839                 end as 'select' ,
1840                 -- PENDING indicates user checked a box
1841                 case
1842                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1843                         else FALSE
1844                 end as 'pending'
1845                     from FN_APP a
1846                     -- Portal assigns role 999 to app administrator
1847             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1848                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1849                         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'
1850                         where a.ENABLED = 'Y'
1851                         -- Show accessible apps first, then the rest; sort by name within each set.
1852             order by access desc, app_name asc
1853                     ;
1854                 ]]>
1855         </sql-query>
1856
1857         <sql-query name="userAppCatalogRoles">
1858                 <return alias="epUserAppCatalogRoles"
1859                         class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1860                 <![CDATA[
1861         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
1862        from ep_user_roles_request A left join ep_user_roles_request_det B
1863                    on a.req_id = b.req_id
1864                    where  A.user_id=:userid
1865        and A.app_id IN (select app_id from fn_app where app_name=:appName)
1866        and A.request_status ='P'
1867        ;
1868                 ]]>
1869         </sql-query>
1870
1871         <sql-query name="getUserApproles">
1872                 <return alias="businessCardUserApplicationRoles"
1873                         class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1874                 <![CDATA[
1875
1876                 select   fr.role_name, fa.app_name
1877           from
1878         fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1879           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)
1880           ;
1881
1882                 ]]>
1883         </sql-query>
1884
1885     <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1886         <sql-query name="deleteNotificationsFromEpNotificationTable">
1887                 <![CDATA[
1888                 delete from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1889                 ]]>
1890         </sql-query>
1891
1892
1893                 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1894         <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1895                 <![CDATA[
1896         delete from ep_role_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1897                 ]]>
1898         </sql-query>
1899
1900         <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1901         <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1902                 <![CDATA[
1903         delete from ep_user_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1904                 ]]>
1905         </sql-query>
1906
1907         <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1908         <sql-query name="getAppRoleFunctionList">
1909                 <return alias="appRoleFunctionList"
1910                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1911                 <![CDATA[
1912
1913                         SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1914                         where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1915                         ;
1916
1917                 ]]>
1918         </sql-query>
1919
1920         <!-- Gets list of all role functions -->
1921         <sql-query name="getAllRoleFunctions">
1922                 <return alias="allRoleFunctions"
1923                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1924                 <![CDATA[
1925
1926                         SELECT * from ep_app_function where app_id =:appId
1927                         ;
1928
1929                 ]]>
1930         </sql-query>
1931
1932         <!-- Get ep_app_function records provided by single parameter -->
1933         <sql-query name="getRoleFunction">
1934                 <return alias="RoleFunction"
1935                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1936                         <![CDATA[
1937
1938                         SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1939                         ;
1940
1941                         ]]>
1942         </sql-query>
1943
1944         <!-- Gets the current user app roles records and requires two parameters  -->
1945         <sql-query name="getUserAppCurrentRoles">
1946                 <return alias="userAppCurrentRoles"
1947                         class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
1948                         <![CDATA[
1949
1950                         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
1951                         ;
1952                         ]]>
1953         </sql-query>
1954
1955         <!-- Gets the current user app roles along with account administrator and requires two parameters  -->
1956         <sql-query name="getUserAppExistingRoles">
1957                 <return alias="userAppExistingRoles"
1958                         class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
1959                         <![CDATA[
1960                         select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1961                         left outer join fn_role fr on fu.role_id = fr.role_id
1962                         where fu.user_id =:userId and fu.app_id =:appId
1963                         ;
1964                         ]]>
1965         </sql-query>
1966
1967         <!-- Gets all functions of portal app -->
1968         <sql-query name="getAllFunctions">
1969                 <return alias="allFunctions"
1970                         class="org.onap.portalsdk.core.domain.RoleFunction" />
1971                         <![CDATA[
1972                         select * from fn_function
1973                         ;
1974                         ]]>
1975         </sql-query>
1976
1977         <!-- Gets the current user app roles records and requires two parameters  -->
1978         <sql-query name="getPartnerAppFunctions">
1979                 <return alias="partnerAppFunctions"
1980                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1981                         <![CDATA[
1982                         select * from ep_app_function where app_id =:appId
1983                         ;
1984                         ]]>
1985         </sql-query>
1986
1987         <!-- Gets the  all role functions and requires one parameters -->
1988         <sql-query name="uploadAllRoleFunctions">
1989                 <return alias="allRoleFunctions"
1990                         class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
1991                         <![CDATA[
1992                                 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
1993                                 ;
1994                         ]]>
1995         </sql-query>
1996
1997                 <!-- Gets the  all application role functions from ep_app_function table and requires one parameters -->
1998         <sql-query name="uploadPartnerRoleFunctions">
1999                 <return alias="partnerRoleFunctions"
2000                         class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2001                         <![CDATA[
2002                                 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
2003                                 ;
2004                         ]]>
2005         </sql-query>
2006
2007         <sql-query name="getRoletoUpdateInExternalAuthSystem">
2008                 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2009                         <![CDATA[
2010                                         SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2011                         ;
2012                         ]]>
2013         </sql-query>
2014
2015                 <!-- Gets all active records from fn_role that is associated with this app-->
2016         <sql-query name="getActiveRolesOfApplication">
2017                 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2018                 <![CDATA[
2019                 select * from fn_role where active_yn = 'Y' and app_id=:appId
2020                 ;
2021                 ]]>
2022         </sql-query>
2023
2024         <sql-query name="getBulkUserRoles">
2025                 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2026                         <![CDATA[
2027                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2028                         left outer join fn_role fr on fr.role_id = fur.role_id
2029                         left outer join fn_app fa on fa.app_id = fur.app_id
2030                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2031                         ;
2032                         ]]>
2033         </sql-query>
2034
2035         <sql-query name="getBulkUsersForSingleRole">
2036                 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2037                         <![CDATA[
2038                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2039                         left outer join fn_role fr on fr.role_id = fur.role_id
2040                         left outer join fn_app fa on fa.app_id = fur.app_id
2041                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2042                         ;
2043                         ]]>
2044         </sql-query>
2045
2046         <sql-query name="getCentralizedApps">
2047                 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2048                         <![CDATA[
2049                         select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2050                         ;
2051                         ]]>
2052         </sql-query>
2053
2054         <sql-query name="getUserRoles">
2055                         <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2056                 <![CDATA[
2057
2058         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
2059          ;
2060                 ]]>
2061         </sql-query>
2062                 <sql-query name="getRoleFunctionsOfUser">
2063                         <![CDATA[
2064                         select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2065                         where fu.role_id = ep.role_id
2066                         and fu.app_id = ep.app_id
2067                         and fu.user_id =:userId and ep.app_id = 1
2068                         and ea.function_cd = ep.function_cd
2069                     union
2070             select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2071                         where role_id = 999
2072                         and app_r_f.function_cd = a_f.function_cd
2073                         and exists
2074                         (
2075                         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
2076                         and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2077                         );
2078                         ]]>
2079         </sql-query>
2080
2081         <sql-query name="getUserRolesForLeftMenu">
2082                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2083
2084                 <![CDATA[
2085                 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
2086         INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2087         INNER JOIN fn_app app ON app.app_id= userrole.app_id
2088         INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2089         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)
2090         ;
2091                 ]]>
2092         </sql-query>
2093
2094         <sql-query name="getRolesForLeftMenu">
2095                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2096
2097                 <![CDATA[
2098                 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
2099         INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2100         INNER JOIN fn_app app ON app.app_id= userrole.app_id
2101         INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2102         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)
2103         ;
2104                 ]]>
2105         </sql-query>
2106
2107         <!-- Gets all functions for an application along with global functions and requires single parameter -->
2108         <sql-query name="getMenuFunctions">
2109                 <![CDATA[
2110                         select f.function_cd from ep_app_function f
2111                         where f.app_id =:appId
2112                         UNION
2113                         select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2114                         and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2115                  ;
2116                 ]]>
2117         </sql-query>
2118
2119         <sql-query name="getRequestIdsForApp">
2120         <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2121                 <![CDATA[
2122                 select * from ep_user_roles_request where app_id =:app_id
2123                 ;
2124                 ]]>
2125         </sql-query>
2126
2127         <sql-query name="ApplicationUserRoles">
2128                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2129                 <![CDATA[
2130                    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,
2131            fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2132            from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2133            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
2134            union
2135            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,
2136            fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2137            from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2138            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'
2139            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';
2140                 ]]>
2141         </sql-query>
2142
2143         <sql-query name="getCurrentAppRoleFunctions">
2144                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2145                 <![CDATA[
2146                 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
2147                 and ef.function_cd =:functionCd and epr.app_id =:appId
2148                 ;
2149                 ]]>
2150         </sql-query>
2151
2152         <sql-query name="deleteUserWidgetPlacement">
2153                 <![CDATA[
2154                 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2155                 ;
2156                 ]]>
2157         </sql-query>
2158
2159         <sql-query name="getCentralizedAppsOfUser">
2160                 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2161                 <![CDATA[
2162
2163                 select distinct fa.app_id, fa.app_name
2164                 from  fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2165                 Where  fu.user_id =  fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2166                 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)
2167                 ;
2168
2169                 ]]>
2170         </sql-query>
2171
2172     <query name="getEPUserByOrgUserId">
2173         <![CDATA[
2174                 FROM EPUser WHERE orgUserId = :org_user_id
2175         ]]>
2176     </query>
2177
2178         <query name="getEPUserByLoginId">
2179         <![CDATA[
2180                 FROM EPUser WHERE loginId = :login_id
2181         ]]>
2182     </query>
2183
2184     <query name="getEPUserByLoginIdLoginPwd">
2185         <![CDATA[
2186                 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2187         ]]>
2188     </query>
2189
2190     <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2191                 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2192                 <![CDATA[
2193                 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
2194                 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2195                 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
2196                 ;
2197                 ]]>
2198         </sql-query>
2199
2200      <sql-query name="getGlobalRolesOfPortal">
2201                 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2202                 <![CDATA[
2203                 select * from fn_role where role_name  like 'global_%' and app_id is null and active_yn='Y'
2204                 ;
2205                 ]]>
2206         </sql-query>
2207
2208         <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2209                 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2210                 <![CDATA[
2211                 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2212                 ;
2213                 ]]>
2214         </sql-query>
2215
2216         <sql-query name="getAppFunctionOnCodeAndAppId">
2217                 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2218                 <![CDATA[
2219                 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2220                 ;
2221                 ]]>
2222         </sql-query>
2223
2224         <sql-query name="getGlobalRoleForRequestedApp">
2225                 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2226                 <![CDATA[
2227                 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
2228         from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2229         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
2230         ;
2231                 ]]>
2232         </sql-query>
2233
2234         <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2235                 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2236                 <![CDATA[
2237                 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
2238                 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2239                 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
2240                 ;
2241                 ]]>
2242         </sql-query>
2243
2244         <sql-query name="updateMenuFunctionalAndRoles">
2245                 <![CDATA[
2246                   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
2247                  ;
2248                 ]]>
2249         </sql-query>
2250
2251         <!-- Remove any favorites associated with a menu item that is associated with this app -->
2252         <sql-query name="removeAppFromMenuFavorites">
2253                 <![CDATA[
2254                 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
2255                 ;
2256                 ]]>
2257         </sql-query>
2258
2259         <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2260         <sql-query name="removeAppFromMenuFunctionalRoles">
2261                 <![CDATA[
2262                 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2263                 ;
2264                 ]]>
2265         </sql-query>
2266
2267         <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2268         <sql-query name="removeAppFromEpAppRoleFunction">
2269                 <![CDATA[
2270                 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2271                 ;
2272                 ]]>
2273         </sql-query>
2274
2275         <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2276         <sql-query name="removeAppFromEpAppFunction">
2277                 <![CDATA[
2278                 DELETE FROM ep_app_function WHERE app_id=:app_id
2279                 ;
2280                 ]]>
2281         </sql-query>
2282
2283         <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2284         <sql-query name="removeAppFromFnUserRole">
2285                 <![CDATA[
2286                 DELETE FROM fn_user_role WHERE app_id=:app_id
2287                 ;
2288                 ]]>
2289         </sql-query>
2290
2291         <!-- Remove any widgets that is associated with this app-->
2292         <sql-query name="removeAppFromEpWidgetCatalogRole">
2293                 <![CDATA[
2294                 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2295                 ;
2296                 ]]>
2297         </sql-query>
2298
2299         <!-- Remove any notifications that is associated with this app-->
2300         <sql-query name="removeAppFromEpRoleNotification">
2301                 <![CDATA[
2302                 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2303                 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2304                 ;
2305                 ]]>
2306         </sql-query>
2307
2308         <!-- Remove all records from fn_role that is associated with this app-->
2309         <sql-query name="removeAppFromFnRole">
2310                 <![CDATA[
2311                 DELETE FROM fn_role where app_id=:app_id
2312                 ;
2313                 ]]>
2314         </sql-query>
2315
2316         <!-- Remove app contact us entries that is associated with this app-->
2317         <sql-query name="removeAppFromAppContactUs">
2318                 <![CDATA[
2319                 DELETE FROM fn_app_contact_us where app_id=:app_id
2320                 ;
2321                 ]]>
2322         </sql-query>
2323
2324         <!-- Remove app personalization entries that is associated with this app-->
2325         <sql-query name="removeAppFromEpPersUserAppSel">
2326                 <![CDATA[
2327                 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2328                 ;
2329                 ]]>
2330         </sql-query>
2331
2332         <!-- Remove app personalization sorting entries that is associated with this app-->
2333         <sql-query name="removeAppFromEpPersUserAppManSort">
2334                 <![CDATA[
2335                 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2336                 ;
2337                 ]]>
2338         </sql-query>
2339
2340         <!-- Remove rows from user role request table entries that is associated with this app-->
2341         <sql-query name="removeAppFromEpUserRolesRequest">
2342                 <![CDATA[
2343                 DELETE FROM ep_user_roles_request where app_id=:app_id
2344                 ;
2345                 ]]>
2346         </sql-query>
2347
2348         <!-- Remove rows from web analytics table entries that is associated with this app-->
2349         <sql-query name="removeAppFromEpWebAnalytics">
2350                 <![CDATA[
2351                 DELETE FROM ep_web_analytics_source where app_id=:app_id
2352                 ;
2353                 ]]>
2354         </sql-query>
2355
2356         <!-- Remove row from fn app table -->
2357         <sql-query name="removeAppFromFnApp">
2358                 <![CDATA[
2359                 DELETE FROM fn_app where app_id=:app_id
2360                 ;
2361                 ]]>
2362         </sql-query>
2363
2364
2365         <query name="getBasicauthAccount">
2366       select id,password from BasicAuthCredentials
2367     </query>
2368
2369      <query name="getMicroserviceInfo">
2370       select id,password from MicroserviceData
2371     </query>
2372
2373         <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2374                 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2375                 <![CDATA[
2376                 select * from fn_user_role where user_id= :userId
2377                 and role_id= :roleId
2378                 and app_id= :appId
2379                 ;
2380                 ]]>
2381         </sql-query>
2382
2383         <sql-query name="userAppGlobalRoles">
2384                 <return alias="userAppGlobalRoles"
2385                         class="org.onap.portalapp.portal.domain.EPRole" />
2386        <![CDATA[
2387         select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2388         from fn_user_role a, fn_role fr, fn_user fu
2389         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
2390         ;
2391        ]]>
2392         </sql-query>
2393
2394         <sql-query name="getAllCentralizedAppsRoles">
2395                 <return alias="allCentralizedAppsRoles"
2396                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2397        <![CDATA[
2398         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
2399                 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 ;
2400        ]]>
2401         </sql-query>
2402
2403         <sql-query name="getUserCentralizedAppRoles">
2404                 <return alias="userCentralizedAppRoles"
2405                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2406        <![CDATA[
2407                 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
2408                 where fa.app_id = fur.app_id
2409                 and fr.role_id = fur.role_id
2410                 and fu.user_id = fur.user_id
2411                 and fu.ORG_USER_ID = :orgUserId
2412                 and fa.auth_namespace is not null
2413                 and fr.active_yn = 'Y'
2414                 order by role_id;
2415                 ]]>
2416         </sql-query>
2417
2418 </hibernate-mapping>