Oracle 18c

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

create table cfl_param (
contract_type varchar2(20),
new_cfl_month number,
cfl_percentage number
);
create table cfl_table (
contract_id varchar2(20),
contract_type varchar2(20),
cfl_analyze varchar2(20),
cfl_date date,
cfl_value number
);
insert into cfl_param values
('A', 6, 0.4);
1 rows affected

insert into cfl_param values
('A', 9, 0.3);
1 rows affected

insert into cfl_table values
('A_1', 'A', 'N', date '2020-07-31', 1000);
1 rows affected

insert into cfl_param values
('A', 0, 0.3);
1 rows affected

select ct.contract_id, ct.contract_type, 'Y' as cfl_analyze,
  add_months(ct.cfl_date, cp.new_cfl_month) as cfl_date,
  ct.cfl_value * cp.cfl_percentage as cfl_value
from cfl_table ct
join cfl_param cp on cp.contract_type = ct.contract_type
where ct.contract_id = 'A_1';


CONTRACT_ID
CONTRACT_TYPE
CFL_ANALYZE
CFL_DATE
CFL_VALUE


A_1
A
Y
31-JAN-21
400

A_1
A
Y
30-APR-21
300

A_1
A
Y
31-JUL-20
300


insert into cfl_table (contract_id, contract_type, cfl_analyze, cfl_date, cfl_value)
select ct.contract_id, ct.contract_type, 'Y',
  add_months(ct.cfl_date, cp.new_cfl_month),
  ct.cfl_value * cp.cfl_percentage
from cfl_table ct
join cfl_param cp on cp.contract_type = ct.contract_type
where ct.contract_id = 'A_1';
3 rows affected

select * from cfl_table;


CONTRACT_ID
CONTRACT_TYPE
CFL_ANALYZE
CFL_DATE
CFL_VALUE


A_1
A
N
31-JUL-20
1000

A_1
A
Y
31-JAN-21
400

A_1
A
Y
30-APR-21
300

A_1
A
Y
31-JUL-20
300

CONTRACT_ID	CONTRACT_TYPE	CFL_ANALYZE	CFL_DATE	CFL_VALUE
A_1	A	N	31-JUL-20	1000
A_1	A	Y	31-JAN-21	400
A_1	A	Y	30-APR-21	300
A_1	A	Y	31-JUL-20	300