FINCS_UH_SUPPLIERNODE

@AbapCatalog.sqlViewName: 'FINCS_UH_CS09N'
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Universal Hierarchy Supplier Node'
@ObjectModel.usageType: {
  dataClass: #MIXED,
  serviceQuality: #C,
  sizeCategory: #S
}
define view FINCS_UH_SUPPLIERNODE as select from uhdt_node as node
inner join uhdt_vrsn as version on node.ver_id = version.ver_id
left outer join uhdt_ntxt as ntxt on node.node_id = ntxt.node_id
                         and ntxt.lang = $session.system_language
association[1..1] to fac_cds_uh_version as to_version on $projection.VersionId = to_version.VersionId
{
  key node.node_id as NodeId,
  node.ver_id as VersionId,
  version.hier_hid as HierarchyId,
  version.ver_nbr as VersionNumber,
  version.ver_msg as VersionMessage,
  version.ver_vldfm as ValidFrom,
  case
  when ( ver_status = 'R' or ver_status = 'A' ) and locked_by = $session.user
  then 'I' 
  else version.ver_status end as VersionStatus,
  case node.node_hval
  when '' then cast(node.node_lval as ncval) 
  else cast (concat(node.node_lval,concat('~',node.node_hval)) as ncval) end as NodeValue,
  ntxt.lval_txt as NodeText,
  case node.node_hval
  when '' then case ntxt.lval_txt 
               when '' then cast( node.node_lval as nltxt )
               else cast( concat( node.node_lval, concat('(',concat( ntxt.lval_txt, ')' ) ) ) as nltxt ) end 
  else cast (concat(node.node_lval,concat('~',node.node_hval)) as nltxt) end as NodeValueText,

  to_version
}
where version.hier_catg = 'CS09'
and (node.node_type = 'N' or node.node_type = 'R')
and version.ver_status <> 'T'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"UHDT_NODE",
"UHDT_NTXT",
"UHDT_VRSN"
],
"ASSOCIATED":
[
"FAC_CDS_UH_VERSION"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/