AMS - VB

UPDATE B SET B.BRANCH_ID = C.BRANCH_ID, B.DEP_ID = D.DEP_ID,B.POS_CODE = A.POS_CODE, B.POS_NAME = A.POS_NAME

FROM CM_EMPLOYEE_LOG A

INNER JOIN CM_EMPLOYEE B ON A.EMP_CODE = B.EMP_CODE

LEFT JOIN CM_BRANCH C ON A.BRANCH_CODE = C.BRANCH_CODE

LEFT JOIN CM_DEPARTMENT D ON A.DEP_CODE = D.DEP_CODE

DECLARE @EMP_CODE VARCHAR(100), @EMP_NAME NVARCHAR(1000), @BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15), @POS_NAME NVARCHAR(500), @POS_CODE NVARCHAR(500)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR

SELECT A.EMP_CODE,A.EMP_NAME,C.BRANCH_ID,D.DEP_ID,A.POS_NAME,A.POS_CODE 

FROM CM_EMPLOYEE_LOG A

LEFT JOIN CM_EMPLOYEE B ON A.EMP_CODE = B.EMP_CODE

LEFT JOIN CM_BRANCH C ON A.BRANCH_CODE = C.BRANCH_CODE

LEFT JOIN CM_DEPARTMENT D ON A.DEP_CODE = D.DEP_CODE

WHERE B.EMP_ID IS NULL AND A.EMP_CODE <> '0000-0000'

OPEN cur

FETCH NEXT FROM cur INTO @EMP_CODE ,@EMP_NAME ,@BRANCH_ID , @DEP_ID ,@POS_NAME,@POS_CODE

WHILE @@FETCH_STATUS = 0 BEGIN

	DECLARE @l_EMP_ID VARCHAR(15)

	EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID OUT		

INSERT INTO CM_EMPLOYEE([EMP_ID],[EMP_CODE],[EMP_NAME],[BRANCH_ID],[DEP_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT])

VALUES(@l_EMP_ID ,@EMP_CODE ,@EMP_NAME ,@BRANCH_ID ,@DEP_ID ,N'EMP_GEN_26/04/2023' ,'1' ,'A' ,'admin' ,GETDATE() ,'admin' ,GETDATE() )

	FETCH NEXT FROM cur INTO @EMP_CODE ,@EMP_NAME ,@BRANCH_ID , @DEP_ID ,@POS_NAME,@POS_CODE

END

CLOSE cur

DEALLOCATE cur