ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_KT_Upd]
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_DT VARCHAR(20)= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@P_REQ_ENTRIES nvarchar(MAX)	= NULL,
@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_RECEIVER_PO	nvarchar(250)	= NULL,
@p_REQ_PAY_TYPE	varchar(15)	= NULL,
@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,
@p_REQ_AMT	decimal(18, 0)	= NULL,
@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,
@p_MAKER_ID	varchar(15)	= NULL,
@p_CREATE_DT	varchar(25)	= NULL,
@p_EDITOR_ID	varchar(15)	= NULL,
@p_AUTH_STATUS	varchar(1)	= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_APPROVE_DT	varchar(25)	= NULL,
@p_CREATE_DT_KT	varchar(25)	= NULL,
@p_MAKER_ID_KT	varchar(15)	= NULL,
@p_AUTH_STATUS_KT	varchar(1)	= NULL,
@p_CHECKER_ID_KT	varchar(1)	= NULL,
@p_APPROVE_DT_KT  varchar(25)= null,
@p_CONFIRM_NOTE	nvarchar(500)	= NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_NOTES	varchar(15)	= NULL,
@p_RECORD_STATUS	varchar(1)	= NULL,
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_PROCESS	varchar(15)	= NULL,
@p_PAY_PHASE VARCHAR(15) = NULL,
@p_XMP_TEMP XML = NULL,
@p_XMP_TEMP_2 XML = NULL
AS

--Validation is here
IF ((SELECT AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) ='R')
	BEGIN
		SELECT '-1' Result,'' REQ_PAY_ID,N'Phiếu đề nghị thanh toán đang được trả về đơn vị. Vui lòng chờ đơn vị cập nhật thông tin và duyệt lại!' ErrorDesc
		RETURN '-1'
	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(100)
	DECLARE @VAT decimal(18, 0) =NULL, @TRANS_NO nvarchar(50)=NULL, @TRANS_DT VARCHAR(20)=NULL,@INVOICE_SIGN nvarchar(50) = NULL,@INVOICE_NO_SIGN nvarchar(50)=null,
		@INVOICE_NO nvarchar(50) = NULL,@INVOICE_DT VARCHAR(20) = NULL,@SELLER nvarchar(250) = NULL,@TAX_NO nvarchar(15) = NULL,@GOODS_NAME nvarchar(250) = NULL,
		@PRICE decimal(18, 0) = NULL,@TAX decimal(18, 0) = NULL,@NOTES NVARCHAR(MAX) = NULL,@VAT_RATE decimal(18,2),
		@ACC_NO VARCHAR(25),@ACC_NAME NVARCHAR(250),@ISSUED_BY NVARCHAR(250),@ISSUED_DT VARCHAR(20),@RATE DECIMAL(18,2),@PRICE_KT DECIMAL(18,2) =0,@TOTAL_AMT_KT DECIMAL(18,2) =0,@VAT_KT DECIMAL(18,2) =0,
		@TYPE_VAT VARCHAR(15),@TYPE_FUNC VARCHAR(15)
DECLARE @hdoc INT;
	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;
	DECLARE XmlData CURSOR FOR
	SELECT *
	FROM
	OPENXML(@hdoc, '/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(100))
	OPEN XmlData;
	---
	DECLARE XmlInvoice CURSOR FOR
	SELECT *
	FROM
	OPENXML(@hdoc, '/Root/XmlInvoice', 2)
	WITH(TRANS_NO nvarchar(50),TRANS_DT VARCHAR(20),INVOICE_SIGN nvarchar(50),INVOICE_NO_SIGN nvarchar(50),
		 INVOICE_NO nvarchar(50),INVOICE_DT VARCHAR(20) ,SELLER nvarchar(250),TAX_NO nvarchar(15),GOODS_NAME nvarchar(250) ,
		 PRICE decimal(18,2),TAX decimal(18, 2),VAT decimal(18,2),NOTE NVARCHAR(MAX),VAT_RATE decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),PRICE_KT DECIMAL(18,2),VAT_KT DECIMAL(18,2),TOTAL_AMT_KT DECIMAL(18,2),TYPE_VAT VARCHAR(15), TYPE_FUNC VARCHAR(15))
		OPEN XmlInvoice;
	DECLARE @INDEX INT  =0, @INDEX_IV INT =0
	BEGIN TRANSACTION
		-- KHAI BAO CAC BUOC DUYET- XAC NHAN
		DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT
		SET @LEVEL_JOB =(SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT AND STATUS_JOB='C')
		SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1
		----
		--IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND MAKER_ID_KT IS NULL))

		IF(@p_XMP_TEMP IS NOT NULL)
		BEGIN
			UPDATE TR_REQ_PAYMENT SET 
			AUTH_STATUS_KT ='U', CREATE_DT_KT = GETDATE(),MAKER_ID_KT =@p_MAKER_ID_KT,CHECKER_ID_KT=NULL,APPROVE_DT_KT = NULL, CONFIRM_NOTE=@p_CONFIRM_NOTE
			WHERE REQ_PAY_ID =@p_REQ_PAY_ID
			DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID
			DELETE FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID= @p_REQ_PAY_ID
		IF @@Error <> 0 GOTO ABORT
		DECLARE @DR_CR_NAME NVARCHAR(50),@ACC_NAME_FN NVARCHAR(500)
		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
			SET @GL_CODE = RIGHT(@GL_CODE,9)
			IF(@DR_CR='D')
			BEGIN
				SET @DR_CR_NAME =N'Nợ'
			END
			ELSE
			BEGIN
				SET @DR_CR_NAME =N'Có'
			END
			--SET @ACC_NAME_FN =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)
			IF(@ACCT_NAME IS NULL OR @ACCT_NAME ='')
			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 ((SELECT ISNULL(TK_GL,'') FROM CM_ACCOUNT WHERE ACC_NO =@ACCT) ='' OR (SELECT ISNULL(TK_GL_NAME,'') FROM CM_ACCOUNT WHERE ACC_NO =@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 CASA chưa có thông tin GL Mapping. Vui lòng bấm vào nút Kiểm tra tài khoản để đồng bộ tài khoản từ Core' ErrorDesc
				RETURN '-1'
			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(@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ị nhậ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(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
			BEGIN
				IF(@DR_CR ='C' AND ISNULL(@AMT,0) <> (SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlData;
					DEALLOCATE XmlData;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Số tiền hạch toán có của tài khoản: '+@ACCT + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###')  ErrorDesc
					RETURN '-1'
				END
			END
			--IF(LEN(@ACCT) >9 AND ISNULL(@ACCT,'') <> '' 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_REQ_PAY_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,@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,CREATE_DT) VALUES
				(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin',GETDATE())
			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
				--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), CREATE_DT = GETDATE()
				--WHERE ACC_NO=@ACCT
			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;
	-- UPDATE INVOICE
	--Insert XmlData
		
		FETCH NEXT FROM XmlInvoice  INTO @TRANS_NO , @TRANS_DT  ,@INVOICE_SIGN ,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,
		@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC
		WHILE @@fetch_status=0 BEGIN
			SET @INDEX_IV = @INDEX_IV+1
			IF(LEN(@INVOICE_NO) >7)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' không được lớn hơn 7 kí tự' ErrorDesc
					RETURN '-1'
				END	
			  IF(LEN(@INVOICE_NO) <7)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' phải đủ 7 kí tự' ErrorDesc
					RETURN '-1'
				END	
				IF(@TYPE_FUNC ='HC')
				BEGIN
					IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN AND INVOICE_SIGN =@INVOICE_SIGN AND 
					TAX_NO =@TAX_NO AND TAX =@TAX AND TYPE_FUNC ='HC' AND AUTH_STATUS <>'D' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'E')))
					BEGIN
						ROLLBACK TRANSACTION
						SELECT '-1' as Result, '' REQ_PAY_ID,N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc
						RETURN '-1'
					END	
				END
				IF(@TYPE_FUNC ='KT')
				BEGIN
					IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN 
											AND INVOICE_SIGN =@INVOICE_SIGN AND TAX_NO =@TAX_NO AND TAX =@TAX AND TYPE_FUNC ='KT' AND AUTH_STATUS <>'D' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS <> 'E')))
					BEGIN
						ROLLBACK TRANSACTION
						SELECT '-1' as Result, '' REQ_PAY_ID,N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc
						RETURN '-1'
					END	
				END
			DECLARE @p_REQ_INV_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE', @p_REQ_INV_ID OUT;
			IF @p_REQ_INV_ID='' OR @p_REQ_INV_ID IS NULL GOTO ABORT;
			INSERT INTO TR_REQ_PAY_INVOICE(REQ_PAYDT_ID,REQ_PAY_ID,TRANS_NO,TRANS_DT,INVOICE_SIGN,INVOICE_NO,INVOICE_DT,SELLER,TAX_NO,GOODS_NAME,PRICE,TAX,VAT,NOTE,
		MAKER_ID,CREATE_DT,EDITOR_ID,AUTH_STATUS,CHECKER_ID,APPROVE_DT,
		CREATE_DT_KT,MAKER_ID_KT,AUTH_STATUS_KT,CHECKER_ID_KT,APPROVE_DT_KT,RECORD_STATUS,INVOICE_NO_SIGN,VAT_RATE,CURRENCY,RATE,PRICE_KT ,VAT_KT,TOTAL_AMT_KT,TYPE_VAT,TYPE_FUNC) 
		VALUES (@p_REQ_INV_ID,@p_REQ_PAY_ID , @TRANS_NO , CONVERT(DATE,@TRANS_DT,103)  ,@INVOICE_SIGN ,@INVOICE_NO ,CONVERT(DATE,@INVOICE_DT,103) ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,
		@p_MAKER_ID,GETDATE(),NULL,'U',NULL,NULL,GETDATE(),@p_MAKER_ID_KT,'U',NULL,NULL,'1',@INVOICE_NO_SIGN,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC)
		IF @@error<>0 GOTO ABORT;
		FETCH NEXT FROM XmlInvoice
		INTO @TRANS_NO , @TRANS_DT,@INVOICE_SIGN,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,
		@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@PRICE_KT ,@VAT_KT,@TOTAL_AMT_KT,@TYPE_VAT,@TYPE_FUNC
		END;
		CLOSE XmlInvoice;
		DEALLOCATE XmlInvoice;
		--- Luu log chinh sua
		INSERT INTO TR_REQ_PAY_INVOICE_LOG SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID
		-----
	--- BAT DAU CAP NHAT XAC DINH CAC BUOC DUYET SAU KHI KE TOAN CAP NHAT THONG TIN
	END
	COMMIT TRANSACTION
	IF(@p_XMP_TEMP IS NULL)
			BEGIN
				IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'S'))
				BEGIN
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được được đề xuất từ chối! Vui lòng đợi KSV xử lý phiếu' ErrorDesc
					RETURN '-1'
				END
				IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'P'))
				BEGIN
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được gửi phê  duyệt thành công trước đó! Vui lòng đợi KSV xử lý phiếu' ErrorDesc
					RETURN '-1'
				END
				-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP
				IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_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_REQ_PAY_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_REQ_PAY_ID AND DR_CR ='D'),0)
					IF(@SUM_CR <> @SUM_DR)
					BEGIN
					--ROLLBACK TRANSACTION
					--CLOSE XmlData;
					--DEALLOCATE XmlData;
						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ó' ErrorDesc
						RETURN '-1'
					END
				END
				UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT
				UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)
				-- CAP NHAT TINH TRANG DUYET KE TOAN LA DANG XU LY
				UPDATE TR_REQ_PAYMENT SET AUTH_STATUS_KT='P',CREATE_DT_KT = GETDATE() WHERE REQ_PAY_ID=@p_REQ_PAY_ID
				INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
				VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID_KT,GETDATE(), N'Giao dịch viên gửi phê duyệt',N'Giao dịch viên cập nhật thông tin')
				SELECT '4' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
				RETURN '4'
END
SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		CLOSE XmlData;
		DEALLOCATE XmlData;
		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
		RETURN '-1'
End

¿



ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_KT_Upd]
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_DT VARCHAR(20)= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@P_REQ_ENTRIES nvarchar(MAX)	= NULL,
@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_RECEIVER_PO	nvarchar(250)	= NULL,
@p_RECEIVER_DEBIT	nvarchar(250)	= NULL,
@p_REQ_PAY_TYPE	varchar(15)	= NULL,
@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,
@p_REQ_AMT	decimal(18, 0)	= NULL,
@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,
@p_MAKER_ID	varchar(15)	= NULL,
@p_CREATE_DT	varchar(25)	= NULL,
@p_EDITOR_ID	varchar(15)	= NULL,
@p_AUTH_STATUS	varchar(1)	= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_APPROVE_DT	varchar(25)	= NULL,
@p_CREATE_DT_KT	varchar(25)	= NULL,
@p_MAKER_ID_KT	varchar(15)	= NULL,
@p_AUTH_STATUS_KT	varchar(1)	= NULL,
@p_CHECKER_ID_KT	varchar(1)	= NULL,
@p_APPROVE_DT_KT  varchar(25)= null,
@p_CONFIRM_NOTE	nvarchar(500)	= NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_NOTES	varchar(15)	= NULL,
@p_RECORD_STATUS	varchar(1)	= NULL,
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_PROCESS	varchar(15)	= NULL,
@p_PAY_PHASE VARCHAR(15)= NULL,
@p_DVDM_ID VARCHAR(15) = NULL,
@p_RATE DECIMAL(18,0)= 0,
@p_XMP_TEMP XML = NULL
AS
--Validation is here
/*
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 @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;
DECLARE XmlData CURSOR FOR
SELECT *
FROM
	OPENXML(@hdoc, '/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 @INDEX INT  =0
BEGIN TRANSACTION
		-- KHAI BAO CAC BUOC DUYET- XAC NHAN
		DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT
		SET @LEVEL_JOB =(SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT AND TYPE_JOB ='XL')
		SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1
		----
		--IF(EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND MAKER_ID_KT IS NULL))
		IF(@p_XMP_TEMP IS NOT NULL)
		BEGIN
			UPDATE TR_REQ_ADVANCE_PAYMENT SET 
			AUTH_STATUS_KT ='U', CREATE_DT_KT = GETDATE(),MAKER_ID_KT =@p_MAKER_ID_KT,CHECKER_ID_KT=NULL,APPROVE_DT_KT = NULL, CONFIRM_NOTES=@p_CONFIRM_NOTE
			WHERE REQ_PAY_ID =@p_REQ_PAY_ID
			DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID
		IF @@Error <> 0 GOTO ABORT
		DECLARE @DR_CR_NAME NVARCHAR(50)
		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
			--SET @ACCT_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)
			IF(@ACCT_NAME IS NULL OR @ACCT_NAME ='')
			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 ((SELECT ISNULL(TK_GL,'') FROM CM_ACCOUNT WHERE ACC_NO =@ACCT) ='' OR (SELECT ISNULL(TK_GL_NAME,'') FROM CM_ACCOUNT WHERE ACC_NO =@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 CASA chưa có thông tin GL Mapping. Vui lòng bấm vào nút Kiểm tra tài khoản để đồng bộ tài khoản từ Core' ErrorDesc
				RETURN '-1'
			END
			IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
			BEGIN
				IF(@DR_CR ='C' AND ISNULL(@AMT,0) <> (SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlData;
					DEALLOCATE XmlData;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Số tiền hạch toán có của tài khoản: '+@ACCT + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###')  ErrorDesc
					RETURN '-1'
				END
			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
			--IF(@DR_ACCT IS NULL OR @DR_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
			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_REQ_PAY_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,@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;
	END
COMMIT TRANSACTION
--- BAT DAU CAP NHAT XAC DINH CAC BUOC DUYET SAU KHI KE TOAN CAP NHAT THONG TIN
	IF(@p_XMP_TEMP IS NULL)
	BEGIN
		-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP
		IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_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_REQ_PAY_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_REQ_PAY_ID AND DR_CR ='D'),0)
			IF(@SUM_CR<>@SUM_DR)
			BEGIN
			--ROLLBACK TRANSACTION
			--CLOSE XmlData;
			--DEALLOCATE XmlData;
				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ó' ErrorDesc
				RETURN '-1'
			END
		END
		UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT
		UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)
		-- CAP NHAT TINH TRANG VE DANG XU LY
		UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS_KT='P',CREATE_DT_KT =GETDATE() WHERE REQ_PAY_ID=@p_REQ_PAY_ID
		INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
				   VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID_KT,GETDATE(), N'Giao dịch viên gửi phê duyệt phiếu',N'Giao dịch viên cập nhật thông tin')
		SELECT '4' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N'đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
		RETURN '4'
	END
SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		CLOSE XmlData;
		DEALLOCATE XmlData;
		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
		RETURN '-1'
End