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

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