Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with table1(ID, NAME, INSERT_DT) as

(

SELECT 1, 'XY', to_date('29/04/2021', 'DD/mm/YYYY') from dual

table2(ID_FK, DESC_, REC_BEG_DT, REC_END_DT, REC_INDC, SYSTEM_CODE, SERVICE_SRT_DT, SERVICE_END_DT) as (

select 1,'XY DESC1', to_date('1/4/2021', 'DD/mm/YYYY'), to_date('30/04/2021', 'DD/mm/YYYY'), 'I' , 'ABC', to_date('1/3/2021', 'DD/mm/YYYY'), to_date('30/03/2021', 'DD/mm/YYYY') from dual union all

select 1,'XY DESC2', to_date('1/3/2021', 'DD/mm/YYYY'), to_date('30/03/2021', 'DD/mm/YYYY'), 'I' , 'ABC', to_date('1/4/2021', 'DD/mm/YYYY'), to_date('30/04/2021', 'DD/mm/YYYY') from dual union all

select 1,'XY DESC3', to_date('1/3/2021', 'DD/mm/YYYY'), to_date('30/03/2021', 'DD/mm/YYYY'), 'I' , 'ABC', to_date('1/3/2021', 'DD/mm/YYYY'), to_date('30/03/2021', 'DD/mm/YYYY') from dual

)

select *

from

(

select table1.INSERT_DT, table2.* ,

-- priority flags of optional conditions

f.flags,

row_number() over(partition by Table1.id order by f.flags) rn

from table1

join Table2 on

-- mandatory condition

Table1.ID=Table2.ID_FK

AND SYSTEM_CODE='ABC'

cross join lateral(

select

case when REC_INDC='A' then '0' else '9' end

|| case when Table1.INSERT_DT >= Table2.SERVICE_SRT_DT AND Table1.INSERT_DT <= Table2.SERVICE_END_DT then '0' else '9' end

|| case when Table1.INSERT_DT >= Table2.REC_BEG_DT AND Table1.INSERT_DT <= Table2.REC_END_DT then '0' else '9' end flags

from dual ) f

) t

--where rn = 1

INSERT_DT	ID_FK	DESC_	REC_BEG_DT	REC_END_DT	REC_INDC	SYSTEM_CODE	SERVICE_SRT_DT	SERVICE_END_DT	FLAGS	RN
29-APR-21	1	XY DESC2	01-MAR-21	30-MAR-21	I	ABC	01-APR-21	30-APR-21	909	1
29-APR-21	1	XY DESC1	01-APR-21	30-APR-21	I	ABC	01-MAR-21	30-MAR-21	990	2
29-APR-21	1	XY DESC3	01-MAR-21	30-MAR-21	I	ABC	01-MAR-21	30-MAR-21	999	3