S/4HANA Multi Level BOM Unboxed: Navigating BOM Hierarchy: A Recursive Adventure - Part1

[VarunVarshney]

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(Bottom to Top Approach - but Vice-Versa Analytics is same )

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.

[TinaPetry]

Dear Varun,

great blog - this is exactly what I need for my customer requirement.

I'm a beginner concerning CDS Views - to get familiar with Yout solution I tried to take it over to the customers system. Unfortunately I get error for the first CDS View ZTESTSTPOEQSTEQUI which selects from I_BillOfMaterialItemBasic: system says that _BOMEqstEqui is not known.

On which SAP release You has implemented the CDS Views?
My customer system has release S4HANA ON PREMISE 2020. I tried it also on a system with release S4HANA ON PREMISE 2021, I get the same error.

Would it be possible, that You post the code of CDS View I_BillOfMaterialItemBasic? Then I can create it as Z-CDS View in my system.

Thanks very much and kind regards
Tina

[VarunVarshney]

Hello tina_petry
I appreciate you reading this blog.
However, ZTESTSTPOEQSTEQUI- its a local/Test CDS created by me on my personal system which wont be available to others.
You can try creating by copy pasting the code but changing the CDS View name to any name you want.
I_BillOfMaterialItemBasic - standard CDS View which exists in every system.

[TinaPetry]

Dear Varun,

thanks very much for You fast response!

I'm trying to describe my problem better. This is my CDS View code (copied from Your code):

@AbapCatalog.sqlViewName: 'ZTPYSTPOEQSTEQUI'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Comprehensive CDS View (CDS1)'

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

I get errors for the rows, where _BOMEqstEqui. , _ComponentBOMEquip. ... are used:

CDS View errors

Systems says i.g.: Table name _BOMEqstEqui is not know or is shadowed by Alias.

I don't know, how I can solve this. I_BillOfMaterialItemBasic doesn't contains this elements.

Thanks very much and kind regards
Tina

[VarunVarshney]

Yes because these are specific associations which doesnt exists in CDS View "I_BILLOFMATERIALITEMBASIC" . You can remove those lines and associations. With more practice , you will get to know about all these.

define view ZTESTSTPOEQSTEQUI

  as select from I_BillOfMaterialItemBasic

{

  key          BillOfMaterialCategory,

  key          BillOfMaterial,

  key          BillOfMaterialItemNodeNumber,

  key          BOMItemInternalChangeCount,

               BillOfMaterialComponent

      



          

[TinaPetry]

Thanks very much  - I give my best and try to understand everything.

I will try to take over the principle from your example in my customer CDS view to get a BoM explsion. If it works, I'll post the code. 🙂