ALTER PROCEDURE [dbo].[TR_REQ_PAY_TRANSFER_V1_Upd]
@p_TRANSFER_ID	varchar(15),
@p_TRANSFER_CODE varchar(15),
@p_REQ_PAY_ID	varchar(15)	,
@p_MAKER_ID	varchar(15)	,
@p_CREATE_DT	VARCHAR(25)	,
@p_AUTH_STATUS	varchar(15)	,
@p_CHECKER_ID	varchar(15)	,
@p_APPROVE_DT	datetime,	
@p_FR_USER	varchar(15)	,
@p_TO_USER	varchar(15)	,
@p_REASON	nvarchar(4000),	
@p_NOTES	nvarchar(4000)	,
@p_DESCRIPTION	nvarchar(4000),	
@p_CONFIRM_NOTES	nvarchar(4000)	,
@p_FR_ACC VARCHAR(50),
@p_TO_ACC VARCHAR(50),
@p_MAKER_ID_KT	varchar(15),
@P_LISTASSET XML,
@P_LISTASSET_v1 XML
AS
	IF(@p_FR_USER ='' or @p_FR_USER IS NULL)
	BEGIN
			--ROLLBACK TRANSACTION
			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Người cho phiếu không được để trống' AS ErrorDesc 
			RETURN '-1'
	END
	IF(@p_TO_USER ='' or @p_TO_USER IS NULL)
	BEGIN
			--ROLLBACK TRANSACTION
			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Người nhận phiếu không được để trống' AS ErrorDesc 
			RETURN '-1'
	END
	IF(@p_CONFIRM_NOTES ='' or @p_CONFIRM_NOTES IS NULL)
	BEGIN
			--ROLLBACK TRANSACTION
			SELECT '-1' Result, @p_TRANSFER_ID AS TRANSFER_ID, N'Lý do điều chuyển không được để trống' AS ErrorDesc 
			RETURN '-1'
	END
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET
	DECLARE TransferDetail CURSOR LOCAL FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/TransferDetail',2)
	WITH 
	(
		TRAN_DT_ID	varchar(15)				 ,
		TRANSFER_ID	varchar(15)				 ,
		REQ_PAY_ID	varchar(15)				 ,
		TOTAL_AMT	decimal(18, 0)			 ,
		TOAL_AMT_DO	decimal(18, 0)			 ,
		TOTAL_AMT_REMAIN	decimal(18, 0)	 ,
		TOTAL_AMT_REAL	decimal(18, 0)		 ,
		TOTAL_AMT_REMAIN_FN	decimal(18, 0)	 ,
		NOTES	nvarchar(4000)				 ,
		REASON	nvarchar(4000)				 
	)
	OPEN TransferDetail
	PRINT 'PASS KHOI TAO'
	BEGIN TRANSACTION
		--insert master
		--DECLARE @l_CONTRACT_ID VARCHAR(15)
		UPDATE TR_REQ_PAY_TRANSFER SET REASON =@p_REASON, CONFIRM_NOTES =@p_CONFIRM_NOTES WHERE TRANSFER_ID =@p_TRANSFER_ID
		IF @@Error <> 0 GOTO ABORT
		PRINT 'INSERT MASTER SUCCESS'

		Declare 
		@l_TRAN_DT_ID	varchar(15)				 ,
		@l_TRANSFER_ID	varchar(15)				 ,
		@l_REQ_PAY_ID	varchar(15)				 ,
		@l_TOTAL_AMT	decimal(18, 0)			 ,
		@l_TOAL_AMT_DO	decimal(18, 0)			 ,
		@l_TOTAL_AMT_REMAIN	decimal(18, 0)	 ,
		@l_TOTAL_AMT_REAL	decimal(18, 0)		 ,
		@l_TOTAL_AMT_REMAIN_FN	decimal(18, 0)	 ,
		@l_NOTES	nvarchar(4000)				 ,
		@l_REASON	nvarchar(4000)				 
		--Insert detail
		DELETE FROM TR_REQ_PAY_TRANSFER_DT WHERE TRANSFER_ID =@p_TRANSFER_ID OR TRANSFER_ID IS NULL OR TRANSFER_ID =''
		FETCH NEXT FROM TransferDetail INTO @l_TRAN_DT_ID,@l_TRANSFER_ID,@l_REQ_PAY_ID,@l_TOTAL_AMT,@l_TOAL_AMT_DO,@l_TOTAL_AMT_REMAIN,@l_TOTAL_AMT_REAL,@l_TOTAL_AMT_REMAIN_FN,@l_NOTES,@l_REASON
		-- Validate lưới chi tiết rỗng hay không
		IF (@@FETCH_STATUS < 0)
		BEGIN
			ROLLBACK TRANSACTION
			CLOSE TransferDetail;
			DEALLOCATE TransferDetail;
			SELECT '-1' as Result, '' AS TRANSFER_ID, N'Thông tin phiếu đề nghị tạm ứng không được để trống' ErrorDesc
			RETURN '-1'
		END
		DECLARE @IDX INT = 0
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			SET @IDX = @IDX + 1
			-- Kiểm tra nếu PĐN tạm ứng đang được điều chuyển ở một giao dịch khác thì không cho tạo
			IF EXISTS (SELECT A.REQ_PAY_ID FROM TR_REQ_PAY_TRANSFER_DT A
						LEFT JOIN TR_REQ_PAY_TRANSFER B ON A.TRANSFER_ID = B.TRANSFER_ID AND A.REQ_PAY_ID =@l_REQ_PAY_ID
						WHERE B.TRANSFER_ID <> @p_TRANSFER_ID AND B.AUTH_STATUS = 'U')
			BEGIN
				ROLLBACK TRANSACTION
				CLOSE TransferDetail;
				DEALLOCATE TransferDetail;
				SELECT '-1' as Result, '' AS TRANSFER_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@IDX) + N' Phiếu đề nghị tạm ứng đang được điều chuyển ở một giao dịch khác' ErrorDesc
				RETURN '-1'
			END
			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_TRANSFER_DT', @l_TRAN_DT_ID out
			IF @l_TRAN_DT_ID='' OR @l_TRAN_DT_ID IS NULL GOTO ABORT
			INSERT INTO TR_REQ_PAY_TRANSFER_DT(TRAN_DT_ID, TRANSFER_ID,REQ_PAY_ID,TOTAL_AMT, TOAL_AMT_DO, TOTAL_AMT_REMAIN, TOTAL_AMT_REAL,  TOTAL_AMT_REMAIN_FN, NOTES, REASON)
			VALUES ( @l_TRAN_DT_ID,@p_TRANSFER_ID,@l_REQ_PAY_ID,@l_TOTAL_AMT,@l_TOAL_AMT_DO,@l_TOTAL_AMT_REMAIN,@l_TOTAL_AMT_REAL,@l_TOTAL_AMT_REMAIN_FN,@l_NOTES,@l_REASON)
			IF @@Error <> 0 GOTO ABORT			
		-- next Group_Id
			FETCH NEXT FROM TransferDetail INTO @l_TRAN_DT_ID,@l_TRANSFER_ID,@l_REQ_PAY_ID,@l_TOTAL_AMT,@l_TOAL_AMT_DO,@l_TOTAL_AMT_REMAIN,@l_TOTAL_AMT_REAL,@l_TOTAL_AMT_REMAIN_FN,@l_NOTES,@l_REASON
		END
	DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(500), @AMT decimal(18,2),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),
	@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000), @GL_CODE VARCHAR(10)
	DECLARE @hdoc2 INT;
	EXEC sp_xml_preparedocument @hdoc2 OUTPUT, @P_LISTASSET_v1;
	DECLARE XmlData CURSOR LOCAL FOR
	SELECT *
	FROM
	OPENXML(@hdoc2, '/Root/XmlData', 2)
	WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),
	BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000),GL_CODE VARCHAR(10))
	OPEN XmlData;
		DECLARE @DR_CR_NAME NVARCHAR(50), @INDEX INT =0, @ACC_NAME_FN VARCHAR(500)
		--
		DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID
		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
		WHILE @@fetch_status=0 BEGIN
			SET @INDEX= @INDEX+1
			IF(@DR_CR='D')
			BEGIN
				SET @DR_CR_NAME =N'Nợ'
			END
			ELSE
			BEGIN
				SET @DR_CR_NAME =N'Có'
			END
			IF(@DR_CR IS NULL OR @DR_CR ='')
			BEGIN
				ROLLBACK TRANSACTION
				CLOSE XmlData;
				DEALLOCATE XmlData;
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản nợ không được phép để trống' ErrorDesc
				RETURN '-1'
			END
			IF(@ACCT IS NULL OR @ACCT ='')
			BEGIN
				ROLLBACK TRANSACTION
				--CLOSE XmlData;
				--DEALLOCATE XmlData;
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản có không được phép để trống' ErrorDesc
				RETURN '-1'
			END
			IF(@TRN_DESC IS NULL OR @TRN_DESC ='')
			BEGIN
				ROLLBACK TRANSACTION
				--CLOSE XmlData;
				--DEALLOCATE XmlData;
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Diễn giải hạch toán không được phép để trống' ErrorDesc
				RETURN '-1'
			END
			IF(@BRANCH_ID IS NULL OR @BRANCH_ID ='')
			BEGIN
				ROLLBACK TRANSACTION
				--CLOSE XmlData;
				--DEALLOCATE XmlData;
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Đơn vị không được phép để trống' ErrorDesc
				RETURN '-1'
			END
			SET @ACC_NAME_FN =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)
			IF(@ACC_NAME_FN IS NULL OR @ACC_NAME_FN ='')
			BEGIN
					SET @ACC_NAME_FN =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT_PAY WHERE ACC_NUM =@ACCT)
			END
			IF((@ACCT_NAME IS NULL OR @ACCT_NAME ='' OR @ACC_NAME_FN IS NULL OR @ACC_NAME_FN ='' ) OR (ISNULL(@ACCT_NAME,'') <> ISNULL(@ACC_NAME_FN,'')))
			BEGIN
				ROLLBACK TRANSACTION
				--CLOSE XmlData;
				--DEALLOCATE XmlData;
				SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc
				RETURN '-1'
			END
			IF(LEN(@ACCT) >9 AND (@GL_CODE IS NULL OR @GL_CODE =''))
			BEGIN
				ROLLBACK TRANSACTION
					CLOSE XmlData;
					DEALLOCATE XmlData;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N'Nếu bạn đang hạch toán CASA. Vui lòng bấm vào nút kiểm tra tài khoản để lấy chính xác thông tin tài khoản (tên tài khoản, tài khoản GL tương tứng) từ Core'  ErrorDesc
					RETURN '-1'
			---
			END
			DECLARE @p_ET_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @p_ET_ID OUT;
			IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;
			INSERT INTO TR_REQ_PAY_ENTRIES (REQ_PAY_DT_ID,REQ_PAY_ID,ENTRY_PAIR,DR_CR,DR_CR_NAME,ACCT,ACCT_NAME,AMT,CURRENCY,EXC_RATE,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT)
			VALUES (@p_ET_ID,@p_TRANSFER_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,UPPER(@TRN_DESC),GETDATE(),@p_MAKER_ID_KT)
			-- NEU CHUA CO TAI KHOAN THI THEM VO CM_ACCOUNT
			IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@ACCT))
			BEGIN
				INSERT INTO CM_ACCOUNT (ACC_NO, ACC_NAME, TK_GL, TK_GL_NAME, MAKER_ID, CHECKER_ID) VALUES
				(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin')
			END
			ELSE
			BEGIN
				IF(@GL_CODE IS NOT NULL AND @GL_CODE <> '')
				BEGIN
					UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE)
					WHERE ACC_NO=@ACCT
				END
			END
			IF @@error<>0 GOTO ABORT;
		FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
		END;
	CLOSE XmlData;
	DEALLOCATE XmlData;	
	IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID))
			BEGIN
				DECLARE  @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)
				SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID AND DR_CR ='C'),0)
				SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRANSFER_ID AND DR_CR ='D'),0)
				IF(@SUM_CR <> @SUM_DR)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có: ' + FORMAT(@SUM_DR,'#,###') ErrorDesc
					RETURN '-1'
				END
	END
COMMIT TRANSACTION
SELECT '0' as Result, @p_TRANSFER_ID  TRANSFER_ID, N'Giao dịch điều chuyển tạm ứng có ID: '+ @p_TRANSFER_ID +N' đã được tạo bản nháp thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' TRANSFER_ID, '' ErrorDesc
		RETURN '-1'
End