DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

/*

[CM_EMPLOYEE_Upd]

@p_EMP_ID= 'EMP000000000002' ,

@p_EMP_CODE	 ='EMP2013/0001',

@p_EMP_NAME	 = NULL ,

@p_BRANCH_ID= NULL ,

@p_DEP_ID= NULL ,

@p_NOTES= NULL ,

@p_RECORD_STATUS= NULL ,

@p_AUTH_STATUS = NULL ,

@p_MAKER_ID	= NULL ,

@p_CREATE_DT = NULL,

@p_CHECKER_ID= NULL ,

@p_APPROVE_DT = NULL

*/

ALTER PROCEDURE [dbo].[CM_EMPLOYEE_Upd]

@p_EMP_ID	varchar(15) = null ,

@p_EMP_CODE	varchar(15) = NULL ,

@p_EMP_NAME	nvarchar(50) = NULL ,

@p_BRANCH_ID	varchar(15) = NULL ,

@p_DEP_ID	varchar(15) = NULL ,

@p_NOTES	nvarchar(1000) = NULL ,

@p_RECORD_STATUS	varchar(1) = NULL ,

@p_AUTH_STATUS	varchar(1) = NULL ,

@p_MAKER_ID	varchar(15) = NULL ,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_CHECKER_ID	varchar(15) = NULL ,

@p_APPROVE_DT	VARCHAR(20) = NULL

AS

--Validation is here

	DECLARE @ERRORSYS NVARCHAR(15) = '' 

	IF ( EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE EMP_CODE = @p_EMP_CODE AND EMP_ID!=@p_EMP_ID))

	BEGIN

		SET @ERRORSYS = 'EMP-0001'

	END

	IF @ERRORSYS <> '' 

	BEGIN

		--SELECT ErrorCode Result, ''  EMP_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

		SELECT @ERRORSYS Result, ''  EMP_ID, N'Nhân viên có mã: '+ @p_EMP_CODE + N' đã tồn tại trong hệ thống. Vui lòng kiểm tra lại thông tin' ErrorDesc 

		RETURN '-1'

	END

BEGIN TRANSACTION

		UPDATE CM_EMPLOYEE SET [EMP_CODE] = @p_EMP_CODE,[EMP_NAME] = @p_EMP_NAME,[BRANCH_ID] = @p_BRANCH_ID,[DEP_ID] = @p_DEP_ID,[NOTES] = @p_NOTES,[RECORD_STATUS] = @p_RECORD_STATUS,[AUTH_STATUS] = @p_AUTH_STATUS,[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103)

WHERE  EMP_ID= @p_EMP_ID

		IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

		SELECT '0' as Result, @p_EMP_ID  EMP_ID, '' ErrorDesc

		RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' EMP_ID, '' ErrorDesc

		RETURN '-1'

End

¿

ALTER PROCEDURE [dbo].[CM_EMPLOYEE_Ins]

@p_EMP_CODE	varchar(15)  = NULL,

@p_EMP_NAME	nvarchar(50)  = NULL,

@p_BRANCH_ID	varchar(15)  = NULL,

@p_DEP_ID	varchar(15)  = NULL,

@p_NOTES	nvarchar(1000)  = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_AUTH_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(15)  = NULL,

@p_CREATE_DT	VARCHAR(20) = NULL,

@p_CHECKER_ID	varchar(15)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL

AS

--Validation is here

DECLARE @ERRORSYS NVARCHAR(15) = '' 

  IF ( EXISTS ( SELECT * FROM CM_EMPLOYEE WHERE EMP_CODE=@p_EMP_CODE AND RECORD_STATUS = '1'))

	 SET @ERRORSYS = 'EMP-0001'

IF @ERRORSYS <> '' 

BEGIN

	SELECT @ERRORSYS Result, ''  EMP_ID, N'Nhân viên có mã: '+ @p_EMP_CODE + N' đã tồn tại trong hệ thống. Vui lòng kiểm tra lại thông tin' ErrorDesc 

	RETURN '-1'

END

BEGIN TRANSACTION

DECLARE @l_EMP_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out		

		IF @l_EMP_ID='' OR @l_EMP_ID IS NULL GOTO ABORT

		print @l_EMP_ID

		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 ,@p_EMP_CODE ,@p_EMP_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )

		IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @l_EMP_ID  EMP_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' EMP_ID, '' ErrorDesc

		RETURN '-1'

End