SELECT 
        OrderID,
        DeliveryDate,
        DeliverySequence,
        RecordUpdateDTTM,
        CASE 
            WHEN DeliveryDate = '1999-12-31' THEN NULL
            ELSE DeliveryDate
        END AS UpdatedDeliveryDate
        INTO #TEMP
FROM Orders

;WITH CTE AS (
    SELECT 
        OrderID,
        DeliveryDate,
        DeliverySequence,
        RecordUpdateDTTM,
        UpdatedDeliveryDate AS ExpectedOutput
    FROM #TEMP
    WHERE DeliverySequence = 1 

    UNION ALL

    SELECT 
        c.OrderID,
        c.DeliveryDate,
        c.DeliverySequence,
        c.RecordUpdateDTTM,
        COALESCE(c.UpdatedDeliveryDate, r.ExpectedOutput) AS ExpectedOutput
    FROM #TEMP c
    INNER JOIN CTE r 
        ON c.OrderID = r.OrderID AND c.DeliverySequence = r.DeliverySequence + 1
)
SELECT 
    OrderID,
    DeliveryDate,
    DeliverySequence,
    RecordUpdateDTTM,
    ExpectedOutput
FROM CTE
ORDER BY OrderID, DeliverySequence;

If you want the latest valid delivery date for a group of order_id, you can simply PARTITION BY order_id and ORDER BY delivery_sequence and use a MAX() to get the latest delivery_date for each order_id

Fiddle

SELECT 
    o.order_id,
    o.delivery_date,
    o.delivery_sequence,
    o.record_update_dttm,
    COALESCE(
        NULLIF(o.delivery_date, '1999-12-31'),
        MAX(CASE WHEN o.delivery_date != '1999-12-31' THEN o.delivery_date END) 
            OVER (PARTITION BY o.order_id ORDER BY o.delivery_sequence )
    ) AS expected_output
FROM orders o
ORDER BY o.order_id, o.delivery_sequence;

Output

You can query the max(delivery_date) for each order id, and then join the result to the original table. For the expected_output column, use a case when delivery_date = ‘1999-12-31’.

select se.order_id,
       se.delivery_date,
       se.delivery_sequence,
       se.record_update_dttm,
       case when se.delivery_date = '1999-12-31' then agg.max_delivery_date
            else se.delivery_date end expected_output
from se_orders se
inner join
(
    select order_id, max(delivery_date) max_delivery_date
    from se_orders
    group by order_id
) agg
on se.order_id = agg.order_id
order by se.order_id, se.delivery_sequence;