About the SQL first-in-first-out write-off quantity

Question

At present, there are BOM and picking materials. According to the demand quantity of BOM and the principle of first-in-first-out, which picking materials are used by BOM, the currently conceivable solution is to use the cursor to judge the order line by line and record the number of write-offs. In relatively large cases, the execution efficiency is very low. Is there a more efficient solution?

IF OBJECT_ID('tempdb.dbo.#BOM') IS NOT NULL DROP TABLE #BOM
CREATE TABLE #BOM
(
    in_Code NVARCHAR(60),
    in_Date DATETIME,
    Orders NVARCHAR(60),
    BOM_materials NVARCHAR(60),
    in_Qty DECIMAL(18,4)
)
INSERT INTO #BOM
(in_Code, in_Date, Orders, BOM_materials, in_Qty)
VALUES (N'RK0001', '2022-03-08', N'A01', N'L01', 50),
       (N'RK0001', '2022-03-08', N'A01', N'L02', 30),
       (N'RK0002', '2022-03-15', N'A01', N'L01', 50)
SELECT * FROM #BOM

in_Code	in_Date	Orders	BOM_materials	in_Qty
RK0001	2022-03-05	A01	L01	50
RK0001	2022-03-05	A01	L02	30
RK0002	2022-03-18	A01	L01	50

IF OBJECT_ID('tempdb.dbo.#picking') IS NOT NULL DROP TABLE #picking
CREATE TABLE #picking
(
    out_Code NVARCHAR(60),
    out_Date DATETIME,
    Orders NVARCHAR(60),
    materials NVARCHAR(60),
    out_Qty DECIMAL(18,4),
    out_ID BIGINT
)
INSERT INTO #picking
(out_Code, out_Date, Orders, materials, out_Qty, out_ID)
VALUES (N'CK0001', '2022-01-08', N'A01', N'L01', 90, 1),
       (N'CK0002', '2022-01-20', N'A01', N'L01', 70, 2),
       (N'CK0003', '2022-01-30', N'A01', N'L02', 10, 3)
SELECT * FROM #picking

out_Code	out_Date	Orders	materials	out_Qty	out_ID
CK0001	2022-01-08	A01	L01	90	1
CK0002	2022-01-20	A01	L01	70	2
CK0003	2022-01-30	A01	L02	10	3

Desired result

in_Code	in_Date	Orders	BOM_materials	in_Qty	out_Code	out_Qty	need_Qty	out_id
RK0001	2022-03-05	A01	L01	50	CK0001	90	50	1
RK0001	2022-03-05	A01	L02	30	CK0003	10	10	3
RK0002	2022-03-18	A01	L01	50	CK0001	90	40	1
RK0002	2022-03-18	A01	L01	50	CK0002	70	10	2

My current solution,But the result is wrong。

SELECT A.in_Code, A.in_Date, A.Orders, A.BOM_materials, A.in_Qty, B.out_Code, B.out_Qty, 
    CASE 
            WHEN (
                    SELECT A.in_Qty - ( ISNULL(SUM(out_Qty),0) ) 
                    FROM #picking 
                    WHERE Orders = A.Orders     
                        AND materials = A.BOM_materials
                        AND out_Date <= B.out_Date
                    ) 
                    >= 0 THEN B.out_Qty
            ELSE
                CASE 
                    WHEN (
                            SELECT A.in_Qty - ( ISNULL(SUM(out_Qty),0) ) 
                            FROM #picking 
                            WHERE Orders = A.Orders     
                                AND materials = A.BOM_materials
                                AND out_Date < B.out_Date
                            ) 
                            < 0 THEN 0
                        ELSE (
                                SELECT A.in_Qty - ( ISNULL(SUM(out_Qty),0) ) 
                                FROM #picking 
                                WHERE Orders = A.Orders     
                                    AND materials = A.BOM_materials
                                    AND out_Date < B.out_Date
                            ) 
                END
        END  AS need_Qty, B.out_ID
FROM #BOM A
    LEFT JOIN #picking B ON A.Orders = B.Orders AND A.BOM_materials = B.materials