Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


alter session set nls_date_format='yyyy-mm-dd'
create table data (id, company_name, vat_id, url, created) as
    select 1, 'aaa',   '123', 'aaa.com', date'2021-05-01' from dual union all
    select 2, 'bbb',   '111', 'bbb.de' , date'2021-05-01' from dual union all
    select 3, 'ccc',   '345', 'ccc.de ', date'2021-05-01' from dual union all
    select 5, 'ddd',   '098', 'ddd.de' , date'2021-05-21' from dual
4 rows affected

create table data_hist (tag, id, company_name, vat_id, url, delete_flag) as
    select date'2021-05-25', 1, 'aaa',   '123',   'aaa.com', null from dual union all
    select date'2021-05-25', 3, 'ccc',   '345',   'ccc.de ', null from dual union all
    select date'2021-05-25', 4, 'xxx',   '789',   'xxx.com',    1 from dual union all
    select date'2021-05-21', 1, 'aaa-2', '123',   'aaa.com', null from dual union all
    select date'2021-05-21', 3, 'ccc',   '345-2', 'ccc.de ', null from dual union all
    select date'2021-05-20', 1, 'aaa-3', '123-3', 'aaa.com', null from dual union all
    select date'2021-05-20', 2, 'bbb',   '111',   'bbb.de ', null from dual
7 rows affected

select * from data;


ID
COMPANY_NAME
VAT_ID
URL
CREATED


1
aaa
123
aaa.com
2021-05-01

2
bbb
111
bbb.de
2021-05-01

3
ccc
345
ccc.de
2021-05-01

5
ddd
098
ddd.de
2021-05-21


select * from data_hist;


TAG
ID
COMPANY_NAME
VAT_ID
URL
DELETE_FLAG


2021-05-25
1
aaa
123
aaa.com
null

2021-05-25
3
ccc
345
ccc.de
null

2021-05-25
4
xxx
789
xxx.com
1

2021-05-21
1
aaa-2
123
aaa.com
null

2021-05-21
3
ccc
345-2
ccc.de
null

2021-05-20
1
aaa-3
123-3
aaa.com
null

2021-05-20
2
bbb
111
bbb.de
null


with param (asofday) as (
    select date'2021-05-20' from dual 
), t (tag, id, company_name, vat_id, url, delete_flag) as (
    select date'9999-12-31', id, company_name, vat_id, url, null
    from data, param
    where created <= param.asofday 
    union all
    select tag, id, company_name, vat_id, url, delete_flag
    from data_hist)
select lastchanged,	id,	company_name, vat_id, url, delete_flag
from t t1
cross apply (
    select max (t2.tag) keep (dense_rank last order by tag desc) lastchanged
    from t t2, param
    where t2.id = t1.id
    and t2.tag >= param.asofday
    group by t2.id
) where tag = lastchanged
order by id


LASTCHANGED
ID
COMPANY_NAME
VAT_ID
URL
DELETE_FLAG


2021-05-20
1
aaa-3
123-3
aaa.com
null

2021-05-20
2
bbb
111
bbb.de
null

2021-05-21
3
ccc
345-2
ccc.de
null

2021-05-25
4
xxx
789
xxx.com
1


create global temporary table stage (id, company_name, vat_id, url, created) as 
    select * from data where 1=0
create or replace type stagerow is object (
    id int, company_name varchar(96), vat_id varchar(96), url varchar(96), created date, flagexists int)
/
create or replace type stagetab is table of stagerow
/
insert into stage 
    select 1, 'aaa',   '123', 'aaa.com', date'2021-05-01' from dual union all
    select 2, 'bbb',   '111', 'bbb.de' , date'2021-05-01' from dual union all
    select 3, 'ccc',   '345', 'ccc.de ', date'2021-05-01' from dual union all
    select 4, 'xxx',   '789', 'xxx.com', date'2021-05-01' from dual union all
    select 5, 'ddd',   '098', 'ddd.de' , date'2021-05-21' from dual
5 rows affected

select * from stage


ID
COMPANY_NAME
VAT_ID
URL
CREATED


1
aaa
123
aaa.com
2021-05-01

2
bbb
111
bbb.de
2021-05-01

3
ccc
345
ccc.de
2021-05-01

4
xxx
789
xxx.com
2021-05-01

5
ddd
098
ddd.de
2021-05-21


declare
    st stagetab;
begin
    select stagerow (
        s.id, s.company_name, s.vat_id, s.url, s.created, case when d.id is null then 0 else 1 end)  
    bulk collect into st
    from stage s
    left join data d on d.id = s.id;
    dbms_output.put_line ('staged '||sql%rowcount); 

    insert into data_hist
    select trunc (sysdate), id, company_name, vat_id, url, null  
    from data d
    where exists (
        select 1 
        from table (st) s
        where s.id = d.id and s.flagexists = 1);
    dbms_output.put_line ('copied '||sql%rowcount); 
    
    delete 
    from data d
    where exists (
        select 1 
        from table (st) s
        where s.id = d.id and s.flagexists = 1);
    dbms_output.put_line ('deleted '||sql%rowcount); 

    insert into data 
        select id, company_name, vat_id, url, created
        from table (st);
    dbms_output.put_line ('inserted '||sql%rowcount); 
end;
/
1 rows affected

dbms_output:
staged 5
copied 4
deleted 4
inserted 5
ID	COMPANY_NAME	VAT_ID	URL	CREATED
1	aaa	123	aaa.com	2021-05-01
2	bbb	111	bbb.de	2021-05-01
3	ccc	345	ccc.de	2021-05-01
5	ddd	098	ddd.de	2021-05-21
TAG	ID	COMPANY_NAME	VAT_ID	URL	DELETE_FLAG
2021-05-25	1	aaa	123	aaa.com	null
2021-05-25	3	ccc	345	ccc.de	null
2021-05-25	4	xxx	789	xxx.com	1
2021-05-21	1	aaa-2	123	aaa.com	null
2021-05-21	3	ccc	345-2	ccc.de	null
2021-05-20	1	aaa-3	123-3	aaa.com	null
2021-05-20	2	bbb	111	bbb.de	null