I_CnsldtnSubitemHierNode

@AbapCatalog:{
  sqlViewName: 'ICCSUBITEMHN',
  compiler.compareFilter: true,
  preserveKey: true
  }
@Hierarchy.parentChild: {
  recurse:          { parent:    'ParentNode',
                      child:     'HierarchyNode'   },
  siblingsOrder:    [{ by:       'HierarchyNodeSequence',
                      direction: #ASC   }],
//  orphanedNode:     { handling:  #IGNORE  },

  directory:        '_Hierarchy'
}
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata:{
  ignorePropagatedAnnotations: true
  }
@ObjectModel: {
  usageType: {
    dataClass: #MASTER,
    serviceQuality: #A,
    sizeCategory: #XL},
  dataCategory: #HIERARCHY,
  representativeKey: 'HierarchyNode'
}
@VDM.viewType: #BASIC
@EndUserText.label: 'Consolidation Subitem - HierarchyNode'

define view I_CnsldtnSubitemHierNode
  as select from hrrp_node      as _CnsldtnSubitemHierNode

    inner join   hrrp_directory as _CnsldtnSubitemHierDir on  _CnsldtnSubitemHierDir.hryid    = _CnsldtnSubitemHierNode.hryid
                                                          and _CnsldtnSubitemHierDir.hryver   = _CnsldtnSubitemHierNode.hryver
                                                          and _CnsldtnSubitemHierDir.hryvalto = _CnsldtnSubitemHierNode.hryvalto

  association [0..1] to I_CnsldtnSubitemHierDir   as _Hierarchy       on  $projection.ConsolidationSubitemHierarchy = _Hierarchy.ConsolidationSubitemHierarchy

  association [0..1] to I_CnsldtnSubItemCategory  as _SubItemCategory on  $projection.SubItemCategory = _SubItemCategory.SubItemCategory

  association [0..*] to I_CnsldtnSubitemHierNodeT as _Text            on  $projection.ConsolidationSubitemHierarchy = _Text.ConsolidationSubitemHierarchy
                                                                      and $projection.HierarchyNode                 = _Text.HierarchyNode
                                                                      and $projection.SubItem                       = ''

  association [0..1] to I_CnsldtnSubItem          as _SubItem         on  $projection.SubItemCategory = _SubItem.SubItemCategory
                                                                      and $projection.SubItem         = _SubItem.SubItem
{

      @Consumption.filter: {mandatory : true, selectionType : #SINGLE, multipleSelections : false }
      @ObjectModel.foreignKey.association: '_Hierarchy'
  key _CnsldtnSubitemHierNode.hryid                               as ConsolidationSubitemHierarchy,

      @ObjectModel.text.association: '_Text'
  key _CnsldtnSubitemHierNode.hrynode                             as HierarchyNode,

      _CnsldtnSubitemHierNode.parnode                             as ParentNode,

      _CnsldtnSubitemHierNode.hryver                              as HierarchyVersion,

      @ObjectModel.foreignKey.association: '_SubItemCategory'
      @Consumption.valueHelpDefinition: [{
        entity: {
          name: 'I_CnsldtnSubItemCategoryVH',
          element: 'SubItemCategory'
        }
      }]
      cast( left(_CnsldtnSubitemHierNode.nodecls, 3) as fc_sityp) as SubItemCategory,

      @ObjectModel.foreignKey.association: '_SubItem'
      @Consumption.valueHelpDefinition: [{
        entity: {
          name: 'I_CnsldtnSubItemVH',
          element: 'SubItem' }
//        TODO SubItemCategory shal be a key!

//        additionalBinding: [{ localElement: 'SubItemCategory',

//                              element: 'SubItemCategory' }]

      }]
      case _CnsldtnSubitemHierNode.nodetype
        when 'L' then  cast( left( _CnsldtnSubitemHierNode.nodevalue, 10 ) as fc_sitem )
        else  ''
      end                                                         as SubItem,

      _CnsldtnSubitemHierNode.hryseqnbr                           as HierarchyNodeSequence,
      _CnsldtnSubitemHierNode.hrylevel                            as HierarchyNodeLevel,
      _CnsldtnSubitemHierNode.nodetype                            as NodeType,


      _Text,
      _SubItem,
      _Hierarchy,
      _SubItemCategory

}
where
          _CnsldtnSubitemHierDir.hrytyp      = 'CS18'
  and(
          _CnsldtnSubitemHierNode.nodetype   = 'R'
    or    _CnsldtnSubitemHierNode.nodetype   = 'N'
    or    _CnsldtnSubitemHierNode.nodetype   = 'L'
  )
  and(
    (
          _CnsldtnSubitemHierNode.hryvalfrom = '19000101'
      and _CnsldtnSubitemHierNode.hryvalto   = '99991231'
    )
    or(
          _CnsldtnSubitemHierNode.hryvalfrom = '00000000'
      and _CnsldtnSubitemHierNode.hryvalto   = '00000000'
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"HRRP_DIRECTORY",
"HRRP_NODE"
],
"ASSOCIATED":
[
"I_CNSLDTNSUBITEM",
"I_CNSLDTNSUBITEMCATEGORY",
"I_CNSLDTNSUBITEMHIERDIR",
"I_CNSLDTNSUBITEMHIERNODET"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/