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