WITH D AS
(
	SELECT
		LAYER
		,UP_DEPT_CODE
		,UPDATE_USER_NAME
	FROM
		DEPARTMENT
	WHERE
		DEPT_CODE = 'A100'
)		

--処理1 :新設された部門('B300')を'A100'のデータを利用して作成する。		
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 D)
	,(SELECT UP_DEPT_CODE FROM D)
	, '1'
	, (SELECT UPDATE_USER_NAME FROM D)
	, '2023-04-01 00:00:00')
;

--処理2 :統合される部門('A100','A200')を無効に変更する。
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')
;

--処理3 : 社員テーブル(EMP)の部門コード(DEPT_CODE)が統合される部門コードになっているデータの部門コードを、新設される部門コードに変更する。ただし、有効なデータ(VALID_FLG = '1')のみ対象とする。
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 IN ('A100','A200')
;