ALTER PROCEDURE [dbo].[TR_REQ_PAY_INVOICE_DRAFT_Ins]
@p_REQ_PAY_ID	VARCHAR(15) = NULL,
@p_REQ_PAY_CODE	VARCHAR(50)	= NULL,
@p_MAKER_ID VARCHAR(15) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
@p_DEP_LOGIN VARCHAR(15) = NULL,
@p_XMP_TEMP XML = NULL
AS
	IF(ISNULL((SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_CODE = @p_REQ_PAY_CODE), '') <> @p_REQ_PAY_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Thêm mới PYC xóa hóa đơn đính kèm lưu nháp/ từ chối thất bại! Mã số phiếu đề nghị thanh toán không hợp lệ. Vui lòng kiểm tra lại mã số phiếu đề nghị thanh toán và bấm tra cứu hóa đơn trước khi tạo PYC' ErrorDesc
		RETURN '-1'
	END

	/* Bỏ điều kiện này vì đã bỏ quy trình gửi duyệt và duyệt
	IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND AUTH_STATUS <> '' AND AUTH_STATUS IS NOT NULL AND AUTH_STATUS <> 'E' AND AUTH_STATUS <> 'R'))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Thêm mới PYC xóa hóa đơn đính kèm lưu nháp/ từ chối thất bại! Phiếu đề nghị thanh toán có mã ' + @p_REQ_PAY_CODE + N' đang không ở trạng thái lưu nháp hoặc từ chối' ErrorDesc
		RETURN '-1'
	END
	*/

BEGIN TRANSACTION
--VALIDATION
	/* Bỏ điều kiện này vì đã bỏ quy trình gửi duyệt và duyệt
	IF(EXISTS (SELECT * FROM TR_REQ_PAY_INVOICE_DRAFT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND AUTH_STATUS <> 'A'))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Đang tồn tại PYC xóa hóa đơn đính kèm lưu nháp/ từ chối của phiếu đề nghị thanh toán có mã ' + @p_REQ_PAY_CODE + N' đang chờ duyệt' ErrorDesc
		RETURN '-1'
	END
	*/
-- INSERT MASTER
	DECLARE @l_UPD_ID VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE_DRAFT', @l_UPD_ID OUT;
	IF @l_UPD_ID='' OR @l_UPD_ID IS NULL GOTO ABORT;
	INSERT INTO TR_REQ_PAY_INVOICE_DRAFT (UP_ID, REQ_PAY_ID, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, AUTH_STATUS, BRANCH_LOGIN, DEP_LOGIN) 
	VALUES (@l_UPD_ID, @p_REQ_PAY_ID, @p_MAKER_ID, GETDATE(), NULL, NULL, 'E', @p_BRANCH_LOGIN, @p_DEP_LOGIN)

-- INSERT LUOI HOA DON
	DECLARE @UP_ID VARCHAR(15), @REQ_PAY_ID VARCHAR(15), @REQ_PAYDT_ID VARCHAR(15)
	DECLARE @hdoc INT

	EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;
	DECLARE XmlInvoice CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlData', 2)
	WITH(UP_ID VARCHAR(15), REQ_PAY_ID VARCHAR(15), REQ_PAYDT_ID VARCHAR(15))
	OPEN XmlInvoice;


	FETCH NEXT FROM XmlInvoice INTO @UP_ID, @REQ_PAY_ID, @REQ_PAYDT_ID
	WHILE @@fetch_status=0 
	BEGIN
		
		--INSERT INTO TR_REQ_PAY_INVOICE_DRAFT_DT(UP_ID, REQ_PAY_ID, REQ_PAYDT_ID) 
		--VALUES (@l_UPD_ID, @p_REQ_PAY_ID, @REQ_PAYDT_ID)

		INSERT INTO TR_REQ_PAY_INVOICE_DRAFT_DT
		(	UP_ID, REQ_PAY_ID, REQ_PAYDT_ID, TRANS_NO , TRANS_DT , INVOICE_SIGN , INVOICE_NO , INVOICE_DT , 
			SELLER , TAX_NO , GOODS_NAME , PRICE , TAX , VAT , MAKER_ID , CREATE_DT , 
			EDITOR_ID , EDITTOR_DT , AUTH_STATUS , CHECKER_ID , APPROVE_DT , CREATE_DT_KT , 
			MAKER_ID_KT , AUTH_STATUS_KT , CHECKER_ID_KT , APPROVE_DT_KT , CORE_NOTE , 
			BRANCH_CREATE , NOTE , RECORD_STATUS , INVOICE_NO_SIGN , VAT_RATE , CURRENCY , 
			RATE , PRICE_KT , VAT_KT , TOTAL_AMT_KT , TYPE_VAT, TYPE_FUNC
		)
		SELECT @l_UPD_ID, @p_REQ_PAY_ID, @REQ_PAYDT_ID, TRANS_NO , TRANS_DT , INVOICE_SIGN , INVOICE_NO , INVOICE_DT , 
		SELLER , TAX_NO , GOODS_NAME , PRICE , TAX , VAT , MAKER_ID , CREATE_DT , 
		EDITOR_ID , EDITTOR_DT , AUTH_STATUS , CHECKER_ID , APPROVE_DT , CREATE_DT_KT , 
		MAKER_ID_KT , AUTH_STATUS_KT , CHECKER_ID_KT , APPROVE_DT_KT , CORE_NOTE , 
		BRANCH_CREATE , NOTE , RECORD_STATUS , INVOICE_NO_SIGN , VAT_RATE , CURRENCY , 
		RATE , PRICE_KT , VAT_KT , TOTAL_AMT_KT , TYPE_VAT, TYPE_FUNC
		FROM TR_REQ_PAY_INVOICE
		WHERE REQ_PAYDT_ID = @REQ_PAYDT_ID
	IF @@error<>0 GOTO ABORT;
	FETCH NEXT FROM XmlInvoice INTO @UP_ID, @REQ_PAY_ID, @REQ_PAYDT_ID
	END;
	CLOSE XmlInvoice;
	DEALLOCATE XmlInvoice;

-- Duyệt ngay sau khi thêm mới: Bỏ qua quy trình gửi duyệt và duyệt được áp dụng trước đó
	UPDATE TR_REQ_PAY_INVOICE_DRAFT SET AUTH_STATUS ='A', APPROVE_DT= GETDATE(), CHECKER_ID= @p_MAKER_ID WHERE UP_ID = @l_UPD_ID
	-- ĐIỀU KIỆN NÀY ĐỂ CHẮC CHẮN SẼ KHÔNG XÓA HẾT HÓA ĐƠN CÓ TRONG DATABASE
	IF	(
			(	SELECT COUNT(*) 
				FROM TR_REQ_PAY_INVOICE 
				WHERE 1 = 1
				AND REQ_PAY_ID = (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAY_INVOICE_DRAFT WHERE UP_ID = @l_UPD_ID)
				AND REQ_PAYDT_ID NOT IN (SELECT REQ_PAYDT_ID FROM TR_REQ_PAY_INVOICE_DRAFT_DT)
			) > 10
		)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, N'Duyệt PYC xóa hóa đơn đính kèm lưu nháp/ từ chối thất bại! Mỗi lần bạn chỉ được phép xóa tối đa 10 hóa đơn' ErrorDesc
		RETURN '-1'
	END

	DELETE FROM TR_REQ_PAY_INVOICE 
	WHERE 1 = 1
	AND REQ_PAY_ID = (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAY_INVOICE_DRAFT WHERE UP_ID = @l_UPD_ID)
	AND REQ_PAYDT_ID NOT IN (SELECT REQ_PAYDT_ID FROM TR_REQ_PAY_INVOICE_DRAFT_DT WHERE UP_ID = @l_UPD_ID)

COMMIT TRANSACTION

	SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, @l_UPD_ID UP_ID, 'Thêm mới PYC xóa hóa đơn đính kèm lưu nháp/ từ chối thành công' ErrorDesc
	RETURN '0'

ABORT:
BEGIN
	ROLLBACK TRANSACTION
	CLOSE XmlInvoice;
	DEALLOCATE XmlInvoice;
	SELECT '-1' as Result, '' REQ_PAY_ID, '' UP_ID, '' ErrorDesc
	RETURN '-1'
END

GO

ALTER PROC [dbo].[CM_ACCOUNT_Edit]
@p_REF_ID VARCHAR(15) = NULL
AS
BEGIN TRANSACTION
-- VALIDATE
	DECLARE @TOTAL_AMT DECIMAL(18,0) =	(	SELECT SUM(ISNULL(REQ_AMT, 0) - ISNULL(PAY_AMT, 0)) 
											FROM TR_REQ_ADVANCE_PAYMENT 
											WHERE 1=1
											AND REQ_TYPE = 'I' 
											AND AUTH_STATUS IN ('U', 'A')
											AND REF_ID = @p_REF_ID
										)
	IF(@TOTAL_AMT > 0)
	BEGIN
		DECLARE @REQ_PAYMENT_LIST_STRING NVARCHAR(MAX)
		SELECT @REQ_PAYMENT_LIST_STRING =	STUFF	(
														(   SELECT ', ' + REQ_PAY_CODE 
															FROM TR_REQ_ADVANCE_PAYMENT 
															WHERE 1=1
															AND REQ_TYPE = 'I' 
															AND AUTH_STATUS IN ('U', 'A')
															AND REF_ID = @p_REF_ID
															AND ISNULL(REQ_AMT, 0) - ISNULL(PAY_AMT, 0) > 0
															FOR XML PATH(''), TYPE
														).value('.[1]', 'nvarchar(max)'), 1, 1, ''
													)
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REF_ID, N'Chỉnh sửa tài khoản tạm ứng thất bại! user ' + @p_REF_ID + N' vẫn chưa hoàn tất hoàn ứng nội bộ.'
		+ CHAR(10) +
		N'</br>* Số tiền còn lại cần hoàn ứng: ' + FORMAT(ISNULL(@TOTAL_AMT,0),'#,#', 'vi-VN') +' VND' +
		+ CHAR(10) +
		N'</br>* Danh sách số phiếu tạm ứng nội bộ chưa hoàn tất hoàn ứng: ' + @REQ_PAYMENT_LIST_STRING 
		ErrorDesc
		RETURN '-1'
	END

	-- LUU LOG
	INSERT INTO CM_ACCOUNT_PAY_LOG
	SELECT * FROM CM_ACCOUNT_PAY WHERE REF_ID = @p_REF_ID

	UPDATE CM_ACCOUNT_PAY SET AUTH_STATUS = 'U', CHECKER_ID = NULL, APPROVE_DT = NULL WHERE REF_ID = @p_REF_ID

COMMIT TRANSACTION

SELECT '0' as Result, @p_REF_ID  REF_ID, N'Cập nhật thành công. Bây giờ bạn có thể chỉnh sửa tài khoản tạm ứng này' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REF_ID, 'ERROR ABORT!' ErrorDesc
		RETURN '-1'
END

GO

ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES_Ins]
@p_REQ_PAY_AUTO_ID	VARCHAR(15) = NULL,
@p_REQ_PAY_AUTO_CODE	VARCHAR(50)	= NULL,
@p_MAKER_ID VARCHAR(15) = NULL,
@p_XMP_ENTRIES XML = NULL
AS
	IF(ISNULL((SELECT TOP 1 REQ_PAY_AUTO_ID FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE), '') <> @p_REQ_PAY_AUTO_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Thêm mới chỉnh sửa hạch toán PYC TTTĐ thất bại! Mã PYC TTTĐ không hợp lệ. Vui lòng kiểm tra lại mã PYC TTTĐ và bấm tra cứu' ErrorDesc
		RETURN '-1'
	END

BEGIN TRANSACTION
--VALIDATION
	IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID AND AUTH_STATUS <> 'A'))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Đang tồn tại PYC chỉnh sửa hạch toán PYC TTTĐ của PYC TTTĐ có mã ' + @p_REQ_PAY_AUTO_CODE + N' đang chờ duyệt' ErrorDesc
		RETURN '-1'
	END
-- INSERT MASTER
	DECLARE @l_ENTRY_AUTO_ID VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES', @l_ENTRY_AUTO_ID OUT;
	IF @l_ENTRY_AUTO_ID='' OR @l_ENTRY_AUTO_ID IS NULL GOTO ABORT;
	INSERT INTO TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES (ENTRY_AUTO_ID, REQ_PAY_AUTO_ID, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, AUTH_STATUS) 
	VALUES (@l_ENTRY_AUTO_ID, @p_REQ_PAY_AUTO_ID, @p_MAKER_ID, GETDATE(), NULL, NULL, 'E')

-- BBEGIN INSERT LUOI HACH TOAN
	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(255),@GL_CODE VARCHAR(100),@GL_CODE_ACCNO VARCHAR(20), @REQ_PAY_AUTO_DT_ID VARCHAR(15)
	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), @IS_TRANSFER_EXTERNAL VARCHAR(15)
	DECLARE @TYPE_BUDGET_ALLOCAITON_ENTRIES VARCHAR(20), @PERCENT_BUDGET_ALLOCAITON_ENTRIES DECIMAL(18,2), @AMT_BUDGET_ALLOCAITON_ENTRIES DECIMAL(18,2)
	DECLARE @DR_CR_NAME NVARCHAR(50),@ACC_NAME_FN NVARCHAR(500)

	DECLARE @hdocEntries INT;
	EXEC sp_xml_preparedocument @hdocEntries OUTPUT, @p_XMP_ENTRIES;
	DECLARE XmlDataEntries CURSOR FOR SELECT * FROM OPENXML(@hdocEntries, '/Root/XmlDataEntries', 2)
	WITH(REQ_PAY_AUTO_DT_ID VARCHAR(20), 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),GL_CODE_ACCNO VARCHAR(20), IS_TRANSFER_EXTERNAL VARCHAR(15),
	TYPE_BUDGET_ALLOCAITON VARCHAR(20), PERCENT_BUDGET_ALLOCAITON DECIMAL(18,2), AMT_BUDGET_ALLOCAITON DECIMAL(18,2))
	OPEN XmlDataEntries;

	IF @@Error <> 0 GOTO ABORT
	FETCH NEXT FROM XmlDataEntries INTO @REQ_PAY_AUTO_DT_ID, @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE, @GL_CODE_ACCNO, 
	@IS_TRANSFER_EXTERNAL, @TYPE_BUDGET_ALLOCAITON_ENTRIES, @PERCENT_BUDGET_ALLOCAITON_ENTRIES, @AMT_BUDGET_ALLOCAITON_ENTRIES
	WHILE @@fetch_status=0 
	BEGIN
		IF(@EXC_RATE IS NULL)
		BEGIN
			SET @EXC_RATE = 1
		END

		SET @ACC_NAME =UPPER(dbo.fChuyenCoDauThanhKhongDau(@ACCT_NAME))

		-- NEU TEN TAI KHOAN RONG, THI LAY TEN TU CHECK CASA
		IF(ISNULL(@GL_CODE, '') <> '')
		BEGIN
			SET @ACC_NAME = STUFF(@GL_CODE, LEN(@GL_CODE)-8, 9, '')
			SET @ACCT_NAME = STUFF(@GL_CODE, LEN(@GL_CODE)-8, 9, '')
		END
		-- NEU SO TAI KHOAN RONG, THI LAY SO TAI KHOAN TU CHECK CASA
		SET @GL_CODE = RIGHT(@GL_CODE,9)
		IF(ISNULL(@ACCT, '') = '' AND ISNULL(@GL_CODE_ACCNO, '') <> '')
		BEGIN
			SET @ACCT = @GL_CODE_ACCNO
		END

		IF(@DR_CR='D')
		BEGIN
			SET @DR_CR_NAME =N'Nợ'
		END
		ELSE
		BEGIN
			SET @DR_CR_NAME =N'Có'
		END

		DECLARE @p_ET_ID VARCHAR(15);
		EXEC SYS_CodeMasters_Gen 'TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES_ENTRIEs', @p_ET_ID OUT;
		IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;

		INSERT INTO TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES_ENTRIEs (REQ_PAY_AUTO_DT_ID, ENTRY_AUTO_ID, REQ_PAY_AUTO_ID,ENTRY_PAIR,DR_CR,DR_CR_NAME,ACCT,ACCT_NAME,AMT,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT, TRN_TYPE, CURRENCY, EXC_RATE, IS_TRANSFER_EXTERNAL, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON)
		VALUES (@p_ET_ID, @l_ENTRY_AUTO_ID, @p_REQ_PAY_AUTO_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT, @BRANCH_ID, @DEP_ID,@TRN_DESC,GETDATE(), @p_MAKER_ID, 'PAYMENT', 'VND', 1, @IS_TRANSFER_EXTERNAL, @TYPE_BUDGET_ALLOCAITON_ENTRIES, @PERCENT_BUDGET_ALLOCAITON_ENTRIES, @AMT_BUDGET_ALLOCAITON_ENTRIES)

		-- 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 <> '' AND @GL_CODE <> '0')
			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), EDITOR_DT = GETDATE()
				WHERE ACC_NO=@ACCT
			END
		END
	IF @@error<>0 GOTO ABORT;
	FETCH NEXT FROM XmlDataEntries INTO @REQ_PAY_AUTO_DT_ID, @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE, @GL_CODE_ACCNO, 
	@IS_TRANSFER_EXTERNAL, @TYPE_BUDGET_ALLOCAITON_ENTRIES, @PERCENT_BUDGET_ALLOCAITON_ENTRIES, @AMT_BUDGET_ALLOCAITON_ENTRIES
	END;--END WHILE
	CLOSE XmlDataEntries;
	DEALLOCATE XmlDataEntries;
-- END INSERT LUOI HACH TOAN
	/*
	IF(1=1)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ENTRY_AUTO_ID, '' ID, 'Check data' ErrorDesc
		RETURN '-1'
	END
	*/

COMMIT TRANSACTION

	SELECT '0' as Result, @p_REQ_PAY_AUTO_ID  REQ_PAY_ID, @l_ENTRY_AUTO_ID ID, N'Thêm mới chỉnh sửa hạch toán PYC TTTĐ thành công' ErrorDesc
	RETURN '0'

	ABORT:
	BEGIN
		ROLLBACK TRANSACTION
		CLOSE XmlDataEntries;
		DEALLOCATE XmlDataEntries;
		SELECT '-1' as Result, '' ENTRY_AUTO_ID, '' ID, 'ERROR ABORT' ErrorDesc
		RETURN '-1'
	END

--INSERT INTO [dbo].[SYS_PREFIX](ID,Prefix,Description) VALUES('TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES','PAEE',N'Chỉnh sửa hạch toán PYC TTTĐ')
--INSERT INTO [dbo].[SYS_PREFIX](ID,Prefix,Description) VALUES('TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES_ENTRIEs','PAEEE',N'Chi tiết chỉnh sửa hạch toán PYC TTTĐ')

GO

ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES_App]
@p_ENTRY_AUTO_ID VARCHAR(15) = NULL,
@p_CHECKER_ID VARCHAR(15) = NULL
AS
-- VALIDATION
	IF(ISNULL((SELECT TOP 1 ENTRY_AUTO_ID FROM TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES WHERE ENTRY_AUTO_ID = @p_ENTRY_AUTO_ID), '') <> @p_ENTRY_AUTO_ID)
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Chỉnh sửa PYC chỉnh sửa hạch toán PYC TTTĐ thất bại! Mã PYC TTTĐ không hợp lệ. Vui lòng kiểm tra lại mã PYC TTTĐ và bấm tra cứu trước khi tạo PYC' ErrorDesc
		RETURN '-1'
	END

BEGIN TRANSACTION
-- UPDATE MASTER
	UPDATE TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES SET CHECKER_ID = @p_CHECKER_ID, APPROVE_DT= GETDATE(), AUTH_STATUS = 'A' WHERE ENTRY_AUTO_ID = @p_ENTRY_AUTO_ID

-- CHINH SUA LUOI HACH TOAN
	DELETE FROM TR_REQ_PAY_AUTO_ENTRIES WHERE REQ_PAY_AUTO_ID = (SELECT TOP 1 ISNULL(REQ_PAY_AUTO_ID, '') FROM TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES WHERE ENTRY_AUTO_ID = @p_ENTRY_AUTO_ID )
	
	INSERT INTO TR_REQ_PAY_AUTO_ENTRIES(REQ_PAY_AUTO_DT_ID, REQ_PAY_AUTO_ID, FUNCTION_TYPE, REF_ID, ENTRY_PAIR, DR_CR, DR_CR_NAME, ACCT, ACCT_NAME, BRANCH_ID, DEP_ID, AMT, CURRENCY, EXC_RATE, TRN_DATE, TRN_DESC,
	MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, MAKER_ID_KT, CREATE_DT_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, RECORD_STATUS, IS_TRANSFER_EXTERNAL, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON)
	SELECT REQ_PAY_AUTO_DT_ID, REQ_PAY_AUTO_ID, FUNCTION_TYPE, REF_ID, ENTRY_PAIR, DR_CR, DR_CR_NAME, ACCT, ACCT_NAME, BRANCH_ID, DEP_ID, AMT, CURRENCY, EXC_RATE, TRN_DATE, TRN_DESC,
	MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, MAKER_ID_KT, CREATE_DT_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, RECORD_STATUS, IS_TRANSFER_EXTERNAL, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON
	FROM TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES_ENTRIES
	WHERE ENTRY_AUTO_ID = @p_ENTRY_AUTO_ID

	-- INSERT LICH SU XU LY
	INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
	VALUES(@p_ENTRY_AUTO_ID,'APPR',@p_CHECKER_ID,GETDATE(), N'Kiểm sát viên phê duyệt phiếu',N'Kiểm sát viên phê duyệt chỉnh sửa PYC chỉnh sửa hạch toán PYC TTTĐ')

COMMIT TRANSACTION
		
	SELECT '0' as Result, @p_ENTRY_AUTO_ID  REQ_PAY_AUTO_ID, @p_ENTRY_AUTO_ID ID, N'Duyệt chỉnh sửa hạch toán PYC TTTĐ thành công' ErrorDesc
	RETURN '0'
	
ABORT:
BEGIN
	ROLLBACK TRANSACTION
	SELECT '-1' as Result, '' ENTRY_AUTO_ID, '' ID, 'ERROR ABORT. Vui lòng liên hệ IT ' ErrorDesc
	RETURN '-1'
END

GO

ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_Search]
@p_REQ_PAY_AUTO_ID	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_CODE	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_NAME	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_DT	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_TYPE	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_SERVICE_TYPE	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_STATUS	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_EFFECTIVE_DT	VARCHAR(20)= NULL,
@p_BRANCH_ID	VARCHAR(20)= NULL,
@p_BRANCH_CODE	VARCHAR(20)= NULL,
@p_BRANCH_NAME	VARCHAR(20)= NULL,
@p_DEP_ID	VARCHAR(20)= NULL,
@p_DEP_CODE	VARCHAR(20)= NULL,
@p_DEP_NAME	VARCHAR(20)= NULL,
@p_BRANCH_CREATE	VARCHAR(20)= NULL,
@p_BRANCH_CREATE_CODE	VARCHAR(20)= NULL,
@p_BRANCH_CREATE_NAME	VARCHAR(20)= NULL,
@p_TRANSFER_USER_RECEIVE	VARCHAR(20)= NULL,
@p_TRANSFER_USER_RECEIVE_NAME	VARCHAR(20)= NULL,
@p_CONFIRM_NOTE	VARCHAR(20)= NULL,
@p_CONTRACT_ID	VARCHAR(20)= NULL,
@p_CONTRACT_CODE	VARCHAR(20)= NULL,
@p_CONTRACT_NAME	VARCHAR(20)= NULL,
@p_PROCESS	VARCHAR(20)= NULL,
@p_TRANSFER_MAKER	VARCHAR(20)= NULL,
@p_TRANSFER_DT	VARCHAR(20)= NULL,
@p_MAKER_ID	VARCHAR(20)= NULL,
@p_MAKER_NAME	VARCHAR(20)= NULL,
@p_CREATE_DT	VARCHAR(20)= NULL,
@p_EDITOR_ID	VARCHAR(20)= NULL,
@p_EDITOR_NAME	VARCHAR(20)= NULL,
@p_EDIT_DT	VARCHAR(20)= NULL,
@p_CHECKER_ID	VARCHAR(20)= NULL,
@p_CHECKER_NAME	VARCHAR(20)= NULL,
@p_APPROVE_DT	VARCHAR(20)= NULL,
@p_AUTH_STATUS	VARCHAR(20)= NULL,
@p_MAKER_ID_KT	VARCHAR(20)= NULL,
@p_MAKER_KT_NAME	VARCHAR(20)= NULL,
@p_CREATE_DT_KT	VARCHAR(20)= NULL,
@p_CHECKER_ID_KT	VARCHAR(20)= NULL,
@p_CHECKER_KT_NAME	VARCHAR(20)= NULL,
@p_APPROVE_DT_KT	VARCHAR(20)= NULL,
@p_AUTH_STATUS_KT	VARCHAR(20)= NULL,
@p_AUTH_STATUS_KT_DESC	VARCHAR(20)= NULL,
@p_RECORD_STATUS	VARCHAR(20)= NULL,
@p_FRMDATE	VARCHAR(20)= NULL,
@p_TODATE	VARCHAR(20)= NULL,
@p_LEVEL	VARCHAR(20)= NULL,
@p_USER_LOGIN	VARCHAR(20)= NULL,
@p_IS_SEND_APPR	VARCHAR(20)= NULL,
@p_TYPE_SEARCH	VARCHAR(20)= NULL,
@p_BRANCH_LOGIN	VARCHAR(20)= NULL,
@p_EXEC_USER_KT	nvarchar(20)	= NULL,
@p_IS_UPDATE_KT VARCHAR(15) = NULL,
@p_BRANCH_MANAGE_ID	VARCHAR(20)= NULL,
@p_TOP INT = NULL
AS
/*
1. Trạng thái chờ duyệt ở DVKD: Sẽ kiếm thấy các phiếu chờ người đó xử lý, hoặc do người đó gửi phê duyệt
	- Do người này gửi duyệt(U-0)
	- Do người này đề nghị tạm dừng(A-6)
	- Do người này đề nghị khôi phục(A-9)
	- Do người này đề nghị hủy phiếu(A-15)

*/
BEGIN -- PAGING
-- BEGIN KHAI BÁO
	IF(@p_TYPE_SEARCH = 'HC')
	BEGIN
		SET @p_LEVEL = 'UNIT'
	END
	IF(ISNULL(@p_BRANCH_ID, '') = '')
	BEGIN
		SET @p_BRANCH_ID = @p_BRANCH_CREATE
	END
	IF(@p_BRANCH_LOGIN <> 'DV0001')
	BEGIN
		SET @p_DEP_ID = NULL
	END
-- ĐƠN VỊ YÊU CẦU
	DECLARE @tmp_branch TABLE(BRANCH_ID VARCHAR(15))
	DECLARE @tmp_dep TABLE(DEP_ID VARCHAR(15))
	DECLARE @DEP_ID VARCHAR(15) = NULL
	INSERT INTO @tmp_branch  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
	DECLARE @BRANCH_TYPE VARCHAR(15)
	SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_AUTO_ID))

-- ĐƠN VỊ ĐĂNG NHẬP
	DECLARE @tmp_branch_login TABLE(BRANCH_ID VARCHAR(15))
	DECLARE @tmp_dep_login TABLE(DEP_ID VARCHAR(15))
	INSERT INTO @tmp_branch_login  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
	INSERT INTO @tmp_branch_login VALUES (@p_BRANCH_LOGIN)
	INSERT INTO @tmp_branch_login	SELECT BRANCH_ID 
									FROM TL_SYS_ROLE_MAPPING 
									WHERE TLNAME =@P_USER_LOGIN 
									AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
									AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') 
									AND RECORD_STATUS = '1'
	DECLARE @BRANCH_TYPE_LG VARCHAR(15)
	SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
-- PHÒNG BAN ĐĂNG NHẬP
	DECLARE @DEP_ID_LG VARCHAR(15) = NULL
	SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
	INSERT INTO @tmp_dep_login VALUES (@DEP_ID_LG)
	INSERT INTO @tmp_dep_login SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@DEP_ID_LG
	INSERT INTO @tmp_dep_login	SELECT DEP_ID 
								FROM TL_SYS_ROLE_MAPPING 
								WHERE TLNAME =@P_USER_LOGIN 
								AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
								AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') 
								AND RECORD_STATUS = '1'
-- ĐƠN VỊ ỦY QUYỀN KIÊM NHIỆM
DECLARE @tmp_branch_auth TABLE (BRANCH_ID VARCHAR(15))
INSERT INTO @tmp_branch_auth VALUES (@p_BRANCH_LOGIN)
INSERT INTO @tmp_branch_auth	SELECT BRANCH_ID 
								FROM TL_SYS_ROLE_MAPPING 
								WHERE TLNAME =@P_USER_LOGIN 
								AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
								AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') 
								AND RECORD_STATUS = '1'
-- PHÒNG BAN ỦY QUYỀN KIÊM NHIỆM
DECLARE @tmp_dep_auth TABLE (DEP_ID VARCHAR(15))
INSERT INTO @tmp_dep_auth VALUES (@DEP_ID_LG)
INSERT INTO @tmp_dep_auth	SELECT DEP_ID 
							FROM TL_SYS_ROLE_MAPPING 
							WHERE TLNAME =@P_USER_LOGIN 
							AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
							AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') 
							AND RECORD_STATUS = '1'
INSERT INTO @tmp_dep_auth SELECT DEP_ID FROM CM_DEPARTMENT WHERE FATHER_ID =@DEP_ID_LG

-- ROLE
	DECLARE @ROLE_ID VARCHAR(20)
	SET @ROLE_ID = (SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
	DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))  
	INSERT INTO @TABLE_ROLE VALUES (@ROLE_ID)
	INSERT INTO @TABLE_ROLE SELECT ROLE_NEW 
							FROM TL_SYS_ROLE_MAPPING 
							WHERE ROLE_OLD =@ROLE_ID AND TLNAME =@P_USER_LOGIN  
							AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
							AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') 
							AND RECORD_STATUS = '1'
	
-- NEU USER KHONG CHON TU NGAY THI TU NGAY BANG NGAY 1 1 HANG THANG 20211116
	DECLARE @DATE DATE
	IF ((@p_FRMDATE IS NULL OR @p_FRMDATE ='') AND ISNULL(@p_REQ_PAY_AUTO_ID,'')='' AND @p_AUTH_STATUS ='') -- MỤC ĐÍCH NẾU NHƯ XEM CHI TIẾT 1 PĐN THANH TOÁN DẠNG POPUP THÌ BỎ QUA ĐIỀU KIỆN NÀY, TỪ NGÀY VẪN LÀ NULL
	BEGIN
		SET @DATE = CONVERT(DATE,GETDATE(),103)
		SET @DATE = DATEADD(MONTH,-2,@DATE)
	END
	ELSE
	BEGIN
		SET @DATE = CONVERT(DATE,@p_FRMDATE,103)
	END
-- CHECK NGUOI DUYET
	DECLARE @IS_TDV VARCHAR(1) = 'N'
	IF	(		
				(	SELECT COUNT(*) 
					FROM @TABLE_ROLE A 
					WHERE A.ROLE_AUTH IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')
				) = 0
		)
	BEGIN
		SET @IS_TDV = 'N'
	END
	ELSE
	BEGIN
		SET @IS_TDV = 'Y'
	END
-- END KHAI BÁO
	IF(@p_TYPE_SEARCH = 'HC')
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP=0)
		BEGIN
			IF(@DEP_ID_LG = 'DEP000000000022')
			BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.BRANCH_NAME + ISNULL	(	' - ' + (	SELECT TOP 1 BRANCH_NAME 
																						FROM CM_BRANCH 
																						WHERE BRANCH_ID =	(	SELECT TOP 1 BRANCH_ID 
																												FROM TR_CONTRACT 
																												WHERE CONTRACT_ID = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				--I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				CASE 
					WHEN A.REQ_PAY_AUTO_TYPE = 'A' THEN N.CONTENT
					ELSE O.CONTENT
				END REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN CM_ALLCODE N ON A.REQ_PAY_AUTO_SERVICE_TYPE = N.CDVAL AND N.CDNAME = 'PAY_SER_AUTO_TS' AND N.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE O ON A.REQ_PAY_AUTO_SERVICE_TYPE = O.CDVAL AND O.CDNAME = 'PAY_SER_AUTO' AND O.CDTYPE = 'REQ_AUTO'
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				AND	(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	A.BRANCH_ID = @p_BRANCH_ID 
						OR ISNULL(@p_BRANCH_ID, '') = ''
						OR A.BRANCH_ID IN	(	SELECT BRANCH_ID 
												FROM @tmp_branch
											)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END
			ELSE
			BEGIN-- DVKD Search
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.BRANCH_NAME + ISNULL	(	' - ' + (	SELECT TOP 1 BRANCH_NAME 
																						FROM CM_BRANCH 
																						WHERE BRANCH_ID =	(	SELECT TOP 1 BRANCH_ID 
																												FROM TR_CONTRACT 
																												WHERE CONTRACT_ID = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				--I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				CASE 
					WHEN A.REQ_PAY_AUTO_TYPE = 'A' THEN N.CONTENT
					ELSE O.CONTENT
				END REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN CM_ALLCODE N ON A.REQ_PAY_AUTO_SERVICE_TYPE = N.CDVAL AND N.CDNAME = 'PAY_SER_AUTO_TS' AND N.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE O ON A.REQ_PAY_AUTO_SERVICE_TYPE = O.CDVAL AND O.CDNAME = 'PAY_SER_AUTO' AND O.CDTYPE = 'REQ_AUTO'
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'N' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'Y' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
				-- BEGIN VALIDATE FLOW
				AND	(
						(
								(	A.MAKER_ID = @p_USER_LOGIN)
						)-- NGUOI TAO
					OR	(
							(	A.TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND A.AUTH_STATUS IN ('U', 'R', 'A', 'N') AND PROCESS IN ('0', '6', '9', '15') )
						)-- NGUOI DUYET TRUNG GIAN
					OR	(
							(	A.AUTH_STATUS IN ('R', 'A', 'N') AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') = '' AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') <> '' AND ISNULL(A.PROCESS, '') = '1' AND @IS_TDV = 'Y')
						)-- TRUONG DON VI
					OR	(
							A.AUTH_STATUS = 'A'
							
						)-- VA NHUNG PHIEU DA DUYET
					)
				AND	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh va cac don vi con
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	@p_LEVEL='UNIT' 
								AND	(	A.BRANCH_ID = @p_BRANCH_ID	-- PYC cua don vi minh
										OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
									)
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
							
				AND	(
						A.DEP_ID IN	(
										SELECT * 
										FROM @tmp_dep_auth
									)
						OR A.BRANCH_ID <> 'DV0001'
					)
				-- END VALIDATE FLOW
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END
		END;
		ELSE -- TOP IS NOT NULL
		BEGIN
			IF(@DEP_ID_LG = 'DEP000000000022')
			BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.BRANCH_NAME + ISNULL	(	' - ' + (	SELECT TOP 1 BRANCH_NAME 
																						FROM CM_BRANCH 
																						WHERE BRANCH_ID =	(	SELECT TOP 1 BRANCH_ID 
																												FROM TR_CONTRACT 
																												WHERE CONTRACT_ID = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				--I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				CASE 
					WHEN A.REQ_PAY_AUTO_TYPE = 'A' THEN N.CONTENT
					ELSE O.CONTENT
				END REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN CM_ALLCODE N ON A.REQ_PAY_AUTO_SERVICE_TYPE = N.CDVAL AND N.CDNAME = 'PAY_SER_AUTO_TS' AND N.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE O ON A.REQ_PAY_AUTO_SERVICE_TYPE = O.CDVAL AND O.CDNAME = 'PAY_SER_AUTO' AND O.CDTYPE = 'REQ_AUTO'
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				AND	(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	A.BRANCH_ID = @p_BRANCH_ID 
						OR ISNULL(@p_BRANCH_ID, '') = ''
						OR A.BRANCH_ID IN	(	SELECT BRANCH_ID 
												FROM @tmp_branch
											)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END--END @DEP_ID_LG = 'DEP000000000022'
			ELSE
			BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.BRANCH_NAME + ISNULL	(	' - ' + (	SELECT TOP 1 BRANCH_NAME 
																						FROM CM_BRANCH 
																						WHERE BRANCH_ID =	(	SELECT TOP 1 BRANCH_ID 
																												FROM TR_CONTRACT 
																												WHERE CONTRACT_ID = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				--I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				CASE 
					WHEN A.REQ_PAY_AUTO_TYPE = 'A' THEN N.CONTENT
					ELSE O.CONTENT
				END REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN CM_ALLCODE N ON A.REQ_PAY_AUTO_SERVICE_TYPE = N.CDVAL AND N.CDNAME = 'PAY_SER_AUTO_TS' AND N.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE O ON A.REQ_PAY_AUTO_SERVICE_TYPE = O.CDVAL AND O.CDNAME = 'PAY_SER_AUTO' AND O.CDTYPE = 'REQ_AUTO'
				WHERE 1=1 
				-- BEGIN FILTER
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS = A.AUTH_STATUS)	-- dùng cho: lưu nháp, phiếu bị hủy
					OR	(@p_AUTH_STATUS = 'W' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'G' AND A.PROCESS IN ('1', '7', '10', '16')) -- cấp trung gian xác nhận
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'N' AND A.PROCESS IN ('0', '6', '9', '15')) -- chờ duyệt CTG
					OR	(@p_AUTH_STATUS = 'U' AND @IS_TDV = 'Y' AND A.PROCESS IN ('0', '1', '6', '7', '9', '10', '15', '16')) -- chờ duyệt TDV
					OR	(@p_AUTH_STATUS = 'A' AND A.PROCESS IN ('1', '2', '7', '8', '10', '11', '16', '17')) -- đã duyệt
					OR	(@p_AUTH_STATUS = 'R' AND ISNULL(A.PROCESS, '') IN ('', '7.1', '8.1', '10.1', '11.1', '14.1', '16.1', '17.1', '20.1') AND ISNULL(A.AUTH_STATUS, '') <> 'E') -- từ chối
					OR (ISNULL(@p_AUTH_STATUS, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				-- END FILTER
				-- BEGIN VALIDATE FLOW
				AND	(
						(
								(	A.MAKER_ID = @p_USER_LOGIN)
						)-- NGUOI TAO
					OR	(
							(	A.TRANSFER_USER_RECEIVE = @p_USER_LOGIN AND A.AUTH_STATUS IN ('U', 'R', 'A', 'N') AND PROCESS IN ('0', '6', '9', '15') )
						)-- NGUOI DUYET TRUNG GIAN
					OR	(
							(	A.AUTH_STATUS IN ('R', 'A', 'N') AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') = '' AND @IS_TDV = 'Y')
						OR	(	A.AUTH_STATUS = 'U' AND ISNULL(A.TRANSFER_USER_RECEIVE, '') <> '' AND ISNULL(A.PROCESS, '') = '1' AND @IS_TDV = 'Y')
						)-- TRUONG DON VI
					OR	(
							A.AUTH_STATUS = 'A'
							
						)-- VA NHUNG PHIEU DA DUYET
					)
				AND	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh va cac don vi con
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	@p_LEVEL='UNIT' 
								AND	(	A.BRANCH_ID = @p_BRANCH_ID	-- PYC cua don vi minh
										OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
									)
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
							
				AND	(
						A.DEP_ID IN	(
										SELECT * 
										FROM @tmp_dep_auth
									)
						OR A.BRANCH_ID <> 'DV0001'
					)
				-- END VALIDATE FLOW
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
			END
		END;
	END
	ELSE IF(@p_TYPE_SEARCH = 'KT')
	BEGIN
		IF(@p_TOP IS NULL OR @p_TOP = 0)
		BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.BRANCH_NAME + ISNULL	(	' - ' + (	SELECT TOP 1 BRANCH_NAME 
																						FROM CM_BRANCH 
																						WHERE BRANCH_ID =	(	SELECT TOP 1 BRANCH_ID 
																												FROM TR_CONTRACT 
																												WHERE CONTRACT_ID = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				--I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				CASE 
					WHEN A.REQ_PAY_AUTO_TYPE = 'A' THEN N.CONTENT
					ELSE O.CONTENT
				END REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
		-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_AUTO_ID AND PC3.STATUS_JOB ='C' 
				LEFT JOIN CM_ALLCODE N ON A.REQ_PAY_AUTO_SERVICE_TYPE = N.CDVAL AND N.CDNAME = 'PAY_SER_AUTO_TS' AND N.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE O ON A.REQ_PAY_AUTO_SERVICE_TYPE = O.CDVAL AND O.CDNAME = 'PAY_SER_AUTO' AND O.CDTYPE = 'REQ_AUTO'
				WHERE 1=1 
				-- BEGIN FILTER
				AND A.AUTH_STATUS IN ('A', 'N', 'D')
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS_KT = A.AUTH_STATUS_KT) -- dùng cho từ chối
					OR	(@p_AUTH_STATUS_KT = 'U' AND A.PROCESS IN ('3', '12', '18')) -- chờ duyệt
					OR	(@p_AUTH_STATUS_KT = 'P' AND A.PROCESS IN ('4', '13', '13.1', '19', '19.1')) -- đang xử lý
					OR	(@p_AUTH_STATUS_KT = 'S' AND A.PROCESS IN ('4', '13.1', '19.1')) -- đề xuất từ chối
					OR	(@p_AUTH_STATUS_KT = 'A' AND A.PROCESS IN ('5', '14', '20')) -- đã duyệt
					OR	(@p_AUTH_STATUS_KT = 'R' AND A.PROCESS IN ('20.2', '14.2')) -- từ chối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '' AND A.PROCESS  IN ('2', '11', '17')) -- khi KSV chưa điều phối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				--AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	
									@p_LEVEL='UNIT' 
								AND A.BRANCH_ID = @p_BRANCH_ID
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				AND(	PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL OR PC3.TLNAME = '' OR PC3.TLNAME IS NULL)
				AND	(
						(
							EXISTS	(	SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE	REQ_ID = A.REQ_PAY_AUTO_ID 
																								AND (
																										X.TLNAME= @p_USER_LOGIN 
																										OR X.TLNAME =@p_EXEC_USER_KT
																									)
									)
						) 
						OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
						OR (SELECT COUNT(*) FROM @TABLE_ROLE WHERE ROLE_AUTH = 'KSV') > 0
						OR ISNULL(@p_EXEC_USER_KT, '') = ''
					)	
				AND	(	(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='Y'
						)
						OR	(	(	A.MAKER_ID_KT IS NULL 
									AND @p_IS_UPDATE_KT='N'
								)
							)
						OR @p_IS_UPDATE_KT IS NULL 
						OR @p_IS_UPDATE_KT=''
					)
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
		END;
		ELSE 
		BEGIN
	-- PAGING BEGIN
				SELECT A.*,
				--B.BRANCH_CODE, B.BRANCH_NAME,
				CASE	WHEN	(	SELECT TOP 1 BRANCH_ID 
									FROM TR_CONTRACT 
									WHERE CONTRACT_ID = A.CONTRACT_ID
								) <> 'DV0001'
								THEN  B.BRANCH_NAME + ISNULL	(	' - ' + (	SELECT TOP 1 BRANCH_NAME 
																						FROM CM_BRANCH 
																						WHERE BRANCH_ID =	(	SELECT TOP 1 BRANCH_ID 
																												FROM TR_CONTRACT 
																												WHERE CONTRACT_ID = A.CONTRACT_ID
																											)
																			),''
																)
						ELSE B.BRANCH_NAME 
				END BRANCH_NAME,
				C.DEP_CODE, C.DEP_NAME, 
				D.CONTRACT_CODE, D.[CONTRACT_NAME], 
				E.TLFullName AS MAKER_NAME, 
				F.TLFullName AS MAKER_NAME_KT, 
				G.TLFullName AS CHECKER_NAME_KT,
				H.CONTENT AS REQ_PAY_AUTO_STATUS_NAME,
				--I.CONTENT AS REQ_PAY_AUTO_TYPE_NAME,
				CASE 
					WHEN A.REQ_PAY_AUTO_TYPE = 'A' THEN N.CONTENT
					ELSE O.CONTENT
				END REQ_PAY_AUTO_TYPE_NAME,
				J.CONTENT AS AUTH_STATUS_NAME,
				K.CONTENT AS AUTH_STATUS_KT_NAME,
				L.TLFullName AS TRANSFER_USER_RECEIVE_NAME,
				M.BRANCH_CODE AS BRANCH_MANAGE_CODE, M.BRANCH_NAME AS BRANCH_MANAGE_NAME
	-- SELECT END
				FROM TR_REQ_PAYMENT_AUTO A
				LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
				LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
				LEFT JOIN TR_CONTRACT D ON A.CONTRACT_ID = D.CONTRACT_ID
				LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
				LEFT JOIN TL_USER F ON A.MAKER_ID_KT = F.TLNANME
				LEFT JOIN TL_USER G ON A.CHECKER_ID_KT = G.TLNANME
				LEFT JOIN CM_ALLCODE H ON A.REQ_PAY_AUTO_STATUS = H.CDVAL AND H.CDNAME = 'PAY_AUTO_STATUS' AND H.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE I ON A.REQ_PAY_AUTO_TYPE = I.CDVAL AND I.CDNAME = 'PAY_TYPE_AUTO' AND I.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE J ON A.AUTH_STATUS = J.CDVAL AND J.CDNAME = 'AUTH_STATUS' AND J.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN CM_ALLCODE K ON A.AUTH_STATUS_KT = K.CDVAL AND K.CDNAME = 'AUTH_STATUS_KT' AND K.CDTYPE = 'TR_REQ_PAYMENT'
				LEFT JOIN TL_USER L ON A.TRANSFER_USER_RECEIVE = L.TLNANME
				LEFT JOIN CM_BRANCH M ON A.BRANCH_MANAGE_ID = M.BRANCH_ID
				LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_AUTO_ID AND PC3.STATUS_JOB ='C' 
				LEFT JOIN CM_ALLCODE N ON A.REQ_PAY_AUTO_SERVICE_TYPE = N.CDVAL AND N.CDNAME = 'PAY_SER_AUTO_TS' AND N.CDTYPE = 'REQ_AUTO'
				LEFT JOIN CM_ALLCODE O ON A.REQ_PAY_AUTO_SERVICE_TYPE = O.CDVAL AND O.CDNAME = 'PAY_SER_AUTO' AND O.CDTYPE = 'REQ_AUTO'
				WHERE 1=1 
				-- BEGIN FILTER
				AND A.AUTH_STATUS IN ('A', 'N', 'D')
				AND	(	A.REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID OR ISNULL(@p_REQ_PAY_AUTO_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
				--AND	(	A.AUTH_STATUS = @p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS, '') = '')
				AND (	(@p_AUTH_STATUS_KT = A.AUTH_STATUS_KT) -- dùng cho từ chối
					OR	(@p_AUTH_STATUS_KT = 'U' AND A.PROCESS IN ('3', '12', '18')) -- chờ duyệt
					OR	(@p_AUTH_STATUS_KT = 'P' AND A.PROCESS IN ('4', '13', '13.1', '19', '19.1')) -- đang xử lý
					OR	(@p_AUTH_STATUS_KT = 'S' AND A.PROCESS IN ('4', '13.1', '19.1')) -- đề xuất từ chối
					OR	(@p_AUTH_STATUS_KT = 'A' AND A.PROCESS IN ('5', '14', '20')) -- đã duyệt
					OR	(@p_AUTH_STATUS_KT = 'R' AND A.PROCESS IN ('20.2', '14.2')) -- từ chối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '' AND A.PROCESS  IN ('2', '11', '17')) -- khi KSV chưa điều phối
					OR (ISNULL(@p_AUTH_STATUS_KT, '') = '')
					)
				AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
				--AND	(	A.AUTH_STATUS_KT = @p_AUTH_STATUS_KT OR ISNULL(@p_AUTH_STATUS_KT, '') = '')
				AND	(	M.BRANCH_ID = @p_BRANCH_MANAGE_ID OR ISNULL(@p_BRANCH_MANAGE_ID, '') = '')
				AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
				AND	(	A.REQ_PAY_AUTO_STATUS = @p_REQ_PAY_AUTO_STATUS OR ISNULL(@p_REQ_PAY_AUTO_STATUS, '') = '')
				AND(	CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR ISNULL(@p_FRMDATE, '')= '' OR A.CREATE_DT IS NULL)
				AND(	CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR ISNULL(@p_TODATE, '')= '' OR A.CREATE_DT IS NULL)
				AND	(	(	@p_LEVEL='ALL' 
							AND	(	A.BRANCH_ID IN	(	SELECT BRANCH_ID 
														FROM @tmp_branch
													) -- PYC cua don vi minh
									OR A.BRANCH_ID IN (SELECT * FROM @tmp_branch_auth) -- PYC cua don vi kiem nhiem
								)
						)
						OR	(	
									@p_LEVEL='UNIT' 
								AND A.BRANCH_ID = @p_BRANCH_ID
							)
					)
				AND	(	A.DEP_ID = @p_DEP_ID 
						OR ISNULL(@p_DEP_ID, '') = ''
					)
				AND	(	A.MAKER_ID = @p_MAKER_ID OR ISNULL(@p_MAKER_ID, '') = '')
				AND	(	A.MAKER_ID_KT = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
				AND(	PC3.TLNAME = @p_EXEC_USER_KT OR @p_EXEC_USER_KT = '' OR @p_EXEC_USER_KT IS NULL OR PC3.TLNAME = '' OR PC3.TLNAME IS NULL)
				AND	(
						(
							EXISTS	(	SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE	REQ_ID = A.REQ_PAY_AUTO_ID 
																								AND (
																										X.TLNAME= @p_USER_LOGIN 
																										OR X.TLNAME =@p_EXEC_USER_KT
																									)
									)
						) 
						OR	(A.MAKER_ID_KT = @p_MAKER_ID_KT OR PC3.TLNAME = @p_MAKER_ID_KT)
						OR (SELECT COUNT(*) FROM @TABLE_ROLE WHERE ROLE_AUTH = 'KSV') > 0
					)	
				AND	(	(	A.MAKER_ID_KT IS NOT NULL 
							AND @p_IS_UPDATE_KT='Y'
						)
						OR	(	(	A.MAKER_ID_KT IS NULL 
									AND @p_IS_UPDATE_KT='N'
								)
							)
						OR @p_IS_UPDATE_KT IS NULL 
						OR @p_IS_UPDATE_KT=''
					)
				
				-- END FILTER
			ORDER BY A.CREATE_DT DESC
	-- PAGING END
	   END;
	END

END -- PAGING

GO

ALTER PROCEDURE [dbo].[TR_REQ_PAY_AUTO_CREATE_REQ_PAYMENT_FROM_SYSTEM_INVOICE]
@p_XMP_RECURRING XML
AS
BEGIN TRANSACTION

	DECLARE @list_req_payment NVARCHAR(MAX) = ''
-- DS CAC KY DA THANH TOAN
	DECLARE @billRef VARCHAR(20), @billCode VARCHAR(20), @customerName NVARCHAR(MAX), @amount VARCHAR(20), @accountNo VARCHAR(20), @accountType VARCHAR(20), 
	@createdTime VARCHAR(21), @partner VARCHAR(20), @resultCode VARCHAR(20), @resultDesc NVARCHAR(MAX), @transDesc NVARCHAR(MAX), @coreXref VARCHAR(20), @partnerAccount VARCHAR(20), 
	@billCodeAlias VARCHAR(20), @prvCode VARCHAR(20), @sevCode VARCHAR(20), @year VARCHAR(20), @customerAddress VARCHAR(20), @month VARCHAR(20), 
	@accountName VARCHAR(20), @accountNoRec VARCHAR(20), @accountNameRec VARCHAR(20), @billDate VARCHAR(20)

	DECLARE @hDocRecurring INT EXEC sp_xml_preparedocument @hDocRecurring OUTPUT, @p_XMP_RECURRING;
	DECLARE XmlAutoRecurring CURSOR LOCAL FOR
	SELECT * FROM OPENXML(@hDocRecurring, 'Root/XmlAutoRecurring',2) 
	WITH(billRef VARCHAR(20), billCode VARCHAR(20), customerName NVARCHAR(MAX), amount VARCHAR(20), accountNo VARCHAR(20), accountType VARCHAR(20), createdTime VARCHAR(30),
	[partner] VARCHAR(20), resultCode VARCHAR(20), resultDesc NVARCHAR(MAX), transDesc NVARCHAR(MAX), coreXref VARCHAR(20), partnerAccount VARCHAR(20), [month] VARCHAR(20))
	OPEN XmlAutoRecurring

--- DUYET QUA TUNG KY THANH TOAN
	FETCH NEXT FROM XmlAutoRecurring INTO @billRef, @billCode, @customerName, @amount, @accountNo, @accountType, @createdTime,
	@partner, @resultCode, @resultDesc, @transDesc, @coreXref, @partnerAccount, @month
	WHILE @@fetch_status=0 
	BEGIN
		-- LUU LOG KY THANH TOAN
		INSERT INTO TR_REQ_PAY_AUTO_RECURRING_LOG(billRef, billCode, customerName, amount, accountNo, accountType, createdTime,
		[partner], resultCode, resultDesc, transDesc, coreXref, partnerAccount, MAKER_ID, CREATE_DT)
		VALUES (@billRef, @billCode, @customerName, @amount, @accountNo, @accountType, CONVERT(DATE, @createdTime, 103),
		@partner, @resultCode, @resultDesc, @transDesc, @coreXref, @partnerAccount, 'admin', GETDATE())

		IF(@resultCode NOT IN  ('00', '100'))
		BEGIN
			PRINT 'KY THANH TOAN KHONG HOP LE' + @billRef
			-- THEM VAO LOG CAC KY THANH TOAN LOI
		END
		ELSE IF(@accountType <> 'A')
		BEGIN
			PRINT 'KHONG PHAI THANH TOAN BANG THE' + @billRef
		END
		ELSE IF(EXISTS(SELECT * FROM TR_REQ_PAY_AUTO_RECURRING_HISTORY WHERE billRef = @billRef AND @billCode = billCode AND @coreXref = coreXref))
		BEGIN
			PRINT 'KY THANH TOAN DA TON TAI TRONG HE THONG - DA THUC HIEN THANH TOAN TREN AMS: ' + @billRef
		END
		ELSE IF(NOT EXISTS(SELECT * FROM TR_REQ_PAY_AUTO_RECURRING WHERE BILLCODE = @billCode))
		BEGIN
			PRINT 'KHONG CO BILLCODE TRONG HE THONG AMS: ' + @billCode
		END
		ELSE IF(NOT EXISTS	(SELECT *	FROM TR_REQ_PAY_AUTO_RECURRING A
										LEFT JOIN TR_REQ_PAYMENT_AUTO B ON A.REQ_PAY_AUTO_ID = B.REQ_PAY_AUTO_ID 
										WHERE A.BILLCODE = @billCode
							)
				)
		BEGIN
			PRINT 'KHONG CO PHIEU YEU CAU THANH TOAN TU DONG TRONG HE THONG AMS: ' + @billCode
		END
		ELSE
		BEGIN
			DECLARE @REQ_PAY_ID VARCHAR(20), @REQ_PAY_CODE VARCHAR(20), @CONTRACT_ID VARCHAR(20), @CONTRACT_CODE VARCHAR(20), @CONTRACT_NAME NVARCHAR(500),
			@BRANCH_ID NVARCHAR(20), @REQ_PAY_AUTO_SERVICE_TYPE_NAME NVARCHAR(200), @DEP_ID VARCHAR(20), @BRANCH_CREATE VARCHAR(20), 
			@REQ_PAY_AUTO_ID VARCHAR(20), @TRANSFER_USER_RECEIVE VARCHAR(20), @TRANS_GLCODE VARCHAR(20), @TRANS_GLNAME NVARCHAR(500)
			PRINT 'GET DATA BILLCODE'
			-- LAY DATA CUA BILLCODE DANG KY TRONG AMS
			SELECT TOP 1 @CONTRACT_ID = B.CONTRACT_ID, @CONTRACT_CODE = E.CONTRACT_CODE, @CONTRACT_NAME = E.[CONTRACT_NAME], 
			@BRANCH_ID = B.BRANCH_ID, @REQ_PAY_AUTO_SERVICE_TYPE_NAME = D.CONTENT, @DEP_ID = B.DEP_ID, @BRANCH_CREATE = B.BRANCH_CREATE,
			@REQ_PAY_AUTO_ID = B.REQ_PAY_AUTO_ID, @TRANSFER_USER_RECEIVE = B.TRANSFER_USER_RECEIVE, 
			@TRANS_GLCODE = A.TRANS_GLCODE, @TRANS_GLNAME = A.TRANS_GLNAME
			FROM TR_REQ_PAY_AUTO_RECURRING A
			LEFT JOIN TR_REQ_PAYMENT_AUTO B ON A.REQ_PAY_AUTO_ID = B.REQ_PAY_AUTO_ID
			LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
			LEFT JOIN CM_ALLCODE D ON B.REQ_PAY_AUTO_SERVICE_TYPE = D.CDVAL AND D.CDNAME = 'PAY_SER_AUTO' AND CDTYPE = 'REQ_AUTO'
			LEFT JOIN TR_CONTRACT E ON B.CONTRACT_ID = E.CONTRACT_ID
			WHERE BILLCODE = @billCode
			PRINT 'LUU LOG KY THANH TOAN'
			print @createdTime
			-- LUU LOG KY THANH TOAN
			INSERT INTO TR_REQ_PAY_AUTO_RECURRING_HISTORY(billRef, billCode, customerName, amount, accountNo, accountType, createdTime,
			[partner], resultCode, resultDesc, transDesc, coreXref, partnerAccount, MAKER_ID, CREATE_DT)
			VALUES (@billRef, @billCode, @customerName, @amount, @accountNo, @accountType, CONVERT(DATE, @createdTime, 103),
			@partner, @resultCode, @resultDesc, @transDesc, @coreXref, @partnerAccount, 'admin', GETDATE())

			-------------------- BEGIN TAO PHIEU DE NGHI THANH TOAN TU DONG ----------------------------------

			-- BEGIN KHOI TAO GIA TRI
			-- ID PDN THANH TOAN
			PRINT 'GEN ID PDN THANH TOAN'
			EXEC [dbo].[PAY_CODE_GenKey] 'TR_REQ_PAYMENT', '','', @REQ_PAY_CODE out
			IF EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_CODE  = @REQ_PAY_CODE )
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' as Result, @list_req_payment LIST_REQ_PAYMENT, N'Số phiếu đề nghị thanh toán đã tồn tại trong hệ thống' ErrorDesc
				RETURN '-1'
			END
			PRINT 'GEN MA CODE PDN THANH TOAN'
			-- MA CODE PDN THANH TOAN
			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAYMENT', @REQ_PAY_ID out
			IF @REQ_PAY_ID='' OR @REQ_PAY_ID IS NULL GOTO ABORT
			PRINT 'GET DON VI QUAN LY HOP DONG'
			DECLARE @BRANCH_MANAGE_ID NVARCHAR(250) =	(	SELECT TOP 1 BRANCH_ID 
															FROM CM_BRANCH 
															WHERE BRANCH_ID = (	SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @CONTRACT_ID)
														)
			
			DECLARE @BRANCH_MANAGE_NAME NVARCHAR(250) = (	SELECT TOP 1 BRANCH_NAME 
															FROM CM_BRANCH 
															WHERE BRANCH_ID = (	SELECT TOP 1 BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @CONTRACT_ID)
														)

			-- NOI DUNG THANH TOAN, DUNG CHO NHIEU TABLE
			DECLARE @PAY_AUTO_DESC_DETAIL NVARCHAR(1000) = ''
			DECLARE @PAY_AUTO_TRN_DESC_DETAIL VARCHAR(1000) = ''

			DECLARE @l_Month VARCHAR(2), @l_Year VARCHAR(4)
			SET @l_Month =  RIGHT('0' + CAST(DATEPART(MM, DATEADD(MONTH, -1, GETDATE())) AS VARCHAR(2)), 2);
			SET @l_Year =  RIGHT(YEAR(GETDATE()), 4);

			IF(ISNULL(@month, '') = '')
			BEGIN
				PRINT 'HOP DONG KHONG CO MA CODE: ' + @PAY_AUTO_DESC_DETAIL
				SET @month = @l_Month + '/' + @l_Year
				SET @PAY_AUTO_DESC_DETAIL = @BRANCH_MANAGE_NAME + N' thanh toán chi phí ' + @REQ_PAY_AUTO_SERVICE_TYPE_NAME + ' ' + @l_Month + '/' + @l_Year + N' theo GDNTT số ' + @REQ_PAY_CODE
				SET @PAY_AUTO_TRN_DESC_DETAIL = UPPER(dbo.fChuyenCoDauThanhKhongDau(@BRANCH_MANAGE_NAME + N' thanh toán chi phí ' + @REQ_PAY_AUTO_SERVICE_TYPE_NAME + ' ' + @l_Month + '/' + @l_Year + N' theo GDNTT số ' + @REQ_PAY_CODE))
			END
			ELSE
			BEGIN
				PRINT 'HOP DONG CO MA CODE: ' + @PAY_AUTO_DESC_DETAIL
				SET @PAY_AUTO_DESC_DETAIL = @BRANCH_MANAGE_NAME + N' thanh toán chi phí ' + @REQ_PAY_AUTO_SERVICE_TYPE_NAME + N', ' + @month + N' theo GDNTT số ' + @REQ_PAY_CODE
				SET @PAY_AUTO_TRN_DESC_DETAIL = UPPER(dbo.fChuyenCoDauThanhKhongDau(@BRANCH_MANAGE_NAME + N' thanh toán chi phí ' + @REQ_PAY_AUTO_SERVICE_TYPE_NAME + N', ' + @month + N' theo GDNTT số ' + @REQ_PAY_CODE))
			END

			-- END KHOI TAO GIA TRI
			PRINT 'TAO PHIEU DE NGHI THANH TOAN'
			INSERT INTO [dbo].[TR_REQ_PAYMENT]
			(REQ_PAY_ID, REQ_PAY_CODE, BRANCH_ID, DEP_ID, REQ_REASON, REQ_TYPE, 
			REQ_ENTRIES, REQ_DESCRIPTION, REF_ID, PAY_PHASE, RECEIVER_PO, --NULL
			REQ_PAY_TYPE, REQ_DT, REQ_TYPE_CURRENCY, REQ_AMT, REQ_TEMP_AMT, 
			MAKER_ID, CREATE_DT, EDITOR_ID, AUTH_STATUS, CHECKER_ID, APPROVE_DT, --HC
			CREATE_DT_KT, MAKER_ID_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, CONFIRM_NOTE, --KT
			BRANCH_CREATE, NOTES, RECORD_STATUS, TRANSFER_MAKER, TRANSFER_DT, TRASFER_USER_RECIVE, PROCESS, RATE, IS_PERIOD, AMT_PAY, IS_CREATE_AUTO, TYPE_AUTO, SYSTEM_INVOICE_ID, IS_CREATE_AUTO_DONE)
			VALUES
			(@REQ_PAY_ID, @REQ_PAY_CODE, @BRANCH_ID, @DEP_ID, @PAY_AUTO_DESC_DETAIL, 'P', 
			NULL, NULL,  NULL, NULL, NULL,  --NULL
			'1', GETDATE(), 'VND', @amount, NULL, 
			'admin', GETDATE(), NULL, 'A', 'admin', GETDATE(),  --HC
			GETDATE(), 'admin', 'A', 'admin', GETDATE(), NULL, --KT
			@BRANCH_CREATE, NULL, '1', 'admin', GETDATE(), NULL, '0', 1, 'Y', NULL, 'Y', 'B', @billRef, 'N')
			PRINT 'TAO LUOI PDN THANH TOAN CHO PYC TTTD'
			-- TẠO LƯỚI PHIEU DE NGHI THANH TOAN CHO PYC
			INSERT INTO [dbo].[TR_REQ_PAY_AUTO_PAYMENTS]
			(REQ_PAY_AUTO_ID, REQ_PAY_ID, REQ_PAY_CODE, BRANCH_ID, DEP_ID, REQ_REASON, REQ_TYPE, 
			REQ_ENTRIES, REQ_DESCRIPTION, REF_ID, PAY_PHASE, RECEIVER_PO, --NULL
			REQ_PAY_TYPE, REQ_DT, REQ_TYPE_CURRENCY, REQ_AMT, REQ_TEMP_AMT, 
			MAKER_ID, CREATE_DT, EDITOR_ID, AUTH_STATUS, CHECKER_ID, APPROVE_DT, --HC
			CREATE_DT_KT, MAKER_ID_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, CONFIRM_NOTE, --KT
			BRANCH_CREATE, NOTES, RECORD_STATUS, TRANSFER_MAKER, TRANSFER_DT, TRASFER_USER_RECIVE, PROCESS, RATE, IS_PERIOD, AMT_PAY)
			VALUES
			(@REQ_PAY_AUTO_ID ,@REQ_PAY_ID, @REQ_PAY_CODE, @BRANCH_ID, @DEP_ID, @PAY_AUTO_DESC_DETAIL, 'P', 
			NULL, NULL,  NULL, NULL, NULL,  --NULL
			'1', GETDATE(), 'VND', @amount, NULL, 
			'admin', GETDATE(), NULL, 'A', 'admin', GETDATE(),  --HC
			GETDATE(), 'admin', 'A', 'admin', GETDATE(), NULL, --KT
			@BRANCH_CREATE, NULL, '1', 'admin', GETDATE(), NULL, '0', 1, 'Y', NULL)
			PRINT 'THEM LICH SU XU LY'
			INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
			VALUES(@REQ_PAY_ID,'AUTO_APPR','admin',GETDATE(), N'Hệ thống tạo phiếu thanh toán tự động',N'Hệ thống tạo phiếu thanh toán tự động')

			
			-------------------- END TAO PHIEU DE NGHI THANH TOAN TU DONG ----------------------------------

			-- TẠO LƯỚI THÔNG TIN HỢP ĐỒNG ĐỊNH KỲ
			PRINT 'TAO LUOI THONG TIN HDDK'
			DECLARE @REQ_PAYDTID_PERIOD VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_DT', @REQ_PAYDTID_PERIOD OUT;
			IF @REQ_PAYDTID_PERIOD='' OR @REQ_PAYDTID_PERIOD IS NULL GOTO ABORT;
			INSERT INTO TR_REQ_ADVANCE_DT (REQ_PAYDTID, REQ_PAY_ID,REF_ID, [TYPE], MAKER_ID, CREATE_DT, IS_CLOSED, REF_TYPE, DELIVERY_DT) 
			VALUES(@REQ_PAYDTID_PERIOD, @REQ_PAY_ID, @CONTRACT_ID, 'PAY', 'admin', GETDATE(), 'N', 'C', CONVERT(DATE,NULL,103))
			
			-- TẠO LƯỚI THÔNG TIN THANH TOÁN HỢP ĐỒNG ĐỊNH KỲ
			PRINT 'TAO LUOI THONG TIN THANH TOAN HDDK'
			DECLARE @PERIOD_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_PERIOD', @PERIOD_ID OUT;
			IF @PERIOD_ID='' OR @PERIOD_ID IS NULL GOTO ABORT;
			INSERT INTO TR_REQ_PAY_PERIOD(PERIOD_ID, REQ_PAY_ID, PAY_TYPE, CONTRACT_ID, PAY_DT_REAL ,OLD_INDEX, NEW_INDEX, AMT_PAY, 
			AUTH_STATUS_KT, CURRENCY, RATE, FROM_DATE, TO_DATE,AD_PAY_ID, PROCESS, PARENT_ID, PAY_PHASE, REASON, TYPE_COST,FR_LEVEL , TO_LEVEL)
			VALUES (@PERIOD_ID, @REQ_PAY_ID,'PAY', @CONTRACT_ID, NULL, NULL , NULL , @amount, 
			'A', 'VND', '1', NULL, NULL, NULL, '1', NULL, @month, @PAY_AUTO_DESC_DETAIL, NULL, NULL, NULL )

-- TẠO LƯỚI THÔNG TIN HẠCH TOÁN

	-- BEGIN BUT TOAN CHI PHI
				PRINT 'BUT TOAN CHI PHI'
				DECLARE @FUNCTION_TYPE VARCHAR(15), @TRN_TYPE NVARCHAR(40), @REF_ID VARCHAR(15), @ENTRY_PAIR varchar(50), @DR_CR varchar(2), @DR_CR_NAME nvarchar(50), 
				@ACCT varchar(100), @ACCT_NAME varchar(500), @AMT DECIMAL(18,2), @EXC_RATE DECIMAL(18,2), @TRN_DATE VARCHAR(20), @TRN_DESC nvarchar(1000), @AUTH_STATUS varchar(2), @APPROVE_DT varchar(20),
				@CREATE_DT_KT varchar(20), @APPROVE_DT_KT varchar(20), @IS_TRANSFER_EXTERNAL varchar(20), @DEP_ID_ENTRY varchar(20), 
				@CURRENCY varchar(20), @MAKER_ID varchar(20), @CREATE_DT varchar(20), @CHECKER_ID varchar(20), @MAKER_ID_KT varchar(20), @AUTH_STATUS_KT varchar(2), @CHECKER_ID_KT varchar(20), 
				@RECORD_STATUS varchar(20)
				DECLARE @l_TR_REQ_PAY_ENTRIES_D VARCHAR(15);

				DECLARE @TYPE_BUDGET_ALLOCAITON_ENTRIES VARCHAR(20), @PERCENT_BUDGET_ALLOCAITON_ENTRIES DECIMAL(18,2), @AMT_BUDGET_ALLOCAITON_ENTRIES DECIMAL(18,2)

				DECLARE @RES VARCHAR(10)
				DECLARE @TOTAL_CREDIT_AMT DECIMAL(18,0) = @amount;
				DECLARE @TAX DECIMAL(18,1) = 0.1
				DECLARE @TOTAL_PERCENT DECIMAL(18,2) = 0, @TOTAL_PERCENT_AMT DECIMAL(18,0) = 0;
				DECLARE @TOTAL_MONEY DECIMAL(18,2) = 0, @TOTAL_MONEY_AMT DECIMAL(18,0) = (SELECT SUM(ISNULL(AMT_BUDGET_ALLOCAITON, 0)) FROM TR_REQ_PAY_AUTO_ENTRIES);
				
				-- KHAI BAO CONST
				DECLARE @PERCENT_VAT DECIMAL(18,2) = 1.1;
				-- NEU LA HOA DON DIEN THI THUE SE LA 8%. VIEC NAY CO HIEU LUC DEN HET NAM 2023
				IF	(	(	SELECT TOP 1 ISNULL(B.REQ_PAY_AUTO_SERVICE_TYPE, '')
							FROM TR_REQ_PAY_AUTO_RECURRING A
							INNER JOIN TR_REQ_PAYMENT_AUTO B ON A.REQ_PAY_AUTO_ID = B.REQ_PAY_AUTO_ID
							WHERE A.BILLCODE = @billCode
						) = 'ELECTRIC'
					)
				BEGIN
					SET @PERCENT_VAT = 1.08;
				END

				-- CO THUE
				IF(EXISTS(SELECT * FROM TR_REQ_PAY_AUTO_ENTRIES WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND ACCT = '353200002'))
				BEGIN
					IF(EXISTS(SELECT * FROM TR_REQ_PAY_AUTO_BUDGET_ALLOCATION WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND TYPE_BUDGET_ALLOCAITON = 'PERCENT' AND 1=2))-- THEM DIEU KIEN 1=2 VI CHUC NANG NAY CHUA HOAT DONG
					BEGIN
						PRINT 'CO THUE, CO PHAN CHIA'
					-- CREATE CURSOR CÁC ĐẦU CÓ --> LẤY ĐƯỢC CÁC CẶP BÚT TOÁN
						DECLARE cursorEntries_C CURSOR LOCAL FOR 
						SELECT REQ_PAY_AUTO_ID, ENTRY_PAIR
						FROM TR_REQ_PAY_AUTO_ENTRIES
						WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND DR_CR = 'C'

						Open cursorEntries_C
						FETCH NEXT FROM cursorEntries_C INTO @REQ_PAY_AUTO_ID, @ENTRY_PAIR
						WHILE @@FETCH_STATUS = 0
						BEGIN
						-- BEGIN CHẠY TỪNG CẶP BÚT TOÁN
							DECLARE cursorEntries CURSOR LOCAL FOR 
							SELECT REQ_PAY_AUTO_ID, FUNCTION_TYPE, TRN_TYPE, REF_ID, ENTRY_PAIR, DR_CR, DR_CR_NAME, ACCT, ACCT_NAME, BRANCH_ID, DEP_ID, AMT, CURRENCY, EXC_RATE,
							TRN_DATE, TRN_DESC, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, MAKER_ID_KT, CREATE_DT_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, RECORD_STATUS,
							IS_TRANSFER_EXTERNAL, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON
							FROM TR_REQ_PAY_AUTO_ENTRIES
							WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND ENTRY_PAIR = @ENTRY_PAIR

							Open cursorEntries
							FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
							@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS, 
							@IS_TRANSFER_EXTERNAL, @TYPE_BUDGET_ALLOCAITON_ENTRIES, @PERCENT_BUDGET_ALLOCAITON_ENTRIES, @AMT_BUDGET_ALLOCAITON_ENTRIES
							WHILE @@FETCH_STATUS = 0
							BEGIN

								EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES_D OUT;
								IF @l_TR_REQ_PAY_ENTRIES_D='' OR @l_TR_REQ_PAY_ENTRIES_D IS NULL GOTO ABORT;
								SET @PERCENT_BUDGET_ALLOCAITON_ENTRIES = (SELECT TOP 1 ISNULL(PERCENT_BUDGET_ALLOCAITON, 0) FROM TR_REQ_PAY_AUTO_ENTRIES WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND ENTRY_PAIR = @ENTRY_PAIR AND ACCT <> '353200002')

								IF(@DR_CR = 'D')
								BEGIN
									IF(ISNULL(@TOTAL_PERCENT, 0) + ISNULL(@PERCENT_BUDGET_ALLOCAITON_ENTRIES, 0) = 100)
									BEGIN
										PRINT 'DONG NO CUOI'
										DECLARE @A_CREDIT_AMT DECIMAL(18,0) = @amount - @TOTAL_PERCENT_AMT
										DECLARE @A_DEBIT_AMT DECIMAL(18,0) = @A_CREDIT_AMT/@PERCENT_VAT
										DECLARE @A_3532_AMT DECIMAL(18,0) = @A_CREDIT_AMT - @A_DEBIT_AMT
										print '@A_CREDIT_AMT: ' + CONVERT(VARCHAR(15),@A_CREDIT_AMT) 
										print '@A_DEBIT_AMT: ' + CONVERT(VARCHAR(15),@A_DEBIT_AMT) 
										print '@A_3532_AMT: ' + CONVERT(VARCHAR(15),@A_3532_AMT) 

										IF(ISNULL(@ACCT, '') = '353200002')
										BEGIN
											print '@ACCT 3532: ' + @ACCT
											print '@A_3532_AMT 3532: ' + CONVERT(VARCHAR(15),@A_3532_AMT) 
											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @A_3532_AMT, @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
										ELSE
										BEGIN
											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @A_DEBIT_AMT, @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
									END
									ELSE
									BEGIN
										PRINT 'CHUA PHAI DONG NO CUOI'
										DECLARE @B_CREDIT_AMT DECIMAL(18,0) = CONVERT(DECIMAL, @amount*@PERCENT_BUDGET_ALLOCAITON_ENTRIES/100)
										DECLARE @B_DEBIT_AMT DECIMAL(18,0) = @B_CREDIT_AMT/@PERCENT_VAT
										DECLARE @B_3532_AMT DECIMAL(18,0) = @B_CREDIT_AMT - @B_DEBIT_AMT
										print '@B_CREDIT_AMT: ' + CONVERT(VARCHAR(15),@B_CREDIT_AMT)
										print '@B_DEBIT_AMT: ' + CONVERT(VARCHAR(15),@B_DEBIT_AMT)
										print '@B_3532_AMT: ' +CONVERT(VARCHAR(15),@B_3532_AMT) 

										IF(ISNULL(@ACCT, '') = '353200002')
										BEGIN
											print '@ACCT 3532: ' + @ACCT
											print '@B_3532_AMT hach toan: ' +CONVERT(VARCHAR(15),@B_3532_AMT) 
											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @B_3532_AMT, @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
										ELSE
										BEGIN
											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @B_DEBIT_AMT, @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
									END
								END
								ELSE
								BEGIN
									IF(ISNULL(@TOTAL_PERCENT, 0) + ISNULL(@PERCENT_BUDGET_ALLOCAITON_ENTRIES, 0) = 100)
									BEGIN
										PRINT 'DONG CO CUOI'
										INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
										[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
										VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @amount - @TOTAL_PERCENT_AMT, @CURRENCY, @EXC_RATE,
										@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
									END
									ELSE
									BEGIN
										PRINT 'CHUA PHAI DONG CO CUOI'
										INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
										[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
										VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, CONVERT(DECIMAL, @amount*@PERCENT_BUDGET_ALLOCAITON_ENTRIES/100), @CURRENCY, @EXC_RATE,
										@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
									END
										
								END

							IF @@error<>0 GOTO ABORT;
							FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
							@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS,
							@IS_TRANSFER_EXTERNAL, @TYPE_BUDGET_ALLOCAITON_ENTRIES, @PERCENT_BUDGET_ALLOCAITON_ENTRIES, @AMT_BUDGET_ALLOCAITON_ENTRIES
							END
							CLOSE cursorEntries
							DEALLOCATE cursorEntries
						-- END CHẠY TỪNG CẶP BÚT TOÁN
								
							SET @TOTAL_PERCENT = @TOTAL_PERCENT + @PERCENT_BUDGET_ALLOCAITON_ENTRIES;
							SET @TOTAL_PERCENT_AMT = @TOTAL_PERCENT_AMT + @amount*@PERCENT_BUDGET_ALLOCAITON_ENTRIES/100

						IF @@error<>0 GOTO ABORT;
						FETCH NEXT FROM cursorEntries_C INTO @REQ_PAY_AUTO_ID, @ENTRY_PAIR
						END
						CLOSE cursorEntries_C
						DEALLOCATE cursorEntries_C

							
						--- BAN BUT TOAN VAO CORE
						EXEC [dbo].[PAY_ENTRIES_POST_SetApp] @REQ_PAY_ID,'admin','admin', @RES OUT
					END
					ELSE
					BEGIN
						PRINT 'CO THUE, KHONG PHAN CHIA 2 NO - 1 CO'
						DECLARE @DEBIT_AMT_1 DECIMAL(18,0) = @TOTAL_CREDIT_AMT/@PERCENT_VAT
						DECLARE @3532_AMT DECIMAL(18,0) = @TOTAL_CREDIT_AMT - @DEBIT_AMT_1

						DECLARE cursorEntries CURSOR LOCAL FOR 
						SELECT REQ_PAY_AUTO_ID, FUNCTION_TYPE, TRN_TYPE, REF_ID, ENTRY_PAIR, DR_CR, DR_CR_NAME, ACCT, ACCT_NAME, BRANCH_ID, DEP_ID, AMT, CURRENCY, EXC_RATE,
						TRN_DATE, TRN_DESC, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, MAKER_ID_KT, CREATE_DT_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, RECORD_STATUS, IS_TRANSFER_EXTERNAL
						FROM TR_REQ_PAY_AUTO_ENTRIES
						WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID
						Open cursorEntries
						FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
						@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL
						WHILE @@FETCH_STATUS = 0
						BEGIN

							EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES_D OUT;
							IF @l_TR_REQ_PAY_ENTRIES_D='' OR @l_TR_REQ_PAY_ENTRIES_D IS NULL GOTO ABORT;

							IF(@DR_CR = 'D')	-- Nợ
							BEGIN
								IF(@ACCT = '353200002')
								BEGIN
									print '3532'
									INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
									[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
									VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, '1', @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @3532_AMT, @CURRENCY, @EXC_RATE,
									@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
								END
								ELSE
								BEGIN
									print 'No TKCP'
									INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
									[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
									VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, '1', @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @DEBIT_AMT_1, @CURRENCY, @EXC_RATE,
									@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
								END
							END
							ELSE
							BEGIN
								print 'Co TKTT'
								INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
								[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
								VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, '1', @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @amount, @CURRENCY, @EXC_RATE,
								@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
							END

							
						IF @@error<>0 GOTO ABORT;
						FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
						@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL
						END
						CLOSE cursorEntries
						DEALLOCATE cursorEntries
						--- BAN BUT TOAN VAO CORE
						EXEC [dbo].[PAY_ENTRIES_POST_SetApp] @REQ_PAY_ID,'admin','admin', @RES OUT
					END
				END
				-- KHONG THUE
				ELSE	
				BEGIN
					IF(EXISTS(SELECT * FROM TR_REQ_PAY_AUTO_BUDGET_ALLOCATION WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID))
					BEGIN
						PRINT 'KHONG THUE, CO PHAN CHIA'
						IF(EXISTS(SELECT * FROM TR_REQ_PAY_AUTO_BUDGET_ALLOCATION WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND TYPE_BUDGET_ALLOCAITON = 'PERCENT'))
						BEGIN
							PRINT 'KHONG THUE, CO PHAN CHIA, PHAN CHIA THEO TY LE'
						-- CREATE CURSOR CÁC ĐẦU CÓ
							DECLARE cursorEntries_C CURSOR LOCAL FOR 
							SELECT REQ_PAY_AUTO_ID, ENTRY_PAIR
							FROM TR_REQ_PAY_AUTO_ENTRIES
							WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND DR_CR = 'C'

							Open cursorEntries_C
							FETCH NEXT FROM cursorEntries_C INTO @REQ_PAY_AUTO_ID, @ENTRY_PAIR
							WHILE @@FETCH_STATUS = 0
							BEGIN
							-- BEGIN CHẠY TỪNG CẶP BÚT TOÁN
								DECLARE cursorEntries CURSOR LOCAL FOR 
								SELECT REQ_PAY_AUTO_ID, FUNCTION_TYPE, TRN_TYPE, REF_ID, ENTRY_PAIR, DR_CR, DR_CR_NAME, ACCT, ACCT_NAME, BRANCH_ID, DEP_ID, AMT, CURRENCY, EXC_RATE,
								TRN_DATE, TRN_DESC, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, MAKER_ID_KT, CREATE_DT_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, RECORD_STATUS,
								IS_TRANSFER_EXTERNAL, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON
								FROM TR_REQ_PAY_AUTO_ENTRIES
								WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID AND ENTRY_PAIR = @ENTRY_PAIR

								Open cursorEntries
								FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
								@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS, 
								@IS_TRANSFER_EXTERNAL, @TYPE_BUDGET_ALLOCAITON_ENTRIES, @PERCENT_BUDGET_ALLOCAITON_ENTRIES, @AMT_BUDGET_ALLOCAITON_ENTRIES
								WHILE @@FETCH_STATUS = 0
								BEGIN

									EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES_D OUT;
									IF @l_TR_REQ_PAY_ENTRIES_D='' OR @l_TR_REQ_PAY_ENTRIES_D IS NULL GOTO ABORT;

									IF(@DR_CR = 'D')
									BEGIN
										IF(ISNULL(@TOTAL_PERCENT, 0) + ISNULL(@PERCENT_BUDGET_ALLOCAITON_ENTRIES, 0) = 100)
										BEGIN
											PRINT 'DONG NO CUOI'
											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @amount - @TOTAL_PERCENT_AMT, @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
										ELSE
										BEGIN
											PRINT 'CHUA PHAI DONG NO CUOI'

											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, CONVERT(DECIMAL, @amount*@PERCENT_BUDGET_ALLOCAITON_ENTRIES/100), @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
									END
									ELSE
									BEGIN
										IF(ISNULL(@TOTAL_PERCENT, 0) + ISNULL(@PERCENT_BUDGET_ALLOCAITON_ENTRIES, 0) = 100)
										BEGIN
											PRINT 'DONG CO CUOI'
											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @amount - @TOTAL_PERCENT_AMT, @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
										ELSE
										BEGIN
											PRINT 'CHUA PHAI DONG CO CUOI'
											INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
											[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
											VALUES (@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, CONVERT(DECIMAL, @amount*@PERCENT_BUDGET_ALLOCAITON_ENTRIES/100), @CURRENCY, @EXC_RATE,
											@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', @CREATE_DT, @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', @CREATE_DT_KT, @AUTH_STATUS_KT, 'admin', @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL)
										END
										
									END

								IF @@error<>0 GOTO ABORT;
								FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
								@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS,
								@IS_TRANSFER_EXTERNAL, @TYPE_BUDGET_ALLOCAITON_ENTRIES, @PERCENT_BUDGET_ALLOCAITON_ENTRIES, @AMT_BUDGET_ALLOCAITON_ENTRIES
								END
								CLOSE cursorEntries
								DEALLOCATE cursorEntries
							-- END CHẠY TỪNG CẶP BÚT TOÁN
								
								SET @TOTAL_PERCENT = @TOTAL_PERCENT + @PERCENT_BUDGET_ALLOCAITON_ENTRIES;
								SET @TOTAL_PERCENT_AMT = @TOTAL_PERCENT_AMT + @amount*@PERCENT_BUDGET_ALLOCAITON_ENTRIES/100

							IF @@error<>0 GOTO ABORT;
							FETCH NEXT FROM cursorEntries_C INTO @REQ_PAY_AUTO_ID, @ENTRY_PAIR
							END
							CLOSE cursorEntries_C
							DEALLOCATE cursorEntries_C

							
							--- BAN BUT TOAN VAO CORE
							EXEC [dbo].[PAY_ENTRIES_POST_SetApp] @REQ_PAY_ID,'admin','admin', @RES OUT
						END
						ELSE
						BEGIN
							PRINT 'KHONG THUE, CO PHAN CHIA, PHAN CHIA THEO SO TIEN'
						END
					END
					ELSE
					BEGIN
						PRINT 'KHONG THUE, KHONG PHAN CHIA 1 NO - 1 CO'

						DECLARE cursorEntries CURSOR LOCAL FOR 
						SELECT REQ_PAY_AUTO_ID, FUNCTION_TYPE, TRN_TYPE, REF_ID, ENTRY_PAIR, DR_CR, DR_CR_NAME, ACCT, ACCT_NAME, BRANCH_ID, DEP_ID, AMT, CURRENCY, EXC_RATE,
						TRN_DATE, TRN_DESC, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, MAKER_ID_KT, CREATE_DT_KT, AUTH_STATUS_KT, CHECKER_ID_KT, APPROVE_DT_KT, RECORD_STATUS, IS_TRANSFER_EXTERNAL
						FROM TR_REQ_PAY_AUTO_ENTRIES
						WHERE REQ_PAY_AUTO_ID = @REQ_PAY_AUTO_ID
						Open cursorEntries
				
						FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
						@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL
						WHILE @@FETCH_STATUS = 0
						BEGIN
							DECLARE @l_TR_REQ_PAY_ENTRIES VARCHAR(15);
							EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES OUT;
							IF @l_TR_REQ_PAY_ENTRIES='' OR @l_TR_REQ_PAY_ENTRIES IS NULL GOTO ABORT;

							INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID], [AMT],[CURRENCY],[EXC_RATE],
							[TRN_DATE],[TRN_DESC],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
							VALUES (@l_TR_REQ_PAY_ENTRIES, @REQ_PAY_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, CONVERT(DECIMAL(18,2), @amount), 'VND', @EXC_RATE,
							@TRN_DATE, @PAY_AUTO_TRN_DESC_DETAIL, 'admin', GETDATE(), @AUTH_STATUS, 'admin', @APPROVE_DT, 'admin', GETDATE(), 'A', 'admin', @APPROVE_DT_KT, '1', @IS_TRANSFER_EXTERNAL)

						IF @@error<>0 GOTO ABORT;
						FETCH NEXT FROM cursorEntries INTO @REQ_PAY_AUTO_ID, @FUNCTION_TYPE, @TRN_TYPE, @REF_ID, @ENTRY_PAIR, @DR_CR, @DR_CR_NAME, @ACCT, @ACCT_NAME, @BRANCH_ID, @DEP_ID_ENTRY, @AMT, @CURRENCY, @EXC_RATE,
						@TRN_DATE, @TRN_DESC, @MAKER_ID, @CREATE_DT, @AUTH_STATUS, @CHECKER_ID, @APPROVE_DT, @MAKER_ID_KT, @CREATE_DT_KT, @AUTH_STATUS_KT, @CHECKER_ID_KT, @APPROVE_DT_KT, @RECORD_STATUS, @IS_TRANSFER_EXTERNAL
						END
						CLOSE cursorEntries
						DEALLOCATE cursorEntries

						--- BAN BUT TOAN VAO CORE
						EXEC [dbo].[PAY_ENTRIES_POST_SetApp] @REQ_PAY_ID,'admin','admin', @RES OUT 
					END
				END
				
	-- END BUT TOAN CHI PHI



	-- BEGIN BUT TOAN HE THONG: BUT TOAN SO 0
			-- NỢ
				EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES_D OUT;
				INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],
				[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],
				[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID],
				[AMT],[CURRENCY],[EXC_RATE],[TRN_DATE],[TRN_DESC],
				[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
				VALUES(@l_TR_REQ_PAY_ENTRIES_D, @REQ_PAY_ID,NULL,'PAYMENT',NULL,
				'0', N'D', N'Nợ',
				@accountNo, @customerName, N'DV0001', N'',
				CONVERT(DECIMAL(18,2), @amount), N'VND', 1.00, GETDATE(), @PAY_AUTO_TRN_DESC_DETAIL,
				'admin',GETDATE(),'A','admin', GETDATE(),'admin',GETDATE(),'A','admin',GETDATE(),'1',NULL)

				DECLARE @l_PAY_ENTRIES_POST_D VARCHAR(15);
				EXEC SYS_CodeMasters_Gen 'PAY_ENTRIES_POST', @l_PAY_ENTRIES_POST_D OUT;
				INSERT INTO [PAY_ENTRIES_POST] ([ET_ID],[REF_NO],[TRN_ID],[REF_ID],[TRN_TYPE],
							[ENTRY_PAIR],[DO_BRN],[DRCR],[ACCT],[BRN_ID],[DEPT_ID],
							[AMT],[CURRENCY],[EXC_RATE],[EXP_TO_CORE],[TRN_DATE],[TRN_DESC],[MAKER_ID],[CHECKER_ID])
				VALUES	(@l_PAY_ENTRIES_POST_D, @coreXref, @REQ_PAY_ID, @l_TR_REQ_PAY_ENTRIES_D,'PAYMENT',
						'0',N'DV0001',N'D',
						@accountNo,N'DV0001',N'',
						CONVERT(DECIMAL(18,2), @amount),N'VND',1.00,N'1',GETDATE(),
						@PAY_AUTO_TRN_DESC_DETAIL,'admin','admin')

			-- CÓ
				DECLARE @l_TR_REQ_PAY_ENTRIES_C VARCHAR(15);
				EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @l_TR_REQ_PAY_ENTRIES_C OUT;
				INSERT INTO [TR_REQ_PAY_ENTRIES] ([REQ_PAY_DT_ID],[REQ_PAY_ID],[FUNCTION_TYPE],[TRN_TYPE],[REF_ID],
				[ENTRY_PAIR],[DR_CR],[DR_CR_NAME],
				[ACCT],[ACCT_NAME],[BRANCH_ID],[DEP_ID],
				[AMT],[CURRENCY],[EXC_RATE],[TRN_DATE],[TRN_DESC],
				[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[MAKER_ID_KT],[CREATE_DT_KT],[AUTH_STATUS_KT],[CHECKER_ID_KT],[APPROVE_DT_KT],[RECORD_STATUS],[IS_TRANSFER_EXTERNAL])
				VALUES(@l_TR_REQ_PAY_ENTRIES_C, @REQ_PAY_ID,NULL,'PAYMENT',NULL,
				'0',N'C',N'Có',
				@partnerAccount, @partner,N'DV0001',N'',
				CONVERT(DECIMAL(18,2), @amount),N'VND',1.00,GETDATE(),@PAY_AUTO_TRN_DESC_DETAIL,
				'admin',GETDATE(),'A','admin', GETDATE(),'admin',GETDATE(),'A','admin',GETDATE(),'1',NULL)

				DECLARE @l_PAY_ENTRIES_POST_C VARCHAR(15);
				EXEC SYS_CodeMasters_Gen 'PAY_ENTRIES_POST', @l_PAY_ENTRIES_POST_C OUT;
				INSERT INTO [PAY_ENTRIES_POST] ([ET_ID],[REF_NO],[TRN_ID],[REF_ID],[TRN_TYPE],
							[ENTRY_PAIR],[DO_BRN],[DRCR],[ACCT],[BRN_ID],[DEPT_ID],
							[AMT],[CURRENCY],[EXC_RATE],[EXP_TO_CORE],[TRN_DATE],[TRN_DESC],[MAKER_ID],[CHECKER_ID])
				VALUES	(@l_PAY_ENTRIES_POST_C, @coreXref, @REQ_PAY_ID,@l_TR_REQ_PAY_ENTRIES_C,'PAYMENT',
						'0',N'DV0001',N'C',
						@partnerAccount,N'DV0001',N'',
						CONVERT(DECIMAL(18,2), @amount),N'VND',1.00,N'1',GETDATE(),
						@PAY_AUTO_TRN_DESC_DETAIL, 'admin', 'admin')
	-- END BUT TOAN HE THONG: BUT TOAN SO 0

			-- Mỗi lần chỉ quét 1 bill hợp lệ
			CLOSE XmlAutoRecurring;
			DEALLOCATE XmlAutoRecurring;
			COMMIT TRANSACTION
			SELECT '0' as Result, @list_req_payment LIST_REQ_PAYMENT, N'Quét bill thành công!' ErrorDesc
			RETURN '0'

		END
		
	IF @@error<>0 GOTO ABORT;
	FETCH NEXT FROM XmlAutoRecurring INTO @billRef, @billCode, @customerName, @amount, @accountNo, @accountType, @createdTime,
	@partner, @resultCode, @resultDesc, @transDesc, @coreXref, @partnerAccount, @month
	END;
	CLOSE XmlAutoRecurring;
	DEALLOCATE XmlAutoRecurring;
	
COMMIT TRANSACTION

SELECT '0' as Result, @list_req_payment LIST_REQ_PAYMENT, N'Quét bill thành công!' ErrorDesc
RETURN '0'

ABORT:
BEGIN
	ROLLBACK TRANSACTION
	SELECT '-1' as Result, @list_req_payment LIST_REQ_PAYMENT, N'Lỗi không xác định' ErrorDesc
	RETURN '-1'
End

GO

INSERT INTO [dbo].[SYS_PREFIX](ID,Prefix,Description) VALUES('TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES','PAEE',N'Chỉnh sửa hạch toán PYC TTTĐ')
INSERT INTO [dbo].[SYS_PREFIX](ID,Prefix,Description) VALUES('TR_REQ_PAYMENT_AUTO_EDIT_ENTRIES_ENTRIEs','PAEEE',N'Chi tiết chỉnh sửa hạch toán PYC TTTĐ')

GO

--18072023_secretkey