FAC_CDS_UH_STSTCLKEYFIG

@AbapCatalog.sqlViewName: 'FACV_UH_STKFHIER'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType: {
  dataClass: #MIXED,
  serviceQuality: #C,
  sizeCategory: #S
}
@EndUserText.label: 'Universal Hier Statistical Key Figure'
define view FAC_CDS_UH_STSTCLKEYFIG
  as select from    uhdt_vrsn     as version
    left outer join uhdt_hier     as hier      on  version.hier_hid  = hier.hier_hid
                                               and version.hier_catg = hier.hier_catg
                                               and hier.lang         = $session.system_language
    inner join      uhdt_clfd_val as clfdvalue on version.ver_id = clfdvalue.obj_id
  association [1..1] to fac_cds_uh_category          as to_category       on  $projection.HierarchyCategory = to_category.CategoryKey
{
  version.ver_id                                  as VersionId,
  version.ver_vldfm                               as ValidityStartDate,
  version.ver_vldto                               as ValidityEndDate,
  version.ver_msg                                 as VersionMessage,
  case
  when ( ver_status = 'R' or ver_status = 'A' ) and locked_by = $session.user
  then cast ('I' as vrsst )
  else version.ver_status end                     as VersionStatus,
  cast( '' as vrstxt)                             as VersionStatusText,
  version.hier_catg                               as HierarchyCategory,
  cast( version.hier_hid as vhrid)                as HierarchyId, //remove value help

  hier.hier_desc                                  as HierarchyText,
  cast( version.hier_hid as vhdec)                as HierarchyVersionText,
  case clfdvalue.val_sign
  when 'S' then cast( clfdvalue.val_low as cctrl)
  when 'R' then cast( concat(clfdvalue.val_low,concat('~',clfdvalue.val_high)) as cctrl)
  else cast( clfdvalue.val_low as cctrl) end      as ControllingArea,
  case when hier.hier_desc is null then ''
  else cast( upper(hier.hier_desc) as vhdec ) end as HierarchyTextUpper,
  to_category
}
where
       version.hier_catg                       =  '0104'
  and  clfdvalue.fld_name                      =  'CONTROLLINGAREA'
  and  version.ver_status                      <> 'T'
  and  version.ver_status                      <> 'B'
 
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"UHDT_CLFD_VAL",
"UHDT_HIER",
"UHDT_VRSN"
],
"ASSOCIATED":
[
"FAC_CDS_UH_CATEGORY"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/