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