with tmp as (
    select layer,up_dept_code,update_user_name from department
    where dept_code='A100'
)
insert into department 
select
    'B300','2023-04-01',null,'新設部門',tmp.layer,tmp.up_dept_code,1,tmp.update_user_name,'2023-04-01 00:00:00' from tmp;

update department 
    set end_date='2023-03-31',valid_flg='0',update_user_name='ORG_CHG',user_update_datetime='2023-03-31 23:59:59' 
where dept_code in ('A100','A200');

update emp 
    set dept_code='B300',start_date='2023-04-01',update_user_name='ORG_CHG',user_update_datetime='2023-04-01 00:00:00:'
where dept_code in ('A100','A200') and valid_flg='1';


select * from department;

select * from emp;