BOM - SAP 4 HANA

SELECT
    2 as Type, -- 1 = full structure, 2 = one level only
    tm.MTBEZ as MaterialGroupFinishedGoods,
    LTRIM(MAST.MATNR,'0') AS FinishedGoodCode,
    MAKT.MAKTX AS FinishedGoodName,
    LTRIM(STKO.STLNR,'0') AS BOMNumber,
    jm.MSEH3 AS FinishedGoodUnit,
    STKO.BMENG AS BaseQuantity,
    ROUND(CASE
        WHEN MARA.NTGEW > 0 THEN
            CASE MARA.GEWEI
                WHEN 'KG' THEN 1 / MARA.NTGEW
                WHEN 'G' THEN 1000 / MARA.NTGEW
                ELSE 1
            END
        ELSE COALESCE(MARM.UMREZ / MARM.UMREN, 1)
    END, 4) AS ConversionTo1Kg,

    tm2.MTBEZ as MaterialGroupRawMaterial,
    LTRIM(STPO.IDNRK,'0') AS RawMaterialCode,
    MAKT2.MAKTX AS RawMaterialName,
    jm2.MSEH3 AS RawMaterialUnit,
    STPO.MENGE AS RawMaterialQuantity,  -- should be decimal (18,4)
    STPO.AUSCH AS ScrapPercentage,

    ROUND(CAST((1 + (STPO.AUSCH / 100)) * STPO.MENGE AS decimal(18,8)), 4) AS QuantityWithScrap,

    ROUND(CAST(
        (1 + (STPO.AUSCH / 100)) * STPO.MENGE *
        (1000 / STKO.BMENG) * -- scaling base quantity to 1000
        ROUND(CASE
            WHEN MARA.NTGEW > 0 THEN
                CASE MARA.GEWEI
                    WHEN 'KG' THEN 1 / MARA.NTGEW
                    WHEN 'G' THEN 1000 / MARA.NTGEW
                    ELSE 1
                END
            ELSE COALESCE(MARM.UMREZ / MARM.UMREN, 1)
        END, 4)
    AS decimal(18,8)), 4) AS QuantityWithScrapPer1000Kg

FROM SAPHANADB.MAST MAST

-- adjustment: not all materials have alternative BOM = 1
INNER JOIN (
    SELECT MATNR, MIN(STLAL) AS STLAL
    FROM SAPHANADB.MAST
    WHERE MANDT = '100'
    GROUP BY MATNR
) MAST_ALT ON MAST.MATNR = MAST_ALT.MATNR AND MAST.STLAL = MAST_ALT.STLAL

INNER JOIN SAPHANADB.MARA MARA ON MARA.MATNR = MAST.MATNR
    AND MARA.MANDT = MAST.MANDT

INNER JOIN SAPHANADB.MAKT MAKT ON MAKT.MATNR = MAST.MATNR
    AND MAKT.MANDT = MAST.MANDT
    AND MAKT.SPRAS = '6' -- language key, 6 = English

INNER JOIN SAPHANADB.STKO STKO ON MAST.STLNR = STKO.STLNR
    AND STKO.MANDT = MAST.MANDT
    AND STKO.STLTY = 'M' -- material BOM type
    AND STKO.STLAL = MAST.STLAL
    AND STKO.STLST = '01' -- BOM status Active

INNER JOIN SAPHANADB.STPO STPO ON STPO.STLNR = STKO.STLNR
    AND STPO.MANDT = MAST.MANDT
    AND STPO.STLTY = 'M'

INNER JOIN SAPHANADB.STAS STAS ON STPO.STLNR = STAS.STLNR
    AND STAS.MANDT = MAST.MANDT
    AND STAS.STLKN = STPO.STLKN
    AND STAS.STLAL = MAST.STLAL

INNER JOIN SAPHANADB.MARA MARA2 ON MARA2.MATNR = STPO.IDNRK
    AND MARA2.MANDT = MAST.MANDT

INNER JOIN SAPHANADB.MAKT MAKT2 ON MAKT2.MATNR = STPO.IDNRK
    AND MAKT2.MANDT = MAST.MANDT
    AND MAKT2.SPRAS = '6'

INNER JOIN SAPHANADB.T006A jm ON jm.MSEHI = STKO.BMEIN -- unit of measure for FG
    AND jm.MANDT = MAST.MANDT
    AND jm.SPRAS = '6'

INNER JOIN SAPHANADB.T006A jm2 ON jm2.MSEHI = STPO.MEINS -- unit of measure for raw material
    AND jm2.MANDT = '100'
    AND jm2.SPRAS = '6'

INNER JOIN SAPHANADB.T418T T418T ON T418T.POSTP = STPO.POSTP
    AND T418T.MANDT = MAST.MANDT
    AND T418T.SPRAS = '6'

INNER JOIN SAPHANADB.T134T tm ON tm.MTART = MARA.MTART
    AND tm.MANDT = MAST.MANDT
    AND tm.SPRAS = '6'

INNER JOIN SAPHANADB.T134T tm2 ON tm2.MTART = MARA2.MTART
    AND tm2.MANDT = MAST.MANDT
    AND tm2.SPRAS = '6'

LEFT JOIN SAPHANADB.MARM MARM ON MARM.MATNR = MAST.MATNR -- conversion to 1 KG
    AND MARM.MANDT = MAST.MANDT
    AND MARM.MEINH = 'KG'

WHERE
    MAST.MANDT = '100'
--  AND MAST.STLAL = '01' -- BOM alternative
    AND MAST.STLAN = '1' -- BOM usage (1 - production)
--  AND LTRIM(MAST.MATNR, '0') in (2001078,6000000,6000001,7000000,7000001)

-- MKAL.MKSP - BOM locking flag!?

ORDER BY
    MAST.MATNR,
    MAST.STLNR,
    STKO.STLAL,
    STPO.POSNR