--Ph1...新設された部門の作成
INSERT INTO DEPARTMENT
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'
);
--Ph2...統合される部門の無効化
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 = 'A100' OR DEPT_CODE = 'A200';
--Ph3...社員テーブルの部門コードを統合される部門コードに変更する。また、有効なデータのみ
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
 VALID_FLG = '1' AND (DEPT_CODE = 'A100' OR DEPT_CODE = 'A200');
 
SELECT
 *
FROM
 EMP