MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Item (
  ItemID Integer,
  ItemMfgTypeID Integer);

INSERT INTO Item (ItemID,ItemMfgTypeID)
VALUES (10004,2),
       (10001,1),
       (10008,1),
       (10009,1),
       (10010,1);

CREATE TABLE ItemMtl (
  ItemID Integer,
  Seq Integer,
  Line Integer,
  MaterialItemID Integer,
  UtopianQty Double,
  WastePC Double,
  OptionStdCost Boolean,
  EffectDate Date,
  ExpireDate Date);

INSERT INTO ItemMtl (ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost,EffectDate,ExpireDate)
VALUES (10004,1,10,10001,6,0,True,NULL,NULL),
       (10004,2,20,10008,5.5,0,True,NULL,NULL),
       (10004,3,30,10009,0.35,0,True,NULL,NULL),
       (10004,4,40,10010,0.25,0,True,NULL,NULL);

WITH RECURSIVE cte AS (
  SELECT Item.ItemID AS RootItemID,
    0 AS LevelID,
    Item.ItemID AS ParentItemID,
    0 AS Seq,
    0 AS Line,
    ItemID AS ItemID,
    CAST(1 AS DECIMAL(9,2)) AS LevelQty,
    0 AS LevelWastePC,
    CAST(1 AS DECIMAL(9,2)) AS RollDownQty,
    True AS OptionStdCost
  FROM Item
  WHERE ItemMfgTypeID > 1

UNION ALL

SELECT cte.RootItemID,
    cte.LevelID + 1,
    cte.ItemID,
    l.Seq,
    l.Line,
    l.MaterialItemID,
    l.UtopianQty,
    l.WastePC,
    l.UtopianQty,
    l.OptionStdCost
  FROM ItemMtl AS l JOIN cte USING (ItemID)
  WHERE (EffectDate IS NULL OR EffectDate <= curdate())
    AND (ExpireDate IS NULL OR curdate() < ExpireDate)
)
SELECT RootItemID,
  CAST(1000*LevelID + ROW_NUMBER() OVER (PARTITION BY LevelID ORDER BY Line) AS CHAR(44)) AS SortChar,
  LevelID,
  ParentItemID,
  Seq,
  Line,
  ItemID,
  LevelQty,
  LevelWastePC,
  RollDownQty,
  OptionStdCost
FROM cte;

Records: 5  Duplicates: 0  Warnings: 0

Records: 4  Duplicates: 0  Warnings: 0

RootItemID	SortChar	LevelID	ParentItemID	Seq	Line	ItemID	LevelQty	RollDownQty	OptionStdCost
10004	1	0	10004	0	0	10004	1.00	1.00	1
10004	1001	1	10004	1	10	10001	6.00	6.00	1
10004	1002	1	10004	2	20	10008	5.50	5.50	1
10004	1003	1	10004	3	30	10009	0.35	0.35	1
10004	1004	1	10004	4	40	10010	0.25	0.25	1