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.
No comments:
Post a Comment