insert into
    DEPARTMENT(
        DEPT_CODE,
        START_DATE,
        END_DATE,
        DEPT_NAME,
        LAYER,
        UP_DEPT_CODE,
        VALID_FLG,
        UPDATE_USER_NAME,
        USER_UPDATE_DATETIME
    )
values
    (
        'B300',
        '2023-04-01',
        NULL,
        '新設部門',
        (
            select
                layer
            from
                DEPARTMENT
            where
                dept_code = 'A100'
        ),
        (
            select
                UP_DEPT_CODE
            from
                DEPARTMENT
            where
                dept_code = 'A100'
        ),
        '1',
        (
            select
                UPDATE_USER_NAME
            from
                DEPARTMENT
            where
                dept_code = 'A100'
        ),
        '2023-04-01 00:00:00'
    );

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';