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  distinct
1310                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1311                         from FN_AUDIT_LOG c
1312                          )B
1313                         on A.app_id = B.Affected_record_id
1314                         order by AUDIT_DATE DESC;
1315                         ]]>
1316         </sql-query>
1317
1318         <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1319         <sql-query name="getUserAppsOrderByLastUsed">
1320                 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1321                 <![CDATA[
1322                         select * from (select
1323                                 distinct  -- multiple roles yield multiple rows
1324                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1325                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1326                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1327                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1328                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1329                                 a.AUTH_NAMESPACE
1330                     from FN_APP a
1331                     -- Portal assigns role 999 to app administrator
1332                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1333                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1334                         where a.ENABLED = 'Y'
1335                     and (
1336                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1337                         or
1338                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1339                         )union
1340                 select distinct  -- multiple roles yield multiple rows
1341                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1342                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1343                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1344                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1345           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1346           b.AUTH_NAMESPACE
1347           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1348           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
1349
1350                         ) A
1351                 left outer join
1352                         (select  distinct
1353                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1354                         from FN_AUDIT_LOG c
1355                          )B
1356                         on A.app_id = B.Affected_record_id
1357                         order by AUDIT_DATE DESC;
1358
1359
1360                 ]]>
1361
1362         </sql-query>
1363
1364         <!--Gets personalized list of enabled and accessible Super Admin apps by
1365                 Most Used -->
1366         <sql-query name="getAdminAppsOrderByMostUsed">
1367                 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1368                 <![CDATA[
1369                         select
1370                         *
1371                         from (select
1372                                 distinct  -- multiple roles yield multiple rows
1373                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1374                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1375                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1376                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1377                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1378                                 a.AUTH_NAMESPACE
1379                     from FN_APP a
1380                     -- Portal assigns role 999 to app administrator
1381                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1382                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1383                         where a.ENABLED = 'Y'
1384                     and (
1385                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1386                         or
1387                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1388                                 or
1389                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1390                         )union
1391                    select distinct  -- multiple roles yield multiple rows
1392                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1393                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1394                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1395                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1396           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1397           b.AUTH_NAMESPACE
1398           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d , ep_app_function e
1399           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
1400                         ) A
1401                         left outer join
1402                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1403                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1404                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1405                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1406                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1407                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1408                          )B
1409                         on A.app_id = B.Affected_record_id
1410                         order by total_visits DESC;
1411                 ]]>
1412
1413         </sql-query>
1414
1415         <!--Gets personalized list of enabled and accessible NON Super Admin User
1416                 apps by Most Used -->
1417         <sql-query name="getUserAppsOrderByMostUsed">
1418                 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1419                 <![CDATA[
1420                         select * from
1421                         (select
1422                                 distinct  -- multiple roles yield multiple rows
1423                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1424                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1425                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1426                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1427                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE             , a.AUTH_CENTRAL ,
1428                                 a.AUTH_NAMESPACE
1429                     from FN_APP a
1430                     -- Portal assigns role 999 to app administrator
1431                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1432                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1433                         where a.ENABLED = 'Y'
1434                     and (
1435                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1436                         or
1437                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1438                         )union
1439                   select distinct  -- multiple roles yield multiple rows
1440                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1441                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1442                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1443                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1444           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1445           b.AUTH_NAMESPACE
1446           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1447           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
1448                         )A
1449                         left outer join
1450                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1451                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1452                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1453                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1454                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1455                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1456                          )B
1457                         on A.app_id = B.Affected_record_id
1458                         order by total_visits DESC;
1459                 ]]>
1460         </sql-query>
1461
1462         <!--Gets personalized list of enabled and accessible Super Admin apps by
1463                 Manual -->
1464         <sql-query name="getAdminAppsOrderByManual">
1465                 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1466                 <![CDATA[
1467                         select * from
1468                         (select
1469                                 distinct  -- multiple roles yield multiple rows
1470                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1471                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1472                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1473                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1474                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1475                                 a.AUTH_NAMESPACE
1476                     from FN_APP a
1477                     -- Portal assigns role 999 to app administrator
1478                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1479                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1480                         where a.ENABLED = 'Y'
1481                     and (
1482                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1483                         or
1484                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1485                                 or
1486                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1487                         )union
1488                 select distinct  -- multiple roles yield multiple rows
1489                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1490                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1491                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1492                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1493           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1494           b.AUTH_NAMESPACE
1495           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1496           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
1497                         ) A
1498                         left outer join
1499                          (select m.app_id, m.sort_order
1500                         from ep_pers_user_app_man_sort m
1501                         where USER_ID = :userId
1502                         ) B
1503                         on A.APP_ID = B.app_id
1504                         order by sort_order ASC
1505                     ;
1506                 ]]>
1507
1508         </sql-query>
1509
1510         <!--Gets personalized list of enabled and accessible NON Super admin User
1511                 apps by Manual -->
1512         <sql-query name="getUserAppsOrderByManual">
1513                 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1514                 <![CDATA[
1515                                                         select * from   (select
1516                                 distinct  -- multiple roles yield multiple rows
1517                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1518                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1519                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1520                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1521                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1522                                 a.AUTH_NAMESPACE
1523                     from FN_APP a
1524                     -- Portal assigns role 999 to app administrator
1525                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1526                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1527                         where a.ENABLED = 'Y'
1528                     and (
1529                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1530                         or
1531                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1532                         )union
1533                 select distinct  -- multiple roles yield multiple rows
1534                             b.APP_ID                    , b.APP_NAME            , b.APP_IMAGE_URL               ,b.APP_DESCRIPTION ,
1535                         b.APP_NOTES             , b.APP_URL             , b.APP_ALTERNATE_URL   , b.APP_REST_ENDPOINT ,
1536                                 b.ML_APP_NAME           , b.ML_APP_ADMIN_ID , b.MOTS_ID                         , b.APP_PASSWORD ,
1537                         b.THUMBNAIL             , b.APP_USERNAME        , b.OPEN                                , b.ENABLED ,
1538           b.UEB_TOPIC_NAME      , b.UEB_KEY             , b.UEB_SECRET                  , b.APP_TYPE         , b.AUTH_CENTRAL ,
1539           b.AUTH_NAMESPACE
1540           from fn_user_role a, fn_app b, ep_app_role_function c   , fn_role d, ep_app_function e
1541           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
1542                         ) A
1543                         left outer join
1544                          (select m.app_id, m.sort_order
1545                         from ep_pers_user_app_man_sort m
1546                         where USER_ID = :userId
1547                         ) B
1548                         on A.APP_ID = B.app_id
1549                         order by sort_order ASC
1550                     ;
1551                 ]]>
1552         </sql-query>
1553
1554         <!-- Gets regular user's list of enabled apps including accessible and select
1555                 statuses -->
1556         <sql-query name="getUserAppCatalog">
1557                 <return alias="userAppCatalog"
1558                         class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1559                 <!-- This query requires one parameter: userId (number) -->
1560                 <![CDATA[
1561                                                 select
1562                                 distinct  -- multiple roles yield multiple rows
1563                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1564                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1565                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1566                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1567                             IF(a.open = 'Y', TRUE, FALSE) as open,
1568                 -- ACCESS(-ible) means user has a defined role OR the application is open
1569                                 case
1570                                         -- regular app and user has a role
1571                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1572                     -- open app
1573                     when a.OPEN = 'Y' then TRUE
1574                     else FALSE
1575                 end as 'access' ,
1576                 -- SELECT(-ed) indicates user personalization
1577                 case
1578                                         -- regular app, user has a role, no personalization
1579                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1580                     -- open app and has personalization
1581                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1582                     else FALSE
1583                 end as 'select' ,
1584                 -- PENDING indicates user checked a box
1585                 case
1586                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1587                         else FALSE
1588                 end as 'pending'
1589                     from FN_APP a
1590                     -- Portal assigns role 999 to app administrator
1591             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1592                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1593       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'
1594       where a.ENABLED = 'Y'
1595
1596                         -- Show accessible apps first, then the rest; sort by name within each set.
1597             order by access desc, app_name asc
1598                     ;
1599                 ]]>
1600         </sql-query>
1601
1602         <sql-query name="getMyloginAppDetails">
1603                 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1604                 <![CDATA[
1605                 SELECT * FROM fn_app  where ml_app_name =:appName
1606                 ;
1607                 ]]>
1608         </sql-query>
1609
1610         <sql-query name="getAppDetailsByAppName">
1611                 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1612                 <![CDATA[
1613                 SELECT * FROM fn_app  where app_name =:appName
1614                 ;
1615                 ]]>
1616         </sql-query>
1617
1618                 <sql-query name="getMyAppDetailsByUebKey">
1619                 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1620                 <![CDATA[
1621                 SELECT * FROM fn_app  where ueb_key =:appKey
1622                 ;
1623                 ]]>
1624         </sql-query>
1625
1626
1627         <sql-query name="deleteAccountEndpoint">
1628                 <![CDATA[
1629                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1630                 ;
1631                 ]]>
1632         </sql-query>
1633
1634         <sql-query name="deleteAccountEndpointRecord">
1635                 <![CDATA[
1636                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1637                 ;
1638                 ]]>
1639         </sql-query>
1640
1641         <sql-query name="deleteEPEndpoint">
1642                 <![CDATA[
1643                 DELETE FROM ep_endpoints WHERE id =:epId
1644                 ;
1645                 ]]>
1646         </sql-query>
1647
1648         <sql-query name="deleteBasicAuthAccount">
1649                 <![CDATA[
1650                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1651                 ;
1652                 ]]>
1653         </sql-query>
1654
1655         <sql-query name="getAppRoles">
1656                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1657                 <![CDATA[
1658                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1659                 ;
1660                 ]]>
1661         </sql-query>
1662
1663         <sql-query name="getPartnerAppRolesList">
1664                 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1665                 <![CDATA[
1666                 SELECT * FROM fn_role where app_id =:appId
1667                 ;
1668                 ]]>
1669         </sql-query>
1670
1671         <sql-query name="getPortalAppRolesList">
1672                 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1673                 <![CDATA[
1674                 SELECT * FROM fn_role where app_id is null
1675                 ;
1676                 ]]>
1677         </sql-query>
1678
1679         <sql-query name="getPortalAppRoleByRoleId">
1680                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1681                 <![CDATA[
1682                 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1683                 ;
1684                 ]]>
1685         </sql-query>
1686
1687         <sql-query name="getPartnerAppRoleByRoleId">
1688                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1689                 <![CDATA[
1690                 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1691                 ;
1692                 ]]>
1693         </sql-query>
1694
1695         <sql-query name="getPartnerAppRoleById">
1696                 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1697                 <![CDATA[
1698                 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1699                 ;
1700                 ]]>
1701         </sql-query>
1702
1703         <sql-query name="getPortalAppRoles">
1704                 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1705                 <![CDATA[
1706                 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1707                 ;
1708                 ]]>
1709         </sql-query>
1710
1711         <sql-query name="deleteMicroserviceParameter">
1712                 <![CDATA[
1713                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1714                 ;
1715                 ]]>
1716         </sql-query>
1717
1718         <sql-query name="deleteMicroserviceParameterById">
1719                 <![CDATA[
1720                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1721                 ;
1722                 ]]>
1723         </sql-query>
1724
1725         <sql-query name="deleteWidgetCatalogParameter">
1726                 <![CDATA[
1727                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1728                 ;
1729                 ]]>
1730         </sql-query>
1731
1732         <sql-query name="deleteMicroservice">
1733                 <![CDATA[
1734                 DELETE FROM ep_microservice WHERE id =:serviceId
1735                 ;
1736                 ]]>
1737         </sql-query>
1738
1739         <sql-query name="epUserAppId">
1740                 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1741                 <![CDATA[
1742                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1743                 ;
1744                 ]]>
1745         </sql-query>
1746
1747         <sql-query name="userAppsSortPreferenceQuery">
1748                 <return alias="appsSortPreferenceQuery"
1749                         class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1750                 <![CDATA[
1751                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1752
1753                 ]]>
1754         </sql-query>
1755
1756         <sql-query name="userAppsManualSortPrfQuery">
1757                 <return alias="AppsManualSortPrfQuery"
1758                         class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1759                 <![CDATA[
1760                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1761
1762                 ]]>
1763         </sql-query>
1764
1765         <sql-query name="userWidgetManualSortPrfQuery">
1766                 <return alias="widgetManualSortPrfQuery"
1767                         class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1768                 <![CDATA[
1769                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1770                 ]]>
1771         </sql-query>
1772
1773         <sql-query name="appRoles">
1774                 <return alias="rolesForApp"
1775                         class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1776                 <![CDATA[
1777                         SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1778                         ;
1779                 ]]>
1780         </sql-query>
1781
1782         <sql-query name="userAppRolesRequestList">
1783                 <return alias="appRolesRequestList"
1784                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1785                 <![CDATA[
1786                         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'
1787                         ;
1788                 ]]>
1789         </sql-query>
1790
1791         <sql-query name="userAppRolesRequestDetailList">
1792                 <return alias="appRolesRequestDetailList"
1793                         class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1794                 <![CDATA[
1795
1796                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1797                         ;
1798                 ]]>
1799         </sql-query>
1800
1801         <!-- Gets list of enabled apps including accessible and select statuses -->
1802         <sql-query name="getAdminAppCatalog">
1803                 <return alias="adminAppCatalog"
1804                         class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1805                 <!-- This query requires one parameter: userId (number) -->
1806                 <![CDATA[
1807                         select
1808                                 distinct  -- multiple roles yield multiple rows
1809                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1810                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1811                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1812                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1813                             IF(a.open = 'Y', TRUE, FALSE) as open,
1814                 -- ACCESS(-ible) means user has a defined role OR the application is open
1815                                 case
1816                                         -- regular app and user has a role
1817                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1818                     -- open app
1819                     when a.OPEN = 'Y' then TRUE
1820                     else FALSE
1821                 end as 'access' ,
1822                 -- SELECT(-ed) indicates user personalization
1823                 case
1824                                         -- regular app, user has a role, no personalization
1825                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1826                                         -- regular app, user has role, admin forced a personalization
1827                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1828                                         -- regular app, user has no role, admin forced a personalization
1829                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1830                     -- open app and has personalization
1831                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1832                     else FALSE
1833                 end as 'select' ,
1834                 -- PENDING indicates user checked a box
1835                 case
1836                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1837                         else FALSE
1838                 end as 'pending'
1839                     from FN_APP a
1840                     -- Portal assigns role 999 to app administrator
1841             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1842                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1843                         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'
1844                         where a.ENABLED = 'Y'
1845                         -- Show accessible apps first, then the rest; sort by name within each set.
1846             order by access desc, app_name asc
1847                     ;
1848                 ]]>
1849         </sql-query>
1850
1851         <sql-query name="userAppCatalogRoles">
1852                 <return alias="epUserAppCatalogRoles"
1853                         class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1854                 <![CDATA[
1855         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
1856        from ep_user_roles_request A left join ep_user_roles_request_det B
1857                    on a.req_id = b.req_id
1858                    where  A.user_id=:userid
1859        and A.app_id IN (select app_id from fn_app where app_name=:appName)
1860        and A.request_status ='P'
1861        ;
1862                 ]]>
1863         </sql-query>
1864
1865         <sql-query name="getUserApproles">
1866                 <return alias="businessCardUserApplicationRoles"
1867                         class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1868                 <![CDATA[
1869
1870                 select   fr.role_name, fa.app_name
1871           from
1872         fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1873           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)
1874           ;
1875
1876                 ]]>
1877         </sql-query>
1878
1879     <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1880         <sql-query name="deleteNotificationsFromEpNotificationTable">
1881                 <![CDATA[
1882                 delete from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1883                 ]]>
1884         </sql-query>
1885
1886
1887                 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1888         <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1889                 <![CDATA[
1890         delete from ep_role_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1891                 ]]>
1892         </sql-query>
1893
1894         <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1895         <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1896                 <![CDATA[
1897         delete from ep_user_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1898                 ]]>
1899         </sql-query>
1900
1901         <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1902         <sql-query name="getAppRoleFunctionList">
1903                 <return alias="appRoleFunctionList"
1904                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1905                 <![CDATA[
1906
1907                         SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1908                         where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1909                         ;
1910
1911                 ]]>
1912         </sql-query>
1913
1914         <!-- Gets list of all role functions -->
1915         <sql-query name="getAllRoleFunctions">
1916                 <return alias="allRoleFunctions"
1917                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1918                 <![CDATA[
1919
1920                         SELECT * from ep_app_function where app_id =:appId
1921                         ;
1922
1923                 ]]>
1924         </sql-query>
1925
1926         <!-- Get ep_app_function records provided by single parameter -->
1927         <sql-query name="getRoleFunction">
1928                 <return alias="RoleFunction"
1929                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1930                         <![CDATA[
1931
1932                         SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1933                         ;
1934
1935                         ]]>
1936         </sql-query>
1937
1938         <!-- Gets the current user app roles records and requires two parameters  -->
1939         <sql-query name="getUserAppCurrentRoles">
1940                 <return alias="userAppCurrentRoles"
1941                         class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
1942                         <![CDATA[
1943
1944                         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
1945                         ;
1946                         ]]>
1947         </sql-query>
1948
1949         <!-- Gets the current user app roles along with account administrator and requires two parameters  -->
1950         <sql-query name="getUserAppExistingRoles">
1951                 <return alias="userAppExistingRoles"
1952                         class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
1953                         <![CDATA[
1954                         select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1955                         left outer join fn_role fr on fu.role_id = fr.role_id
1956                         where fu.user_id =:userId and fu.app_id =:appId
1957                         ;
1958                         ]]>
1959         </sql-query>
1960
1961         <!-- Gets all functions of portal app -->
1962         <sql-query name="getAllFunctions">
1963                 <return alias="allFunctions"
1964                         class="org.onap.portalsdk.core.domain.RoleFunction" />
1965                         <![CDATA[
1966                         select * from fn_function
1967                         ;
1968                         ]]>
1969         </sql-query>
1970
1971         <!-- Gets the current user app roles records and requires two parameters  -->
1972         <sql-query name="getPartnerAppFunctions">
1973                 <return alias="partnerAppFunctions"
1974                         class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1975                         <![CDATA[
1976                         select * from ep_app_function where app_id =:appId
1977                         ;
1978                         ]]>
1979         </sql-query>
1980
1981         <!-- Gets the  all role functions and requires one parameters -->
1982         <sql-query name="uploadAllRoleFunctions">
1983                 <return alias="allRoleFunctions"
1984                         class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
1985                         <![CDATA[
1986                                 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
1987                                 ;
1988                         ]]>
1989         </sql-query>
1990
1991                 <!-- Gets the  all application role functions from ep_app_function table and requires one parameters -->
1992         <sql-query name="uploadPartnerRoleFunctions">
1993                 <return alias="partnerRoleFunctions"
1994                         class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
1995                         <![CDATA[
1996                                 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
1997                                 ;
1998                         ]]>
1999         </sql-query>
2000
2001         <sql-query name="getRoletoUpdateInExternalAuthSystem">
2002                 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2003                         <![CDATA[
2004                                         SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2005                         ;
2006                         ]]>
2007         </sql-query>
2008
2009                 <!-- Gets all active records from fn_role that is associated with this app-->
2010         <sql-query name="getActiveRolesOfApplication">
2011                 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2012                 <![CDATA[
2013                 select * from fn_role where active_yn = 'Y' and app_id=:appId
2014                 ;
2015                 ]]>
2016         </sql-query>
2017
2018         <sql-query name="getBulkUserRoles">
2019                 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2020                         <![CDATA[
2021                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2022                         left outer join fn_role fr on fr.role_id = fur.role_id
2023                         left outer join fn_app fa on fa.app_id = fur.app_id
2024                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2025                         ;
2026                         ]]>
2027         </sql-query>
2028
2029         <sql-query name="getBulkUsersForSingleRole">
2030                 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2031                         <![CDATA[
2032                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2033                         left outer join fn_role fr on fr.role_id = fur.role_id
2034                         left outer join fn_app fa on fa.app_id = fur.app_id
2035                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2036                         ;
2037                         ]]>
2038         </sql-query>
2039
2040         <sql-query name="getCentralizedApps">
2041                 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2042                         <![CDATA[
2043                         select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2044                         ;
2045                         ]]>
2046         </sql-query>
2047
2048         <sql-query name="getUserRoles">
2049                         <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2050                 <![CDATA[
2051
2052         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
2053          ;
2054                 ]]>
2055         </sql-query>
2056                 <sql-query name="getRoleFunctionsOfUser">
2057                         <![CDATA[
2058                         select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2059                         where fu.role_id = ep.role_id
2060                         and fu.app_id = ep.app_id
2061                         and fu.user_id =:userId and ep.app_id = 1
2062                         and ea.function_cd = ep.function_cd
2063                     union
2064             select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2065                         where role_id = 999
2066                         and app_r_f.function_cd = a_f.function_cd
2067                         and exists
2068                         (
2069                         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
2070                         and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2071                         );
2072                         ]]>
2073         </sql-query>
2074
2075         <sql-query name="getUserRolesForLeftMenu">
2076                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2077
2078                 <![CDATA[
2079                 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
2080         INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2081         INNER JOIN fn_app app ON app.app_id= userrole.app_id
2082         INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2083         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)
2084         ;
2085                 ]]>
2086         </sql-query>
2087
2088         <sql-query name="getRolesForLeftMenu">
2089                         <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2090
2091                 <![CDATA[
2092                 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
2093         INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2094         INNER JOIN fn_app app ON app.app_id= userrole.app_id
2095         INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2096         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)
2097         ;
2098                 ]]>
2099         </sql-query>
2100
2101         <!-- Gets all functions for an application along with global functions and requires single parameter -->
2102         <sql-query name="getMenuFunctions">
2103                 <![CDATA[
2104                         select f.function_cd from ep_app_function f
2105                         where f.app_id =:appId
2106                         UNION
2107                         select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2108                         and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2109                  ;
2110                 ]]>
2111         </sql-query>
2112
2113         <sql-query name="getRequestIdsForApp">
2114         <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2115                 <![CDATA[
2116                 select * from ep_user_roles_request where app_id =:app_id
2117                 ;
2118                 ]]>
2119         </sql-query>
2120
2121         <sql-query name="ApplicationUserRoles">
2122                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2123                 <![CDATA[
2124                    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,
2125            fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2126            from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2127            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
2128            union
2129            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,
2130            fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2131            from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2132            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'
2133            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';
2134                 ]]>
2135         </sql-query>
2136
2137         <sql-query name="getCurrentAppRoleFunctions">
2138                 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2139                 <![CDATA[
2140                 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
2141                 and ef.function_cd =:functionCd and epr.app_id =:appId
2142                 ;
2143                 ]]>
2144         </sql-query>
2145
2146         <sql-query name="deleteUserWidgetPlacement">
2147                 <![CDATA[
2148                 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2149                 ;
2150                 ]]>
2151         </sql-query>
2152
2153         <sql-query name="getCentralizedAppsOfUser">
2154                 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2155                 <![CDATA[
2156
2157                 select distinct fa.app_id, fa.app_name
2158                 from  fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2159                 Where  fu.user_id =  fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2160                 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)
2161                 ;
2162
2163                 ]]>
2164         </sql-query>
2165
2166     <query name="getEPUserByOrgUserId">
2167         <![CDATA[
2168                 FROM EPUser WHERE orgUserId = :org_user_id
2169         ]]>
2170     </query>
2171
2172         <query name="getEPUserByLoginId">
2173         <![CDATA[
2174                 FROM EPUser WHERE loginId = :login_id
2175         ]]>
2176     </query>
2177
2178     <query name="getEPUserByLoginIdLoginPwd">
2179         <![CDATA[
2180                 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2181         ]]>
2182     </query>
2183
2184     <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2185                 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2186                 <![CDATA[
2187                 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
2188                 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2189                 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
2190                 ;
2191                 ]]>
2192         </sql-query>
2193
2194      <sql-query name="getGlobalRolesOfPortal">
2195                 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2196                 <![CDATA[
2197                 select * from fn_role where role_name  like 'global_%' and app_id is null and active_yn='Y'
2198                 ;
2199                 ]]>
2200         </sql-query>
2201
2202         <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2203                 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2204                 <![CDATA[
2205                 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2206                 ;
2207                 ]]>
2208         </sql-query>
2209
2210         <sql-query name="getAppFunctionOnCodeAndAppId">
2211                 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2212                 <![CDATA[
2213                 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2214                 ;
2215                 ]]>
2216         </sql-query>
2217
2218         <sql-query name="getGlobalRoleForRequestedApp">
2219                 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2220                 <![CDATA[
2221                 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
2222         from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2223         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
2224         ;
2225                 ]]>
2226         </sql-query>
2227
2228         <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2229                 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2230                 <![CDATA[
2231                 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
2232                 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2233                 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
2234                 ;
2235                 ]]>
2236         </sql-query>
2237
2238         <sql-query name="updateMenuFunctionalAndRoles">
2239                 <![CDATA[
2240                   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
2241                  ;
2242                 ]]>
2243         </sql-query>
2244
2245         <!-- Remove any favorites associated with a menu item that is associated with this app -->
2246         <sql-query name="removeAppFromMenuFavorites">
2247                 <![CDATA[
2248                 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
2249                 ;
2250                 ]]>
2251         </sql-query>
2252
2253         <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2254         <sql-query name="removeAppFromMenuFunctionalRoles">
2255                 <![CDATA[
2256                 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2257                 ;
2258                 ]]>
2259         </sql-query>
2260
2261         <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2262         <sql-query name="removeAppFromEpAppRoleFunction">
2263                 <![CDATA[
2264                 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2265                 ;
2266                 ]]>
2267         </sql-query>
2268
2269         <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2270         <sql-query name="removeAppFromEpAppFunction">
2271                 <![CDATA[
2272                 DELETE FROM ep_app_function WHERE app_id=:app_id
2273                 ;
2274                 ]]>
2275         </sql-query>
2276
2277         <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2278         <sql-query name="removeAppFromFnUserRole">
2279                 <![CDATA[
2280                 DELETE FROM fn_user_role WHERE app_id=:app_id
2281                 ;
2282                 ]]>
2283         </sql-query>
2284
2285         <!-- Remove any widgets that is associated with this app-->
2286         <sql-query name="removeAppFromEpWidgetCatalogRole">
2287                 <![CDATA[
2288                 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2289                 ;
2290                 ]]>
2291         </sql-query>
2292
2293         <!-- Remove any notifications that is associated with this app-->
2294         <sql-query name="removeAppFromEpRoleNotification">
2295                 <![CDATA[
2296                 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2297                 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2298                 ;
2299                 ]]>
2300         </sql-query>
2301
2302         <!-- Remove all records from fn_role that is associated with this app-->
2303         <sql-query name="removeAppFromFnRole">
2304                 <![CDATA[
2305                 DELETE FROM fn_role where app_id=:app_id
2306                 ;
2307                 ]]>
2308         </sql-query>
2309
2310         <!-- Remove app contact us entries that is associated with this app-->
2311         <sql-query name="removeAppFromAppContactUs">
2312                 <![CDATA[
2313                 DELETE FROM fn_app_contact_us where app_id=:app_id
2314                 ;
2315                 ]]>
2316         </sql-query>
2317
2318         <!-- Remove app personalization entries that is associated with this app-->
2319         <sql-query name="removeAppFromEpPersUserAppSel">
2320                 <![CDATA[
2321                 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2322                 ;
2323                 ]]>
2324         </sql-query>
2325
2326         <!-- Remove app personalization sorting entries that is associated with this app-->
2327         <sql-query name="removeAppFromEpPersUserAppManSort">
2328                 <![CDATA[
2329                 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2330                 ;
2331                 ]]>
2332         </sql-query>
2333
2334         <!-- Remove rows from user role request table entries that is associated with this app-->
2335         <sql-query name="removeAppFromEpUserRolesRequest">
2336                 <![CDATA[
2337                 DELETE FROM ep_user_roles_request where app_id=:app_id
2338                 ;
2339                 ]]>
2340         </sql-query>
2341
2342         <!-- Remove rows from web analytics table entries that is associated with this app-->
2343         <sql-query name="removeAppFromEpWebAnalytics">
2344                 <![CDATA[
2345                 DELETE FROM ep_web_analytics_source where app_id=:app_id
2346                 ;
2347                 ]]>
2348         </sql-query>
2349
2350         <!-- Remove row from fn app table -->
2351         <sql-query name="removeAppFromFnApp">
2352                 <![CDATA[
2353                 DELETE FROM fn_app where app_id=:app_id
2354                 ;
2355                 ]]>
2356         </sql-query>
2357
2358
2359         <query name="getBasicauthAccount">
2360       select id,password from BasicAuthCredentials
2361     </query>
2362
2363      <query name="getMicroserviceInfo">
2364       select id,password from MicroserviceData
2365     </query>
2366
2367         <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2368                 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2369                 <![CDATA[
2370                 select * from fn_user_role where user_id= :userId
2371                 and role_id= :roleId
2372                 and app_id= :appId
2373                 ;
2374                 ]]>
2375         </sql-query>
2376
2377         <sql-query name="userAppGlobalRoles">
2378                 <return alias="userAppGlobalRoles"
2379                         class="org.onap.portalapp.portal.domain.EPRole" />
2380        <![CDATA[
2381         select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2382         from fn_user_role a, fn_role fr, fn_user fu
2383         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
2384         ;
2385        ]]>
2386         </sql-query>
2387
2388         <sql-query name="getAllCentralizedAppsRoles">
2389                 <return alias="allCentralizedAppsRoles"
2390                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2391        <![CDATA[
2392         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
2393                 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 ;
2394        ]]>
2395         </sql-query>
2396
2397         <sql-query name="getUserCentralizedAppRoles">
2398                 <return alias="userCentralizedAppRoles"
2399                         class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2400        <![CDATA[
2401                 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
2402                 where fa.app_id = fur.app_id
2403                 and fr.role_id = fur.role_id
2404                 and fu.user_id = fur.user_id
2405                 and fu.ORG_USER_ID = :orgUserId
2406                 and fa.auth_namespace is not null
2407                 and fr.active_yn = 'Y'
2408                 order by role_id;
2409                 ]]>
2410         </sql-query>
2411
2412 </hibernate-mapping>