ecomp-portal-BE-common/src/main/webapp/WEB-INF/fusion/orm/EP.hbm.xml

   1 <?xml version="1.0"?>
   2 <!--
   3   ================================================================================
   4   ECOMP Portal
   5   ================================================================================
   6   Copyright (C) 2017 AT&T Intellectual Property
   7   ================================================================================
   8   Licensed under the Apache License, Version 2.0 (the "License");
   9   you may not use this file except in compliance with the License.
  10   You may obtain a copy of the License at
  11
  12        http://www.apache.org/licenses/LICENSE-2.0
  13
  14   Unless required by applicable law or agreed to in writing, software
  15   distributed under the License is distributed on an "AS IS" BASIS,
  16   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17   See the License for the specific language governing permissions and
  18   limitations under the License.
  19   ================================================================================
  20   -->
  21 <!DOCTYPE hibernate-mapping PUBLIC
  22         "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  23         "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
  24
  25 <!-- Publishes mappings and queries specific to the ECOMP Portal application. -->
  26 <hibernate-mapping package="org.openecomp.portalapp.portal.domain">
  27
  28         <!-- Widget class mapping details -->
  29         <class name="Widget" table="FN_WIDGET">
  30                 <id name="id" column="WIDGET_ID">
  31                         <generator class="native">
  32                                 <param name="sequence">seq_fn_widget</param>
  33                         </generator>
  34                 </id>
  35                 <property name="width" type="integer">
  36                         <column name="WDG_WIDTH" not-null="true" default="0"></column>
  37                 </property>
  38                 <property name="height" type="integer">
  39                         <column name="WDG_HEIGHT" not-null="true" default="0"></column>
  40                 </property>
  41                 <property name="url" type="string">
  42                         <column name="WDG_URL" not-null="true" default="?"></column>
  43                 </property>
  44                 <property name="name" type="string">
  45                         <column name="WDG_NAME" not-null="true" default="?"></column>
  46                 </property>
  47
  48                 <property name="appId" type="long">
  49                         <column name="APP_ID" not-null="true"></column>
  50                 </property>
  51
  52         </class>
  53
  54         <class name="EPEndpoint" table="EP_ENDPOINTS">
  55                 <id name="id" column="id">
  56                         <generator class="native">
  57                                 <param name="sequence">seq_ep_endpoint</param>
  58                         </generator>
  59                 </id>
  60                 <property name="name" type="string">
  61                         <column name="url" not-null="true"></column>
  62                 </property>
  63         </class>
  64
  65
  66         <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
  67                 <id name="id" column="id">
  68                         <generator class="native">
  69                                 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
  70                         </generator>
  71                 </id>
  72                 <property name="ep_id" type="long">
  73                         <column name="ep_id"></column>
  74                 </property>
  75                 <property name="account_id" type="long">
  76                         <column name="account_id"></column>
  77                 </property>
  78         </class>
  79
  80         <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
  81                 <id name="id" column="id">
  82                         <generator class="native">
  83                                 <param name="sequence">seq_ep_widget_catalog_parameter</param>
  84                         </generator>
  85                 </id>
  86                 <property name="widgetId" type="long">
  87                         <column name="widget_id"></column>
  88                 </property>
  89                 <property name="userId" type="long">
  90                         <column name="user_id" not-null="true"></column>
  91                 </property>
  92                 <property name="paramId" type="long">
  93                         <column name="param_id" not-null="true"></column>
  94                 </property>
  95
  96                 <property name="user_value" type="string">
  97                         <column name="user_value" not-null="true"></column>
  98                 </property>
  99
 100         </class>
 101
 102         <class name="BasicAuthCredentials" table="ep_basic_auth_account">
 103         <id name="id" column="id">
 104             <generator class="native">
 105                                 <param name="sequence">seq_ep_basic_auth_account</param>
 106                         </generator>
 107         </id>
 108                 <property name="applicationName" type="string">
 109                         <column name="ext_app_name" not-null="true" default="?"></column>
 110                 </property>
 111                 <property name="username" type="string">
 112                         <column name="username"></column>
 113                 </property>
 114                 <property name="password" type="string">
 115                         <column name="password"></column>
 116                 </property>
 117                 <property name="isActive" type="string">
 118                         <column name="active_yn" not-null="true" default="Y"></column>
 119                 </property>
 120     </class>
 121
 122         <!-- EPUserNotification class mapping details -->
 123         <class name="EPUserNotification" table="ep_user_notification">
 124                 <id name="id" column="id">
 125                         <generator class="native">
 126                                 <param name="sequence">seq_ep_user_notification</param>
 127                         </generator>
 128                 </id>
 129                 <property name="userId" column="User_ID" />
 130                 <property name="notificationId" column="notification_ID" />
 131                 <property name="viewed" column="is_viewed" />
 132                 <property name="updateTime" column="updated_time" />
 133         </class>
 134
 135
 136         <!-- User class mapping details -->
 137         <class name="EPUser" table="FN_USER">
 138                 <id name="id" column="user_id">
 139                         <generator class="native">
 140                                 <param name="sequence">seq_fn_user</param>
 141                         </generator>
 142                 </id>
 143
 144                 <property name="orgId" column="org_id" />
 145                 <property name="managerId" column="manager_id" />
 146                 <property name="firstName" column="first_name" />
 147                 <property name="middleInitial" column="middle_name" />
 148                 <property name="lastName" column="last_name" />
 149                 <property name="phone" column="phone" />
 150                 <property name="fax" column="fax" />
 151                 <property name="cellular" column="cellular" />
 152                 <property name="email" column="email" />
 153                 <property name="addressId" column="address_id" />
 154                 <property name="alertMethodCd" column="alert_method_cd" />
 155
 156                 <property name="address1" column="address_line_1" />
 157                 <property name="address2" column="address_line_2" />
 158                 <property name="city" column="city" />
 159                 <property name="state" column="state_cd" />
 160                 <property name="zipCode" column="zip_code" />
 161                 <property name="country" column="country_cd" />
 162
 163                 <property name="hrid" column="hrid" />
 164                 <property name="orgUserId" column="org_user_id" />
 165                 <property name="orgCode" column="org_code" />
 166                 <property name="loginId" column="login_id" />
 167                 <property name="loginPwd" column="login_pwd" />
 168                 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
 169
 170                 <property name="locationClli" column="location_clli" />
 171                 <property name="orgManagerUserId" column="org_manager_userid" />
 172                 <property name="company" column="company" />
 173                 <property name="department" column="department" />
 174                 <property name="departmentName" column="department_name" />
 175                 <property name="businessUnit" column="business_unit" />
 176                 <property name="businessUnitName" column="business_unit_name" />
 177                 <property name="jobTitle" column="job_title" />
 178                 <property name="siloStatus" column="silo_status" />
 179                 <property name="financialLocCode" column="fin_loc_code" />
 180
 181                 <property name="active" column="active_yn" type="yes_no" />
 182                 <property name="internal" column="is_internal_yn" type="yes_no" />
 183
 184                 <property name="created" type="timestamp" column="created_date" />
 185                 <property name="modified" type="timestamp" column="modified_date" />
 186
 187                 <property name="createdId" column="created_id" />
 188                 <property name="modifiedId" column="modified_id" />
 189                 <property name="timeZoneId" column="timezone" />
 190
 191                 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
 192                         inverse="true">
 193                         <key column="user_id" />
 194                         <one-to-many class="org.openecomp.portalapp.portal.domain.EPUserApp" />
 195                 </set>
 196
 197         </class>
 198
 199         <class name="MicroserviceData" table="ep_microservice">
 200                 <id name="id" column="id">
 201                         <generator class="native">
 202                                 <param name="sequence">seq_ep_microservice</param>
 203                         </generator>
 204                 </id>
 205
 206                 <property name="name" type="string">
 207                         <column name="name" not-null="true" default="?"></column>
 208                 </property>
 209
 210                 <property name="desc" type="string">
 211                         <column name="description" not-null="true" default="?"></column>
 212                 </property>
 213
 214                 <property name="appId" type="long">
 215                         <column name="appId" not-null="true"></column>
 216                 </property>
 217
 218                 <property name="url" type="string">
 219                         <column name="endpoint_url" not-null="true" default="?"></column>
 220                 </property>
 221
 222                 <property name="securityType" type="string">
 223                         <column name="security_type" not-null="true" default="?"></column>
 224                 </property>
 225
 226
 227                 <property name="username" type="string">
 228                         <column name="username" not-null="true" default="?"></column>
 229                 </property>
 230
 231                 <property name="password" type="string">
 232                         <column name="password" not-null="true" default="?"></column>
 233                 </property>
 234
 235                 <property name="active" type="string">
 236                         <column name="active" not-null="true" default="Y"></column>
 237                 </property>
 238         </class>
 239
 240         <class name="MicroserviceParameter" table="ep_microservice_parameter">
 241                 <id name="id" column="id">
 242                         <generator class="native">
 243                                 <param name="sequence">seq_ep_microservice_parameter</param>
 244                         </generator>
 245                 </id>
 246
 247                 <property name="serviceId" type="long">
 248                         <column name="service_id" not-null="true"></column>
 249                 </property>
 250
 251                 <property name="para_key" type="string">
 252                         <column name="para_key" not-null="true" default="?"></column>
 253                 </property>
 254
 255                 <property name="para_value" type="string">
 256                         <column name="para_value" not-null="true" default="?"></column>
 257                 </property>
 258         </class>
 259
 260         <!-- UserApp class mapping details -->
 261         <class name="EPUserApp" table="fn_user_role">
 262                 <composite-id>
 263                         <key-property name="userId" type="long">
 264                                 <column name="user_id" precision="11" scale="0" />
 265                         </key-property>
 266                         <key-many-to-one name="app"
 267                                 class="org.openecomp.portalapp.portal.domain.EPApp" column="app_id" />
 268                         <key-many-to-one name="role"
 269                                 class="org.openecomp.portalapp.portal.domain.EPRole" column="role_id" />
 270                 </composite-id>
 271                 <property name="priority" type="java.lang.Short">
 272                         <column name="priority" precision="4" scale="0" />
 273                 </property>
 274         </class>
 275
 276         <!-- User App class mapping details -->
 277         <class name="EPApp" table="fn_app">
 278                 <id name="id" column="app_id">
 279                         <generator class="native">
 280                                 <param name="sequence">seq_fn_app</param>
 281                         </generator>
 282                 </id>
 283                 <property name="name" type="string">
 284                         <column name="app_name" not-null="true" default="?"></column>
 285                 </property>
 286                 <property name="imageUrl" column="app_image_url" />
 287                 <property name="description" column="app_description" />
 288                 <property name="notes" column="app_notes" />
 289                 <property name="url" column="app_url" />
 290                 <property name="alternateUrl" column="app_alternate_url" />
 291                 <property name="appRestEndpoint" column="app_rest_endpoint" />
 292                 <property name="mlAppName" type="string">
 293                         <column name="ml_app_name" not-null="true" default="?"></column>
 294                 </property>
 295                 <property name="mlAppAdminId" type="string">
 296                         <column name="ml_app_admin_id" not-null="true" default="?"></column>
 297                 </property>
 298                 <property name="motsId" column="mots_id" />
 299                 <property name="appPassword" type="string">
 300                         <column name="app_password" not-null="true" default="?"></column>
 301                 </property>
 302                 <property name="thumbnail" column="thumbnail" />
 303                 <property name="username" type="string">
 304                         <column name="app_username" not-null="true" default="?"></column>
 305                 </property>
 306                 <property name="open" type="yes_no">
 307                         <column name="open" not-null="true" default="Y"></column>
 308                 </property>
 309                 <property name="enabled" type="yes_no">
 310                         <column name="enabled" not-null="true" default="N"></column>
 311                 </property>
 312                 <property name="uebTopicName" type="string">
 313                         <column name="ueb_topic_name"></column>
 314                 </property>
 315                 <property name="uebKey" type="string">
 316                         <column name="ueb_key"></column>
 317                 </property>
 318                 <property name="uebSecret" type="string">
 319                         <column name="ueb_secret"></column>
 320                 </property>
 321                 <property name="appType" type="integer">
 322                         <column name="app_type"></column>
 323                 </property>
 324
 325                 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
 326
 327         </class>
 328
 329         <!-- EPUserAppsSortPreference class mapping details -->
 330         <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
 331                 <id name="id" column="ID">
 332                         <generator class="native">
 333                                 <param name="sequence">seq_ep_usrappsrtprf</param>
 334                         </generator>
 335                 </id>
 336                 <property name="userId" column="USER_ID" />
 337                 <property name="sortPref" column="SORT_PREF" />
 338         </class>
 339
 340         <!-- EPUserAppsManualSortPreference class mapping details -->
 341         <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
 342                 <id name="id" column="ID">
 343                         <generator class="native">
 344                                 <param name="sequence">seq_ep_usrmanappsrtprf</param>
 345                         </generator>
 346                 </id>
 347                 <property name="userId" column="user_id"
 348                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 349                 <property name="appId" column="app_id"
 350                         unique-key="uk_1_ep_pers_user_app_man_sort" />
 351                 <property name="appManualSortOrder" column="sort_order" type="int" />
 352         </class>
 353
 354         <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
 355                 <id name="id" column="ID">
 356                         <generator class="native">
 357                                 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
 358                         </generator>
 359                 </id>
 360                 <property name="userId" column="user_id" />
 361                 <property name="widgetId" column="widget_id" />
 362                 <property name="widgetRow" column="x" />
 363                 <property name="widgetCol" column="y" />
 364                 <property name="widgetHeight" column="height" />
 365                 <property name="widgetWidth" column="width" />
 366         </class>
 367
 368         <!-- User Role class mapping details -->
 369         <class name="EPRole" table="FN_ROLE">
 370                 <id name="id" column="role_id">
 371                         <generator class="native">
 372                                 <param name="sequence">seq_fn_role</param>
 373                         </generator>
 374                 </id>
 375
 376                 <property name="name" column="role_name" />
 377                 <property name="priority" column="priority" />
 378                 <property name="active" column="active_yn" type="yes_no" />
 379                 <!-- 2 lines below may be removed! -->
 380                 <property name="appId" column="APP_ID" />
 381                 <property name="appRoleId" column="APP_ROLE_ID" />
 382                 <!-- 2 lines above may be removed -->
 383
 384                 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
 385                         sort="natural">
 386                         <key column="role_id" />
 387                         <many-to-many column="function_cd"
 388                                 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
 389                 </set>
 390
 391                 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 392                         sort="natural">
 393                         <key column="parent_role_id" />
 394                         <many-to-many column="child_role_id"
 395                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 396                 </set>
 397
 398                 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
 399                         sort="natural">
 400                         <key column="child_role_id" />
 401                         <many-to-many column="parent_role_id"
 402                                 class="org.openecomp.portalapp.portal.domain.EPRole" />
 403                 </set>
 404
 405         </class>
 406
 407         <!-- User App class mapping details -->
 408         <class name="AppContactUs" table="fn_app_contact_us">
 409                 <id name="id" column="app_id">
 410                         <generator class="foreign">
 411                                 <param name="property">app</param>
 412                         </generator>
 413                 </id>
 414                 <property name="url" column="url" />
 415                 <property name="description" column="description" />
 416                 <property name="contactName" column="contact_name" />
 417                 <property name="contactEmail" column="contact_email" />
 418                 <property name="activeYN" column="active_yn" />
 419
 420                 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
 421
 422         </class>
 423
 424         <!-- Personalization of user app selections -->
 425         <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
 426                 <id name="id" column="id">
 427                         <generator class="native">
 428                                 <param name="property">seq_fn_pers_user_app_sel</param>
 429                         </generator>
 430                 </id>
 431                 <property name="userId" column="user_id" />
 432                 <property name="appId" column="app_id" />
 433                 <property name="statusCode" column="status_cd" />
 434         </class>
 435
 436         <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
 437                 <id name="id" column="id">
 438                         <generator class="native">
 439                                 <param name="property">seq_ep_pers_user_widget_sel</param>
 440                         </generator>
 441                 </id>
 442                 <property name="userId" column="user_id" />
 443                 <property name="widgetId" column="widget_id" />
 444                 <property name="statusCode" column="status_cd" />
 445         </class>
 446
 447         <!-- EPUserAppRolesRequest class mapping details -->
 448         <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
 449                 <id name="id" column="req_id">
 450                         <generator class="native">
 451                                 <param name="sequence">seq_ep_user_role_request</param>
 452                         </generator>
 453                 </id>
 454                 <property name="userId" column="user_id" />
 455                 <property name="appId" column="app_id" />
 456                 <property name="createdDate" column="created_date" type="timestamp" />
 457                 <property name="updatedDate" column="updated_date" type="timestamp" />
 458                 <property name="requestStatus" column="request_status" />
 459
 460                 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
 461                         lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
 462                         <key column="req_id" />
 463                         <one-to-many
 464                                 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
 465                 </set>
 466         </class>
 467
 468         <!-- EPUserAppRolesRequestDetail class mapping details -->
 469         <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
 470                 <id name="id" column="id">
 471                         <generator class="native">
 472                                 <param name="sequence">seq_ep_user_role_request_det</param>
 473                         </generator>
 474                 </id>
 475                 <property name="reqRoleId" column="requested_role_id" />
 476                 <property name="reqType" column="request_type" />
 477                 <many-to-one name="epRequestIdData" fetch="select"
 478                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
 479                         <column name="req_id" not-null="true" />
 480                 </many-to-one>
 481         </class>
 482
 483         <!-- show the current user plus related users -->
 484         <sql-query name="relatedUsers">
 485                 <return-scalar column="org_user_id" type="java.lang.String" />
 486                 <![CDATA[
 487                         select t.org_user_id from (
 488                                 select distinct c.org_user_id org_user_id, c.last_login_date from
 489                                         (select c.org_user_id, c.last_login_date from
 490                                                 fn_user_role a,
 491                                                 (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,
 492                                                 fn_user c
 493                                                 where a.ROLE_ID = b.role_id
 494                                                 and a.APP_ID = b.app_id
 495                                                 and a.USER_ID = c.user_id
 496                                         union
 497                                         select org_user_id , last_login_date from fn_user where org_user_id=:userId
 498                                         ) c
 499                                 order by c.last_login_date desc limit 10
 500                         ) t
 501                 ]]>
 502         </sql-query>
 503
 504         <!-- requires values for named parameters :searchQuery and :userId -->
 505         <sql-query name="searchPortal">
 506                 <return alias="searchResult"
 507                         class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
 508                 <![CDATA[
 509                         select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
 510                                 ( (
 511                                 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
 512                                 from fn_user where
 513                                 upper( :searchQuery ) != ''
 514                                 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
 515                                 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
 516                                 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
 517                                 and  upper(active_yn) = 'Y'
 518                                 )
 519                                 union
 520                                 (
 521                                 select distinct 'Application' CATEGORY, b1.app_name NAME,
 522                                 if (b1.app_type = 2,'false','true') UUID,
 523                                 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
 524                                 from
 525                                 (
 526                                 select c.*
 527                                 from fn_user_role a, fn_user b, fn_app c
 528                                 where a.USER_ID = b.user_id
 529                                 and upper(b.org_user_id) = upper(  :userId )
 530                                 and a.app_id = c.app_id
 531                                 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 532                                 and upper(c.ENABLED) = 'Y'
 533                                 ) a1 right outer join
 534                                 (select * from fn_app where
 535                                 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
 536                                 and upper(ENABLED) = 'Y') b1
 537                                 on a1.APP_ID = b1.app_id
 538                                 )
 539                                 union
 540                                 (
 541                                 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
 542                                 if (b1.app_type = 2,'false','true') UUID,
 543                                 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
 544                                 from
 545                                 (select distinct  d.*, e.app_type from fn_user a,
 546                                 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
 547                                 where a.USER_ID = b.user_id
 548                                 and b.role_id = c.role_id
 549                                 and c.menu_id = d.menu_id
 550                                 and upper(a.org_user_id) = upper( :userId )
 551                                 and c.APP_ID = e.app_id
 552                                 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 553                                 and upper(d.active_yn) = 'Y'
 554                                 and upper(e.enabled) = 'Y'
 555                                 ) a1 right outer join
 556                                 (
 557                                 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
 558                                 where active_yn = 'Y'
 559                                 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
 560                                 and a.menu_id = b.menu_id
 561                                 and b.app_id = c.app_id
 562                                 and c.enabled = 'Y'
 563                                 ) b1
 564                                 on a1.menu_id = b1.menu_id
 565                                 )
 566                                 union
 567                                 select distinct CATEGORY, NAME, UUID, TARGET
 568                                 from
 569                                 (
 570                                 (
 571                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 572                                 d.widget_id UUID,
 573                                 d.wdg_desc TARGET
 574                                 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
 575                                 where upper(a.org_user_id) = upper( :userId )
 576                                 and a.user_id = b.user_id
 577                                 and b.role_id = c.role_id
 578                                 and c.widget_id = d.widget_id
 579                                 and d.all_user_flag<>'Y'
 580                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 581                                 )
 582                                 union
 583                                 (
 584                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 585                                 d.widget_id UUID,
 586                                 d.wdg_desc TARGET
 587                                 from ep_widget_catalog d
 588                                 where d.all_user_flag='Y'
 589                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 590                                 )
 591                                 union
 592                                 (
 593                                 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
 594                                 d.widget_id UUID,
 595                                 d.wdg_desc TARGET
 596                                 from fn_user a, fn_user_role b, ep_widget_catalog d
 597                                 where upper(a.org_user_id) = upper( :userId )
 598                                 and a.user_id = b.user_id
 599                                 and b.role_id = 1
 600                                 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
 601                                 )
 602                                 ) y
 603                                 ) t,
 604                                 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
 605                                                                         ;
 606
 607                 ]]>
 608         </sql-query>
 609
 610         <!-- requires values for named parameters :searchQuery and :userId -->
 611         <sql-query name="getNotifications">
 612                 <return alias="notificationResult"
 613                         class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
 614                 <![CDATA[
 615                         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, active_YN from
 616                         (
 617
 618                         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,active_YN
 619                         from
 620                                 (
 621                                 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, creator_ID,active_YN
 622                                 from
 623                                 (
 624                                 select a.*, b.role_id,b.recv_user_id
 625                                 from ep_notification a, ep_role_notification b
 626                                 where a.notification_id = b.notification_id
 627                                 and (end_time is null ||  SYSDATE() <= end_time )
 628                                 and (start_time is null ||  SYSDATE() >= start_time)
 629                                 and a.is_for_all_roles = 'N'
 630                                 ) a,
 631                                 (
 632                                 select a.user_id, c.role_id, c.app_id, d.APP_NAME
 633                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 634                                 where a.user_id = b.user_id
 635                                 and a.user_id = :user_id
 636                                 and b.role_id = c.role_id
 637                                 and c.app_id = d.app_id
 638                                 and d.enabled='Y'
 639                                 )b
 640                                 where
 641                                 (
 642                                 a.role_id = b.role_id||a.recv_user_id=:user_id
 643                                 )
 644                                 union
 645                                 (
 646                                 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, creator_ID,active_YN
 647                                 from ep_notification a
 648                                 where a.notification_id
 649                                 and (end_time is null ||  SYSDATE() <= end_time )
 650                                 and (start_time is null ||  SYSDATE() >= start_time)
 651                                 and a.is_for_all_roles = 'Y'
 652                                 )
 653                                 ) a
 654                                 where
 655                                         active_YN = 'Y'
 656                                 and
 657                                         not exists
 658                                 (
 659                                 select * from ep_user_notification m where user_id = :user_id and m.notification_id = a.notification_id and is_viewed = 'Y'
 660                                 )
 661                                 order by priority desc, start_Time desc,created_date desc
 662
 663
 664                          ) t,
 665                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 666                         ;
 667                 ]]>
 668
 669         </sql-query>
 670
 671                 <sql-query name="getUsersByOrgIdsNotifications">
 672                 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
 673                 <![CDATA[
 674                         SELECT * from fn_user where org_user_id in (:OrgIds)
 675                 ]]>
 676
 677         </sql-query>
 678
 679
 680         <!-- Gets all Admin notification history; accepts no parameters -->
 681         <sql-query name="getAdminNotificationHistoryVO">
 682                 <return alias="adminNotificationHistoryVOResult"
 683                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 684                 <![CDATA[
 685                 SELECT
 686                         n.notification_ID AS notificationId,
 687                         n.is_for_online_users AS isForOnlineUsers,
 688                         n.is_for_all_roles AS isForAllRoles,
 689                         n.active_YN AS activeYn,
 690                         n.msg_header AS msgHeader,
 691                         n.msg_description AS msgDescription,
 692                         n.msg_source AS msgSource,
 693                         n.start_Time AS startTime,
 694                         n.end_time AS endTime,
 695                         n.priority,
 696                         n.creator_ID AS creatorId,
 697                         n.created_date AS createdDate,
 698                         u.org_user_id AS loginId
 699                 FROM
 700                         ep_notification n
 701                 LEFT JOIN
 702                         fn_user u on u.user_id = n.creator_id
 703                 WHERE
 704                         n.active_YN='Y' order by n.start_time desc
 705                 ]]>
 706         </sql-query>
 707
 708         <sql-query name="getEpNotificationAppRoles">
 709                 <return alias="notificationAppRoles"
 710                         class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
 711                 <!-- This query requires no parameters. -->
 712                 <![CDATA[
 713
 714 select  a.app_id, a.app_name, b.role_id, b.role_name from
 715 (select * from fn_app where app_id = 1) a,
 716 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
 717 union
 718 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
 719 from fn_app, fn_role
 720 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' order by app_name
 721
 722
 723
 724                 ]]>
 725         </sql-query>
 726
 727         <!-- Gets all notification history -->
 728         <sql-query name="getNotificationHistoryVO">
 729                 <return alias="notificationHistoryVOResult"
 730                         class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
 731                 <![CDATA[
 732                 SELECT
 733                         notificationId, isForOnlineUsers, isForAllRoles, msgHeader,     msgDescription,msgSource,
 734                         startTime, endTime, priority, createdDate,      creatorId, loginId,     activeYn
 735                 FROM
 736                         (
 737                                         select distinct
 738                                         a.notification_ID AS notificationId,
 739                                         is_for_online_users AS isForOnlineUsers,
 740                                         is_for_all_roles AS isForAllRoles,
 741                                         msg_header AS msgHeader,
 742                                         msg_description AS msgDescription,
 743                                         msg_source AS msgSource,
 744                                         start_Time AS startTime,
 745                                         end_time AS endTime,
 746                                         priority,
 747                                         created_date AS createdDate,
 748                                         creator_ID AS creatorId,
 749                                         login_id AS loginId,
 750                                         active_YN AS activeYn,
 751                                         if (is_viewed is null, 'N', is_viewed)
 752                         from
 753                         (
 754                                 select
 755                                         user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
 756                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
 757                                         creator_ID,active_YN
 758                                 from
 759                                 (
 760                                         select a.*, b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
 761
 762                    ELSE NULL
 763               END  AS login_id,b.recv_user_id
 764                                 from ep_notification a, ep_role_notification b,fn_user u
 765                                 where a.notification_id = b.notification_id and  (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
 766                                 and a.is_for_all_roles = 'N'
 767                                 and (
 768                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 769                                 or
 770                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 771                                 or
 772                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 773                                 or
 774                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 775                                 )
 776                                 ) a,
 777                                 (
 778                                 select a.user_id, c.role_id, c.app_id, d.APP_NAME
 779                                 from fn_user a, fn_user_role b, fn_role c, fn_app d
 780                                 where a.user_id = b.user_id
 781                                 and a.user_id = :user_id
 782                                 and b.role_id = c.role_id
 783                                 and c.app_id = d.app_id
 784                                 and d.enabled='Y'
 785                                 ) b
 786                                 where
 787                                 (
 788                                 a.role_id = b.role_id||a.recv_user_id=:user_id
 789                                 )
 790                                 union
 791                                 (
 792                                 select
 793                                         :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
 794                                         msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
 795                                         creator_ID, a.active_YN
 796                                 from ep_notification a  JOIN fn_user b on b.user_id=a.creator_ID
 797                                 where a.notification_id
 798                                 and a.is_for_all_roles = 'Y'
 799                                 and (
 800                                 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
 801                                 or
 802                                 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 803                                 or
 804                                 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 805                                 or
 806                                 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL  -31 DAY))
 807                                 )
 808                                 )
 809                                 ) a left outer join (
 810                                 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
 811                                 ) m
 812                                 on  a.notification_id = m.notification_ID
 813                                 where
 814                                 active_YN = 'Y'
 815
 816                                 order by  start_Time desc,end_time desc
 817                         ) t,
 818                      (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
 819                 ]]>
 820
 821         </sql-query>
 822
 823                 <!-- shows the received recipient to whom the notification is delivered from external system -->
 824         <sql-query name="messageRecipients">
 825                 <return-scalar column="org_user_id" type="java.lang.String" />
 826                 <![CDATA[
 827                         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
 828                 ]]>
 829         </sql-query>
 830
 831
 832         <!-- Gets role details for a specified notification -->
 833         <sql-query name="getNotificationRoles">
 834                 <return alias="notificationRolesResult"
 835                         class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
 836                 <![CDATA[
 837                         SELECT *
 838                         FROM
 839                                 ep_role_notification
 840                         WHERE
 841                                 notification_Id = :notificationId
 842                 ]]>
 843         </sql-query>
 844
 845         <!-- Gets all applications, possibly with contact information -->
 846         <sql-query name="getAppsAndContacts">
 847                 <return alias="appContactUs"
 848                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 849                 <!-- This query requires no parameters. -->
 850                 <![CDATA[
 851                         select
 852                                 a.app_id as appId, a.app_name as appName,
 853                                 c.contact_name as contactName,
 854                                 c.contact_email as contactEmail, c.url, c.description,
 855                                 c.active_yn as activeYN
 856                         from
 857                                 fn_app a
 858                         left join
 859                                 fn_app_contact_us c
 860                                 on a.app_id = c.app_id
 861                         where
 862                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 863                         ;
 864                 ]]>
 865         </sql-query>
 866
 867         <!-- Gets all rows from the app-contact-us table, extended with app information -->
 868         <sql-query name="getAppContactUsItems">
 869                 <return alias="appContactUs"
 870                         class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
 871                 <!-- This query requires no parameters. -->
 872                 <![CDATA[
 873                         select
 874                                 c.app_id as appId, c.contact_name as contactName,
 875                                 c.contact_email as contactEmail, c.url, c.description,
 876                                 c.active_yn as activeYN, a.app_name as appName
 877                         from
 878                                 fn_app_contact_us c
 879                         left join
 880                                 fn_app a
 881                                 on a.app_id = c.app_id
 882                         where
 883                                 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
 884                         ;
 885                 ]]>
 886         </sql-query>
 887
 888         <!-- Gets one row for each app with the category and catenated functions. -->
 889         <sql-query name="getAppCategoryFunctions">
 890                 <return alias="appCategoryFunctions"
 891                         class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
 892                 <!-- This query requires no parameters. -->
 893                 <![CDATA[
 894                         SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
 895                                 r.category as category, r.app_id as appId, r.app_name as application,
 896                                 group_concat(function_text separator ', ') as functions
 897                         FROM (
 898                                 SELECT
 899                                 app_id, function_text, app_name,
 900                                 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
 901                                 FROM (
 902                                         SELECT
 903                                         j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
 904                                         k.text parent_menu, k.parent_menu_id parent_menu_id
 905                                         FROM (
 906                                                 SELECT distinct
 907                                                 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
 908                                                 FROM
 909                                                 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
 910                                                 WHERE
 911                                                 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
 912                                                 ) j,
 913                                         fn_menu_functional k
 914                                         WHERE j.parent_menu_id = k.menu_id
 915                                         ) fn,
 916                                 fn_menu_functional a
 917                                 WHERE fn.parent_menu_id = a.menu_id
 918                         ) r,
 919                         (SELECT @rn /*'*/:=/*'*/ 0) t2
 920                         group by r.category, r.app_id, r.app_name
 921                         order by category, app_name
 922                         ;
 923                 ]]>
 924         </sql-query>
 925
 926         <!-- Gets one row for each function-application-role combination. -->
 927         <sql-query name="getAppAccessFunctionRole">
 928                 <return alias="appAccessFunctionRole"
 929                         class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
 930                 <!-- This query requires one parameters. -->
 931                 <![CDATA[
 932                         select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
 933                         (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,
 934                         o.requested_role_id,o.request_type
 935                         from
 936                         (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
 937                         from
 938                         (
 939                         select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
 940                         from
 941                         FN_ROLE C, FN_APP D
 942                         where COALESCE(c.app_id,1) = d.app_id
 943                         and (d.enabled='Y' or d.app_id=1)
 944                         and c.active_yn = 'Y'
 945                         ) a left outer join
 946                         (
 947                         select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
 948                         WHERE
 949                         A.MENU_ID = B.MENU_ID
 950                         and active_yn = 'Y'
 951                         )b
 952                         on a.role_id = b.role_id) t left outer join
 953                         (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
 954                         where er.req_id=ed.req_id
 955                         and upper(ed.request_type)='P'
 956                         and er.user_id =:userId) o
 957                         on t.app_id=o.app_id
 958                         and t.role_id = o.requested_role_id
 959                         JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
 960                         ;
 961                 ]]>
 962         </sql-query>
 963
 964         <query name="getCommonWidgetItem">
 965                 from CommonWidget where category = :cat order by
 966                 sortOrder, title
 967         </query>
 968
 969         <sql-query name="getGuestLastLogin">
 970                 <return-scalar column="audit_date" type="java.util.Date" />
 971                 <![CDATA[
 972                         select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
 973                 ]]>
 974         </sql-query>
 975
 976         <!-- Gets personalized list of enabled and accessible apps for regular user -->
 977         <sql-query name="getPersUserApps">
 978                 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
 979                 <!-- This query requires one parameter: userId (number) -->
 980                 <![CDATA[
 981                     select
 982                                 distinct  -- multiple roles yield multiple rows
 983                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
 984                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
 985                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
 986                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
 987                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
 988                     from FN_APP a
 989                     -- Portal assigns role 999 to app administrator
 990                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
 991                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
 992                         where a.ENABLED = 'Y'
 993                     and (
 994                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
 995                         or
 996                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
 997                         )
 998                         order by app_name
 999                     ;
1000                 ]]>
1001         </sql-query>
1002
1003         <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1004                 admin -->
1005         <sql-query name="getPersAdminApps">
1006                 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1007                 <!-- This query requires one parameter: userId (number) -->
1008                 <![CDATA[
1009                     select
1010                                 distinct  -- multiple roles yield multiple rows
1011                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1012                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1013                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1014                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1015                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1016                     from FN_APP a
1017                     -- Portal assigns role 999 to app administrator
1018                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1019                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1020                         where a.ENABLED = 'Y'
1021                     and (
1022                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1023                         or
1024                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1025                                 or
1026                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1027                         )
1028                         order by app_name
1029                     ;
1030                 ]]>
1031         </sql-query>
1032
1033         <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1034         <sql-query name="getPersAdminAppsOrderByName">
1035                 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1036                 <!-- This query requires one parameter: userId (number) -->
1037                 <![CDATA[
1038                  select
1039                                 distinct  -- multiple roles yield multiple rows
1040                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1041                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1042                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1043                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1044                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1045                     from FN_APP a
1046                     -- Portal assigns role 999 to app administrator
1047                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1048                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1049                         where a.ENABLED = 'Y'
1050                     and (
1051                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1052                         or
1053                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1054                                 or
1055                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1056                         )
1057                         order by app_name
1058                     ;
1059
1060                 ]]>
1061         </sql-query>
1062
1063         <!--Gets personalized list of enabled and accessible User apps by name -->
1064         <sql-query name="getPersUserAppsOrderByName">
1065                 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1066                 <!-- This query requires one parameter: userId (number) -->
1067                 <![CDATA[
1068                 select
1069                                 distinct  -- multiple roles yield multiple rows
1070                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1071                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1072                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1073                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1074                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1075                     from FN_APP a
1076                     -- Portal assigns role 999 to app administrator
1077                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1078                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1079                         where a.ENABLED = 'Y'
1080                     and (
1081                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1082                         or
1083                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1084                         )
1085                         order by app_name
1086                     ;
1087
1088                 ]]>
1089         </sql-query>
1090
1091
1092         <!--Gets personalized list of enabled and accessible Super Admin apps by
1093                 LastUsed -->
1094         <sql-query name="getAdminAppsOrderByLastUsed">
1095                 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1096
1097                 <![CDATA[
1098                     select
1099                         *
1100                         from (select
1101                                 distinct  -- multiple roles yield multiple rows
1102                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1103                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1104                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1105                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1106                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1107                     from FN_APP a
1108                     -- Portal assigns role 999 to app administrator
1109                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1110                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1111                         where a.ENABLED = 'Y'
1112                     and (
1113                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1114                         or
1115                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1116                                 or
1117                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1118                         )
1119                         ) A
1120                 left outer join
1121                         (select  distinct
1122                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1123                         from FN_AUDIT_LOG c
1124                          )B
1125                         on A.app_id = B.Affected_record_id
1126                         order by AUDIT_DATE DESC;
1127                         ]]>
1128         </sql-query>
1129
1130         <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1131         <sql-query name="getUserAppsOrderByLastUsed">
1132                 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1133
1134                 <![CDATA[
1135                         select * from (select
1136                                 distinct  -- multiple roles yield multiple rows
1137                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1138                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1139                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1140                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1141                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1142                     from FN_APP a
1143                     -- Portal assigns role 999 to app administrator
1144                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1145                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1146                         where a.ENABLED = 'Y'
1147                     and (
1148                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1149                         or
1150                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1151                         )) A
1152                 left outer join
1153                         (select  distinct
1154                         c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1155                         from FN_AUDIT_LOG c
1156                          )B
1157                         on A.app_id = B.Affected_record_id
1158                         order by AUDIT_DATE DESC;
1159
1160
1161                 ]]>
1162
1163         </sql-query>
1164
1165         <!--Gets personalized list of enabled and accessible Super Admin apps by
1166                 Most Used -->
1167         <sql-query name="getAdminAppsOrderByMostUsed">
1168                 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1169
1170                 <![CDATA[
1171                         select
1172                         *
1173                         from (select
1174                                 distinct  -- multiple roles yield multiple rows
1175                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1176                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1177                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1178                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1179                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1180                     from FN_APP a
1181                     -- Portal assigns role 999 to app administrator
1182                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1183                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1184                         where a.ENABLED = 'Y'
1185                     and (
1186                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1187                         or
1188                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1189                                 or
1190                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1191                         )
1192                         ) A
1193                         left outer join
1194                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1195                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1196                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1197                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1198                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1199                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1200                          )B
1201                         on A.app_id = B.Affected_record_id
1202                         order by total_visits DESC;
1203                 ]]>
1204
1205         </sql-query>
1206
1207         <!--Gets personalized list of enabled and accessible NON Super Admin User
1208                 apps by Most Used -->
1209         <sql-query name="getUserAppsOrderByMostUsed">
1210                 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1211
1212                 <![CDATA[
1213                         select * from
1214                         (select
1215                                 distinct  -- multiple roles yield multiple rows
1216                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1217                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1218                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1219                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1220                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1221                     from FN_APP a
1222                     -- Portal assigns role 999 to app administrator
1223                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1224                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1225                         where a.ENABLED = 'Y'
1226                     and (
1227                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1228                         or
1229                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1230                         ))A
1231                         left outer join
1232                         (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1233                         from FN_APP LEFT JOIN FN_AUDIT_LOG
1234                         on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1235                         where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1236                         and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1237                         group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
1238                          )B
1239                         on A.app_id = B.Affected_record_id
1240                         order by total_visits DESC;
1241                 ]]>
1242         </sql-query>
1243
1244         <!--Gets personalized list of enabled and accessible Super Admin apps by
1245                 Manual -->
1246         <sql-query name="getAdminAppsOrderByManual">
1247                 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1248
1249                 <![CDATA[
1250                         select * from
1251                         (select
1252                                 distinct  -- multiple roles yield multiple rows
1253                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1254                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1255                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1256                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1257                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1258                     from FN_APP a
1259                     -- Portal assigns role 999 to app administrator
1260                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1261                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1262                         where a.ENABLED = 'Y'
1263                     and (
1264                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1265                         or
1266                                 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1267                                 or
1268                                 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1269                         )) A
1270                         left outer join
1271                          (select m.app_id, m.sort_order
1272                         from ep_pers_user_app_man_sort m
1273                         where USER_ID = :userId
1274                         ) B
1275                         on A.APP_ID = B.app_id
1276                         order by sort_order ASC
1277                     ;
1278                 ]]>
1279
1280         </sql-query>
1281
1282         <!--Gets personalized list of enabled and accessible NON Super admin User
1283                 apps by Manual -->
1284         <sql-query name="getUserAppsOrderByManual">
1285                 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1286
1287                 <![CDATA[
1288                                                         select * from   (select
1289                                 distinct  -- multiple roles yield multiple rows
1290                             a.APP_ID                    , a.APP_NAME            , a.APP_IMAGE_URL               , a.APP_DESCRIPTION ,
1291                         a.APP_NOTES             , a.APP_URL             , a.APP_ALTERNATE_URL   , a.APP_REST_ENDPOINT ,
1292                                 a.ML_APP_NAME           , a.ML_APP_ADMIN_ID , a.MOTS_ID                         , a.APP_PASSWORD ,
1293                         a.THUMBNAIL             , a.APP_USERNAME        , a.OPEN                                , a.ENABLED ,
1294                                 a.UEB_TOPIC_NAME        , a.UEB_KEY             , a.UEB_SECRET                  , a.APP_TYPE
1295                     from FN_APP a
1296                     -- Portal assigns role 999 to app administrator
1297                         left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1298                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1299                         where a.ENABLED = 'Y'
1300                     and (
1301                                 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1302                         or
1303                                 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1304                         )) A
1305                         left outer join
1306                          (select m.app_id, m.sort_order
1307                         from ep_pers_user_app_man_sort m
1308                         where USER_ID = :userId
1309                         ) B
1310                         on A.APP_ID = B.app_id
1311                         order by sort_order ASC
1312                     ;
1313                 ]]>
1314         </sql-query>
1315
1316         <!-- Gets regular user's list of enabled apps including accessible and select
1317                 statuses -->
1318         <sql-query name="getUserAppCatalog">
1319                 <return alias="userAppCatalog"
1320                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1321                 <!-- This query requires one parameter: userId (number) -->
1322                 <![CDATA[
1323                                                 select
1324                                 distinct  -- multiple roles yield multiple rows
1325                             a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
1326                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1327                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1328                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1329                             IF(a.open = 'Y', TRUE, FALSE) as open,
1330                 -- ACCESS(-ible) means user has a defined role OR the application is open
1331                                 case
1332                                         -- regular app and user has a role
1333                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1334                     -- open app
1335                     when a.OPEN = 'Y' then TRUE
1336                     else FALSE
1337                 end as 'access' ,
1338                 -- SELECT(-ed) indicates user personalization
1339                 case
1340                                         -- regular app, user has a role, no personalization
1341                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1342                     -- open app and has personalization
1343                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1344                     else FALSE
1345                 end as 'select' ,
1346                 -- PENDING indicates user checked a box
1347                 case
1348                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1349                         else FALSE
1350                 end as 'pending'
1351                     from FN_APP a
1352                     -- Portal assigns role 999 to app administrator
1353             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1354                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1355       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'
1356       where a.ENABLED = 'Y'
1357
1358                         -- Show accessible apps first, then the rest; sort by name within each set.
1359             order by access desc, app_name asc
1360                     ;
1361                 ]]>
1362         </sql-query>
1363
1364         <sql-query name="getAppDetails">
1365                 <return alias="appDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1366
1367                 <![CDATA[
1368
1369                 SELECT * FROM fn_app  where app_name =:appName
1370                 ;
1371                 ]]>
1372         </sql-query>
1373
1374
1375         <sql-query name="deleteAccountEndpoint">
1376                 <![CDATA[
1377
1378                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1379                 ;
1380                 ]]>
1381         </sql-query>
1382
1383         <sql-query name="deleteAccountEndpointRecord">
1384                 <![CDATA[
1385
1386                 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1387                 ;
1388                 ]]>
1389         </sql-query>
1390
1391         <sql-query name="deleteEPEndpoint">
1392                 <![CDATA[
1393
1394                 DELETE FROM ep_endpoints WHERE id =:epId
1395                 ;
1396                 ]]>
1397         </sql-query>
1398
1399         <sql-query name="deleteBasicAuthAccount">
1400                 <![CDATA[
1401
1402                 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1403                 ;
1404                 ]]>
1405         </sql-query>
1406
1407         <sql-query name="getAppRoles">
1408                 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1409
1410                 <![CDATA[
1411
1412                 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1413                 ;
1414                 ]]>
1415         </sql-query>
1416
1417
1418         <sql-query name="deleteMicroserviceParameter">
1419                 <![CDATA[
1420
1421                 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1422                 ;
1423                 ]]>
1424         </sql-query>
1425
1426         <sql-query name="deleteMicroserviceParameterById">
1427                 <![CDATA[
1428
1429                 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1430                 ;
1431                 ]]>
1432         </sql-query>
1433
1434         <sql-query name="deleteWidgetCatalogParameter">
1435                 <![CDATA[
1436
1437                 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1438                 ;
1439                 ]]>
1440         </sql-query>
1441
1442         <sql-query name="deleteMicroservice">
1443                 <![CDATA[
1444
1445                 DELETE FROM ep_microservice WHERE id =:serviceId
1446                 ;
1447                 ]]>
1448         </sql-query>
1449
1450         <sql-query name="epUserAppId">
1451                 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1452
1453                 <![CDATA[
1454
1455                 SELECT * FROM  FN_USER  where ORG_USER_ID = :orgUserIdValue
1456                 ;
1457                 ]]>
1458         </sql-query>
1459
1460         <sql-query name="userAppsSortPreferenceQuery">
1461                 <return alias="appsSortPreferenceQuery"
1462                         class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1463
1464                 <![CDATA[
1465
1466                 SELECT * from  EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1467
1468                 ]]>
1469         </sql-query>
1470
1471         <sql-query name="userAppsManualSortPrfQuery">
1472                 <return alias="AppsManualSortPrfQuery"
1473                         class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1474
1475                 <![CDATA[
1476
1477                 SELECT * from  EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1478
1479                 ]]>
1480         </sql-query>
1481
1482         <sql-query name="userWidgetManualSortPrfQuery">
1483                 <return alias="widgetManualSortPrfQuery"
1484                         class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1485                 <![CDATA[
1486                 SELECT * from  EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1487                 ]]>
1488         </sql-query>
1489
1490         <sql-query name="appRoles">
1491                 <return alias="rolesForApp"
1492                         class="org.openecomp.portalapp.portal.domain.EpUserAppRoles" />
1493                 <![CDATA[
1494                         SELECT ROLE_ID, APP_ID from  FN_ROLE where APP_ROLE_ID =:appRoleId and app_id =:appId
1495                         ;
1496                 ]]>
1497         </sql-query>
1498
1499         <sql-query name="userAppRolesRequestList">
1500                 <return alias="appRolesRequestList"
1501                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1502                 <![CDATA[
1503                         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'
1504                         ;
1505                 ]]>
1506         </sql-query>
1507
1508         <sql-query name="userAppRolesRequestDetailList">
1509                 <return alias="appRolesRequestDetailList"
1510                         class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1511                 <![CDATA[
1512
1513                         SELECT  id, req_id, requested_role_id, request_type from  EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1514                         ;
1515                 ]]>
1516         </sql-query>
1517
1518         <!-- Gets list of enabled apps including accessible and select statuses -->
1519         <sql-query name="getAdminAppCatalog">
1520                 <return alias="adminAppCatalog"
1521                         class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1522                 <!-- This query requires one parameter: userId (number) -->
1523                 <![CDATA[
1524                         select
1525                                 distinct  -- multiple roles yield multiple rows
1526                             a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
1527                             a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1528                             a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1529                                 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1530                             IF(a.open = 'Y', TRUE, FALSE) as open,
1531                 -- ACCESS(-ible) means user has a defined role OR the application is open
1532                                 case
1533                                         -- regular app and user has a role
1534                                         when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1535                     -- open app
1536                     when a.OPEN = 'Y' then TRUE
1537                     else FALSE
1538                 end as 'access' ,
1539                 -- SELECT(-ed) indicates user personalization
1540                 case
1541                                         -- regular app, user has a role, no personalization
1542                                         when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1543                                         -- regular app, user has no role, admin forced a personalization
1544                                         when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1545                     -- open app and has personalization
1546                     when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1547                     else FALSE
1548                 end as 'select' ,
1549                 -- PENDING indicates user checked a box
1550                 case
1551                         when a.OPEN = 'N' and  q.request_status = 'P' then TRUE
1552                         else FALSE
1553                 end as 'pending'
1554                     from FN_APP a
1555                     -- Portal assigns role 999 to app administrator
1556             left outer join FN_USER_ROLE         r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1557                         left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1558                         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'
1559                         where a.ENABLED = 'Y'
1560                         -- Show accessible apps first, then the rest; sort by name within each set.
1561             order by access desc, app_name asc
1562                     ;
1563                 ]]>
1564         </sql-query>
1565
1566         <sql-query name="userAppCatalogRoles">
1567                 <return alias="epUserAppCatalogRoles"
1568                         class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1569                 <![CDATA[
1570         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
1571        from ep_user_roles_request A left join ep_user_roles_request_det B
1572                    on a.req_id = b.req_id
1573                    where  A.user_id=:userid
1574        and A.app_id IN (select app_id from fn_app where app_name=:appName)
1575        and A.request_status ='P'
1576        ;
1577                 ]]>
1578         </sql-query>
1579
1580         <sql-query name="getUserApproles">
1581                 <return alias="businessCardUserApplicationRoles"
1582                         class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1583
1584                 <![CDATA[
1585
1586                 select   fr.role_name, fa.app_name
1587           from
1588         fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1589           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
1590           ;
1591
1592                 ]]>
1593         </sql-query>
1594
1595 </hibernate-mapping>