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