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