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