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 © 2017 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   ECOMP is a trademark and service mark of AT&T Intellectual Property.
  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 ECOMP Portal application. -->
  44 <hibernate-mapping package="org.openecomp.portalapp.portal.domain">
  45
  46         <!-- Widget class mapping details -->
  47         <class name="Widget" table="FN_WIDGET">
  48                 <id name="id" column="WIDGET_ID">
  49                         <generator class="native">
  50                                 <param name="sequence">seq_fn_widget</param>
  51                         </generator>
  52                 </id>
  53                 <property name="width" type="integer">
  54                         <column name="WDG_WIDTH" not-null="true" default="0"></column>
  55                 </property>
  56                 <property name="height" type="integer">
  57                         <column name="WDG_HEIGHT" not-null="true" default="0"></column>
  58                 </property>
  59                 <property name="url" type="string">
  60                         <column name="WDG_URL" not-null="true" default="?"></column>
  61                 </property>
  62                 <property name="name" type="string">
  63                         <column name="WDG_NAME" not-null="true" default="?"></column>
  64                 </property>
  65
  66                 <property name="appId" type="long">
  67                         <column name="APP_ID" not-null="true"></column>
  68                 </property>
  69
  70         </class>
  71
  72         <class name="EPEndpoint" table="EP_ENDPOINTS">
  73                 <id name="id" column="id">
  74                         <generator class="native">
  75                                 <param name="sequence">seq_ep_endpoint</param>
  76                         </generator>
  77                 </id>
  78                 <property name="name" type="string">
  79                         <column name="url" not-null="true"></column>
  80                 </property>
  81         </class>
  82
  83
  84         <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
  85                 <id name="id" column="id">
  86                         <generator class="native">
  87                                 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
  88                         </generator>
  89                 </id>
  90                 <property name="ep_id" type="long">
  91                         <column name="ep_id"></column>
  92                 </property>
  93                 <property name="account_id" type="long">
  94                         <column name="account_id"></column>
  95                 </property>
  96         </class>
  97
  98         <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
  99                 <id name="id" column="id">
 100                         <generator class="native">
 101                                 <param name="sequence">seq_ep_widget_catalog_parameter</param>
 102                         </generator>
 103                 </id>
 104                 <property name="widgetId" type="long">
 105                         <column name="widget_id"></column>
 106                 </property>
 107                 <property name="userId" type="long">
 108                         <column name="user_id" not-null="true"></column>
 109                 </property>
 110                 <property name="paramId" type="long">
 111                         <column name="param_id" not-null="true"></column>
 112                 </property>
 113
 114                 <property name="user_value" type="string">
 115                         <column name="user_value" not-null="true"></column>
 116                 </property>
 117
 118         </class>
 119
 120         <class name="BasicAuthCredentials" table="ep_basic_auth_account">
 121         <id name="id" column="id">
 122             <generator class="native">
 123                                 <param name="sequence">seq_ep_basic_auth_account</param>
 124                         </generator>
 125         </id>
 126                 <property name="applicationName" type="string">
 127                         <column name="ext_app_name" not-null="true" default="?"></column>
 128                 </property>
 129                 <property name="username" type="string">
 130                         <column name="username"></column>
 131                 </property>
 132                 <property name="password" type="string">
 133                         <column name="password"></column>
 134                 </property>
 135                 <property name="isActive" type="string">
 136                         <column name="active_yn" not-null="true" default="Y"></column>
 137                 </property>
 138     </class>
 139
 140         <!-- EPUserNotification class mapping details -->
 141         <class name="EPUserNotification" table="ep_user_notification">
 142                 <id name="id" column="id">
 143                         <generator class="native">
 144                                 <param name="sequence">seq_ep_user_notification</param>
 145                         </generator>
 146                 </id>
 147                 <property name="userId" column="User_ID" />
 148                 <property name="notificationId" column="notification_ID" />
 149                 <property name="viewed" column="is_viewed" />
 150                 <property name="updateTime" column="updated_time" />
 151         </class>
 152
 153
 154         <!-- User class mapping details -->
 155         <class name="EPUser" table="FN_USER">
 156                 <id name="id" column="user_id">
 157                         <generator class="native">
 158                                 <param name="sequence">seq_fn_user</param>
 159                         </generator>
 160                 </id>
 161
 162                 <property name="orgId" column="org_id" />
 163                 <property name="managerId" column="manager_id" />
 164                 <property name="firstName" column="first_name" />
 165                 <property name="middleInitial" column="middle_name" />
 166                 <property name="lastName" column="last_name" />
 167                 <property name="phone" column="phone" />
 168                 <property name="fax" column="fax" />
 169                 <property name="cellular" column="cellular" />
 170                 <property name="email" column="email" />
 171                 <property name="addressId" column="address_id" />
 172                 <property name="alertMethodCd" column="alert_method_cd" />
 173
 174                 <property name="address1" column="address_line_1" />
 175                 <property name="address2" column="address_line_2" />
 176                 <property name="city" column="city" />
 177                 <property name="state" column="state_cd" />
 178                 <property name="zipCode" column="zip_code" />
 179                 <property name="country" column="country_cd" />
 180
 181                 <property name="hrid" column="hrid" />
 182                 <property name="orgUserId" column="org_user_id" />
 183                 <property name="orgCode" column="org_code" />
 184                 <property name="loginId" column="login_id" />
 185                 <property name="loginPwd" column="login_pwd" />
 186                 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
 187
 188                 <property name="locationClli" column="location_clli" />
 189                 <property name="orgManagerUserId" column="org_manager_userid" />
 190                 <property name="company" column="company" />
 191                 <property name="department" column="department" />
 192                 <property name="departmentName" column="department_name" />
 193                 <property name="businessUnit" column="business_unit" />
 194                 <property name="businessUnitName" column="business_unit_name" />
 195                 <property name="jobTitle" column="job_title" />
 196                 <property name="siloStatus" column="silo_status" />
 197                 <property name="financialLocCode" column="fin_loc_code" />
 198
 199                 <property name="active" column="active_yn" type="yes_no" />
 200                 <property name="internal" column="is_internal_yn" type="yes_no" />
 201
 202                 <property name="created" type="timestamp" column="created_date" />
 203                 <property name="modified" type="timestamp" column="modified_date" />
 204
 205                 <property name="createdId" column="created_id" />
 206                 <property name="modifiedId" column="modified_id" />
 207                 <property name="timeZoneId" column="timezone" />
 208
 209                 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
 210                         inverse="true">
 211                         <key column="user_id" />
 212                         <one-to-many class="org.openecomp.portalapp.portal.domain.EPUserApp" />
 213                 </set>
 214
 215         </class>
 216
 217         <class name="MicroserviceData" table="ep_microservice">
 218                 <id name="id" column="id">
 219                         <generator class="native">
 220                                 <param name="sequence">seq_ep_microservice</param>
 221                         </generator>
 222                 </id>
 223
 224                 <property name="name" type="string">
 225                         <column name="name" not-null="true" default="?"></column>
 226                 </property>
 227
 228                 <property name="desc" type="string">
 229                         <column name="description" not-null="true" default="?"></column>
 230                 </property>
 231
 232                 <property name="appId" type="long">
 233                         <column name="appId" not-null="true"></column>
 234                 </property>
 235
 236                 <property name="url" type="string">
 237                         <column name="endpoint_url" not-null="true" default="?"></column>
 238                 </property>
 239
 240                 <property name="securityType" type="string">
 241                         <column name="security_type" not-null="true" default="?"></column>
 242                 </property>
 243
 244
 245                 <property name="username" type="string">
 246                         <column name="username" not-null="true" default="?"></column>
 247                 </property>
 248
 249                 <property name="password" type="string">
 250                         <column name="password" not-null="true" default="?"></column>
 251                 </property>
 252
 253                 <property name="active" type="string">
 254                         <column name="active" not-null="true" default="Y"></column>
 255                 </property>
 256         </class>
 257
 258         <class name="MicroserviceParameter" table="ep_microservice_parameter">
 259                 <id name="id" column="id">
 260                         <generator class="native">
 261                                 <param name="sequence">seq_ep_microservice_parameter</param>
 262                         </generator>
 263                 </id>
 264
 265                 <property name="serviceId" type="long">
 266                         <column name="service_id" not-null="true"></column>
 267                 </property>
 268
 269                 <property name="para_key" type="string">
 270                         <column name="para_key" not-null="true" default="?"></column>
 271                 </property>
 272
 273                 <property name="para_value" type="string">
 274                         <column name="para_value" not-null="true" default="?"></column>
 275                 </property>
 276         </class>
 277
 278         <!-- UserApp class mapping details -->
 279         <class name="EPUserApp" table="fn_user_role">
 280                 <composite-id>
 281                         <key-property name="userId" type="long">
 282                                 <column name="user_id" precision="11" scale="0" />
 283                         </key-property>
 284                         <key-many-to-one name="app"
 285                                 class="org.openecomp.portalapp.portal.domain.EPApp" column="app_id" />
 286                         <key-many-to-one name="role"
 287                                 class="org.openecomp.portalapp.portal.domain.EPRole" column="role_id" />
 288                 </composite-id>
 289                 <property name="priority" type="java.lang.Short">
 290                         <column name="priority" precision="4" scale="0" />
 291                 </property>
 292         </class>
 293
 294         <!-- User App class mapping details -->
 295         <class name="EPApp" table="fn_app">
 296                 <id name="id" column="app_id">
 297                         <generator class="native">
 298                                 <param name="sequence">seq_fn_app</param>
 299                         </generator>
 300                 </id>
 301                 <property name="name" type="string">
 302                         <column name="app_name" not-null="true" default="?"></column>
 303                 </property>
 304                 <property name="imageUrl" column="app_image_url" />
 305                 <property name="description" column="app_description" />
 306                 <property name="notes" column="app_notes" />
 307                 <property name="url" column="app_url" />
 308                 <property name="alternateUrl" column="app_alternate_url" />
 309                 <property name="appRestEndpoint" column="app_rest_endpoint" />
 310                 <property name="mlAppName" type="string">
 311                         <column name="ml_app_name" not-null="true" default="?"></column>
 312                 </property>
 313                 <property name="mlAppAdminId" type="string">
 314                         <column name="ml_app_admin_id" not-null="true" default="?"></column>
 315                 </property>
 316                 <property name="motsId" column="mots_id" />
 317                 <property name="appPassword" type="string">
 318                         <column name="app_password" not-null="true" default="?"></column>
 319                 </property>
 320                 <property name="thumbnail" column="thumbnail" />
 321                 <property name="username" type="string">
 322                         <column name="app_username" not-null="true" default="?"></column>
 323                 </property>
 324                 <property name="open" type="yes_no">
 325                         <column name="open" not-null="true" default="Y"></column>
 326                 </property>
 327                 <property name="enabled" type="yes_no">
 328                         <column name="enabled" not-null="true" default="N"></column>
 329                 </property>
 330                 <property name="uebTopicName" type="string">
 331                         <column name="ueb_topic_name"></column>
 332                 </property>
 333                 <property name="uebKey" type="string">
 334                         <column name="ueb_key"></column>
 335                 </property>
 336                 <property name="uebSecret" type="string">
 337                         <column name="ueb_secret"></column>
 338                 </property>
 339                 <property name="appType" type="integer">
 340                         <column name="app_type"></column>
 341                 </property>
 342                 <property name="centralAuth" type="yes_no">
 343                         <column name="auth_central"></column>
 344                 </property>
 345                 <property name="nameSpace" type="string">
 346                         <column name="auth_namespace"></column>
 347                 </property>
 348                 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
 349
 350         </class>
 351
 352         <!-- EPUserAppsSortPreference class mapping details -->
 353         <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
 354                 <id name="id" column="ID">
 355                         <generator class="native">
 356                                 <param name="sequence">seq_ep_usrappsrtprf</param>
 357                         </generator>
 358                 </id>
 359                 <property name="userId" column="USER_ID" />
 360                 <property name="sortPref" column="SORT_PREF" />
 361         </class>
 362
 363         <!-- EPUserAppsManualSortPreference class mapping details -->
 364         <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
 365                 <id name="id" column="ID">
 366                         <generator class="native">
 367                                 <param name="sequence">seq_ep_usrmanappsrtprf</param>
 368                         </generator>
 369                 </id>
 370                 <property name="userId" column="user_id"
 371                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 372                 <property name="appId" column="app_id"
 373                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 374                 <property name="appManualSortOrder" column="sort_order" type="int" />
 375         </class>
 376
 377         <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
 378                 <id name="id" column="ID">
 379                         <generator class="native">
 380                                 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
 381                         </generator>
 382                 </id>
 383                 <property name="userId" column="user_id" />
 384                 <property name="widgetId" column="widget_id" />
 385                 <property name="widgetRow" column="x" />
 386                 <property name="widgetCol" column="y" />
 387                 <property name="widgetHeight" column="height" />
 388                 <property name="widgetWidth" column="width" />
 389         </class>
 390
 391         <!-- User Role class mapping details -->
 392         <class name="EPRole" table="FN_ROLE" >
 393                 <id name="id" column="role_id">
 394                         <generator class="native">
 395                                 <param name="sequence">seq_fn_role</param>
 396                         </generator>
 397                 </id>
 398
 399                 <property name="name" column="role_name" />
 400                 <property name="priority" column="priority" />
 401                 <property name="active" column="active_yn" type="yes_no" />
 402                 <!-- 2 lines below may be removed! -->
 403                 <property name="appId" column="APP_ID" />
 404                 <property name="appRoleId" column="APP_ROLE_ID" />
 405                 <!-- 2 lines above may be removed -->
 406
 407                 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
 408                         sort="natural">
 409                         <key column="role_id" />
 410                         <many-to-many column="function_cd"
 411                                 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
 412                 </set>
 413
 414                 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 415                         sort="natural">
 416                         <key column="parent_role_id" />
 417                         <many-to-many column="child_role_id"
 418                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 419                 </set>
 420
 421                 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 422                         sort="natural">
 423                         <key column="child_role_id" />
 424                         <many-to-many column="parent_role_id"
 425                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 426                 </set>
 427
 428         </class>
 429
 430         <!-- User App class mapping details -->
 431         <class name="AppContactUs" table="fn_app_contact_us">
 432                 <id name="id" column="app_id">
 433                         <generator class="foreign">
 434                                 <param name="property">app</param>
 435                         </generator>
 436                 </id>
 437                 <property name="url" column="url" />
 438                 <property name="description" column="description" />
 439                 <property name="contactName" column="contact_name" />
 440                 <property name="contactEmail" column="contact_email" />
 441                 <property name="activeYN" column="active_yn" />
 442
 443                 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
 444
 445         </class>
 446
 447         <!-- Personalization of user app selections -->
 448         <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
 449                 <id name="id" column="id">
 450                         <generator class="native">
 451                                 <param name="property">seq_fn_pers_user_app_sel</param>
 452                         </generator>
 453                 </id>
 454                 <property name="userId" column="user_id" />
 455                 <property name="appId" column="app_id" />
 456                 <property name="statusCode" column="status_cd" />
 457         </class>
 458
 459         <!-- EPAppRoleFunction class mapping details -->
 460         <class name="EPAppRoleFunction" table="ep_app_role_function">
 461                 <id name="id" column="id">
 462                         <generator class="native">
 463                                 <param name="property">seq_epp_app_role_func</param>
 464                         </generator>
 465                 </id>
 466                 <property name="roleId" column="role_id"></property>
 467                 <property name="appId" column="app_id"></property>
 468                 <property name="code" column="function_cd"></property>
 469         </class>
 470
 471         <!-- CentralRoleFunction class mapping details -->
 472         <class name="CentralRoleFunction" table="ep_app_function">
 473                 <composite-id>
 474                         <key-property name="appId"
 475                                 column="app_id" />
 476                         <key-property name="code"
 477                                 column="function_cd" />
 478                 </composite-id>
 479                 <property name="name" column="function_name" />
 480         </class>
 481
 482         <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
 483                 <id name="id" column="id">
 484                         <generator class="native">
 485                                 <param name="property">seq_ep_pers_user_widget_sel</param>
 486                         </generator>
 487                 </id>
 488                 <property name="userId" column="user_id" />
 489                 <property name="widgetId" column="widget_id" />
 490                 <property name="statusCode" column="status_cd" />
 491         </class>
 492
 493         <!-- EPUserAppRolesRequest class mapping details -->
 494         <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
 495                 <id name="id" column="req_id">
 496                         <generator class="native">
 497                                 <param name="sequence">seq_ep_user_role_request</param>
 498                         </generator>
 499                 </id>
 500                 <property name="userId" column="user_id" />
 501                 <property name="appId" column="app_id" />
 502                 <property name="createdDate" column="created_date" type="timestamp" />
 503                 <property name="updatedDate" column="updated_date" type="timestamp" />
 504                 <property name="requestStatus" column="request_status" />
 505
 506                 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
 507                         lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
 508                         <key column="req_id" />
 509                         <one-to-many
 510                                 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
 511                 </set>
 512         </class>
 513
 514         <!-- EPUserAppRolesRequestDetail class mapping details -->
 515         <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
 516                 <id name="id" column="id">
 517                         <generator class="native">
 518                                 <param name="sequence">seq_ep_user_role_request_det</param>
 519                         </generator>
 520                 </id>
 521                 <property name="reqRoleId" column="requested_role_id" />
 522                 <property name="reqType" column="request_type" />
 523                 <many-to-one name="epRequestIdData" fetch="select"
 524                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
 525                         <column name="req_id" not-null="true" />
 526                 </many-to-one>
 527         </class>
 528
 529         <!-- show the current user plus related users -->
 530         <sql-query name="relatedUsers">
 531                 <return-scalar column="org_user_id" type="java.lang.String" />
 532                 <![CDATA[
 533                         select t.org_user_id from (
 534                                 select distinct c.org_user_id org_user_id, c.last_login_date from
 535                                         (select c.org_user_id, c.last_login_date from
 536                                                 fn_user_role a,
 537                                                 (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,
 538                                                 fn_user c
 539                                                 where a.ROLE_ID = b.role_id
 540                                                 and a.APP_ID = b.app_id
 541                                                 and a.USER_ID = c.user_id
 542                                         union
 543                                         select org_user_id , last_login_date from fn_user where org_user_id=:userId
 544                                         ) c
 545                                 order by c.last_login_date desc limit 10
 546                         ) t
 547                 ]]>
 548         </sql-query>
 549
 550         <!-- requires values for named parameters :searchQuery and :userId -->
 551         <sql-query name="searchPortal">
 552                 <return alias="searchResult"
 553                         class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
 554                 <![CDATA[
 555                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 556                                 ( (
 557                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
 558                                 from fn_user where
 559                                 upper( :searchQuery ) != ''
 560                                 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
 561                                 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
 562                                 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
 563                                 and  upper(active_yn) = 'Y'
 564                                 )
 565                                 union
 566                                 (
 567                                 select distinct 'Application' CATEGORY, b1.app_name NAME,
 568                                 if (b1.app_type = 2,'false','true') UUID,
 569                                 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
 570                                 from
 571                                 (
 572                                 select c.*
 573                                 from fn_user_role a, fn_user b, fn_app c
 574                                 where a.USER_ID = b.user_id
 575                                 and upper(b.org_user_id) = upper(  :userId )
 576                                 and a.app_id = c.app_id
 577                                 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 578                                 and upper(c.ENABLED) = 'Y'
 579                                 ) a1 right outer join
 580                                 (select * from fn_app where
 581                                 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 582                                 and upper(ENABLED) = 'Y') b1
 583                                 on a1.APP_ID = b1.app_id
 584                                 )
 585                                 union
 586                                 (
 587                                 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
 588                                 if (b1.app_type = 2,'false','true') UUID,
 589                                 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
 590                                 from
 591                                 (select distinct  d.*, e.app_type from fn_user a,
 592                                 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
 593                                 where a.USER_ID = b.user_id
 594                                 and b.role_id = c.role_id
 595                                 and c.menu_id = d.menu_id
 596                                 and upper(a.org_user_id) = upper( :userId )
 597                                 and c.APP_ID = e.app_id
 598                                 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 599                                 and upper(d.active_yn) = 'Y'
 600                                 and upper(e.enabled) = 'Y'
 601                                 ) a1 right outer join
 602                                 (
 603                                 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
 604                                 where active_yn = 'Y'
 605                                 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 606                                 and a.menu_id = b.menu_id
 607                                 and b.app_id = c.app_id
 608                                 and c.enabled = 'Y'
 609                                 ) b1
 610                                 on a1.menu_id = b1.menu_id
 611                                 )
 612                                 union
 613                                 select distinct CATEGORY, NAME, UUID, TARGET
 614                                 from
 615                                 (
 616                                 (
 617                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 618                                 d.widget_id UUID,
 619                                 d.wdg_desc TARGET
 620                                 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
 621                                 where upper(a.org_user_id) = upper( :userId )
 622                                 and a.user_id = b.user_id
 623                                 and b.role_id = c.role_id
 624                                 and c.widget_id = d.widget_id
 625                                 and d.all_user_flag<>'Y'
 626                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 627                                 )
 628                                 union
 629                                 (
 630                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 631                                 d.widget_id UUID,
 632                                 d.wdg_desc TARGET
 633                                 from ep_widget_catalog d
 634                                 where d.all_user_flag='Y'
 635                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 636                                 )
 637                                 union
 638                                 (
 639                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 640                                 d.widget_id UUID,
 641                                 d.wdg_desc TARGET
 642                                 from fn_user a, fn_user_role b, ep_widget_catalog d
 643                                 where upper(a.org_user_id) = upper( :userId )
 644                                 and a.user_id = b.user_id
 645                                 and b.role_id = 1
 646                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 647                                 )
 648                                 ) y
 649                                 ) t,
 650                                 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
 651                                                                         ;
 652
 653                 ]]>
 654         </sql-query>
 655
 656         <!-- requires values for named parameters :searchQuery and :userId -->
 657         <sql-query name="getNotifications">
 658                 <return alias="notificationResult"
 659                         class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
 660                 <![CDATA[
 661                         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
 662                         (
 663
 664                         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
 665                         from
 666                                 (
 667                                 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
 668                                 from
 669                                 (
 670                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 671                                         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
 672                                 from ep_notification a, ep_role_notification b
 673                                 where a.notification_id = b.notification_id
 674                                 and (end_time is null ||  SYSDATE() <= end_time )
 675                                 and (start_time is null ||  SYSDATE() >= start_time)
 676                                 and a.is_for_all_roles = 'N'
 677                                 ) a,
 678                                 (
 679                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 680                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 681                                 where COALESCE(c.app_id,1) = d.app_id
 682                         and a.user_id = b.user_id
 683                                 and a.user_id = :user_id
 684                                 and b.role_id = c.role_id
 685                         and (d.enabled='Y' or d.app_id=1)
 686                                 )b
 687                                 where
 688                                 (
 689                                 a.role_id = b.role_id
 690                                 )
 691                                 union
 692                                 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
 693                                 from
 694                                 (
 695                                 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 696                                         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
 697                                 from ep_notification a, ep_role_notification b
 698                                 where a.notification_id = b.notification_id
 699                                 and (end_time is null ||  SYSDATE() <= end_time )
 700                                 and (start_time is null ||  SYSDATE() >= start_time)
 701                                 and a.is_for_all_roles = 'N'
 702                                 ) a
 703                                 where
 704                                 (
 705                                 a.recv_user_id=:user_id
 706                                 )
 707                                 union
 708                                 (
 709                                 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
 710                                 from ep_notification a
 711                                 where a.notification_id
 712                                 and (end_time is null ||  SYSDATE() <= end_time )
 713                                 and (start_time is null ||  SYSDATE() >= start_time)
 714                                 and a.is_for_all_roles = 'Y'
 715                                 )
 716                                 ) a
 717                                 where
 718                                         active_YN = 'Y'
 719                                 and
 720                                         not exists
 721                                 (
 722                                 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'
 723                                 )
 724                                 order by priority desc, created_date desc,start_Time desc
 725
 726
 727                          ) t,
 728                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 729                         ;
 730                 ]]>
 731
 732         </sql-query>
 733
 734                 <sql-query name="getUsersByOrgIdsNotifications">
 735                 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
 736                 <![CDATA[
 737                         SELECT * from fn_user where org_user_id in (:OrgIds)
 738                 ]]>
 739
 740         </sql-query>
 741
 742
 743         <!-- Gets all Admin notification history; accepts no parameters -->
 744         <sql-query name="getAdminNotificationHistoryVO">
 745                 <return alias="adminNotificationHistoryVOResult"
 746                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 747                 <![CDATA[
 748                 SELECT * from (
 749 SELECT
 750                         n.notification_ID AS notificationId,
 751                         n.is_for_online_users AS isForOnlineUsers,
 752                         n.is_for_all_roles AS isForAllRoles,
 753                         n.active_YN AS activeYn,
 754                         n.msg_header AS msgHeader,
 755                         n.msg_description AS msgDescription,
 756                         n.msg_source AS msgSource,
 757                         n.start_Time AS startTime,
 758                         n.end_time AS endTime,
 759                         n.priority,
 760                         n.creator_ID AS creatorId,
 761                         n.created_date AS createdDate,
 762                         n.notification_hyperlink AS notificationHyperlink,
 763                         u.org_user_id AS loginId
 764                 FROM
 765                         ep_notification n
 766                 LEFT JOIN
 767                         fn_user u on u.user_id = n.creator_id
 768                 WHERE
 769                         n.active_YN='Y' and msg_source='EP'
 770
 771       UNION
 772
 773       SELECT
 774                         n.notification_ID AS notificationId,
 775                         n.is_for_online_users AS isForOnlineUsers,
 776                         n.is_for_all_roles AS isForAllRoles,
 777                         n.active_YN AS activeYn,
 778                         n.msg_header AS msgHeader,
 779                         n.msg_description AS msgDescription,
 780                         n.msg_source AS msgSource,
 781                         n.start_Time AS startTime,
 782                         n.end_time AS endTime,
 783                         n.priority,
 784                         n.creator_ID AS creatorId,
 785                         n.created_date AS createdDate,
 786                         n.notification_hyperlink AS notificationHyperlink,
 787                         u.org_user_id AS loginId
 788                 FROM
 789                         ep_notification n
 790                 LEFT JOIN
 791                         fn_user u on u.user_id = n.creator_id
 792     JOIN
 793                         ep_role_notification r on r.notification_ID=n.notification_ID
 794                 WHERE
 795                         n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
 796       )n order by n.startTime desc
 797                 ]]>
 798         </sql-query>
 799
 800         <sql-query name="getEpNotificationAppRoles">
 801                 <return alias="notificationAppRoles"
 802                         class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
 803                 <!-- This query requires no parameters. -->
 804                 <![CDATA[
 805
 806 select  a.app_id, a.app_name, b.role_id, b.role_name from
 807 (select * from fn_app where app_id = 1) a,
 808 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
 809 union
 810 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
 811 from fn_app, fn_role
 812 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
 813
 814
 815
 816                 ]]>
 817         </sql-query>
 818
 819         <!-- Gets all notification history -->
 820         <sql-query name="getNotificationHistoryVO">
 821                 <return alias="notificationHistoryVOResult"
 822                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 823                 <![CDATA[
 824                 SELECT
 825                         notificationId, isForOnlineUsers, isForAllRoles, msgHeader,     msgDescription,msgSource,
 826                         startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId,     activeYn
 827                 FROM
 828                         (
 829                                         select distinct
 830                                         a.notification_ID AS notificationId,
 831                                         is_for_online_users AS isForOnlineUsers,
 832                                         is_for_all_roles AS isForAllRoles,
 833                                         msg_header AS msgHeader,
 834                                         msg_description AS msgDescription,
 835                                         msg_source AS msgSource,
 836                                         start_Time AS startTime,
 837                                         end_time AS endTime,
 838                                         priority,
 839                                         created_date AS createdDate,
 840                                         creator_ID AS creatorId,
 841                                         notification_hyperlink AS notificationHyperlink,
 842                                         login_id AS loginId,
 843                                         active_YN AS activeYn,
 844                                         if (is_viewed is null, 'N', is_viewed)
 845                         from
 846                         (
 847                                 select
 848                                         user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 849                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 850                                         creator_ID,notification_hyperlink,active_YN
 851                                 from
 852                                 (
 853                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 854                                         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,
 855                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 856
 857                    ELSE NULL
 858               END  AS login_id,b.recv_user_id
 859                                 from ep_notification a, ep_role_notification b,fn_user u
 860                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 861                                 and a.is_for_all_roles = 'N'
 862                                 and (
 863                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 864                                 or
 865                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 866                                 or
 867                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 868                                 or
 869                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 870                                 )
 871                                 ) a,
 872                                 (
 873                                 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
 874                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 875                                 where COALESCE(c.app_id,1) = d.app_id
 876                         and a.user_id = b.user_id
 877                                 and a.user_id = :user_id
 878                                 and b.role_id = c.role_id
 879                         and (d.enabled='Y' or d.app_id=1)
 880                                 ) b
 881                                 where
 882                                 (
 883                                 a.role_id = b.role_id
 884                                 )
 885                                    UNION
 886                         select
 887                                         :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 888                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 889                                         creator_ID,notification_hyperlink,active_YN
 890                                 from
 891                                 (
 892                                         select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
 893                                         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,
 894                                          b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 895
 896                    ELSE NULL
 897               END  AS login_id,b.recv_user_id
 898                                 from ep_notification a, ep_role_notification b,fn_user u
 899                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 900                                 and a.is_for_all_roles = 'N'
 901                                 and (
 902                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 903                                 or
 904                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 905                                 or
 906                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 907                                 or
 908                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 909                                 )
 910                                 ) a
 911                         where
 912                         (
 913                         a.recv_user_id=:user_id
 914                         )
 915                                 union
 916                                 (
 917                                 select
 918                                         :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
 919                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
 920                                         creator_ID, a.notification_hyperlink,a.active_YN
 921                                 from ep_notification a  JOIN fn_user b on b.user_id=a.creator_ID
 922                                 where a.notification_id
 923                                 and a.is_for_all_roles = 'Y'
 924                                 and (
 925                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 926                                 or
 927                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 928                                 or
 929                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 930                                 or
 931                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 932                                 )
 933                                 )
 934                                 ) a left outer join (
 935                                 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
 936                                 ) m
 937                                 on  a.notification_id = m.notification_ID
 938                                 where
 939                                 active_YN = 'Y'
 940
 941                                 order by  start_Time desc,end_time desc
 942                         ) t,
 943                      (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
 944                 ]]>
 945
 946         </sql-query>
 947
 948                 <!-- shows the received recipient to whom the notification is delivered from external system -->
 949         <sql-query name="messageRecipients">
 950                 <return-scalar column="org_user_id" type="java.lang.String" />
 951                 <![CDATA[
 952                         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
 953                 ]]>
 954         </sql-query>
 955
 956
 957         <!-- Gets role details for a specified notification -->
 958         <sql-query name="getNotificationRoles">
 959                 <return alias="notificationRolesResult"
 960                         class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
 961                 <![CDATA[
 962                         SELECT *
 963                         FROM
 964                                 ep_role_notification
 965                         WHERE
 966                                 notification_Id = :notificationId
 967                 ]]>
 968         </sql-query>
 969
 970         <!-- Gets all applications, possibly with contact information -->
 971         <sql-query name="getAppsAndContacts">
 972                 <return alias="appContactUs"
 973                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 974                 <!-- This query requires no parameters. -->
 975                 <![CDATA[
 976                         select
 977                                 a.app_id as appId, a.app_name as appName,
 978                                 c.contact_name as contactName,
 979                                 c.contact_email as contactEmail, c.url, c.description,
 980                                 c.active_yn as activeYN
 981                         from
 982                                 fn_app a
 983                         left join
 984                                 fn_app_contact_us c
 985                                 on a.app_id = c.app_id
 986                         where
 987                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 988                         ;
 989                 ]]>
 990         </sql-query>
 991
 992         <!-- Gets all rows from the app-contact-us table, extended with app information -->
 993         <sql-query name="getAppContactUsItems">
 994                 <return alias="appContactUs"
 995                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 996                 <!-- This query requires no parameters. -->
 997                 <![CDATA[
 998                         select
 999                                 c.app_id as appId, c.contact_name as contactName,
1000                                 c.contact_email as contactEmail, c.url, c.description,
1001                                 c.active_yn as activeYN, a.app_name as appName
1002                         from
1003                                 fn_app_contact_us c
1004                         left join
1005                                 fn_app a
1006                                 on a.app_id = c.app_id
1007                         where
1008                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1009                         ;
1010                 ]]>
1011         </sql-query>
1012
1013         <!-- Gets one row for each app with the category and catenated functions. -->
1014         <sql-query name="getAppCategoryFunctions">
1015                 <return alias="appCategoryFunctions"
1016                         class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1017                 <!-- This query requires no parameters. -->
1018                 <![CDATA[
1019                         SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1020                                 r.category as category, r.app_id as appId, r.app_name as application,
1021                                 group_concat(function_text separator ', ') as functions
1022                         FROM (
1023                                 SELECT
1024                                 app_id, function_text, app_name,
1025                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1026                                 FROM (
1027                                         SELECT
1028                                         j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1029                                         k.text parent_menu, k.parent_menu_id parent_menu_id
1030                                         FROM (
1031                                                 SELECT distinct
1032                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1033                                                 FROM
1034                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1035                                                 WHERE
1036                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1037                                                 ) j,
1038                                         fn_menu_functional k
1039                                         WHERE j.parent_menu_id = k.menu_id
1040                                         ) fn,
1041                                 fn_menu_functional a
1042                                 WHERE fn.parent_menu_id = a.menu_id
1043                         ) r,
1044                         (SELECT @rn /*'*/:=/*'*/ 0) t2
1045                         group by r.category, r.app_id, r.app_name
1046                         order by category, app_name
1047                         ;
1048                 ]]>
1049         </sql-query>
1050
1051         <!-- Gets one row for each function-application-role combination. -->
1052         <sql-query name="getAppAccessFunctionRole">
1053                 <return alias="appAccessFunctionRole"
1054                         class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
1055                 <!-- This query requires one parameters. -->
1056                 <![CDATA[
1057                         select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1058                         (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,
1059                         o.requested_role_id,o.request_type
1060                         from
1061                         (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1062                         from
1063                         (
1064                         select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1065                         from
1066                         FN_ROLE C, FN_APP D
1067                         where COALESCE(c.app_id,1) = d.app_id
1068                         and (d.enabled='Y' or d.app_id=1)
1069                         and c.active_yn = 'Y'
1070                         ) a left outer join
1071                         (
1072                         select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1073                         WHERE
1074                         A.MENU_ID = B.MENU_ID
1075                         and active_yn = 'Y'
1076                         )b
1077                         on a.role_id = b.role_id) t left outer join
1078                         (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1079                         where er.req_id=ed.req_id
1080                         and upper(ed.request_type)='P'
1081                         and er.user_id =:userId) o
1082                         on t.app_id=o.app_id
1083                         and t.role_id = o.requested_role_id
1084                         JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1085                         ;
1086                 ]]>
1087         </sql-query>
1088
1089         <query name="getCommonWidgetItem">
1090                 from CommonWidget where category = :cat order by
1091                 sortOrder, title
1092         </query>
1093
1094         <sql-query name="getGuestLastLogin">
1095                 <return-scalar column="audit_date" type="java.util.Date" />
1096                 <![CDATA[
1097                         select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1098                 ]]>
1099         </sql-query>
1100
1101         <sql-query name="getActiveUsers">
1102                 <return alias="EPUser" class="org.openecomp.portalapp.portal.domain.EPUser" />
1103                 <![CDATA[
1104                 SELECT * FROM FN_USER WHERE active_yn='Y'
1105                 ]]>
1106         </sql-query>
1107
1108         <sql-query name="getAppsAdmins">
1109                 <return alias="adminUserApp" class="org.openecomp.portalapp.portal.domain.AdminUserApp" />
1110         <![CDATA[
1111         SELECT apps.APP_NAME, apps.APP_ID, user.USER_ID, user.FIRST_NAME, user.LAST_NAME, user.org_user_id FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_app apps ON apps.APP_ID = userrole.APP_ID  WHERE user.active_yn='Y' AND userrole.ROLE_ID = :accountAdminRoleId AND (apps.ENABLED = 'Y' OR apps.APP_ID=1)
1112         ]]>
1113         </sql-query>
1114
1115         <sql-query name="getPortalAdmins">
1116                 <return alias="portalAdmin" class="org.openecomp.portalapp.portal.transport.PortalAdmin" />
1117         <![CDATA[
1118                 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
1119         ]]>
1120         </sql-query>
1121
1122         <!-- Gets personalized list of enabled and accessible apps for regular user -->
1123         <sql-query name="getPersUserApps">
1124                 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1125                 <!-- This query requires one parameter: userId (number) -->
1126                 <![CDATA[
1127                     select
1128                                 distinct  -- multiple roles yield multiple rows
1129                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1130                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1131                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1132                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1133                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL
1134                     from FN_APP a
1135                     -- Portal assigns role 999 to app administrator
1136                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1137                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1138                         where a.ENABLED = 'Y'
1139                     and (
1140                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1141                         or
1142                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1143                         )
1144                         order by app_name
1145                     ;
1146                 ]]>
1147         </sql-query>
1148
1149         <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1150                 admin -->
1151         <sql-query name="getPersAdminApps">
1152                 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1153                 <!-- This query requires one parameter: userId (number) -->
1154                 <![CDATA[
1155                     select
1156                                 distinct  -- multiple roles yield multiple rows
1157                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1158                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1159                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1160                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1161                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE      , a.AUTH_CENTRAL ,
1162                                 a.AUTH_NAMESPACE
1163                     from FN_APP a
1164                     -- Portal assigns role 999 to app administrator
1165                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1166                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1167                         where a.ENABLED = 'Y'
1168                     and (
1169                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1170                         or
1171                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1172                                 or
1173                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1174                         )
1175                         order by app_name
1176                     ;
1177                 ]]>
1178         </sql-query>
1179
1180         <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1181         <sql-query name="getPersAdminAppsOrderByName">
1182                 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1183                 <!-- This query requires one parameter: userId (number) -->
1184                 <![CDATA[
1185                  select
1186                                 distinct  -- multiple roles yield multiple rows
1187                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1188                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1189                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1190                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1191                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1192                                 a.AUTH_NAMESPACE
1193                     from FN_APP a
1194                     -- Portal assigns role 999 to app administrator
1195                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1196                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1197                         where a.ENABLED = 'Y'
1198                     and (
1199                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1200                         or
1201                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1202                                 or
1203                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1204                         )
1205                         order by app_name
1206                     ;
1207
1208                 ]]>
1209         </sql-query>
1210
1211         <!--Gets personalized list of enabled and accessible User apps by name -->
1212         <sql-query name="getPersUserAppsOrderByName">
1213                 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1214                 <!-- This query requires one parameter: userId (number) -->
1215                 <![CDATA[
1216                 select
1217                                 distinct  -- multiple roles yield multiple rows
1218                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1219                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1220                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1221                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1222                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1223                                 a.AUTH_NAMESPACE
1224                     from FN_APP a
1225                     -- Portal assigns role 999 to app administrator
1226                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1227                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1228                         where a.ENABLED = 'Y'
1229                     and (
1230                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1231                         or
1232                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1233                         )
1234                         order by app_name
1235                     ;
1236
1237                 ]]>
1238         </sql-query>
1239
1240
1241         <!--Gets personalized list of enabled and accessible Super Admin apps by
1242                 LastUsed -->
1243         <sql-query name="getAdminAppsOrderByLastUsed">
1244                 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1245
1246                 <![CDATA[
1247                     select
1248                         *
1249                         from (select
1250                                 distinct  -- multiple roles yield multiple rows
1251                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1252                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1253                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1254                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1255                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE          , a.AUTH_CENTRAL ,
1256                                 a.AUTH_NAMESPACE
1257                     from FN_APP a
1258                     -- Portal assigns role 999 to app administrator
1259                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1260                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1261                         where a.ENABLED = 'Y'
1262                     and (
1263                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1264                         or
1265                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1266                                 or
1267                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1268                         )
1269                         ) A
1270                 left outer join
1271                         (select  distinct
1272                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1273                         from FN_AUDIT_LOG c
1274                          )B
1275                         on A.app_id = B.Affected_record_id
1276                         order by AUDIT_DATE DESC;
1277                         ]]>
1278         </sql-query>
1279
1280         <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1281         <sql-query name="getUserAppsOrderByLastUsed">
1282                 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1283
1284                 <![CDATA[
1285                         select * from (select
1286                                 distinct  -- multiple roles yield multiple rows
1287                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1288                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1289                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1290                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1291                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1292                                 a.AUTH_NAMESPACE
1293                     from FN_APP a
1294                     -- Portal assigns role 999 to app administrator
1295                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1296                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1297                         where a.ENABLED = 'Y'
1298                     and (
1299                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1300                         or
1301                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1302                         )) A
1303                 left outer join
1304                         (select  distinct
1305                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1306                         from FN_AUDIT_LOG c
1307                          )B
1308                         on A.app_id = B.Affected_record_id
1309                         order by AUDIT_DATE DESC;
1310
1311
1312                 ]]>
1313
1314         </sql-query>
1315
1316         <!--Gets personalized list of enabled and accessible Super Admin apps by
1317                 Most Used -->
1318         <sql-query name="getAdminAppsOrderByMostUsed">
1319                 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1320
1321                 <![CDATA[
1322                         select
1323                         *
1324                         from (select
1325                                 distinct  -- multiple roles yield multiple rows
1326                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1327                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1328                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1329                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1330                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE         , a.AUTH_CENTRAL ,
1331                                 a.AUTH_NAMESPACE
1332                     from FN_APP a
1333                     -- Portal assigns role 999 to app administrator
1334                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1335                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1336                         where a.ENABLED = 'Y'
1337                     and (
1338                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1339                         or
1340                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1341                                 or
1342                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1343                         )
1344                         ) A
1345                         left outer join
1346                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1347                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1348                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1349                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1350                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1351                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1352                          )B
1353                         on A.app_id = B.Affected_record_id
1354                         order by total_visits DESC;
1355                 ]]>
1356
1357         </sql-query>
1358
1359         <!--Gets personalized list of enabled and accessible NON Super Admin User
1360                 apps by Most Used -->
1361         <sql-query name="getUserAppsOrderByMostUsed">
1362                 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1363
1364                 <![CDATA[
1365                         select * from
1366                         (select
1367                                 distinct  -- multiple roles yield multiple rows
1368                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1369                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1370                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1371                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1372                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE             , a.AUTH_CENTRAL ,
1373                                 a.AUTH_NAMESPACE
1374                     from FN_APP a
1375                     -- Portal assigns role 999 to app administrator
1376                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1377                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1378                         where a.ENABLED = 'Y'
1379                     and (
1380                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1381                         or
1382                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1383                         ))A
1384                         left outer join
1385                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1386                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1387                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1388                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1389                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1390                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1391                          )B
1392                         on A.app_id = B.Affected_record_id
1393                         order by total_visits DESC;
1394                 ]]>
1395         </sql-query>
1396
1397         <!--Gets personalized list of enabled and accessible Super Admin apps by
1398                 Manual -->
1399         <sql-query name="getAdminAppsOrderByManual">
1400                 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1401
1402                 <![CDATA[
1403                         select * from
1404                         (select
1405                                 distinct  -- multiple roles yield multiple rows
1406                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1407                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1408                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1409                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1410                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1411                                 a.AUTH_NAMESPACE
1412                     from FN_APP a
1413                     -- Portal assigns role 999 to app administrator
1414                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1415                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1416                         where a.ENABLED = 'Y'
1417                     and (
1418                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1419                         or
1420                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1421                                 or
1422                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1423                         )) A
1424                         left outer join
1425                          (select m.app_id, m.sort_order
1426                         from ep_pers_user_app_man_sort m
1427                         where USER_ID = :userId
1428                         ) B
1429                         on A.APP_ID = B.app_id
1430                         order by sort_order ASC
1431                     ;
1432                 ]]>
1433
1434         </sql-query>
1435
1436         <!--Gets personalized list of enabled and accessible NON Super admin User
1437                 apps by Manual -->
1438         <sql-query name="getUserAppsOrderByManual">
1439                 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1440
1441                 <![CDATA[
1442                                                         select * from   (select
1443                                 distinct  -- multiple roles yield multiple rows
1444                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1445                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1446                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1447                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1448                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE        , a.AUTH_CENTRAL ,
1449                                 a.AUTH_NAMESPACE
1450                     from FN_APP a
1451                     -- Portal assigns role 999 to app administrator
1452                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1453                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1454                         where a.ENABLED = 'Y'
1455                     and (
1456                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1457                         or
1458                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1459                         )) A
1460                         left outer join
1461                          (select m.app_id, m.sort_order
1462                         from ep_pers_user_app_man_sort m
1463                         where USER_ID = :userId
1464                         ) B
1465                         on A.APP_ID = B.app_id
1466                         order by sort_order ASC
1467                     ;
1468                 ]]>
1469         </sql-query>
1470
1471         <!-- Gets regular user's list of enabled apps including accessible and select
1472                 statuses -->
1473         <sql-query name="getUserAppCatalog">
1474                 <return alias="userAppCatalog"
1475                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1476                 <!-- This query requires one parameter: userId (number) -->
1477                 <![CDATA[
1478                                                 select
1479                                 distinct  -- multiple roles yield multiple rows
1480                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1481                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1482                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1483                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1484                             IF(a.open = 'Y', TRUE, FALSE) as open,
1485                 -- ACCESS(-ible) means user has a defined role OR the application is open
1486                                 case
1487                                         -- regular app and user has a role
1488                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1489                     -- open app
1490                     when a.OPEN = 'Y' then TRUE
1491                     else FALSE
1492                 end as 'access' ,
1493                 -- SELECT(-ed) indicates user personalization
1494                 case
1495                                         -- regular app, user has a role, no personalization
1496                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1497                     -- open app and has personalization
1498                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1499                     else FALSE
1500                 end as 'select' ,
1501                 -- PENDING indicates user checked a box
1502                 case
1503                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1504                         else FALSE
1505                 end as 'pending'
1506                     from FN_APP a
1507                     -- Portal assigns role 999 to app administrator
1508             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1509                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1510       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'
1511       where a.ENABLED = 'Y'
1512
1513                         -- Show accessible apps first, then the rest; sort by name within each set.
1514             order by access desc, app_name asc
1515                     ;
1516                 ]]>
1517         </sql-query>
1518
1519         <sql-query name="getMyloginAppDetails">
1520                 <return alias="myloginAppDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1521
1522                 <![CDATA[
1523                 SELECT * FROM fn_app  where ml_app_name =:appName
1524                 ;
1525                 ]]>
1526         </sql-query>
1527
1528                 <sql-query name="getMyAppDetailsByUebKey">
1529                 <return alias="myAppDetailsByUebKey" class="org.openecomp.portalapp.portal.domain.EPApp" />
1530
1531                 <![CDATA[
1532
1533                 SELECT * FROM fn_app  where ueb_key =:appKey
1534                 ;
1535                 ]]>
1536         </sql-query>
1537
1538
1539         <sql-query name="deleteAccountEndpoint">
1540                 <![CDATA[
1541                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1542                 ;
1543                 ]]>
1544         </sql-query>
1545
1546         <sql-query name="deleteAccountEndpointRecord">
1547                 <![CDATA[
1548                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1549                 ;
1550                 ]]>
1551         </sql-query>
1552
1553         <sql-query name="deleteEPEndpoint">
1554                 <![CDATA[
1555                 DELETE FROM ep_endpoints WHERE id =:epId
1556                 ;
1557                 ]]>
1558         </sql-query>
1559
1560         <sql-query name="deleteBasicAuthAccount">
1561                 <![CDATA[
1562                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1563                 ;
1564                 ]]>
1565         </sql-query>
1566
1567         <sql-query name="getAppRoles">
1568                 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1569
1570                 <![CDATA[
1571                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1572                 ;
1573                 ]]>
1574         </sql-query>
1575
1576         <sql-query name="getPortalAppRoles">
1577                 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1578
1579                 <![CDATA[
1580
1581                 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1582                 ;
1583                 ]]>
1584         </sql-query>
1585
1586         <sql-query name="deleteMicroserviceParameter">
1587                 <![CDATA[
1588
1589                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1590                 ;
1591                 ]]>
1592         </sql-query>
1593
1594         <sql-query name="deleteMicroserviceParameterById">
1595                 <![CDATA[
1596
1597                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1598                 ;
1599                 ]]>
1600         </sql-query>
1601
1602         <sql-query name="deleteWidgetCatalogParameter">
1603                 <![CDATA[
1604
1605                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1606                 ;
1607                 ]]>
1608         </sql-query>
1609
1610         <sql-query name="deleteMicroservice">
1611                 <![CDATA[
1612
1613                 DELETE FROM ep_microservice WHERE id =:serviceId
1614                 ;
1615                 ]]>
1616         </sql-query>
1617
1618         <sql-query name="epUserAppId">
1619                 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1620
1621                 <![CDATA[
1622
1623                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1624                 ;
1625                 ]]>
1626         </sql-query>
1627
1628         <sql-query name="userAppsSortPreferenceQuery">
1629                 <return alias="appsSortPreferenceQuery"
1630                         class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1631
1632                 <![CDATA[
1633
1634                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1635
1636                 ]]>
1637         </sql-query>
1638
1639         <sql-query name="userAppsManualSortPrfQuery">
1640                 <return alias="AppsManualSortPrfQuery"
1641                         class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1642
1643                 <![CDATA[
1644
1645                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1646
1647                 ]]>
1648         </sql-query>
1649
1650         <sql-query name="userWidgetManualSortPrfQuery">
1651                 <return alias="widgetManualSortPrfQuery"
1652                         class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1653                 <![CDATA[
1654                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1655                 ]]>
1656         </sql-query>
1657
1658         <sql-query name="appRoles">
1659                 <return alias="rolesForApp"
1660                         class="org.openecomp.portalapp.portal.domain.EPUserAppRoles" />
1661                 <![CDATA[
1662                         SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1663                         ;
1664                 ]]>
1665         </sql-query>
1666
1667         <sql-query name="userAppRolesRequestList">
1668                 <return alias="appRolesRequestList"
1669                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1670                 <![CDATA[
1671                         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'
1672                         ;
1673                 ]]>
1674         </sql-query>
1675
1676         <sql-query name="userAppRolesRequestDetailList">
1677                 <return alias="appRolesRequestDetailList"
1678                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1679                 <![CDATA[
1680
1681                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1682                         ;
1683                 ]]>
1684         </sql-query>
1685
1686         <!-- Gets list of enabled apps including accessible and select statuses -->
1687         <sql-query name="getAdminAppCatalog">
1688                 <return alias="adminAppCatalog"
1689                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1690                 <!-- This query requires one parameter: userId (number) -->
1691                 <![CDATA[
1692                         select
1693                                 distinct  -- multiple roles yield multiple rows
1694                             a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1695                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1696                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1697                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1698                             IF(a.open = 'Y', TRUE, FALSE) as open,
1699                 -- ACCESS(-ible) means user has a defined role OR the application is open
1700                                 case
1701                                         -- regular app and user has a role
1702                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1703                     -- open app
1704                     when a.OPEN = 'Y' then TRUE
1705                     else FALSE
1706                 end as 'access' ,
1707                 -- SELECT(-ed) indicates user personalization
1708                 case
1709                                         -- regular app, user has a role, no personalization
1710                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1711                                         -- regular app, user has role, admin forced a personalization
1712                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1713                                         -- regular app, user has no role, admin forced a personalization
1714                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1715                     -- open app and has personalization
1716                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1717                     else FALSE
1718                 end as 'select' ,
1719                 -- PENDING indicates user checked a box
1720                 case
1721                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1722                         else FALSE
1723                 end as 'pending'
1724                     from FN_APP a
1725                     -- Portal assigns role 999 to app administrator
1726             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1727                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1728                         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'
1729                         where a.ENABLED = 'Y'
1730                         -- Show accessible apps first, then the rest; sort by name within each set.
1731             order by access desc, app_name asc
1732                     ;
1733                 ]]>
1734         </sql-query>
1735
1736         <sql-query name="userAppCatalogRoles">
1737                 <return alias="epUserAppCatalogRoles"
1738                         class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1739                 <![CDATA[
1740         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
1741        from ep_user_roles_request A left join ep_user_roles_request_det B
1742                    on a.req_id = b.req_id
1743                    where  A.user_id=:userid
1744        and A.app_id IN (select app_id from fn_app where app_name=:appName)
1745        and A.request_status ='P'
1746        ;
1747                 ]]>
1748         </sql-query>
1749
1750         <sql-query name="getUserApproles">
1751                 <return alias="businessCardUserApplicationRoles"
1752                         class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1753
1754                 <![CDATA[
1755
1756                 select   fr.role_name, fa.app_name
1757           from
1758         fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1759           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)
1760           ;
1761
1762                 ]]>
1763         </sql-query>
1764
1765     <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1766         <sql-query name="deleteNotificationsFromEpNotificationTable">
1767
1768                 <![CDATA[
1769                 delete from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1770                 ]]>
1771         </sql-query>
1772
1773
1774                 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1775         <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1776
1777                 <![CDATA[
1778         delete from ep_role_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1779                 ]]>
1780         </sql-query>
1781
1782         <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1783         <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1784
1785                 <![CDATA[
1786         delete from ep_user_notification where  notification_id in (select notification_id from ep_notification where  curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1787                 ]]>
1788         </sql-query>
1789
1790         <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1791         <sql-query name="getAppRoleFunctionList">
1792                 <return alias="appRoleFunctionList"
1793                         class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1794                 <![CDATA[
1795
1796                         SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1797                         where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1798                         ;
1799
1800                 ]]>
1801         </sql-query>
1802
1803         <!-- Gets list of all role functions -->
1804         <sql-query name="getAllRoleFunctions">
1805                 <return alias="allRoleFunctions"
1806                         class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1807                 <![CDATA[
1808
1809                         SELECT * from ep_app_function where app_id =:appId
1810                         ;
1811
1812                 ]]>
1813         </sql-query>
1814
1815         <!-- Get ep_app_function records provided by single parameter -->
1816         <sql-query name="getRoleFunction">
1817                 <return alias="RoleFunction"
1818                         class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1819                         <![CDATA[
1820
1821                         SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1822                         ;
1823
1824                         ]]>
1825         </sql-query>
1826
1827         <!-- Gets the current user app roles records and requires two parameters  -->
1828         <sql-query name="getUserAppCurrentRoles">
1829                 <return alias="userAppCurrentRoles"
1830                         class="org.openecomp.portalapp.portal.transport.EPUserAppCurrentRoles" />
1831                         <![CDATA[
1832
1833                         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
1834                         ;
1835                         ]]>
1836         </sql-query>
1837
1838         <!-- Gets the current user app roles along with account administrator and requires two parameters  -->
1839         <sql-query name="getUserAppExistingRoles">
1840                 <return alias="userAppExistingRoles"
1841                         class="org.openecomp.portalapp.portal.transport.EcompUserAppRoles" />
1842                         <![CDATA[
1843                         select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1844                         left outer join fn_role fr on fu.role_id = fr.role_id
1845                         where fu.user_id =:userId and fu.app_id =:appId
1846                         ;
1847                         ]]>
1848         </sql-query>
1849
1850         <!-- Gets the current user app roles records and requires two parameters  -->
1851         <sql-query name="getAllFunctions">
1852                 <return alias="allFunctions"
1853                         class="org.openecomp.portalsdk.core.domain.RoleFunction" />
1854                         <![CDATA[
1855                         select * from fn_function
1856                         ;
1857                         ]]>
1858         </sql-query>
1859
1860         <!-- Gets the  all role functions and requires one parameters -->
1861         <sql-query name="uploadAllRoleFunctions">
1862                 <return alias="allRoleFunctions"
1863                         class="org.openecomp.portalapp.portal.transport.BulkUploadRoleFunction" />
1864                         <![CDATA[
1865                                 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
1866                                 ;
1867                         ]]>
1868         </sql-query>
1869
1870         <sql-query name="getRoletoUpdateAAF">
1871                 <return alias="getRoletoUpdateAAF" class="org.openecomp.portalapp.portal.domain.EPRole" />
1872                         <![CDATA[
1873                                         SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
1874                         ;
1875                         ]]>
1876         </sql-query>
1877
1878         <sql-query name="getBulkUserRoles">
1879                 <return alias="bulkUserRoles" class="org.openecomp.portalapp.portal.transport.BulkUploadUserRoles" />
1880                         <![CDATA[
1881                         select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
1882                         left outer join fn_role fr on fr.role_id = fur.role_id
1883                         left outer join fn_app fa on fa.app_id = fur.app_id
1884                         left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fu.org_user_id != 'su1234'
1885                         ;
1886                         ]]>
1887         </sql-query>
1888
1889         <sql-query name="getCentralizedApps">
1890                 <return alias="centralizedApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1891                         <![CDATA[
1892                         select * from fn_app where auth_central = 'Y' and open = 'N';
1893                         ;
1894                         ]]>
1895         </sql-query>
1896
1897         <sql-query name="getUserRoles">
1898                         <return alias="getUserRolesList" class="org.openecomp.portalapp.portal.domain.UserRole" />
1899
1900                 <![CDATA[
1901
1902         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
1903          ;
1904                 ]]>
1905         </sql-query>
1906                 <sql-query name="getRoleFunctionsOfUser">
1907                         <![CDATA[
1908                         select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
1909                         where fu.role_id = ep.role_id
1910                         and fu.app_id = ep.app_id
1911                         and fu.user_id =:userId and ep.app_id = 1
1912                         and ea.function_cd = ep.function_cd
1913                         ;
1914                         ]]>
1915         </sql-query>
1916
1917         <sql-query name="getUserRolesForLeftMenu">
1918                         <return alias="getUserRolesListForLeftMenu" class="org.openecomp.portalapp.portal.domain.UserRole" />
1919
1920                 <![CDATA[
1921                 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
1922         INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
1923         INNER JOIN fn_app app ON app.app_id= userrole.app_id
1924         INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
1925         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)
1926         ;
1927                 ]]>
1928         </sql-query>
1929
1930         <!-- Gets all functions for an application along with global functions and requires single parameter -->
1931         <sql-query name="getMenuFunctions">
1932                 <![CDATA[
1933                         select f.function_cd from ep_app_function f
1934                         where f.app_id =:appId
1935                         UNION
1936                         select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
1937                         and epa.function_cd = epr.function_cd and fnr.role_name like 'global%'
1938                  ;
1939                 ]]>
1940         </sql-query>
1941
1942         <sql-query name="getRequestIdsForApp">
1943         <return alias="getRequestIdsForApp" class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1944                 <![CDATA[
1945                 select * from ep_user_roles_request where app_id =:app_id
1946                 ;
1947                 ]]>
1948         </sql-query>
1949
1950         <sql-query name="ApplicationUserRoles">
1951                 <return alias="ApplicationUserRoles" class="org.openecomp.portalapp.portal.transport.EcompUserRoles" />
1952                 <![CDATA[
1953                     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,
1954                         fu.active_yn , fr.app_role_id, fr.role_name
1955                         from fn_user fu, fn_role fr, fn_user_role fur
1956                         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'
1957                 union
1958             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, fu.active_yn , fr.role_id, fr.role_name
1959                         from fn_user fu, fn_role fr, fn_user_role fur
1960                         where fu.user_id = fur.user_id and fu.active_yn='Y' and fur.app_id=:appId and fr.role_name like 'global%' and fr.active_yn='Y'
1961             ;
1962                 ]]>
1963         </sql-query>
1964
1965     <query name="getEPUserByOrgUserId">
1966         <![CDATA[
1967                 FROM EPUser WHERE orgUserId = :org_user_id
1968         ]]>
1969     </query>
1970
1971         <query name="getEPUserByLoginId">
1972         <![CDATA[
1973                 FROM EPUser WHERE loginId = :login_id
1974         ]]>
1975     </query>
1976
1977     <query name="getEPUserByLoginIdLoginPwd">
1978         <![CDATA[
1979                 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
1980         ]]>
1981     </query>
1982
1983 </hibernate-mapping>