DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER PROCEDURE dbo.ASS_TRANSFER_MULTI_MASTER_App

@p_TRANS_MULTI_MASTER_ID varchar(15),

@p_AUTH_STATUS varchar(1)  = NULL,

@p_CHECKER_ID	varchar(15)  = NULL,

@p_APPROVE_DT	VARCHAR(20) = NULL		

AS

BEGIN TRANSACTION

declare @l_OLD_LOCHIST_ID varchar(15)

declare @l_LOCHIST_ID varchar(15)

declare @l_ASSHIST_ID varchar(15)

declare @l_ASSET_ID varchar(15)

declare @l_BRANCH_ID varchar(15)

declare @l_DEPT_ID	varchar(15)

declare @l_EMP_ID	varchar(15)

declare @l_DESC	nvarchar(1000)

declare @l_LOCATION nvarchar(500)

DECLARE @l_MAKER_ID			varchar(15)

DECLARE @sToday varchar(10) = convert(varchar(10), @p_APPROVE_DT, 103)	

DECLARE @l_CUR_BRANCH_ID VARCHAR(15), @l_ASSET_TYPE VARCHAR(15), @l_AMORT_ACCTNO VARCHAR(50), 

		@l_ASSET_GROUP VARCHAR(15), @l_ET_ID VARCHAR(15), @l_TRN_REF_NO VARCHAR(15),

		@l_DO_BRANCH_ID VARCHAR(15), @l_CUR_AMORT_AMT DECIMAL(18), @l_AMORT_STATUS VARCHAR(15)	

DECLARE @NOTE NVARCHAR(500), @CRET DATETIME

SELECT @NOTE = NOTES, @CRET = CREATE_DT

FROM [ASS_TRANSFER_MULTI_MASTER] 

WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID

--LUCTV: 26-12-2018 KIEM TRA NEU TINH TRANG DANG LA 'R' THI KHONG CHO PHEP DUYET

IF(EXISTS(SELECT * FROM ASS_TRANSFER_MULTI_MASTER WHERE AUTH_STATUS ='R' AND TRANS_MULTI_MASTER_ID =@p_TRANS_MULTI_MASTER_ID))

BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, N'Thông tin điều chuyển tài sản đang bị trả về. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc

			RETURN '-1'

END

IF (SELECT CHARINDEX('ATGETD',@NOTE)) > 0

BEGIN

	SET @sToday = convert(varchar(10), @p_APPROVE_DT, 103)

	SET @P_APPROVE_DT = @sToday

END

		--APPROVE MASTER

        UPDATE [dbo].[ASS_TRANSFER_MULTI_MASTER]

		SET AUTH_STATUS = 'A',AUTH_STATUS_KT='U',

			CHECKER_ID = @p_CHECKER_ID,

			APPROVE_DT = CONVERT(datetime, @p_APPROVE_DT, 103),MAKER_ID_KT = NULL,APPROVE_DT_KT = NULL, CHECKER_ID_KT = NULL

		WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID

		DECLARE @TRANSFER_MULTI_ID varchar(15), @IS_PRIVATE int = 0, @count int =0

		DECLARE @ASSET_ID varchar(15), @BRANCH_ID_OLD VARCHAR(15), @DEP_ID_OLD VARCHAR(15), @EMP_ID_OLD VARCHAR(15)

		DECLARE DataCusor SCROLL CURSOR 

		FOR  

		SELECT A.TRANSFER_MULTI_ID,A.ASSET_ID

		FROM [dbo].[ASS_TRANSFER_MULTI_DT] A

	WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID

		OPEN DataCusor  

FETCH NEXT FROM DataCusor INTO  @TRANSFER_MULTI_ID,@ASSET_ID

WHILE @@FETCH_STATUS = 0

BEGIN	

--Lay thong tin

					SELECT	@l_ASSET_ID = ASSET_ID,

							@l_BRANCH_ID = BRANCH_ID,

							@l_DEPT_ID	= DEPT_ID,

							@l_EMP_ID	= EMP_ID,

							@l_DESC	=	[DESCRIPTION],

							@l_LOCATION = LOCATION,

							@l_MAKER_ID = MAKER_ID,

							@BRANCH_ID_OLD = BRANCH_ID_OLD,

							@DEP_ID_OLD = DEPT_ID_OLD, @EMP_ID_OLD = EMP_ID_OLD

					FROM ASS_TRANSFER_MULTI_DT

					WHERE TRANSFER_MULTI_ID = @TRANSFER_MULTI_ID

				--Validation is here

				DECLARE @ERRORSYS NVARCHAR(15) = '' 

				IF ( NOT EXISTS ( SELECT * FROM ASS_TRANSFER_MULTI_DT WHERE TRANSFER_MULTI_ID = @TRANSFER_MULTI_ID))

					SET @ERRORSYS = 'ASST-00001'

				IF @ERRORSYS <> '' 

				BEGIN

					SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

					CLOSE DataCusor

					DEALLOCATE DataCusor

					ROLLBACK TRANSACTION

					RETURN '-1'

				END 

				/*****************NEU CHI CAP NHAT NHAN VIEN THI KHONG CHUYEN QUA KE TOAN BEGIN *****************************************/

				SET @count =  @count + 1

				------THIEUVQ 240419

				IF @l_BRANCH_ID IS NOT NULL AND  @l_DEPT_ID IS NOT NULL AND @l_EMP_ID IS NOT NULL

				AND @l_BRANCH_ID <> '' AND  @l_DEPT_ID <> '' AND @l_EMP_ID <> ''

				AND @BRANCH_ID_OLD IS NOT NULL AND  @DEP_ID_OLD IS NOT NULL 

				AND @BRANCH_ID_OLD <> '' AND  @DEP_ID_OLD <> ''

				AND @l_BRANCH_ID = @BRANCH_ID_OLD AND @l_DEPT_ID = @DEP_ID_OLD

				BEGIN

					SET @IS_PRIVATE = @IS_PRIVATE + 1

					EXEC SYS_CodeMasters_Gen 'ASS_MASTER_HIST', @l_ASSHIST_ID out

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

					INSERT INTO ASS_MASTER_HIST

						SELECT @l_ASSHIST_ID, a.*

						FROM ASS_MASTER a

						where a.ASSET_ID = @l_ASSET_ID

					IF @@Error <> 0 GOTO ABORT

					UPDATE ASS_MASTER

					SET --BRANCH_ID = @l_BRANCH_ID,

						--DEPT_ID = @l_DEPT_ID,

						EMP_ID	=	@l_EMP_ID,

						APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)

					WHERE ASSET_ID = @l_ASSET_ID

					IF @@Error <> 0 GOTO ABORT

					SELECT @l_OLD_LOCHIST_ID = LOCHIST_ID FROM ASS_LOCATION_HIST WHERE  ASSET_ID = @l_ASSET_ID AND ISLEAF='Y'

					--Uptdae isleaf='N' and END_DATE = ngay hien tai cho record hien tai

					UPDATE ASS_LOCATION_HIST

					SET USE_END_DT = CONVERT(DATETIME,@P_APPROVE_DT,103),

						ISLEAF='N'

					WHERE  ASSET_ID = @l_ASSET_ID AND ISLEAF='Y'

					EXEC SYS_CodeMasters_Gen 'ASS_LOCATION_HIST', @l_LOCHIST_ID out

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

					--insert location moi

					INSERT INTO ASS_LOCATION_HIST

					(

						LOCHIST_ID, ASSET_ID, USE_START_DT, USE_END_DT, BRANCH_ID, DEPT_ID, 

						EMP_ID, LOCATION, ISLEAF, PARENT_ID

					)

					VALUES

					(

						@l_LOCHIST_ID, @l_ASSET_ID, CONVERT(DATETIME,@p_APPROVE_DT,103), NULL, @l_BRANCH_ID, @l_DEPT_ID,

						@l_EMP_ID, '', 'Y', @l_OLD_LOCHIST_ID

					)

					IF @@Error <> 0 GOTO ABORT	

					--INSERT VAO BANG ASS_TRANSACTIONS

					INSERT INTO ASS_TRANSACTIONS(ASSET_ID, TRN_ID, TRN_TYPE, TRN_DATE, RECORD_STATUS, AUTH_STATUS, 	

							[MAKER_ID], [CREATE_DT], [CHECKER_ID], [APPROVE_DT]

					)VALUES

					(

						@l_ASSET_ID, @TRANSFER_MULTI_ID, 'TRANSFER', CONVERT(DATETIME, @sToday, 103), '1', 'A', 

						@l_MAKER_ID, CONVERT(DATETIME, @sToday, 103), @p_CHECKER_ID, 	CONVERT(DATETIME, @p_APPROVE_DT, 103) 

					)

					IF @@Error <> 0 GOTO ABORT

				END

				ELSE

				BEGIN

					IF @IS_PRIVATE <> @count AND @IS_PRIVATE > 0 --DA CO 1 TAI SAN KHONG THUOC DIEU CHUYEN NOI BO

					BEGIN

						SELECT '-1' Result,'' COL_MULTI_MASTER_ID, N'Tài sản điều chuyển nội bộ phải tách biệt tài sản điều chuyển khác, vì giao dịch nội bộ sẽ không qua kế toán. Vui lòng kiểm tra lại danh sách.' ErrorDesc 

						CLOSE DataCusor

						DEALLOCATE DataCusor

						ROLLBACK TRANSACTION

						RETURN '-1'

					END

				END

				/*****************NEU CHI CAP NHAT NHAN VIEN THI KHONG CHUYEN QUA KE TOAN END *****************************************/

	FETCH NEXT FROM DataCusor INTO  @TRANSFER_MULTI_ID,@ASSET_ID

END

	--CAP NHAT BO QUA KE TOAN

	IF @IS_PRIVATE = @count

	BEGIN

		UPDATE [dbo].[ASS_TRANSFER_MULTI_MASTER]

		SET AUTH_STATUS_KT = 'A',

			CHECKER_ID_KT = 'admin',

			APPROVE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),

			CREATE_DT_KT = CONVERT(datetime, @p_APPROVE_DT, 103),

			MAKER_ID_KT = 'admin'

		WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID

		UPDATE ASS_TRANSFER_MULTI_DT 

		SET 

			AUTH_STATUS = 'A', 

			CHECKER_ID_KT = 'admin', 

			APPROVE_DT_KT = CONVERT(DATETIME,@p_APPROVE_DT,103),

			CREATE_DT_KT = CONVERT(DATETIME,@p_APPROVE_DT,103),

			MAKER_ID_KT = 'admin'

		WHERE [TRANS_MULTI_MASTER_ID] = @p_TRANS_MULTI_MASTER_ID

		IF @@Error <> 0 GOTO ABORT

	END

	CLOSE DataCusor

	DEALLOCATE DataCusor

COMMIT TRANSACTION

SELECT '0' as Result, @p_TRANS_MULTI_MASTER_ID  COL_MULTI_MASTER_ID, '' ErrorDesc

RETURN '0'

ABORT:

BEGIN

	ROLLBACK TRANSACTION

		CLOSE DataCusor

		DEALLOCATE DataCusor		

		SELECT '-1' as Result, '' COL_MULTI_MASTER_ID, ERROR_MESSAGE() ErrorDesc

		RETURN '-1'

End