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

old_kit	new_kit	valid_from	valid_to
ABC	ABD	01-Sep-24	30-Sep-24
ABD	ABE	01-Oct-24	31-Oct-24
ABE	ABF	01-Nov-24	30-Nov-24

orders

order_no	kit	order_date	qty
100	ABC	15-Aug-24	1
101	ABC	15-Sep-24	1
102	ABC	15-Oct-24	1
103	ABC	15-Nov-24	1
104	ABD	15-Oct-24	1
105	ABE	15-Nov-24	1
106	ABE	15-Oct-24	1
107	DEX	01-Dec-24	1

kit_component

kit	comp	qty
ABC	A	1
ABC	B	2
ABC	C	3
ABD	A	1
ABD	B	2
ABD	D	4
ABE	A	1
ABE	B	2
ABE	E	5
ABF	A	1
ABF	B	2
ABF	F	6
DEX	D	2
DEX	E	3
DEX	X	4

expected_result

order_no	order_date	original_kit	new_kit	order_qty	comp	comp_qty
100	15-Aug-24	ABC	ABC	1	A	1
100	15-Aug-24	ABC	ABC	1	B	2
100	15-Aug-24	ABC	ABC	1	C	3
101	15-Sep-24	ABC	ABD	1	A	1
101	15-Sep-24	ABC	ABD	1	B	2
101	15-Sep-24	ABC	ABD	1	D	4
102	15-Oct-24	ABC	ABE	1	A	1
102	15-Oct-24	ABC	ABE	1	B	2
102	15-Oct-24	ABC	ABE	1	E	5
103	15-Nov-24	ABC	ABF	1	A	1
103	15-Nov-24	ABC	ABF	1	B	2
103	15-Nov-24	ABC	ABF	1	F	6
104	15-Oct-24	ABD	ABE	1	A	1
104	15-Oct-24	ABD	ABE	1	B	2
104	15-Oct-24	ABD	ABE	1	E	5
105	15-Nov-24	ABE	ABF	1	A	1
105	15-Nov-24	ABE	ABF	1	B	2
105	15-Nov-24	ABE	ABF	1	F	6
106	15-Oct-24	ABE	ABE	1	A	1
106	15-Oct-24	ABE	ABE	1	B	2
106	15-Oct-24	ABE	ABE	1	E	5
107	01-Dec-24	DEX	DEX	1	D	2
107	01-Dec-24	DEX	DEX	1	E	3
107	01-Dec-24	DEX	DEX	1	X	4

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;

Solution

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

kit	comp	qty
ABC	A	1
ABC	B	2
ABC	C	3
ABD	A	1
ABD	B	2
ABD	D	4
ABE	A	1
ABE	B	2
ABE	E	5
ABF	A	1
ABF	B	2
ABF	F	6
DEX	D	2
DEX	E	3
DEX	X	4

kit	comp	qty
ABC	A	1
ABC	B	2
ABC	C	3
ABD	A	1
ABD	B	2
ABD	D	4
ABE	A	1
ABE	B	2
ABE	E	5
ABF	A	1
ABF	B	2
ABF	F	6
DEX	D	2
DEX	E	3
DEX	X	4

kit	comp	qty
ABC	A	1
ABC	B	2
ABC	C	3
ABD	A	1
ABD	B	2
ABD	D	4
ABE	A	1
ABE	B	2
ABE	E	5
ABF	A	1
ABF	B	2
ABF	F	6
DEX	D	2
DEX	E	3
DEX	X	4