I_CurEHSLocationInclRoot

@AbapCatalog.sqlViewName: 'ICUREHSLOCINCLR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@EndUserText.label: 'Current revision of EHS Locations'
@ObjectModel.dataCategory: #HIERARCHY

@VDM.viewType: #COMPOSITE
// Client handling by session

@ClientHandling.algorithm: #SESSION_VARIABLE

// used for BOBF Master Data Object

@ObjectModel.usageType:{ serviceQuality: #C,        // < 15 msec

                         sizeCategory:  #M,         // < 100.000

                         dataClass: #MASTER}
/*+[hideWarning] { "IDS" : [ "DOUBLE_JOIN" ] }*/
define view I_CurEHSLocationInclRoot
  as select from    I_EHSLocation
  // get the current revision of the location structure

    inner join      I_EHSLocationHierarchyRevision   on  I_EHSLocationHierarchyRevision.RevisionStartDate <= $session.system_date
                                                     and I_EHSLocationHierarchyRevision.RevisionEndDate   >= $session.system_date

    inner join      I_EHSLocationHierarchyNode       on  I_EHSLocationHierarchyNode.EHSLocationUUID             = I_EHSLocation.EHSLocationUUID
                                                     and I_EHSLocationHierarchyNode.EHSLocHierarchyRevisionUUID = I_EHSLocationHierarchyRevision.EHSLocHierarchyRevisionUUID
    left outer join I_MyEHSLocation as MyEHSLocation on I_EHSLocation.EHSLocationUUID = MyEHSLocation.EHSLocationUUID

  association [1..1] to I_LocationDescFallbackLang as _LocTextDesc         on  $projection.EHSLocationUUID = _LocTextDesc.EHSLocationUUID
  association [1..1] to I_EHSLocationTypeText      as _EHSLocationTypeText on  $projection.EHSLocationType   = _EHSLocationTypeText.EHSLocationType
                                                                           and _EHSLocationTypeText.Language = $session.system_language
  association [0..1] to I_CurEHSLocationInclRoot   as _ParentLocation      on  $projection.EHSParentLocationUUID = _ParentLocation.EHSLocationUUID
  association [0..1] to I_EHSLocAllClassifiersText as _EHSLocAllClassifiersText on $projection.EHSLocationUUID = _EHSLocAllClassifiersText.EHSLocationUUID
{
  key I_EHSLocation.EHSLocationUUID,
      I_EHSLocation.RevisionEndDate,
      I_EHSLocation.RevisionStartDate,
      _LocTextDesc._LocationNameText.EHSLocationName                                   as EHSLocationName,
      EHSLocationType,
      EHSLocationStatus,
      I_EHSLocationHierarchyNode.EHSParentLocationUUID,
      Plant,
      I_EHSLocation.EHSLocationID,
      case
        when MyEHSLocation.EHSLocationUUID is null
            then cast( hextobin( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFE' )  as ehfnd_location_uuid_ref preserving type)
        else MyEHSLocation.EHSLocationUUID end                                         as MyEHSLocationUUID,
      _EHSLocationTypeText.EHSLocationTypeText,
      _EHSLocationTypeText,
      _ParentLocation,
       @Semantics.text: true
      _EHSLocAllClassifiersText.EHSLocClassifiersText,

      // fields for authorization check

      cast( EHSLocationAuthorizationGroup as ehfnd_loc_auth_group_nc preserving type ) as EHSLocationAuthorizationGroup,
      CostCenter,
      CompanyCode,
      BusinessArea
}
where
      I_EHSLocation.RevisionStartDate <= $session.system_date
  and I_EHSLocation.RevisionEndDate   >= $session.system_date // consider current location revisions only


union select from I_SAPClient // this generates a dummy topmost location

  left outer join I_MyEHSLocation as MyEHSLocation on MyEHSLocation.EHSLocationUUID <> hextobin(
                        '00000000000000000000000000000000'
            )
association [0..1] to I_OTRText                as _OTRLocationsText    on  _OTRLocationsText.OnlnTxtRpstryConceptID = '4544824ECF3DF419E10000000A428B9F'
                                                                       and _OTRLocationsText.Language               = $session.system_language
association [1..1] to I_EHSLocationTypeText    as _EHSLocationTypeText on  $projection.EHSLocationType   = _EHSLocationTypeText.EHSLocationType
                                                                       and _EHSLocationTypeText.Language = $session.system_language
association [0..1] to I_CurEHSLocationInclRoot as _ParentLocation      on  $projection.EHSParentLocationUUID = _ParentLocation.EHSLocationUUID
{
  key cast( hextobin( '00000000000000000000000000000000' ) as ehfnd_location_uuid_ref preserving type )           as EHSLocationUUID,
  key cast( '99991231' as ehfnd_revision_split_date )                                                             as RevisionEndDate,
      cast( '00000000' as ehfnd_revision_end_date )                                                               as RevisionStartDate,
      cast( _OTRLocationsText.Text as ehfnd_loc_name )                                                            as EHSLocationName,
      'root'                                                                                                      as EHSLocationType,
      '-1'                                                                                                        as EHSLocationStatus,
      cast( hextobin( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' ) as ehfnd_loc_rev_key preserving type )                 as EHSParentLocationUUID,
      ''                                                                                                          as Plant,
      ''                                                                                                          as EHSLocationID,
      case
        when MyEHSLocation.EHSLocationUUID is null
          then cast( hextobin( '00000000000000000000000000000000' ) as ehfnd_location_uuid_ref preserving type )
        else cast( hextobin( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFE' ) as ehfnd_location_uuid_ref preserving type) end as MyEHSLocationUUID,
      ''                                                                                                          as EHSLocationTypeText,
      _EHSLocationTypeText,
      _ParentLocation,
      ''                                                                                                          as EHSLocClassifiersText,

      // fields for authorization check

      ''                                                                                                          as EHSLocationAuthorizationGroup,
      ''                                                                                                          as CostCenter,
      ''                                                                                                          as CompanyCode,
      ''                                                                                                          as BusinessArea
}
where
  sapclient = $session.client
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_EHSLOCALLCLASSIFIERSTEXT",
"I_EHSLOCATION",
"I_EHSLOCATIONHIERARCHYNODE",
"I_EHSLOCATIONHIERARCHYREVISION",
"I_EHSLOCATIONREVISIONNAMET",
"I_EHSLOCATIONTYPETEXT",
"I_LOCATIONDESCFALLBACKLANG",
"I_MYEHSLOCATION",
"I_OTRTEXT",
"I_SAPCLIENT"
],
"ASSOCIATED":
[
"I_EHSLOCALLCLASSIFIERSTEXT",
"I_EHSLOCATIONTYPETEXT",
"I_LOCATIONDESCFALLBACKLANG"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/