I_EHSCountriesTextExclGroups

@AbapCatalog.sqlViewName: 'EHSCNTRSTEXCLGR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Country/Region names excluding groups'

@VDM.viewType: #BASIC
@ObjectModel.dataCategory: #TEXT

@ObjectModel.representativeKey: 'Country'
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass:  #MASTER
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.compositionRoot:true
 
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #ACTIVE
@AbapCatalog.buffering.type: #FULL
@AbapCatalog.buffering.numberOfKeyFields: 1
@Metadata.ignorePropagatedAnnotations: true

define view I_EHSCountriesTextExclGroups
  as select from I_CountryText
  association [0..1] to I_Language as _Language on $projection.Language = _Language.Language
  association [0..1] to I_Country  as _Country  on $projection.Country = _Country.Country
{
      @ObjectModel.foreignKey.association: '_Country'
  key Country,
      @ObjectModel.foreignKey.association: '_Language'
      @Semantics.language
  key Language, 
      @Semantics.text: true
      CountryName,

      NationalityName,
      NationalityLongName,
      CountryShortName,
 
      _Country,
      _Language

}
where
      Country is not initial
  and Country <> 'EU'
  and Country <> 'NT'
  and Country <> 'UN'

union select from I_SAPClient // this generates a dummy record for 'Worldwide'

association [0..1] to I_Language as _Language on $projection.Language = _Language.Language
association [0..1] to I_Country  as _Country  on $projection.Country = _Country.Country
association [0..*] to I_OTRText  as _OTRText  on _OTRText.Language = $session.system_language
{
  key ''                                                                      as Country,
  key $session.system_language                                                as Language,

      cast( _OTRText[1: OnlnTxtRpstryConceptID = '005056B252701ED3A7DC79FF5C64D2C3'].Text as abap.char(50) ) as CountryName, // OTR Text for "Worldwide"


      ''                                                                      as NationalityName,
      ''                                                                      as NationalityLongName,
      ''                                                                      as CountryShortName,

      _Country,
      _Language
}
where
  sapclient = $session.client
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COUNTRYTEXT",
"I_OTRTEXT",
"I_SAPCLIENT"
],
"ASSOCIATED":
[
"I_COUNTRY",
"I_LANGUAGE"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/