Problem Statement:-
I came around a requirement to create a fiori applications which involves BOM and had to list down all components of Material considering the multi level explosion.
Considering this data forming the base for the overall fiori application, we can not build a logic using CDS view only, as this involves selecting components underneath each component.
So the only approach is to write a recursive logic to fetch all the related components.
Brief Introduction:-
Definition:
A BOM is a comprehensive list of materials, their parts, and components required in the manufacturing of products. Comprehensive is a crucial descriptor, meaning it must include all critical details, such as quantities, prices, and descriptions.
It is also called an assembly component list, product recipe, or product structure. All of these terms describe its essence.
For example:
A company manufactures laptops. The BOM should list all the major parts: CPU, motherboard, keyboard, battery, RAM, touchpad, optical drive, storage device, cooling fan, speaker, and monitor. Under each listed part are the components within them, and each should have detailed descriptions that include the quantity and the price of each.
Types of BOM:
There are two types of BOM: single-level and multi-level bill of materials.
- Single-Level
- This is a BOM in its simplest form and is reminiscent of a shopping list. It merely enumerates the assembly and sub-assembly parts and their quantities.
A single-level BOM doesn’t show the relationship between the parts, which makes it difficult to identify the causes of assembly failure.
- Multi-Level
- This outlines the relationships between assemblies and sub-assemblies. It is sometimes referred to as an indented bill of materials—the indentions signify sub-assemblies or the components that make up an assembled product.
Approach:-
Created CDS View Entity (to select the basic data from underlining tables) and Table Function (to fetch the component data considering multi-level explosion).
Since the data selection involves multi source (table / standard CDS), we have created a basic CDS view entity which will select the data from the standard CDS view, and then we can utilize the custom CDS view entity in our table function.
Standard CDS view used:-
CDS View Name |
Table Involved |
Description |
I_ProductionVersion |
MKAL |
Production Version (assigned to materials in MRP4 View) |
I_Mast |
MAST |
Material to BOM Link |
I_BOMSelectionEAM |
STAS |
Bill of Material Item Selection |
I_BillOfMaterial |
STKO |
BOM Header Data |
I_BillOfMaterialItem |
STPO |
BOM Item Data |
I_Product |
MARA |
Material Master Details |
As one material can have multiple production version assigned in MRP4 View (in MM01/02), we are considering the assumption to pick up the minimum production version from MKAL.
Below are the code snippets:-
CDS View Entity : ZI_BOMProductionVersion
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{ serviceQuality: #C, sizeCategory: #L, dataClass: #MIXED }
@EndUserText.label: 'BOM Production Version'
define view entity ZI_BOMProductionVersion
with parameters
p_datum : datum
as select from I_ProductionVersion
{
key Material,
key Plant,
min(ProductionVersion) as ProductionVersion
}
where
ProductionVersionIsLocked = ' '
and ValidityStartDate <= $parameters.p_datum
and ValidityEndDate >= $parameters.p_datum
group by
Material,
Plant
CDS View Entity : ZI_BillOfMaterial_ML
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Bill of Material - Multi Level Explosion'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{ serviceQuality: #C, sizeCategory: #L, dataClass: #MIXED }
define view entity ZI_BillOfMaterial_ML
with parameters
p_datum : datum
as select from ZI_BOMProductionVersion( p_datum: $parameters.p_datum ) as _ProdVersion
inner join I_ProductionVersion as _ProductionVersion on _ProductionVersion.Material = _ProdVersion.Material
and _ProductionVersion.Plant = _ProdVersion.Plant
and _ProductionVersion.ProductionVersion = _ProdVersion.ProductionVersion
inner join I_Mast as _MaterialBOM on _MaterialBOM.Material = _ProductionVersion.Material
and _MaterialBOM.Plant = _ProductionVersion.Plant
and _MaterialBOM.BillOfMaterialVariantUsage = '1'
and _MaterialBOM.BillOfMaterialVariant = _ProductionVersion.BillOfMaterialVariant
inner join I_BOMSelectionEAM as _BOMItems on _BOMItems.BillOfMaterialCategory = 'M'
and _BOMItems.BillOfMaterial = _MaterialBOM.BillOfMaterial
and _BOMItems.BillOfMaterialVariant = _MaterialBOM.BillOfMaterialVariant
inner join I_BillOfMaterial as _BOMHeader on _BOMHeader.BillOfMaterialCategory = 'M'
and _BOMHeader.BillOfMaterial = _MaterialBOM.BillOfMaterial
and _BOMHeader.BillOfMaterialVariant = _MaterialBOM.BillOfMaterialVariant
inner join I_BillOfMaterialItem as _BOMItem on _BOMItem.BillOfMaterialCategory = 'M'
and _BOMItem.BillOfMaterial = _BOMItems.BillOfMaterial
and _BOMItem.BillOfMaterialItemNodeNumber = _BOMItems.BillOfMaterialItemNodeNumber
inner join I_Product as _Product on _Product.Product = _BOMItem.BillOfMaterialComponent
and _Product.IsMarkedForDeletion = ''
{
key _ProductionVersion.Material as Material,
key _ProductionVersion.Plant as Plant,
_ProductionVersion.ProductionVersion as ProductionVersion,
_ProductionVersion.ValidityStartDate as ValidityStartDate,
_ProductionVersion.ValidityEndDate as ValidityEndDate,
@Semantics.quantity.unitOfMeasure: 'BOMHeaderQtyUnit'
cast( _BOMHeader.BOMHeaderQuantityInBaseUnit as bstmg preserving type ) as BOMHeaderQty,
_BOMHeader.BOMHeaderBaseUnit as BOMHeaderQtyUnit,
_BOMItem.BillOfMaterialComponent as Component,
@Semantics.quantity.unitOfMeasure: 'BOMItemQtyUnit'
_BOMItem.BillOfMaterialItemQuantity as BOMItemQty,
_BOMItem.BillOfMaterialItemUnit as BOMItemQtyUnit,
_BOMItem.ComponentScrapInPercent as ComponentScrapInPercent,
_BOMItem.ValidityStartDate as BOMValidityStartDate,
_BOMItem.ValidityEndDate as BOMValidityEndDate,
_Product.BaseUnit as BaseUnit,
_BOMItem.MaterialIsCoProduct as CoProduct
}
where
_BOMItem.ValidityStartDate <= $parameters.p_datum
and _BOMItem.ValidityEndDate >= $parameters.p_datum
Table Function : ZI_BOM_Components
@ClientHandling.type: #CLIENT_DEPENDENT
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Get BOM Components (Multi Level)'
define table function ZI_BOM_Components
with parameters
@Environment.systemField: #CLIENT
p_clnt : abap.clnt,
p_matnr : matnr,
p_werks : werks_d,
p_datum : datum
returns
{
key Mandt : abap.clnt;
key Material : matnr;
Plant : werks_d;
ProductionVersion : verid;
ValidityStartDate : adatm;
ValidityEndDate : bdatm;
BOMHeaderQty : basmn;
BOMHeaderQtyUnit : basme;
BOMLevel : abap.int4;
Parent : matnr;
Component : matnr;
BOMItemQty : kmpmg;
BOMItemQtyUnit : kmpme;
ComponentScrapInPercent : kausf;
BOMValidityStartDate : datuv;
BOMValidityEndDate : datuv;
BaseUnit : meins;
CoProduct : kzkup;
}
implemented by method
ZCL_GET_BOM_COMPONENTS=>get_bom_component
Class : ZCL_GET_BOM_COMPONENTS
CLASS zcl_get_bom_components DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS get_bom_component FOR TABLE FUNCTION zi_bom_components .
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS zcl_get_bom_components IMPLEMENTATION.
METHOD get_bom_component
BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
USING zi_billofmaterial_ml.
*** variable declaration
declare lv_count integer;
declare lv_level integer;
***initialize the BOM Level = 1
lv_level = 1;
***select the data for 1st Level BOM
root_bom = select mandt,
material,
plant,
productionversion,
validitystartdate,
validityenddate,
bomheaderqty,
bomheaderqtyunit,
:lv_level as bomlevel,
material as parent,
component,
bomitemqty,
bomitemqtyunit,
componentscrapinpercent,
bomvaliditystartdate,
bomvalidityenddate,
baseunit,
coproduct
from zi_billofmaterial_ml( p_datum => :p_datum )
where mandt = :p_clnt
and material = :p_matnr
and plant = :p_werks;
***pass the selected BOM data into OUT_BOM
out_bom = select * from :root_bom;
***select the components from the 1st Level BOM
components = select component from :root_bom;
***select the count of components from the 1st Level BOM
select count ( * ) into lv_count from :components;
***if the components are found, check for next level BOM data
while :lv_count > 0 do
***increment the BOM level
lv_level = lv_level + 1;
***select the data for the BOM of components
child_bom = select a.mandt,
p_matnr as material,
a.plant,
a.productionversion,
a.validitystartdate,
a.validityenddate,
a.bomheaderqty,
a.bomheaderqtyunit,
:lv_level as bomlevel,
b.component as parent,
a.component,
a.bomitemqty,
a.bomitemqtyunit,
a.componentscrapinpercent,
a.bomvaliditystartdate,
a.bomvalidityenddate,
a.baseunit,
a.coproduct
from zi_billofmaterial_ml( p_datum => :p_datum ) as a
inner join :components as b
on a.material = b.component and
a.plant = :p_werks;
***select the components from the above selected child BOM
components = SELECT component from :child_bom;
***select the count of components from the above selected child BOM
*** if the count of component from this level is 0, then while loop will be terminated
select COUNT ( * ) into lv_count from :components;
***merge the BOM data (OUT_BOM) with the components from current level in selection
out_bom = select * from :out_bom
union all
select * from :child_bom;
end while;
***return the data back to table function using RETURN
return select * from :out_bom;
endmethod.
ENDCLASS.
Hope this helps.
Thanks,
Tarun Gambhir
