After taking a very deep dive into this problem, my conclusion is:

1. It is doable using a recursive CTE with with a great deal of effort and lots of tricky coding.
2. Don't do it. The resulting query is extremely complex, difficult to read and comprehend, is probably not efficiently scalable to large data sets, and would be a nightmare for you and those who may come after you to maintain.

Just say no. Instead look into developing an iterative procedural solution that considers and processes one order at a time.

If you really want to see what I came up with, here is the db<>fiddle . I've added quite a few comments, but I expect it would be a challenge for anyone other than a senior, experienced SQL programmer to understand, and even that is a reach.

PLAN A:

My initial plan was to follow your lead and generate a recursive CTE with rows for each combination or order and product. Each CTE row would have dependencies on earlier rows.

Unfortunately, these CTE dependencies do not form a linear path. The updated remaining quantity for a product not only depends on the prior inventory for that product, but on the calculated fulfilment for that order, which in turn depends on all of the current product quantity requests, and all of the prior order remaining inventory quantities.

Ultimately this results in in a one-to-many CTE back-reference dependencies, which is not allowed in SQL server. Even is all back references refer to rows from previous recursion cycles, they are still disallowed.

PLAN B:

Since the only critical piece if information that must be calculated in sequence is the fulfillment flag, I took another stab and a recursive CTE that only results in one row per order and only records the fulfillment flag. Subsequent order calculations could recalculate the remaining inventory from scratch, based on initial inventory, knowledge of earlier-fulfilled orders, and the products allocated to those prior fulfilled orders.

Unfortunately, the same recursive CTE limitation came into play. Each recursive CTE row calculation not only needs to access the prior row, but all prior rows to identify all prior fulfilled orders. Again this is not allowed in SQL server.

PLAN C:

The next approach was to generate a recursive CTE, one per order, that not only sets the fulfillment flag, but also maintains a complete inventory of remaining quantities. A comma separated list was selected to represent the inventory. (XML or JSON might have been workable alternatives.)

Each CTE iteration would unpack the inventory, perform apply the order logic, update the inventory, and pack the results back into a comma-separated list.

Fitting this all into the CTE recursion query involved several nested queries, multiple CROSS APPLYs, and logic to pull the results back together.

Again SQL Server recursive CTE limitations kicked in. Recursive CTEs do not allow OUTER JOIN s. They do not allow GROUP BY . They do not allow aggregate functions like STRING_JOIN() . I even tried a DISTINCT in one lame attempt - not allowed.

I was finally able to fall back on the old FOR XML string aggregation technique and together with a few other klugy techniques, was able to get a working CTE.

The results are not anything that I would ever offer for production use for reasons summarized at the top of this post.

I only post this as a lesson that sometimes going to extreme measures to get a job done using the wrong tools yields a barely workable and completely unmaintainable result, and that the better solution is to take a step back and reevaluate the task and look for a better tool set.

I found this solution and I want to share with you all. We take first order and see if the quantity of each and every ItemNo from this order is found in the Inventory table in sufficient quantity. If yes we flag it with selected 1 else we flag all the order 1 with selected=0. If we flag it with 1 as selected then all the ItemNo will receive an ItemId and the order will be sent to client but if we have flagged it with 0 we will ignore all the ItemNo required by this order and proceed to the next order. Similarly we will do for the next orders with the alocated numbers 2, 3, ... n as they where ordered by OrderID. For each i>=2 and i<=n (last order placed) we will calculate for each ItemNo from this order the necessary quentity on the order , the total quantity in Inventory and the already allocated quantity in the orders already analised ( from 1 to i-1 from temp1 ) but only those flagged with selected=1 in temp 1. If we find that an ItemNo from order i can't be honoured as it does not have enough quantity then we will flag all order with 0 in temp1 but if all ItemNo where having selected=1 then all order i will have selected as 1.

create or alter view o1 as
       select top 100 percent OrderID, RequiredItemNo,
       count(*) as cant , 
       dense_rank() OVER (ORDER BY OrderID) as nr1
       from orders  
        group by OrderID, RequiredItemNo
        order by OrderID, RequiredItemNo;

        create or alter view o1f as
        select OrderID, min(selected) as selected
        from
         (
          select  or1.OrderID, or1.RequiredItemNo, 1 selected from o1 or1
          where exists (select i.ItemNo from Inventory i 
             where i.ItemNo=or1.RequiredItemNo 
             group by i.ItemNo
             having count(*)>=or1.cant) and or1.nr1=1
          union all
          select  or1.OrderID, or1.RequiredItemNo, 0 selected from o1 or1
           where not exists (select i.ItemNo from Inventory i 
                   where i.ItemNo=or1.RequiredItemNo 
                      group by i.ItemNo
                      having count(*)>=or1.cant) and or1.nr1=1
              )a
           group by OrderID;

        CREATE OR ALTER PROCEDURE CalculateSelectedItems
        AS
         BEGIN
          IF OBJECT_ID('temp1') IS NOT NULL
          DROP TABLE temp1;
           CREATE TABLE temp1 (
           OrderIDnr INT,
            selected INT
           );
         DECLARE
             @vnr INT,
             @vselected INT,
             @i INT,
             @n INT;

          SET NOCOUNT ON;

          DELETE FROM temp1;
          SET @vnr = 1;

       SELECT @vselected = selected FROM o1f;
       INSERT INTO temp1 VALUES (@vnr, @vselected);

     SELECT @n = COUNT(DISTINCT OrderID) FROM orders;
     SET @i = 2;

     WHILE (@n >= @i)
     BEGIN
      SET @vnr = @i;
      SET @vselected = 2;

      SELECT @vselected = ISNULL(s.selected, 0)
      FROM (
        SELECT MIN(CAST(sel.selected AS INT)) AS selected
        FROM (
        SELECT or1.OrderID, or1.RequiredItemNo, '1' AS selected
        FROM o1 or1
        WHERE EXISTS (
            SELECT i.ItemNo
            FROM Inventory i 
            WHERE i.ItemNo = or1.RequiredItemNo 
            GROUP BY i.ItemNo
            HAVING COUNT(*) - or1.cant - (
                SELECT ISNULL(SUM(cant), 0)
                FROM o1
                INNER JOIN temp1 ON o1.RequiredItemNo = i.ItemNo 
                    AND o1.nr1 = temp1.OrderIDnr 
                    AND temp1.selected = 1
            ) >= 0
        ) AND or1.nr1 = @vnr
        
        UNION ALL
        
        SELECT or1.OrderID, or1.RequiredItemNo, '0' AS selected
        FROM o1 or1
        WHERE NOT EXISTS (
            SELECT i.ItemNo
            FROM Inventory i 
            WHERE i.ItemNo = or1.RequiredItemNo 
            GROUP BY i.ItemNo
            HAVING COUNT(*) - or1.cant - (
                SELECT ISNULL(SUM(cant), 0)
                FROM o1
                INNER JOIN temp1 ON o1.RequiredItemNo = i.ItemNo 
                    AND o1.nr1 = temp1.OrderIDnr 
                    AND temp1.selected = 1
            ) >= 0
        ) AND or1.nr1 = @vnr
    ) sel
    GROUP BY sel.OrderID
) s;
      INSERT INTO temp1 VALUES (@vnr, @vselected);
     SET @i = @i + 1;
     END;
     select * from temp1
      END;

      exec CalculateSelectedItems

If some MSSQL experienced user can convert this one from ORACLE syntax, at least it seems to work:

with orders(id, reqproduct) as (
    select 'ORD001', 'Prod007' from dual union all
    select 'ORD001', 'Prod008' from dual union all
    select 'ORD001', 'Prod009' from dual union all
    select 'ORD002', 'Prod008' from dual union all
    select 'ORD002', 'Prod009' from dual union all
    select 'ORD002', 'Prod012' from dual union all
    select 'ORD003', 'Prod008' from dual union all
    select 'ORD003', 'Prod014' from dual 
),
warehouse(product) as (
    select 'Prod007' from dual union all
    select 'Prod008' from dual union all
    select 'Prod008' from dual union all
    select 'Prod012' from dual union all
    select 'Prod009' from dual union all
    select 'Prod014' from dual union all
    select 'Prod015' from dual 
),
gwarehouse(product, qty) as (
    select product, count(*) from warehouse group by product
),
porders as (
    select row_number() over(order by ord.id) as rn, ord.id 
    from orders ord
        join gwarehouse war on ord.reqproduct = war.product 
        group by ord.id having( count(*) = (select count(*) from orders ord1 where ord1.id = ord.id))
)
,cte(rn, id, remqty, excluded) as 
(
    select pord.rn, ord.id, war.qty - 1, 
        listagg(
            case when war.qty - 1 = 0 then ord.reqproduct end, ','
        ) within group(order by ord.reqproduct) over(partition by ord.id )
    from porders pord
    join orders ord on pord.id = ord.id
    join gwarehouse war on war.product = ord.reqproduct
    where pord.rn = 1

    union all
    
    select pord.rn, pord.id, c.remqty - 1, 
        c.excluded || ',' || 
        listagg( 
            distinct 
            case when (c.remqty - 1 = 0 or war.qty - 1 = 0) and instr(c.excluded, ord.reqproduct) = 0
            then 
                ord.reqproduct 
            end, ','
        ) within group(order by ord.reqproduct) over(partition by ord.id)       
    from cte c
    join porders pord on pord.rn >= c.rn + 1
    join orders ord on ord.id = pord.id 
    join gwarehouse war on war.product = ord.reqproduct 
    where not exists (
        select 1 from orders ord1 where ord1.id = ord.id and instr(excluded, ord1.reqproduct) > 0 
    )
)
select distinct id from cte
;

ORD001
ORD003