Introduction

Greetings, fellow SAP enthusiasts! Today, we’re diving into the intricate world of Bill of Materials (BOM) and tackling a challenging yet fascinating user requirement. Our mission? To develop a Report that navigates through the recursive nature of BOM data, providing users with a comprehensive view of the hierarchy.

The Challenge

The provided scenario outlines a case requirement wherein the described approach can be employed whenever recursion is necessary, contingent upon external specifications.

Our user wants to start with a material and a specific field, the serial number, from the STPO table. The goal is to traverse through the BOM data, then explore related tables (STKO, EQST, EQUI) to finally present a detailed hierarchy of BOM and its related Composite Equipment’s info like materials and serial numbers.

The Recursive Journey

Initial Query:

User provides a material and serial number from STPO.

Retrieve the corresponding BOM and its hierarchy.

Composite Equipment Exploration:

Obtain the Composite Equipment from EQST related to the BOM.

Extract material and serial numbers from EQUI.

Cycle Continuation:

Take the obtained material and serial number.

Query STPO for a BOM related to the component and specific field serial number.

Repeat the process until no further BOMs are found in STPO.

Hierarchy Presentation:

Display all entries from STPO BOM Item table.

Show hierarchy levels, starting from the parent (topmost hierarchy level 1) and propagating to its children (hierarchy level +1).

Note: I will not be going to basics of S/4 HANA and CDS Modelling. I am assuming that its a completed pre-requisite. 

Absolutely, let’s get into the nitty-gritty of implementing this recursive scenario in SAP S/4HANA using CDS views:

1. Create a Comprehensive CDS View (CDS1):

Assuming you’ve covered the basics of CDS modeling, craft a CDS view named CDS1 that encompasses all the necessary information from the STPO table, equipment details, material numbers, and serial numbers. This consolidated view is the backbone of your recursive journey.

define view ZTESTSTPOEQSTEQUI
  as select from I_BillOfMaterialItemBasic
{
  key          BillOfMaterialCategory,
  key          BillOfMaterial,
  key          BillOfMaterialItemNodeNumber,
  key          BOMItemInternalChangeCount,
               _BOMEqstEqui.Equipment    as Equipment,
               _BOMEqstEqui.Material     as CompositeMaterial,
               _BOMEqstEqui.SerialNumber as Compositeserialnumber,
               SerialNumber              as MountSerialNumber,
               BillOfMaterialComponent,
               BatchNumber,

               /* Associations */
               _BOMEqstEqui,
               _ComponentBOMEquip,
               _Recursion

}

2. Create Recursive Association (CDS1 Association):

Extend CDS1 with a recursive association, enabling the loop back to itself for further BOM exploration. This ensures the recursive nature of the data retrieval.

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

It defines parent-child hierarchy and points at our self-association.

@Hierarchy.parentChild: [ { name: 'Recursion', recurseBy: '_Recursion' } ]
define view ZTESTSTPOEQSTEQUI
  as select from I_BillOfMaterialItemBasic
  association [0..1] to ZTESTSTPOEQSTEQUI as _Recursion on  $projection.CompositeMaterial     = _Recursion.BillOfMaterialComponent
                                                        and $projection.Compositeserialnumber = _Recursion.MountSerialNumber
{
  key          BillOfMaterialCategory,
  key          BillOfMaterial,
  key          BillOfMaterialItemNodeNumber,
  key          BOMItemInternalChangeCount,
               _BOMEqstEqui.Equipment    as Equipment,
               _BOMEqstEqui.Material     as CompositeMaterial,
               _BOMEqstEqui.SerialNumber as Compositeserialnumber,
               SerialNumber              as MountSerialNumber,
               BillOfMaterialComponent,
               BatchNumber,

               /* Associations */
               _BOMEqstEqui,
               _ComponentBOMEquip,
               _Recursion

}

3. Hierarchical CDS View (CDS2):

Create a hierarchical CDS view (CDS2) building upon the recursive association. Utilize the $node function to define the hierarchy levels.

define hierarchy ZTESTSTPOEQSTEQUIHier1
  as parent child hierarchy(
    source ZTESTSTPOEQSTEQUI
    child to parent association _Recursion
    siblings order by
      BillOfMaterial               ascending,
      BillOfMaterialItemNodeNumber ascending
    multiple parents allowed
    orphans root

  )
{
  key          BillOfMaterialCategory,
  key          BillOfMaterial,
  key          BillOfMaterialItemNodeNumber,
  key          BOMItemInternalChangeCount,
               Equipment,
               CompositeMaterial,
               Compositeserialnumber,
               BillOfMaterialComponent,
               MountSerialNumber,
               BatchNumber,
               $node.parent_id       as ParentNode,
               $node.node_id         as ChildNode,
               $node.hierarchy_level as HierarchyLevel
}

4. Result CDS View (CDS3):

Lastly, create a CDS view (CDS3) to consume the hierarchical data and present it in a user-friendly manner.

define view ZTESTSTPOEQSTEQUIHier2
  as select from ZTESTSTPOEQSTEQUIHier1
{
  key     BillOfMaterialCategory,
  key     BillOfMaterial,
  key     BillOfMaterialItemNodeNumber,
  key     BOMItemInternalChangeCount,
          Equipment,
          CompositeMaterial,
          Compositeserialnumber,
          BillOfMaterialComponent,
          MountSerialNumber,
          BatchNumber,
          ParentNode,
          ChildNode,
          HierarchyLevel
}

Let’s extend the existing scenario to include a function that retrieves the ancestors of a given node in the hierarchy. We’ll call this function ” HIERARCHY_ANCESTORS ” .This function will return the ancestors of a given node.

SELECT *
FROM HIERARCHY_ANCESTORS( SOURCE ZTESTSTPOEQSTEQUIHier1
                           START WHERE BillOfMaterialComponent = @lv_component
                           AND MountSerialNumber = @lv_mountserialno
                            )
INTO TABLE @DATA(lt_ancestors).

where ” lv_component ” is the user input material and  ” lv_mountserialno ”  is the user input serial corresponding to specific field in STPO.

In my Case , for example
lv_component = NX_CONSTIT_FANT_1

lv_mountserialno = 100

BILLOFMATERIALCATEGORY  BILLOFMATERIAL  BILLOFMATERIALITEMNODENUMBER  BOMITEMINTERNALCHANGECOUNT  EQUIPMENT           COMPOSITEMATERIAL  COMPOSITESERIALNUMBER  BILLOFMATERIALCOMPONENT  MOUNTSERIALNUMBER   BATCHNUMBER  PARENTNODE                                CHILDNODE                                   HIERARCHYLEVEL  
E                       00000805        00000002                      00000004                    000000000010132848  D261959847-0103    000000000000000195     NX_CONSTIT_FANT_1        000000000000000100               15,D261959847-0103;18,000000000000000195  17,NX_CONSTIT_FANT_1;18,000000000000000100  3               
E                       00000476        00000004                      00000008                    000000000010132348  LG_CONSTIT_ZT04    000001231212424683     D261959847-0103          000000000000000195               15,LG_CONSTIT_ZT04;18,000001231212424683  15,D261959847-0103;18,000000000000000195    2               
E                       00000548        00000004                      00000008                    000000000010132427  LG_CONSTIT_ZT03    000001231212424749     LG_CONSTIT_ZT04          000001231212424683               15,LG_CONSTIT_ZT03;18,000001231212424749  15,LG_CONSTIT_ZT04;18,000001231212424683    1               

Certainly. In the given context, the term “hierarchy level = 1” designates the highest-ranking Header Bill of Materials (BOM). The ascending hierarchy values signify the progression from a Parent BOM to its respective Child BOM. The terms “Parent Node” and “Child Node” with the string type denote the hierarchical relationship, elucidating the association between a parent assembly and its associated child BOM details. So now we can play with this data as we want to display.

Indeed, this methodology facilitates navigation throughout the entire Bill of Materials (BOM) structure based on specific needs. While alternative approaches exist, my intention was to succinctly demonstrate this particular BOM explosion scenario.

In the forthcoming blogs, we will delve into the creation of Report that will leverage and consume this information.

I humbly welcome your comments/feedback/suggestions. Your continued engagement is encouraged, so stay tuned for more updates by following.