Handle SQL recursion for multiple pallets and production runs

I need to trace the costs of production runs and apportion back to the original received pallets. However, once the original pallets goes through a production run, they are mixed with all the other input pallets from that production run and they create multiple output pallets. Those output pallets in theory could be sold OR go back into another production run and come out again as completely different pallets and this process can repeat itself an unknown number of times. The table structure also adds a layer of complication. All of the pallets are created in one table.
Basic Hierarchal Structure

The Pallets table includes two types:

• N: The originally received Pallet
• P: Pallet produced from a Production run and ProdOrd field is populated with the Run Number

Every time I try to build out the CTE recursion for this scenario, I run into a indefinite loop of the recursion because it is a many to many relationship. I'm thinking that SQL server can't handle it at this level. We have thousands of original pallets and then they get mixed in the shuffle.

What I'm hoping for is something like this:

The cost is the Sum of all the production runs apportioned to the original input pallet.

I think I have captured the intent up to the point where the recursive CTE calculates the propagation of pallet contents through the various process runs.

In order to calculate the propagation of pallet content through the entire production series, we must first calculate the propagation from each input pallet to each output pallet at each step. That calculation turns out to be a non-trivial combination of quantity per input pallet, total input by lot, total output by lot, and quantity per output pallet.

The following is what I came up with across several steps.

First, calculate input and output totals for each production run and lot. We will need these values in the calculations of the following step

CREATE TABLE #InOutQty(
  ProdOrd NVARCHAR(10), Lot NVARCHAR(12),
  InTotalQty INT, OutTotalQty INT,
  PassThroughFraction FLOAT, ConsumedFraction FLOAT
)
  
INSERT #InOutQty
    SELECT IQ.ProdOrd, IQ.Lot, IQ.InTotalQty, ISNULL(OQ.OutTotalQty, 0),
        A1.PassThroughFraction,
        (1e0 - A1.PassThroughFraction) AS ConsumedFraction
    FROM (
       -- Total up inputs by production run and lot
       SELECT I.ProdOrd, P.Lot, Sum(I.QTY) AS InTotalQty
       FROM ProdInput I
       JOIN Pallets P ON P.Pallet = I.Pallet
       GROUP BY I.ProdOrd, P.Lot
    ) IQ
    LEFT JOIN(
       -- Total up inputs by production run and lot (might be none for certain inputs) 
       SELECT P.ProdOrd, P.Lot, Sum(P.OrgQTY) AS OutTotalQty
       FROM Pallets P
       WHERE P.Type = 'P'
       GROUP BY P.ProdOrd, P.Lot
    ) OQ
        ON OQ.ProdOrd = IQ.ProdOrd
        AND OQ.Lot = IQ.Lot
    CROSS APPLY (
        -- Calculate how much is unused and repacked on a new pallet 
        SELECT (1e0 * ISNULL(OQ.OutTotalQty, 0) / IQ.InTotalQty) AS PassThroughFraction
    ) A1

Next calculate the input and output attributable to each combination of production run, input, and output pallets. Leftover materials from multiple input pallets of the same lot are assumed to be mixed evenly and reallocated to the output pallets.

CREATE TABLE #PalletInOutQty (
    ProdOrd NVARCHAR(10), Lot NVARCHAR(12),
    InputPallet NVARCHAR(12), OutputPallet NVARCHAR(12),
    InputQty FLOAT, OutputQty FLOAT,
    InputPalletFraction FLOAT, PassThroughFraction FLOAT
)
  
INSERT #PalletInOutQty
SELECT IOQ.ProdOrd, IOQ.Lot, IP.Pallet, OP.Pallet,
    A1.InputQty,
    (A1.InputQty * IOQ.PassThroughFraction) AS OutputQty,
    (A1.InputQty * 1e0 / IP.OrgQty) AS InputPalletFraction,
    IOQ.PassThroughFraction
FROM #InOutQty IOQ
JOIN ProdInput PI ON PI.ProdOrd = IOQ.ProdOrd
JOIN Pallets IP ON IP.Pallet = PI.Pallet AND IP.Lot = IOQ.Lot
JOIN Pallets OP ON OP.ProdOrd = IOQ.ProdOrd AND OP.Lot = IOQ.Lot
CROSS APPLY (
    -- Input quantity for this in/out pallet combination is calculated as the
    -- the selected input pallet qty times the fraction of the total output
    -- included in the selected output pallet.
    SELECT (PI.Qty * (OP.OrgQty * 1e0 / IOQ.OutTotalQty)) AS InputQty
) A1

Now we can use a recursive CTE to retrieve the initial pallet information, and for each pallet, follow the path through the production run to calculate how the content of that original pallet propagates through the series.

IF OBJECT_ID('tempdb..#PalletLineage') IS NOT NULL
    DROP TABLE #PalletLineage
CREATE TABLE #PalletLineage (
    Pallet NVARCHAR(12),
    OriginPallet NVARCHAR(12),
    Lineage NVARCHAR(1000),
    Qty FLOAT,
    OriginPalletFraction FLOAT
)

;WITH cte AS (
    SELECT
        P.Pallet,
        P.Pallet AS OriginPallet, 
        CAST(P.Pallet AS NVARCHAR(MAX)) AS Lineage,
        CAST(P.OrgQty AS Float) AS Qty,
        1.0e0 AS OriginPalletFraction
    FROM Pallets P
    WHERE P.Type = 'N'

    UNION ALL

    SELECT
        PIOQ.OutputPallet AS Pallet,
        cte.OriginPallet AS OriginPallet,
        CONCAT(cte.Lineage, ' > ', PIOQ.OutputPallet) AS Lineage,
        (cte.Qty / P.OrgQty * PIOQ.OutputQty) AS Qty,
        (cte.OriginPalletFraction * PIOQ.InputPalletFraction
            * PIOQ.PassThroughFraction) AS OriginPalletFraction
    FROM cte
    JOIN #PalletInOutQty PIOQ ON PIOQ.InputPallet = cte.Pallet
    JOIN pallets P ON P.Pallet = cte.Pallet
)
INSERT #PalletLineage
SELECT *
FROM cte

See this db<>fiddle for a demo.

This is somewhat over-engineered in that several intermediate calculations are not really used. Feel free to ask questions if you have them, but I'll leave it to you to strip it down, enhance it, or otherwise adapt to your needs if you wish.

Applying additional steps to apply cost calculations is left as a follow-up exercise.