Wednesday, 15 January 2020

ABAP CDS recursive association using hierarchy

Introduction


In this article, I will show a practical example of self-association in CDS. You will learn how to define a simple hierarchy in your CDS and how to consume it in your ABAP code.

The problem


Recently I have faced a quite interesting problem. There is a concept of something like ‘frame contract’. It means that out of this document many others of the same type (or not necessairely) can be created. Let’s take a look at VBFA table (Sales Document Flow). I am interested only in subsequent Sales Orders, therefore I have filtered out only documents of category ‘C’:

SAP ABAP CDS, SAP ABAP Tutorial and Materials, SAP ABAP Learning, SAP ABAP Online Exam, SAP ABAP Learning

What we can see is a ‘chain’ of documents, or even to be more specific – a tree. Let’s take a look at document 24. More friendly and eye-catching representation of the relationship between those documents would be:

SAP ABAP CDS, SAP ABAP Tutorial and Materials, SAP ABAP Learning, SAP ABAP Online Exam, SAP ABAP Learning

Root parent of the above tree would be document 24 – our frame contract. Below it you will see it’s ‘children’ – subsequent documents. Now imagine that you would like to extract all documents related to your frame contract. Lot’s of twisted ABAP! Maybe there is an easier way of solving this riddle?

Gentle solutions


Yes! Fortunately, there is – we can avoid looping, reading, assigning and dumping.

CDS

We can define a CDS view which would have an association with itself. Let’s take a look:

define view Z_VBFA
  as select from vbfa
  association [0..1] to Z_VBFA as _Recursion on $projection.PrecedingDocument = _Recursion.SubsequentDocument
                                             and $projection.PrecedingItem = _Recursion.SubsequentItem
{
  key vbelv   as PrecedingDocument,
  key posnv   as PrecedingItem,
      vbeln   as SubsequentDocument,
      posnn   as SubsequentItem,
      vbtyp_n as SubsequentDocCategory,
      _Recursion
}
where
  vbtyp_n = 'C'

I have defined a view Z_VBFA based on VBFA and added an association to Z_VBFA. As you can see preceding documents will be treated as parents and subsequent ones as children. The cardinality of the above association is [0..1] as there cannot be any multiple parents – one contract can only have one preceding document, however it can have multiple children. In order to allow consumption of such CDS annotation needs to be added:

@Hierarchy.parentChild: [ { name: 'Recursion', recurseBy: '_Recursion' } ]

It defines parent-child hierarchy and points at our self-association. There is also a second way of defining a hierarchy:

@Hierarchy.parentChild: { name: 'Recursion',
                          recurse : { parent: ['PrecedingDocument','PrecedingItem'], child ['SubsequentDocument','SubsequentItem'] }
}

I believe that the first one is much nicer. Remember that in that approach you should not use association.

Hierarchy


You can also define a hierarchy object Z_VBFA_HIERARCHY which would look like this:

define hierarchy Z_VBFA_HIERARCHY 
  as parent child hierarchy (
    source Z_VBFA
    child to parent association _Recursion
    siblings order by PrecedingDocument ascending, PrecedingItem ascending
    orphans root
  )
{
    key PrecedingDocument,
    key PrecedingItem,
    SubsequentDocument,
    SubsequentItem,
    $node.parent_id as ParentNode,
    $node.node_id as ChildNode,
    $node.hierarchy_is_orphan as HierarchyIsOrphan,
    $node.hierarchy_level as HierarchyLevel,
    $node.hierarchy_rank as HierarchyRank,
    $node.hierarchy_parent_rank as HierarchyParentRank,
    $node.hierarchy_tree_size as HierarchyTreeSize  
}

◉ Source – Z_VBFA – previously defined view with self-association
◉ Child to parent association – points at recursion association in the source
◉ Siblings order – order of sibling nodes
◉ Orphans root – orphans will be treated as roots. In our case contract 24 would be such node.
◉ Multiple parents – this syntax is not used here as multiple parents are not allowed in my case

I have also listed out all $node fields. These are hierarchy-specific fields describing a hierarchy. We will take a look at them later. After running the hierarchy you will see a standard table view:

SAP ABAP CDS, SAP ABAP Tutorial and Materials, SAP ABAP Learning, SAP ABAP Online Exam, SAP ABAP Learning

Gentle consumption


Now let’s take advantage of the work we have done here and let’s consume our objects in ABAP.

We will Focus on three of them:

1. HIERARCHY – generates a hierarchy
2. HIERARCHY_DESCENDANTS – Returns all descendants of a set of start nodes in a hierarchy.
3. HIERARCHY_ANCESTORS – Returns all ancestors of a set of start nodes in a hierarchy.

Let’s implement it in ABAP.

HIERARCHY


A hierarchy from CDS view with defined hierarchy will be generated.

SELECT parent_id AS parent,
         node_id AS node,
         hierarchy_level AS level,
         hierarchy_rank AS rank,
         hierarchy_tree_size AS tree_size
    FROM HIERARCHY( SOURCE z_vbfa
                    CHILD TO PARENT ASSOCIATION _recursion
                    START WHERE precedingdocument = '0000000024' AND precedingitem = '000000'
                    SIBLINGS ORDER BY precedingdocument ASCENDING, precedingitem ASCENDING )
    INTO TABLE @DATA(lt_hierarchy).

Selected fields are hierarchy nodes that are always available and describe the structure. You need to select them explicitly and add an alias. Let’s take a look at the SOURCE:

◉ CHILD TO PARENT ASSOCIATION – points at recursive association in the view
◉ START WHERE – points at a starting node in the hierarchy

Result:

INDEX PARENT NODE  LEVEL  RANK  TREE_SIZE 
10,0000000024;6,000000 10,0000000025;6,000000   0 11
10,0000000025;6,000000   10,0000000067;6,000000   0
10,0000000025;6,000000   10,0000000061;6,000000  0
10,0000000025;6,000000   10,0000000060;6,000000   0
10,0000000025;6,000000   10,0000000063;6,000000  0
10,0000000025;6,000000   10,0000000064;6,000000 
10,0000000025;6,000000   10,0000000065;6,000000  0
10,0000000025;6,000000   10,0000000066;6,000000   0
10,0000000025;6,000000   10,0000000068;6,000000   0
10  10,0000000068;6,000000  10,0000000069;6,000000   0
11  10,0000000068;6,000000   10,0000000070;6,000000  0

We get all relationships between nodes, starting from contract 24. What’s great all children are listed out in the node column, so our goal has been achieved. Let’s dig deeper into it however and take a look at hierarchy specific fields which may help us with traversing a hierarchy. Below pictures explain two of them:

SAP ABAP CDS, SAP ABAP Tutorial and Materials, SAP ABAP Learning, SAP ABAP Online Exam, SAP ABAP Learning

SAP ABAP CDS, SAP ABAP Tutorial and Materials, SAP ABAP Learning, SAP ABAP Online Exam, SAP ABAP Learning

HIERARCHY_DESCENDANTS


In this example however we are going to consume our CDS hierarchy directly.

  SELECT *
    FROM HIERARCHY_DESCENDANTS( SOURCE z_vbfa_hierarchy
                                START WHERE precedingdocument = '0000000024' 
                                        AND precedingitem = '000000' )
    INTO TABLE @DATA(lt_descendants).

Result:

INDEX PRECEDING
DOCUMENT
PRECEDING
ITEM
SUBSEQUENT
DOCUMENT
SUBSEQUENT
ITEM
PARENT
NODE
0000000024 0 0000000025 10,0000000024;6,000000
0000000025 0 0000000067  10,0000000025;6,000000
0000000025 0 0000000061  10,0000000025;6,000000 
0000000025 0 0000000060  10,0000000025;6,000000
0000000025 0 0000000063  10,0000000025;6,000000  
0000000025 0 0000000064 10,0000000025;6,000000  
0000000025 0 0000000065  10,0000000025;6,000000  
0000000025 0 0000000066  10,0000000025;6,000000  
0000000025 0 0000000068  10,0000000025;6,000000  
10  0000000068 0 0000000069 10  10,0000000068;6,000000 
11  0000000068 0 0000000070 11  10,0000000068;6,000000

CHILD
NODE
HIERARCHY
ISORPHAN
PRECEDING
ITEM
HIERARCHY
PARENTRANK
HIERARCHY
RANK
HIERARCHY
TREESIZE
10,0000000025;6,000000 1 1 0 2 11
10,0000000067;6,000000 1 2 2 1
10,0000000061;6,000000   1 2 2 1
10,0000000060;6,000000   1 2 2 1
10,0000000063;6,000000   1 2 2 1
10,0000000064;6,000000   1 2 2 1
10,0000000065;6,000000 1 2 2 1
10,0000000066;6,000000   1 2 2 1
10,0000000068;6,000000 1 2 2 10  3
10,0000000069;6,000000  1 3 10 11  1
10,0000000070;6,000000   1 3 10 12  1

I used * sign in select, therefore, all fields from defined earlier CDS hierarchy were selected. Notice that in this case Z_VBFA_HIERARCHY is consumed, not Z_VBFA.

HIERARCHY_ANCESTORS


This function will return the ancestors of a given node.

SELECT *
    FROM HIERARCHY_ANCESTORS( SOURCE z_vbfa_hierarchy
                              START WHERE precedingdocument = '0000000024' 
                                      AND precedingitem = '000000' )
    INTO TABLE @DATA(lt_ancestors).

Result:

INDEX PRECEDING
DOCUMENT
PRECEDING
ITEM
SUBSEQUENT DOCUMENT SUBSEQUENT
ITEM
PARENTNODE
0000000024 0 0000000025 0 10,0000000024;6,000000

CHILDNODE HIERARCHY
ISORPHAN
HIERARCHY
LEVEL
HIERARCHY
RANK
HIERARCHY
PARENTRANK
HIERARCHY
TREESIZE
10,0000000025;6,000000  1 1 2 0 11

I have added orphans root annotation therefore one relationship in the hierarchy is displayed.

Alternative syntax


You can combine HIERARCHY and HIERARCHY_DESCENDANTS/HIERARCHY_ANCESTORS:

SELECT
    FROM HIERARCHY_DESCENDANTS( SOURCE HIERARCHY( SOURCE z_vbfa
                                                    CHILD TO PARENT ASSOCIATION _recursion
                                                    START WHERE precedingdocument = '0000000024'
                                                            AND precedingitem = '000000'
                                                    SIBLINGS ORDER BY precedingdocument ASCENDING, precedingitem ASCENDING )
    START WHERE precedingdocument = '0000000024' AND precedingitem = '000000' )
    FIELDS    node_id,
              parent_id,
              hierarchy_rank,
              hierarchy_level,
              hierarchy_tree_size,
              hierarchy_distance
    INTO TABLE @DATA(lt_descendants_2).

It will dynamically return hierarchy and apply HIERARCHY_DESCENDANTS on the result. Notice that we are not using Z_VBFA_HIERARCHY here but Z_VBFA. The result will be exactly the same as in HIERARCHY_DESCENDANTS.

No comments:

Post a Comment