How to use recursive function for product transition?

In Oracle SQL, I have a product transition table named kit_transition_mapping that is a slowly changing dimension and an orders table. For an order_no based on the order_date, I need to utilize the kit_transition_mapping table to get to the right kit.

For e.g.,

• order 100 is placed on 15-Aug-24. As there is no kit transition happening on that date for ABC, I will utilize the original kit ABC.
• For 101, since the order date is between the transition date for ABC I will update the order to new kit ABD.
• For 102, as order date is 15-Oct-24, and on that date ABD transitions to ABE, and since the order was placed for ABC, we will consider the previous transition of ABC to ABD. Hence the order will be placed for ABE instead of ABC.

Given the above scenario and the kit_component table that breaks down a kit to the component level. How to write a query to get the expected result?

kit_transition_mapping

orders

kit_component

expected_result

Here is the CTEs for the above data:

WITH orders (order_no, kit, order_date, qty) AS (
    SELECT 100, 'ABC', DATE '2024-08-15', 1 FROM DUAL UNION ALL
    SELECT 101, 'ABC', DATE '2024-09-15', 1 FROM DUAL UNION ALL
    SELECT 102, 'ABC', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 103, 'ABC', DATE '2024-11-15', 1 FROM DUAL UNION ALL
    SELECT 104, 'ABD', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 105, 'ABE', DATE '2024-11-15', 1 FROM DUAL UNION ALL
    SELECT 106, 'ABE', DATE '2024-10-15', 1 FROM DUAL UNION ALL
    SELECT 107, 'DEX', DATE '2024-12-01', 1 FROM DUAL
),
kit_transition_mapping (old_kit, new_kit, valid_from, valid_to) AS (
    SELECT 'ABC', 'ABD', DATE '2024-09-01', DATE '2024-09-30' FROM DUAL UNION ALL
    SELECT 'ABD', 'ABE', DATE '2024-10-01', DATE '2024-10-31' FROM DUAL UNION ALL
    SELECT 'ABE', 'ABF', DATE '2024-11-01', DATE '2024-11-30' FROM DUAL
),
kit_component (kit, comp, qty) AS (
    SELECT 'ABC', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABC', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABC', 'C', 3 FROM DUAL UNION ALL
    SELECT 'ABD', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABD', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABD', 'D', 4 FROM DUAL UNION ALL
    SELECT 'ABE', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABE', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABE', 'E', 5 FROM DUAL UNION ALL
    SELECT 'ABF', 'A', 1 FROM DUAL UNION ALL
    SELECT 'ABF', 'B', 2 FROM DUAL UNION ALL
    SELECT 'ABF', 'F', 6 FROM DUAL UNION ALL
    SELECT 'DEX', 'D', 2 FROM DUAL UNION ALL
    SELECT 'DEX', 'E', 3 FROM DUAL UNION ALL
    SELECT 'DEX', 'X', 4 FROM DUAL
),
kit_resolution (order_no, original_kit, kit, order_date, order_qty) AS (
    SELECT ho.order_no, ho.kit AS original_kit, ho.kit, ho.order_date, ho.qty AS order_qty
    FROM orders ho
    UNION ALL
    SELECT kr.order_no, kr.original_kit, km.new_kit, kr.order_date, kr.order_qty
    FROM kit_resolution kr
    JOIN kit_transition_mapping km ON kr.kit = km.old_kit AND kr.order_date BETWEEN km.valid_from AND km.valid_to
)
SELECT 
    kr.order_no, 
    TO_CHAR(kr.order_date, 'DD-Mon-YY') AS order_date,
    kr.original_kit, 
    kr.kit AS new_kit, 
    kr.order_qty, 
    kc.comp, 
    kc.qty * kr.order_qty AS comp_qty
FROM kit_resolution kr
JOIN kit_component kc ON kr.kit = kc.kit
WHERE NOT EXISTS (
    SELECT 1 FROM kit_transition_mapping km
    WHERE kr.kit = km.old_kit AND kr.order_date BETWEEN km.valid_from AND km.valid_to
)
ORDER BY kr.order_no, kc.comp;

Try with:

...
,
kit_resolution(old_kit, new_kit, valid_from, valid_to) AS (
    SELECT old_kit, new_kit, valid_from, valid_to 
    FROM kit_transition_mapping km

    UNION ALL
    
    SELECT km.old_kit, kr.new_kit, kr.valid_from, kr.valid_to
    FROM kit_resolution kr
    JOIN kit_transition_mapping km ON km.valid_to < kr.valid_from
        AND km.new_kit = kr.old_kit
)
SELECT ord.order_no, ord.order_date, ord.kit AS original_kit, 
    NVL(kr.new_kit, ord.kit) AS new_kit, ord.qty, kc.comp, kc.qty AS comp_qty
FROM orders ord
LEFT JOIN kit_resolution kr ON ord.order_date BETWEEN kr.valid_from AND kr.valid_to
    AND ord.kit = kr.old_kit
JOIN kit_component kc ON kc.kit = NVL(kr.new_kit, ord.kit)
;

The kit_transition contains all the pairs(old, new) for each possible date interval:

ABE ABF 01/11/24    30/11/24
ABD ABE 01/10/24    31/10/24
ABC ABD 01/09/24    30/09/24
ABD ABF 01/11/24    30/11/24
ABC ABE 01/10/24    31/10/24
ABC ABF 01/11/24    30/11/24

Final result:

100 15/08/24    ABC ABC 1   B   2
100 15/08/24    ABC ABC 1   A   1
100 15/08/24    ABC ABC 1   C   3
101 15/09/24    ABC ABD 1   A   1
101 15/09/24    ABC ABD 1   B   2
101 15/09/24    ABC ABD 1   D   4
102 15/10/24    ABC ABE 1   B   2
102 15/10/24    ABC ABE 1   A   1
102 15/10/24    ABC ABE 1   E   5
103 15/11/24    ABC ABF 1   B   2
103 15/11/24    ABC ABF 1   A   1
103 15/11/24    ABC ABF 1   F   6
104 15/10/24    ABD ABE 1   E   5
104 15/10/24    ABD ABE 1   A   1
104 15/10/24    ABD ABE 1   B   2
105 15/11/24    ABE ABF 1   B   2
105 15/11/24    ABE ABF 1   F   6
105 15/11/24    ABE ABF 1   A   1
106 15/10/24    ABE ABE 1   E   5
106 15/10/24    ABE ABE 1   A   1
106 15/10/24    ABE ABE 1   B   2
107 01/12/24    DEX DEX 1   D   2
107 01/12/24    DEX DEX 1   E   3
107 01/12/24    DEX DEX 1   X   4