Bill of Materials can play a crucial role in identifying relevant vendors for articles sold in retail. Let us imagine we are selling a product that is assembled or produced in the shop or in the warehouse. A typical example can be steak sold in a supermarket. It is obvious a steak is not purchased from vendors in the packaging it is sold. From a technical point of view it may be challenging assigning steak sales to vendors. To solve this we have to use Bill Of Materials (BOM). In the post below I will give examples how BOM for product articles can be extracted from SAP Retail and how it can be used in the vendor split logic for Vendor P&L reporting.
Vendor Split for Fresh Food
There are numerous ways for assigning vendor codes to sales and stock movements. When we want to construct a Profit & Loss Statement by vendor we have to have business logic on how to allocate sales by vendor. For example we may want to use the following logic for fresh food products:
- Look up for purchases for the article during last 2 months
- If not found look up all parent articles in BOM
- Look up for purchases for all parent articles during last 2 months
- Look up for purchases for the article during last 3 months
- …
- Look up for purchases for the article during last 12 months
- If not found look up for purchases for all parent articles during last 12 months
- If no purchases found apply Main vendor
Here is a data flow example where BOM may be used in the vendor split logic:
Extract BOM from Retail
Bill of Material is stored in three tables in SAP Retail:
We can design a database view (Transaction SE11) where these three tables are linked. The link between MAST & STAS: stlnr+stlal; the link between STAS & STPO : stlty+stlnr+stlkn. If we want to extract BOM for articles only we have to apply filter: STPO-STLTY = ‘M’.
The view may have the following fields (columns):
MATNR CHAR 18 Article Number
IDNRK CHAR 18 BOM component
MANDT CLNT 3 Client
WERKS CHAR 4 Site
STLAN CHAR 1 BOM Usage
STLNR CHAR 8 Bill of material
STLAL CHAR 2 Alternative BOM
STLTY CHAR 1 BOM category
STLKN NUMC 8 BOM item node number
STPOZ NUMC 8 Internal counter
It is a matter of just a few button clicks for designing a generic extractor based on the view defined above (Transaction RSO2). Selection fields for the extractor can be Article Number and BOM component.
Masterdata tables used in the view usually contain a few hundred thousand records or less, therefore running full load for the complete BOM should not pose performance issues.
Apply BOM in Vendor Split
We have to store article BOM in BW in order to access it from a transformation. I suggest using a DSO with two key fields:
- Article Parent
- Article Child
With this we can identify parent articles for a child and apply vendor split for children based on parent article purchases.
We can use a full load to the DSO containing BOM: O_ARTBOM. Therefore, BOM relationships can be used in other transformations containing vendor split logic.
Below is an ABAP code example in the start routine of a transformation connecting source and the “P&L by vendor” DSO:
REFRESH et_target.
LOOP AT SOURCE_PACKAGE ASSIGNING <ls>.
REFRESH vspl.
…
READ TABLE vspl_all WITH TABLE KEY MATERIAL =
<ls>-/BIC/CHARTICLE
CALMONTH = pto TRANSPORTING NO FIELDS.
IF sy-subrc <> 0.
READ TABLE vspl_all WITH TABLE KEY MATERIAL =
<ls>-/BIC/CHARTICLE
CALMONTH = pfr TRANSPORTING NO FIELDS.
ENDIF.
* --- Find last period with purchases for this product & BOM
DATA: tmon TYPE n length 3. tmon = '11'.
TYPES: t_bom TYPE /BIC/AO_ARTBOM00, tsbom TYPE STANDARD TABLE OF t_bom.
FIELD-SYMBOLS: <bom> TYPE t_bom.
DATA : tab_bom TYPE tsbom, tb2 TYPE tsbom.
* --- If purchases not found Build list of articles from BOM
IF sy-subrc <> 0.
SELECT * FROM /BIC/AO_ARTBOM00 INTO TABLE tab_bom
WHERE MATERIAL = <ls>-/BIC/CHARTICLE.
IF sy-subrc = 0.
LOOP AT tab_bom ASSIGNING <bom>.
SELECT * FROM /BIC/AO_ARTBOM00 INTO TABLE tb2
WHERE MATERIAL = <bom>-/BIC/CHARTICLE.
IF sy-subrc = 0. APPEND LINES OF tb2 TO tab_bom. ENDIF.
ENDLOOP.
ELSE.
found_vs = 'm'.
ENDIF.
WHILE ( found_vs = 'n' OR found_vs = 'm' ) AND tmon > 0.
IF found_vs <> 'm'. " If there is BOM
SELECT * FROM /BIC/AO_VENSPL00
INTO CORRESPONDING FIELDS OF TABLE vspl
FOR ALL ENTRIES IN tab_bom WHERE
MATERIAL = tab_bom-/BIC/CHARTICLE AND
CALMONTH >= pfr AND CALMONTH <= pto.
IF sy-subrc = 0. found_vs = 'b'. ENDIF.
ENDIF.
IF found_vs <> 'b'. " Check for original article
pfr = pfr - 1.
IF pfr+4(2) = '00'. pfr = pfr(4) - 1.
CONCATENATE pfr+2(4) '12' INTO pfr.
ENDIF.
READ TABLE vspl_all WITH TABLE KEY MATERIAL =
<ls>-/BIC/CHARTICLE
CALMONTH = pfr TRANSPORTING NO FIELDS.
IF sy-subrc = 0. found_vs = 'y'. ENDIF.
tmon = tmon - 1.
ENDIF.
ENDWHILE.
ELSE.
found_vs = 'y'.
ENDIF.
ELSE.
…
ENDIF.
* Select all relevant purchases for vendor split
IF found_vs = 'n' OR found_vs = 'y'.
LOOP AT vspl_all ASSIGNING <vs> WHERE MATERIAL =
<ls>-/BIC/CHARTICLE.
IF <vs>-CALMONTH >= pfr AND <vs>-CALMONTH <= pto.
APPEND <vs> TO vspl.
found_vs = 'y'.
ENDIF.
ENDLOOP.
…
ENDIF.
IF found_vs = 'y' OR found_vs = 'b'.
* Calculate total article purchased AND Group by vendor
tot = vtot = 0. curv = 'uno'. REFRESH vs_comp.
SORT vspl BY VENDOR ASCENDING.
LOOP AT vspl INTO cvl.
IF cvl-VENDOR <> curv AND curv <> 'uno'.
lastv-/BIC/KFQTRCUB = vtot.
APPEND lastv TO vs_comp.
vtot = 0.
ENDIF.
lastv = cvl.
curv = cvl-VENDOR.
vtot = vtot + cvl-/BIC/KFQTRCUB.
tot = tot + cvl-/BIC/KFQTRCUB.
ENDLOOP.
lastv-/BIC/KFQTRCUB = vtot.
APPEND lastv TO vs_comp.
* Distribute value in proportion to Qty purchased
DATA: t1 TYPE /BIC/OIKFVALSTCK, t2 TYPE /BIC/OIKFVALSTQ.
t1 = <ls>-/BIC/KFVALSTCK. t2 = <ls>-/BIC/KFVALSTQ.
LOOP AT vs_comp ASSIGNING <vs>.
<ls>-VENDOR = <vs>-VENDOR.
IF tot <> 0.
<ls>-/BIC/KFVALSTCK = t1 * <vs>-/BIC/KFQTRCUB / tot.
<ls>-/BIC/KFVALSTQ = t2 * <vs>-/BIC/KFQTRCUB / tot.
ENDIF.
APPEND <ls> TO et_target.
ENDLOOP.
ELSE.
* If no relevant articles purchased assign main vendor
READ TABLE art WITH TABLE KEY
/BIC/CHARTICLE = <ls>-/BIC/CHARTICLE ASSIGNING <art>.
IF sy-subrc = 0. <ls>-VENDOR = <art>-vendor.
ELSE. <ls>-VENDOR = ' '. ENDIF.
APPEND <ls> TO et_target.
ENDIF.
ENDLOOP.
Focus On Performance
In order to address performance we have to make sure we process only records required in the business process: In P&L processing filter out balance GL accounts and all non P&L entries. When we process relevant entries the most time consuming operation is SELECT statement from external database tables (DSOs). We should minimize usage of SELECT statements in the loops and use internal tables wherever possible. However, if we do have to read database tables in the loops in order to improve performance we have to read data using key fields in the proper order sequence (Read more in the post: Key Fields Order Does Matter ).