Using SLT for Advanced Reporting

The SAP Landscape Transformation system or SLT is used to automate replication (with transformations where necessary) between the databases behind SAP and other systems. This whitepaper will act a a repository of SQL code that can be run in Microsoft SQL Server Management Studio or recycled for other purposes.

Related content: Mirror an SAP Server Onto a Local Computer Using VBA, RFCs and SQL Server Express

Any of these can be run as OpenSQL in ABAP or in SQ02 with some complex Datasets, but in the last ten years, BASIS teams have been banning these transactions "because performance". Interrogating mirrored databases doesn't impact SAP performance, so this workaround that simulates SQ00 is moot for businesses with SLT.

Validate Net Weight and Gross Weight based on BOM

The Net Weight and Gross Weight fields (MARA-NTGEW and MARA-BRGEW) are practically free-text fields that aren't validated by SAP, so it is left to the user to provide those values. This snippet of SQL knits together the values from the Material Master and the BOM items providing proposed Net Weight and Gross Weight values.

The weight conversions are a little complicated; the code tries to see if the BOM item has weight Alt UoMs the same as the weight UoM (MARA-GEWEI) of the Material Master for which the Net Weight and Gross Weight are being proposed. If not available then the code tries to convert KG to LB (or LB to KG) from Alt UoMs and then from the weight UoM (MARA-GEWEI) from the BOM item.

Note: This SQL script does not account for BOMs that have been modified with Change Requests. Because only the Valid-From (DATAV) values are included in the tables and not the Valid-To (DATUB), the BOM items that have expired are still represented in the report.

 select  mara.MATNR,makt.MAKTX,mara.MTART,mara.PRDHA,mara.MEINS,MARA.NTGEW,MARA.BRGEW,mara.GEWEI,mast.WERKS,t416t.antxt as BOM_Usage,mast.stlal as AlternateBOM,
 sum(case 
       when marmp.matnr is not null then
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marmp.umren/marmp.umrez)/stko.bmeng)
         end
	   when mara.gewei = 'LB' and marmKG.meinh is not null then
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marmKG.umren/marmKG.umrez)/stko.bmeng*(69730/31629)) -- Convert KG to LB
         end
	   when mara.gewei = 'KG' and marmLB.meinh is not null then
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marmLB.umren/marmLB.umrez)/stko.bmeng*(31629/69730)) -- Convert LB to KG
         end
	   when mara.gewei = marap.gewei then 
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)/stko.bmeng) -- No conversion
         end
	   when mara.gewei = 'LB' and marap.gewei = 'KG' then 
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)/stko.bmeng*(69730/31629)) -- Convert KG to LB
         end
	   when mara.gewei = 'KG' and marap.gewei = 'LB' then 
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)/stko.bmeng*(31629/69730)) -- Convert LB to KG
         end
	   else 0  --Couldn't find a match...
	 end
	) as NetWeightPerBaseUoM,
 sum(case 
       when marmp.matnr is not null then
         ((marmi.umrez/marmi.umren)*stpo.menge*(marmp.umren/marmp.umrez)/stko.bmeng)
	   when mara.gewei = 'LB' and marmKG.meinh is not null then
         (stpo.menge*(marmKG.umren/marmKG.umrez)/stko.bmeng*(69730/31629)) -- Convert KG to LB
	   when mara.gewei = 'KG' and marmLB.meinh is not null then
         ((marmi.umrez/marmi.umren)*stpo.menge*(marmLB.umren/marmLB.umrez)/stko.bmeng*(31629/69730)) -- Convert LB to KG
	   when mara.gewei = marap.gewei then 
         ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)/stko.bmeng) -- No conversion
	   when mara.gewei = 'LB' and marap.gewei = 'KG' then 
         ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)/stko.bmeng*(69730/31629)) -- Convert KG to LB
	   when mara.gewei = 'KG' and marap.gewei = 'LB' then 
         ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)/stko.bmeng*(31629/69730)) -- Convert LB to KG
	   else 0  --Couldn't find a match...
	 end
	) as GrossWeightPerBaseUoM,
 sum(case 
       when marmp.matnr is not null then
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marmp.umren/marmp.umrez))
         end
	   when mara.gewei = 'LB' and marmKG.meinh is not null then
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marmKG.umren/marmKG.umrez)*(69730/31629)) -- Convert KG to LB
         end
	   when mara.gewei = 'KG' and marmLB.meinh is not null then
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marmLB.umren/marmLB.umrez)*(31629/69730)) -- Convert LB to KG
         end
	   when mara.gewei = marap.gewei then 
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)) -- No conversion
         end
	   when mara.gewei = 'LB' and marap.gewei = 'KG' then 
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)*(69730/31629)) -- Convert KG to LB
         end
	   when mara.gewei = 'KG' and marap.gewei = 'LB' then 
         case
           when marap.mtart in ('VERP') then 0  -- Filter out Packaging Materials for Net
           else ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)*(31629/69730)) -- Convert LB to KG
         end
	   else 0  --Couldn't find a match...
	 end
	) as NetBatchWeight,
 sum(case 
       when marmp.matnr is not null then
         ((marmi.umrez/marmi.umren)*stpo.menge*(marmp.umren/marmp.umrez))
	   when mara.gewei = 'LB' and marmKG.meinh is not null then
         (stpo.menge*(marmKG.umren/marmKG.umrez)*(69730/31629)) -- Convert KG to LB
	   when mara.gewei = 'KG' and marmLB.meinh is not null then
         ((marmi.umrez/marmi.umren)*stpo.menge*(marmLB.umren/marmLB.umrez)*(31629/69730)) -- Convert LB to KG
	   when mara.gewei = marap.gewei then 
         ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)) -- No conversion
	   when mara.gewei = 'LB' and marap.gewei = 'KG' then 
         ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)*(69730/31629)) -- Convert KG to LB
	   when mara.gewei = 'KG' and marap.gewei = 'LB' then 
         ((marmi.umrez/marmi.umren)*stpo.menge*(marap.ntgew)*(31629/69730)) -- Convert LB to KG
	   else 0  --Couldn't find a match...
	 end
	) as GrossBatchWeight, 
 sum(
     case 
	   when mara.matnr is not null then 1
	   else 0
     end
	) as BOM_Items,
 sum(
     case 
	   when marmp.matnr is null then 1
	   else 0
     end
	) as WeightConversions,
 sum(
     case 
	   when marmp.matnr is null and marmLB.matnr is null and marmKG.matnr is null and mara.gewei not in ('LB','KG') and marap.gewei not in ('LB','KG') then 1
	   else 0
     end
	) as MissingWeights
 from dgSAP_SERVER.dbo.mara join dgSAP_SERVER.dbo.mast on (mara.matnr=mast.matnr)
 join dgSAP_SERVER.dbo.marc on (mast.matnr =marc.matnr and mast.werks=marc.werks)
 join dgSAP_SERVER.dbo.t416t on (mast.stlan=t416t.stlan and t416t.spras='E')
 join dgSAP_SERVER.dbo.makt on (mara.matnr=makt.matnr and makt.spras='E')
 join dgSAP_SERVER.dbo.stko on (mast.stlnr=stko.stlnr and mast.stlal=stko.stlal)
 join dgSAP_SERVER.dbo.stpo on (stko.stlnr = stpo.stlnr and stko.stlty=stpo.stlty)
 join dgSAP_SERVER.dbo.mara as marap on (stpo.idnrk=marap.matnr)
 join dgSAP_SERVER.dbo.makt as maktp on (marap.matnr=maktp.matnr and maktp.spras='E')
 join dgSAP_SERVER.dbo.marm as marmi on (marap.matnr=marmi.matnr and stpo.meins=marmi.meinh) --needed for conversions when BOM is not in Base UoM
 left outer join dgSAP_SERVER.dbo.marm as marmp on (marap.matnr=marmp.matnr and marmp.meinh=mara.gewei)
 left outer join dgSAP_SERVER.dbo.marm as marmLB on (marap.matnr=marmLB.matnr and marmLB.meinh='LB')
 left outer join dgSAP_SERVER.dbo.marm as marmKG on (marap.matnr=marmKG.matnr and marmKG.meinh='KG')
 where stpo.lkenz<>'X' and mara.prdha like '09%' --Put some filters here for the MARA table
 and mara.mtart='YK' and mara.mstae =4 and mara.lvorm <> 'X' and marc.mmsta=4 and marc.lvorm <> 'X' and stko.loekz <> 'X' and stpo.posnr <> '' and stpo.lkenz <>'X' --ignore deleted junk
 and ( marc.werks like '19%' or marc.werks like '20%' or marc.werks like '21%' ) --Filter the Plants here
 group by mara.MATNR,makt.MAKTX,mara.mtart,mara.MEINS,MARA.NTGEW,MARA.BRGEW,mara.GEWEI,mara.PRDHA,mast.werks,t416t.antxt,mast.stlal
 order by mara.matnr,mast.werks,mast.stlal

Calculate check digits for GTIN-12 values

The GS1 US organization has ratified a GTIN-12 standard whereby the first six digits represents the manufacturer and the last six represent the Material Master, but legacy systems tend to generate eleven-character UPCs without the check digit. SAP may pad the eleven-character EAN11 value with a leading zero in an attempt to make it look like a 12-digit GTIN-12 UPC, but that value is invalid and can lead to catastrophic results if printed on actual manufactured goods.

This snippet of SQL finds all instances of invalid eleven-character EAN11 values and knits in the automatically-generated check digit for easy correction. This is useful where the Material Masters are generated in one system and then cascade via ALE to other downstream systems; the values can be corrected in the production systems.

With very little doctoring, this code can be used to validate existing GTIN-12 UPC codes. The check digit (which is cleverly generated using a prime and a modulo) is the real takeaway from this SQL snippet.

select mara.MATNR,MAKTX,mara.MTART,mara.MATKL,mara.MEINS,mara.PRDHA,mara.ean11 as MARA_EAN11,mara.numtp as MARA_EANTP,marm.meinh as MARM_MEINH,marm.ean11 as MARM_EAN11,marm.numtp as MARM_NUMTP,mean.meinh as MEAN_MEINH,mean.ean11 as MEAN_EAN11,mean.EANTP as MEAN_EANTP,
 mara.ean11+right(300-((3*(  abs(substring(mara.ean11,1,1))+abs(substring(mara.ean11,3,1))+abs(substring(mara.ean11,5,1))+abs(substring(mara.ean11,7,1))+abs(substring(mara.ean11,9,1))+abs(substring(mara.ean11,11,1)) )+abs(substring(mara.ean11,2,1))+abs(substring(mara.ean11,4,1))+abs(substring(mara.ean11,6,1))+abs(substring(mara.ean11,8,1))+abs(substring(mara.ean11,10,1)) )),1) as CheckDigit
 from dgSAP_SERVER.dbo.mara join dgSAP_SERVER.dbo.marm on (mara.matnr =marm.matnr)
 join dgSAP_SERVER.dbo.makt on (mara.matnr=makt.matnr and makt.spras='E')
 left outer join dgSAP_SERVER.dbo.mean on (marm.matnr=mean.matnr and marm.meinh=mean.meinh)
 where (len(mara.ean11) =11 and marm.meinh=mara.meins and mara.ean11 like '190794%') or (len(marm.ean11) = 11 and marm.ean11 like '190794%') or (len(mean.ean11)=11 and mean.ean11 like '190794%')

Find missing Inspection Types and display them in a pivot table

The writing of this particular SQL snippet relied on some trickery to include a pivot table. The request was to produce of list of Material Master and Plant combinations and show where they were missing Inspection Types in the plants.

The @columns variable is populated with a list of Inspection Types that are desired for these Material Master and Plant combinations dynamically and then used as column headers in the report. The TQ30 table wasn't available in the SLT server, but TQ30T was, so only the English language values are used.

Credit is certainly due to the anonymous author of this terrific article. SQL pivots are simple enough after the first few experimental tries, but this programmer knows a little too much for the Dunning-Kruger effect to protect him from their terrifying complexity.

DECLARE @columns NVARCHAR(MAX) = '', @sql NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(art) + ','
FROM dgSAP_SERVER.dbo.tq30t
where sprache='E' and (art like 'ZC%' or art like 'ZG%')
ORDER BY art;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

SET @sql ='
select * from 
(
  select marc.matnr,makt.maktx,marc.werks,qmat.werks as qwerks,qmat.art
   from dgSAP_SERVER.dbo.marc join dgSAP_SERVER.dbo.qmat on (marc.matnr=qmat.matnr and marc.werks=qmat.werks)
   join dgSAP_SERVER.dbo.makt on (marc.matnr =makt.matnr and makt.spras=''E'')
) t
 pivot (
  count(qwerks)
  for art in ('+ @columns +')

) as pivot_table
order by matnr,werks;';


EXECUTE sp_executesql @sql;

Find email addresses for Customer Master Contacts

The email addresses associated with Customer Master Contacts are stored a few layers removed from the KNA1 table in an obtuse triangular relationship. The keys in the ADR6 are stored in both the KNA1 table and in the KNVK table as ADRNR and PRSNR respectively.

select kna1.KUNNR,KNVK.NAME1,KNVK.NAMEV,KNVK.ABTNR,KNVK.PAFKT,KNVK.TELF1,ADR6.SMTP_ADDR
 from dgSAP_SERVER.dbo.kna1 join dgSAP_SERVER.dbo.knvk on (kna1.kunnr=knvk.kunnr) 
 join dgSAP_SERVER.dbo.adr6 on (KNA1.adrnr=adr6.addrnumber and knvk.prsnr=adr6.persnumber)
 where kna1.kunnr in (
-- Put your Customer Master numbers here or apply some filter.
)

Extract all Characteristic Values in all Classes

Because of the joins between fields like MATNR and KUNNR and fields like OBJEK are disallowed in SQ02 and SQVI, this extract is much easier in SLT (but can be done in LSMW using ABAP. This snippet of SQL code can be used to extract the Class and Characteristic values for any objects that are linked indirectly in the INOB table.

select inob.OBJEK,klah.CLASS,ausp.KLART,cabn.ATNAM,cabnt.ATBEZ,ausp.ATINN,ausp.ATWRT,ausp.ATFLV,ATWTB
 from dgSAP_SERVER.dbo.ausp join dgSAP_SERVER.dbo.INOB on (ausp.objek = inob.cuobj and ausp.klart=inob.klart)
 join dgSAP_SERVER.dbo.cabnt on (ausp.atinn=cabnt.atinn and cabnt.spras='E')
 join dgSAP_SERVER.dbo.cabn on (ausp.atinn=cabn.atinn)
 join dgSAP_SERVER.dbo.KSML on (ausp.atinn = ksml.imerk)
 join dgSAP_SERVER.dbo.KSSK on (ausp.objek=kssk.objek and ausp.klart=kssk.klart and ksml.clint=kssk.clint) --  and ausp.adzhl=kssk.adzhl)
 join dgSAP_SERVER.dbo.KLAH on (kssk.clint = klah.clint and kssk.klart = klah.klart)
 left outer join dgSAP_SERVER.dbo.cawn on (cawn.atwrt = ausp.atwrt and cawn.atinn = ausp.atinn) 
 left outer join dgSAP_SERVER.dbo.cawnt on (cawnt.atzhl = cawn.atzhl and cawnt.atinn = ausp.atinn and cawnt.spras = cabnt.spras)
 where 
--ausp.klart='023' and   -- Put your Class type filter here if necessary
 inob.objek in (
-- Put your ojbects here in single quotes separated by commas . . .
)

Find missing plant extensions using a pivot table

This sample code pulls a count of Plant extensions for Material Masters that should should be extended to these Plants. It's an easy way to find the Plant extensions that are missing.

select MATNR,MAKTX,MTART,MATKL,LABOR,MEINS,XCHPF,EAN11,PRDHA,MSTAE,MSTAV,
 [US13],[US14],[US16],[US17],[US18],[US21],[US22],[US24],[US26],[US27],[US28],[US29],[US30],[US32],[US33],[US35],[US36],[US38],[US39],[US40],[US41],[US46],[US47],[US49],[US51]
from 
(
select mara.MATNR,makt.MAKTX,mara.MTART,mara.MATKL,LABOR,mara.MEINS,mara.XCHPF,mara.EAN11,mara.PRDHA,mara.MSTAE,mara.MSTAV,marc.WERKS,marc.MMSTA
 from dgSAP_SERVER.dbo.mara join dgSAP_SERVER.dbo.makt on (mara.matnr=makt.matnr and makt.spras='E')
 left outer join dgSAP_SERVER.dbo.marc on (mara.matnr=marc.matnr)
  where ( mara.prdha like '1142%' or mara.prdha like '1143%' ) and matkl like 'Z%' and mara.mstae = '4' 
) as t
pivot 
(
	count(mmsta)
	for werks in ([US13],[US14],[US16],[US17],[US18],[US21],[US22],[US24],[US26],[US27],[US28],[US29],[US30],[US32],[US33],[US35],[US36],[US38],[US39],[US40],[US41],[US46],[US47],[US49],[US51])
) as pivottable