I_ProdHierByDistributionChain

@AbapCatalog: {
  sqlViewName: 'IPRDHRYBYDSTRCHN',
  compiler.compareFilter: true,
  preserveKey: true
}
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Product Hierarchy by Product Sales Delivery'
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel: {
  usageType: {
    dataClass: #MIXED,
    sizeCategory: #XL,
    serviceQuality: #C
  }
}



define view I_ProdHierByDistributionChain
  as select from hrrp_directory             as hierarchy
    join         hrrp_node                  as node         on  hierarchy.hryid    = node.hryid
                                                            and hierarchy.hryver   = node.hryver
                                                            and hierarchy.hrytyp   = 'MD01'
                                                            and hierarchy.hryvalto = node.hryvalto
                                                            and node.nodetype      = 'L'
  // determine version UUID

    join         hrrp_attr_dir              as avrsn        on  avrsn.hryid       = hierarchy.hryid
                                                            and avrsn.hryver      = hierarchy.hryver
                                                            and avrsn.hryattrname = 'UH_VERSION_ID'
                                                            and avrsn.hryvalto    = hierarchy.hryvalto
  // determine Hierarchy information

    join         uhdt_vrsn                  as dtvrsn       on dtvrsn.ver_id = avrsn.hryattrvalue
  // determine parent node Value

    join         hrrp_node                  as pnode        on  pnode.hryid    = node.hryid
                                                            and pnode.hryver   = node.hryver
                                                            and pnode.nodecls  = node.nodecls
                                                            and pnode.hrynode  = node.parnode
                                                            and pnode.hryvalto = node.hryvalto

    join         I_ProdHierToDistrChnAssgmt as dcassignment on dtvrsn.hier_hid = dcassignment.ProductHierarchy
  //                                                            and dcassignment.UniversalHierVersValidFrom <= $session.system_date

  //                                                            and dcassignment.UniversalHierVersValidTo   >= $session.system_date


  association [1..1] to I_Product as _Product on $projection.Product = _Product.Product

{
  key cast(dtvrsn.hier_hid as prod_hier_id )                                         as ProductHierarchy,
      //      @ObjectModel.text.association: '_Text'

      @EndUserText.label: 'Product Hierarchy Node'
  key cast ( node.hrynode  as prod_hier_node_value )                                 as ProductHierarchyNode,
      @ObjectModel.foreignKey.association: '_Product'
  key cast(node.nodevalue  as productnumber)                                         as Product,
      @Semantics.businessDate.to: true
  key cast(hierarchy.hryvalto    as prodhierarchyvalidityenddate preserving type )   as ProdHierarchyValidityEndDate,
  key dcassignment.SalesProductHierarchyPurpose,

      @Semantics.businessDate.from: true
      cast(hierarchy.hryvalfrom  as prodhierarchyvaliditystartdate preserving type ) as ProdHierarchyValidityStartDate,

      pnode.nodevalue                                                                as ProductHierarchyParentNode,
      dcassignment.SalesOrganization,
      dcassignment.DistributionChannel,
      dcassignment.UniversalHierVersValidFrom,
      dcassignment.UniversalHierVersValidTo,

      _Product
}