P_CtlgSlsProdHierNodeVH

@VDM.private: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #COMPOSITE
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass:#MASTER


// We select from the PH runtime tables "HRRP*", this implicitly ensures that only PHs with status active

// will be selected. As long as formerly active PHs are edited, the existing active version of the 

// hierarchy is still available in the "HRRP*" tables.

define view entity P_CtlgSlsProdHierNodeVH
 as select from hrrp_directory as hierarchy
 
 // We only select PHs which are assigned to a sales area with purpose "Sales" (= '02'), since this is

 // a VH only for this particular characteristic (PH ID wit pruprpose "Sales").                                                            

    join I_ProdHierToDistrChnAssgmt as dcassignment on  dcassignment.ProductHierarchy = hierarchy.hrysid
                                                    and dcassignment.SalesProductHierarchyPurpose = '02'          
                                                                   
 // We only select hierarchy inner nodes (node type 'N'), leaf nodes ('L') and root nodes ('R') are not required:

 // root nodes (= hierarchy ID) are represented by thier own VH (consider: a root node will never be found

 // as a characteristic value); leaf nodes (= products) are not required as characteristic values.

    join hrrp_node as node on  node.hryid = hierarchy.hryid 
                           and node.hryver = hierarchy.hryver
                           and node.hryvalto = hierarchy.hryvalto
                           and node.nodetype = 'N'
                                                             
    association [0..1] to I_ProdUnivHierNodeText_2 as _Text on  _Text.ProdUnivHierarchy = hierarchy.hrysid 
                                                            and _Text.HierarchyNode = node.hrynode
                                                            and _Text.ProdHierarchyValidityEndDate = hierarchy.hryvalto
                                                            and _Text.Language = $session.system_language                                                                                                   
{
   key cast(hierarchy.hrysid as prod_hier_id preserving type ) as ProductHierarchy,
   
   @ObjectModel.text.element: ['ProdUnivHierarchyNodeText']
   key node.nodevalue as ProductHierarchyNode,

// Since there could potentially be more than one text description for different validity end dates of the same

// PH / PH node, we ensure by the MAX operator that only one text description will be chosen. In reality all text descriptions

// for all validity end dates of the same PH / PH node are the same.

   key max(hierarchy.hryvalto) as ProdHierarchyValidityEndDate,
   
   // Required to fulfill DCL checks

   key dcassignment.SalesOrganization as SalesOrganization,

// Required to fulfill DCL checks

   key dcassignment.DistributionChannel as DistributionChannel,
   
   @Semantics.text: true
   _Text.ProdUnivHierarchyNodeText as ProdUnivHierarchyNodeText
} 

where hierarchy.hrytyp = 'MD01' //Select only relevant product hierarchies.


group by hierarchy.hrysid,
         node.nodevalue,
         _Text.ProdUnivHierarchyNodeText,
         dcassignment.SalesOrganization,
         dcassignment.DistributionChannel


/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PRODHIERTODISTRCHNASSGMT",
"I_PRODUNIVHIERNODETEXT_2",
"HRRP_DIRECTORY",
"HRRP_NODE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/