Motivation:
Even the best manufacturing processes will produce scrap, at least a little bit from time to time. Wouldn’t it be interesting to analyze these scrapping costs over time using, storing that data out of your ERP in a datawarehouse? Below’s SQL shows you how to get there, from a SAP Bi point of view. Please note that there is no overall 100% correct and complete solution for this request, since all SAP ERP are corporate unique. But it should definitely guide you the right way.
Solution:
If you are looking for scrap costs, QMFE is the central Sap table of interest. QMEL holds the overall quality notification, while QMFE stores the error per item. Sanctions can be found in QMSM, root cause in QMUR and activities in QMMA:

First we’re going to load table QMFE:
select * into [etl].[qmfe] from [qmfe]
We will only take into consideration those datarows from QMSM which are not canceled (kzloesc<> x). Using the window function row_number and partition by we only select the first datarow per qmnm and fenum into the new table qmsm_decision:
drop table [etl].[qmsm_decision]
select * into [etl].[qmsm_decision]
from
(select a.*,
Row_number() over (partition by qmnum, fenum order by manum desc) rn
from [qmsm] as a
Where mngrp ='WhatEverYourCompany' and kzloesc <>'X'
)src
Where rn ='1'
Order by qmnum;
We want to enrich our etl.qmfe for code group tasks (mngrp) and task codes (mncod) which we receive from table QMSM:
Update [etl].[qmfe]
set QmsmMngrp= qmsm.mngrp, QmsmMncod=qmsm.mncod
FROM [etl].[qmfe] as qmfe
left outer join [etl].[qmsm_decision] as qmsm
on qmfe.QMNUM=qmsm.QMNUM and qmfe.FENUM=qmsm.FENUM
Now comes the interesting part: we will update more specifically for those data rows where QmsmMngrp is currently null and where fenum in QMSM is zero:
Update [etl].[qmfe]
set QmsmMngrp= qmsm.mngrp, QmsmMncod=qmsm.mncod
FROM [etl].[qmfe] as qmfe
left outer join [etl].[qmsm_decision] as qmsm
on qmfe.QMNUM=qmsm.QMNUM
where QmsmMngrp is null and qmsm.FENUM=0
We get the item number from table QMEL:
Update [etl].[qmfe]
Set qmelMatnr=qmel.matnr
From [etl].[qmfe] as qmfe
inner join [QMEL] as qmel
On qmfe.qmnum=qmel.qmnum
And that is also where we get the message type (qmart) and plant for work center (arbplwe), short text (qmtxt) and date for completion of notification (qmdab) from:
Update [etl].[qmfe]
Set QmelQmart =qmel.qmart, QmelQmtext= qmel.qmtxt, qmelqmdab=qmel.qmdab , qmelWerk=qmel.ARBPLWE
From [etl].[qmfe] as qmfe
inner join [QMEL] as qmel
On qmfe.qmnum=qmel.qmnum
Let’s get to the lion’s den, finally starting calculating scrap costs:
Update [etl].[qmfe]
Set scrap=qmsm.mngrp
From [etl].[qmfe] as qmfe
inner join [QMSM] as qmsm
On qmfe.qmnum=qmsm.qmnum
Where qmsm.mngrp='WhatEverYourCompany' and qmsm.mncod='WhatEverYourCompany' and qmsm.kzloesc <>'x'
Let’s talk about over consumption:
Update [etl].[qmfe]
Set OverConsumption=qmsm.mngrp
From [etl].[qmfe] as qmfe
inner join [QMSM] as qmsm
On qmfe.qmnum=qmsm.qmnum
Where qmsm.mngrp='WhatEverYourCompany' and qmsm.mncod='WhatEverYourCompany' and qmsm.kzloesc <>'x'
No more beating about the bush: how much did we lost due to scrap? Standard price (stprs) comes from table MBEW (material valuation). Multiply by defective quantity for item, both external (fmgfrd) and internal (fmgeig), and considering price unit (peinh) makes up the scrap costs:
Update [etl].[qmfe]
Set ScrapCosts=(FMGFRD+FMGEIG)*(STPRS/nullif(Peinh,0))
From [etl].[qmfe] as qmfe
Is it really that bad? Well, as usual, it depends. You might need to consider special conditions in detail: e.g. only consider specific qmtext, mngrp and mncod combinations as real scrap. But that truly depends on your ERP settings.
In case only some status are relevant for your scrap analysis:
update [etl].[qmfe]
set JestStatus = c.stat
from [etl].[qmfe] a
inner join [JEST] c
on ('WhateverYourCompany'+ a.QMNUM ) = c.objnr
where c.inact <>'X'
Often the requirements will change over time. So it might be that in the future you might need to remove some specific plants, OTRGRP and OTEIL from your analysis:
delete from
[etl].[qmfe]
where qmelwerk='WhatEverYourCompany' and otgrp= 'WhatEverYourCompany' and oteil='WhatEverYourCompany' and jeststatus ='WhatEverYourCompany'
QPCT (code texts) is another interesting table regarding scrap costs. We can e.g. extract department details via joining on codegruppe and code:
update [etl].[qmfe]
set Department= [Kurztext]
from [bi].[F_ScrapCosts]
left outer join [qpct]
on OTGRP=Codegruppe and OTEIL=Code
where [catalog]='WhatEverYourCompany'
Congratulations:
We have just calculated scrap costs from our ERP system SAP. Many thanks for reading, hope this was supportive! Any questions, please let me know. You can connect with me on LinkedIn or Twitter.
Originally published on my website DAR-Analytics.