ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_KT_Upd]
@p_REQ_PAY_AUTO_ID	VARCHAR(20)= NULL,
@p_REQ_PAY_AUTO_CODE	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_DEP_ID	VARCHAR(20)= NULL,
@p_BRANCH_CREATE	VARCHAR(20)= NULL,
@p_TRANSFER_USER_RECEIVE	VARCHAR(20)= NULL,
@p_CONFIRM_NOTE	NVARCHAR(500)= NULL,
@p_CONTRACT_ID	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_CREATE_DT	VARCHAR(20)= NULL,
@p_EDITOR_ID	VARCHAR(20)= NULL,
@p_EDIT_DT	VARCHAR(20)= NULL,
@p_CHECKER_ID	VARCHAR(20)= NULL,
@p_APPROVE_DT	VARCHAR(20)= NULL,
@p_AUTH_STATUS	VARCHAR(20)= NULL,
@p_MAKER_ID_KT	VARCHAR(20)= NULL,
@p_CREATE_DT_KT	VARCHAR(20)= NULL,
@p_CHECKER_ID_KT	VARCHAR(20)= NULL,
@p_APPROVE_DT_KT	VARCHAR(20)= NULL,
@p_AUTH_STATUS_KT	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_XMP_BUDGET_ALLOCATION XML = NULL,
@p_XMP_ENTRIES XML = NULL,
@p_XMP_TRANSFER_OUTSIDE_TRANS XML = NULL,
@p_XMP_TRANSFER_OUTSIDE_REC XML = NULL
AS
-- BEGIN VALIDATE
-- END VALIDATE
-- BEGIN VALIDATE FLOW
	IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE ISNULL(AUTH_STATUS, '') <> 'A' AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID AND ISNULL(REQUEST_STATUS, '') = ''))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Cập nhật thất bại! Phiếu yêu cầu thanh toán tự động chưa được chuyển đến phòng kế toán' ErrorDesc
		RETURN '-1'
	END
	IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS_KT = 'A' AND ISNULL(PROCESS, '') = '5' AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Cập nhật thất bại! Phiếu yêu cầu thanh toán tự động đã được kiếm sát viên phê duyệt' ErrorDesc
		RETURN '-1'
	END
	IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS_KT = 'P' AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Cập nhật thất bại! Phiếu yêu cầu thanh toán tự động đã được gửi phê duyệt trước đó' ErrorDesc
		RETURN '-1'
	END
	IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE AUTH_STATUS_KT = 'S' AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Cập nhật thất bại! Phiếu yêu cầu thanh toán tự động đã được đề xuất từ chối trước đó' ErrorDesc
		RETURN '-1'
	END
	IF(NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = @p_REQ_PAY_AUTO_ID))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_AUTO_ID, N'Cập nhật thất bại! Phiếu yêu cầu thanh toán tự động chưa được KSV điều phối' ErrorDesc
		RETURN '-1'
	END
-- END VALIDATE FLOW
BEGIN TRANSACTION
	IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE ISNULL(PROCESS, '') NOT IN ('12', '18') AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))	-- NEU KHONG PHAI XAC NHAN TAM DUNG HOAC HUY PHIEU
	BEGIN
		UPDATE TR_REQ_PAYMENT_AUTO
		SET AUTH_STATUS_KT = 'U', CONFIRM_NOTE = @p_CONFIRM_NOTE, CREATE_DT_KT = GETDATE(), MAKER_ID_KT = @p_USER_LOGIN, CHECKER_ID_KT=NULL, APPROVE_DT_KT = NULL
		WHERE 1 = 1 
		AND REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
		IF @@Error <> 0 GOTO ABORT

		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), @BRANCH_CODE VARCHAR(15), @DEP_CODE 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)
	------------------------------------------------------------------------- BEGIN LUOI HACH TOAN -----------------------------------------------------------------------
		DECLARE @hdoc INT;
		EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_ENTRIES;
		DECLARE XmlDataEntries CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataEntries', 2)
		WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),
		BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000),GL_CODE VARCHAR(100),GL_CODE_ACCNO VARCHAR(20), IS_TRANSFER_EXTERNAL VARCHAR(15))
		OPEN XmlDataEntries;

		DELETE FROM TR_REQ_PAY_AUTO_ENTRIES WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
		DECLARE @INDEX INT = 0;
		IF @@Error <> 0 GOTO ABORT
		DECLARE @DR_CR_NAME NVARCHAR(50),@ACC_NAME_FN NVARCHAR(500)
		FETCH NEXT FROM XmlDataEntries INTO @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
		WHILE @@fetch_status=0 
		BEGIN
			IF(@EXC_RATE IS NULL)
			BEGIN
				SET @EXC_RATE = 1
			END

			SET @INDEX = @INDEX +1
			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

			SET @BRANCH_CODE =(SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_ID)
			SET @DEP_CODE =(SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@DEP_ID)
	
			IF(@p_IS_SEND_APPR = 'SEND')
			BEGIN
				IF(@ENTRY_PAIR IS NULL OR @ENTRY_PAIR ='')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Mã bút toán không được phép để trống' ErrorDesc
					RETURN '-1'
				END

				IF(@DR_CR IS NULL OR @DR_CR ='')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Nợ/có không được phép để trống' ErrorDesc
					RETURN '-1'
				END
				
				IF(@ACCT IS NULL OR @ACCT ='')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Số tài khoản không được phép để trống' ErrorDesc
					RETURN '-1'
				END

				IF(@ACCT_NAME IS NULL OR @ACCT_NAME ='')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc
					RETURN '-1'
				END

				IF(@BRANCH_ID IS NULL OR @BRANCH_ID ='')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Đơn vị nhận không được phép để trống' ErrorDesc
					RETURN '-1'
				END

				IF(@ACCT IS NOT NULL AND @ACCT <> '' AND LEFT(@ACCT,1) IN ('7','8') AND (@DEP_ID IS NULL OR @DEP_ID ='') AND LEN(@ACCT) < 13)
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Tài khoản hạch toán đầu số 7 & 8 vui lòng chọn mã phòng ban' ErrorDesc
					RETURN '-1'
				END

				IF(@GL_CODE IS NULL OR @GL_CODE ='')
				BEGIN
					IF (LEN(@ACCT) >9 AND ((SELECT ISNULL(TK_GL,'') FROM CM_ACCOUNT WHERE ACC_NO =@ACCT) ='' OR (SELECT ISNULL(TK_GL_NAME,'') FROM CM_ACCOUNT WHERE ACC_NO =@ACCT) =''))
					BEGIN
						ROLLBACK TRANSACTION
						CLOSE XmlDataEntries;
						DEALLOCATE XmlDataEntries;
						SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Tài khoản CASA chưa có thông tin GL Mapping. Vui lòng bấm vào nút Kiểm tra tài khoản để đồng bộ tài khoản từ Core' ErrorDesc
						RETURN '-1'
					END
				END

				IF(@ACCT IS NOT NULL AND @ACCT <> '' AND LEFT(@ACCT,1) IN ('7','8') AND (@DEP_ID IS NULL OR @DEP_ID ='') AND LEN(@ACCT) < 13)
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Tài khoản hạch toán đầu số 7 & 8 vui lòng chọn mã phòng ban' ErrorDesc
					RETURN '-1'
				END

				IF(@DEP_ID IS NOT NULL AND @DEP_ID <> '' AND (LEFT(@DEP_CODE,3) <> @BRANCH_CODE))
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;	
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Vui lòng chọn mã phòng ban khớp với mã đơn vị tương ứng với 3 kí tự đầu của tài khoản CASA'  ErrorDesc
					RETURN '-1'
				END

				-- NEU HACH TOAN CASA THI 3 KI TU DAU PHAI KHOP VOI BRANCH CODE CUA DON VI CHIU CHI PHI
				IF(LEN(@ACCT) >9 AND (SUBSTRING(@ACCT,1,3) <> (SELECT ISNULL(BRANCH_CODE,'') FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_ID)))
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataEntries;	
					DEALLOCATE XmlDataEntries;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới hạch toán, dòng '+ CONVERT(VARCHAR(5),@INDEX) + N': Nếu bạn đang hạch toán CASA trong hệ thống. Vui lòng chọn mã đơn vị khớp với mã đơn vị tương ứng với 3 kí tự đầu của tài  khoản CASA'  ErrorDesc
					RETURN '-1'
				END
			END -- END IF VALIDATE
	
			DECLARE @p_ET_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_AUTO_ENTRIES', @p_ET_ID OUT;
			IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;


			INSERT INTO TR_REQ_PAY_AUTO_ENTRIES (REQ_PAY_AUTO_DT_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)
			VALUES (@p_ET_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_USER_LOGIN, 'PAYMENT', 'VND', 1, @IS_TRANSFER_EXTERNAL)

			-- 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 @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
		END;--END WHILE
		CLOSE XmlDataEntries;
		DEALLOCATE XmlDataEntries;
	------------------------------------------------------------------------------------------ END LUOI HACH TOAN --------------------------------------------------------------------

	-- BEGIN INSERT đơn vị chuyển
		DECLARE @hdocOutsideTrans INT
		EXEC sp_xml_preparedocument @hdocOutsideTrans OUTPUT, @p_XMP_TRANSFER_OUTSIDE_TRANS;
		DECLARE  @MAKER_ID_KT VARCHAR(15), @TYPE_TRANSFER_ID VARCHAR(15), @PRODUCT_ID VARCHAR(15), @OBJECT_TRANSFER_ID VARCHAR(15), 
		@ACC_NO_TRANSFER VARCHAR(255), @BRANCH_TRANSFER_ID VARCHAR(15), @BRANCH_TRANFSER_NAME NVARCHAR(255)
		DECLARE XmlDataOutsideTrans CURSOR LOCAL FOR
		SELECT * FROM OPENXML(@hdocOutsideTrans, 'Root/XmlDataTransferOutside',2) 
		WITH(MAKER_ID VARCHAR(15), TYPE_TRANSFER_ID VARCHAR(15), PRODUCT_ID VARCHAR(15), OBJECT_TRANSFER_ID VARCHAR(15), BRANCH_ID VARCHAR(15), 
		ACC_NO_TRANSFER VARCHAR(255), BRANCH_TRANSFER_ID VARCHAR(15), CURRENCY VARCHAR(15), BRANCH_TRANFSER_NAME NVARCHAR(255))

		OPEN XmlDataOutsideTrans
		DELETE FROM TR_REQ_PAY_AUTO_TRANS_OUTSIDE_TRANS WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
		FETCH NEXT FROM XmlDataOutsideTrans INTO @MAKER_ID_KT , @TYPE_TRANSFER_ID, @PRODUCT_ID, @OBJECT_TRANSFER_ID, @BRANCH_ID, 
		@ACC_NO_TRANSFER, @BRANCH_TRANSFER_ID, @CURRENCY, @BRANCH_TRANFSER_NAME
		WHILE @@fetch_status=0 
		BEGIN
			DECLARE @p_TR_REQ_TRANS_OUTSIDE_ID VARCHAR(15);
			EXEC SYS_CodeMasters_Gen 'TR_REQ_AUTO_OUTSIDE_TRANS', @p_TR_REQ_TRANS_OUTSIDE_ID OUT;
			IF @p_TR_REQ_TRANS_OUTSIDE_ID='' OR @p_TR_REQ_TRANS_OUTSIDE_ID IS NULL GOTO ABORT;

			INSERT INTO TR_REQ_PAY_AUTO_TRANS_OUTSIDE_TRANS(TR_REQ_TRANS_OUTSIDE_ID ,REQ_PAY_AUTO_ID ,MAKER_ID , CREATE_DT , AUTH_STATUS, AUTH_STATUS_KT , 
			BRANCH_TRANFSER_NAME,TYPE_TRANSFER_ID , PRODUCT_ID , OBJECT_TRANSFER_ID , BRANCH_ID, ACC_NO_TRANSFER , BRANCH_TRANSFER_ID)
			VALUES (@p_TR_REQ_TRANS_OUTSIDE_ID ,@p_REQ_PAY_AUTO_ID ,@p_MAKER_ID_KT , GETDATE(), NULL , NULL, 
			@BRANCH_TRANFSER_NAME, @TYPE_TRANSFER_ID , @PRODUCT_ID , @OBJECT_TRANSFER_ID , @BRANCH_ID, @ACC_NO_TRANSFER , @BRANCH_TRANSFER_ID)

			IF @@error<>0 GOTO ABORT;
			FETCH NEXT FROM XmlDataOutsideTrans INTO @MAKER_ID_KT , @TYPE_TRANSFER_ID, @PRODUCT_ID, @OBJECT_TRANSFER_ID, @BRANCH_ID, 
			@ACC_NO_TRANSFER, @BRANCH_TRANSFER_ID, @CURRENCY, @BRANCH_TRANFSER_NAME
		END;
		CLOSE XmlDataOutsideTrans;
		DEALLOCATE XmlDataOutsideTrans;
	--- END INSERT đơn vị chuyển

	-- BEGIN INSERT đơn vị nhận
		DECLARE @hdocOutsideRec INT
		EXEC sp_xml_preparedocument @hdocOutsideRec OUTPUT, @p_XMP_TRANSFER_OUTSIDE_REC;

		DECLARE @CHANEL_PAYMENT_ID VARCHAR(15), @CITAD1_ID VARCHAR(15), @BANK_RECEIVE_ID VARCHAR(250), @OBJECT_RECEIVE_ID VARCHAR(15), 
					@BRANCH_RECEIVE_ID VARCHAR(15), @BRANCH_RECEIVE_NAME NVARCHAR(255), @ACC_NO_RECEIVE VARCHAR(255), @TRADE_AMT DECIMAL(18, 0), @TRADE_DESC NVARCHAR(120), @SUB_BANK_RECEIVE_NAME nvarchar(MAX), @REF_NO VARCHAR(50)
	-- INSERT đơn vị nhận
		DECLARE XmlDataOutsideRec CURSOR LOCAL FOR
		SELECT * FROM OPENXML(@hdocOutsideRec, 'Root/XmlDataTransferOutside',2) 
		WITH(MAKER_ID VARCHAR(15), CHANEL_PAYMENT_ID VARCHAR(15), CITAD1_ID VARCHAR(15), BANK_RECEIVE_ID VARCHAR(250), OBJECT_RECEIVE_ID VARCHAR(15), 
		BRANCH_RECEIVE_ID VARCHAR(15), BRANCH_RECEIVE_NAME NVARCHAR(255), ACC_NO_RECEIVE VARCHAR(255), TRADE_AMT DECIMAL(18, 0), TRADE_DESC NVARCHAR(120), SUB_BANK_RECEIVE_NAME nvarchar(MAX),  
		ENTRY_PAIR varchar(20), REF_NO varchar(20)) 
	
		OPEN XmlDataOutsideRec
		DECLARE @INDEX_REC INT
		DELETE FROM TR_REQ_PAY_AUTO_TRANS_OUTSIDE_REC WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
		FETCH NEXT FROM XmlDataOutsideRec INTO @MAKER_ID_KT, @CHANEL_PAYMENT_ID, @CITAD1_ID, @BANK_RECEIVE_ID, @OBJECT_RECEIVE_ID, 
		@BRANCH_RECEIVE_ID, @BRANCH_RECEIVE_NAME, @ACC_NO_RECEIVE, @TRADE_AMT, @TRADE_DESC, @SUB_BANK_RECEIVE_NAME, @ENTRY_PAIR, @REF_NO
		WHILE @@fetch_status=0 
		BEGIN
			IF(@p_IS_SEND_APPR = 'SEND')
			BEGIN
			SET @INDEX_REC = @INDEX_REC+1
				IF(ISNULL(@ACC_NO_RECEIVE, '') = '')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataOutsideRec;
					DEALLOCATE XmlDataOutsideRec;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới đơn vị nhận, dòng '+ CONVERT(VARCHAR(5),@INDEX_REC) + N': Số tài khoản không được để trống' ErrorDesc
					RETURN '-1'
				END
				IF(ISNULL(@BRANCH_RECEIVE_NAME, '') = '')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataOutsideRec;
					DEALLOCATE XmlDataOutsideRec;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới đơn vị nhận, dòng '+ CONVERT(VARCHAR(5),@INDEX_REC) + N': Tên đơn vị nhận không được để trống' ErrorDesc
					RETURN '-1'
				END
				IF(ISNULL(@OBJECT_RECEIVE_ID, '') = '')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataOutsideRec;
					DEALLOCATE XmlDataOutsideRec;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới đơn vị nhận, dòng '+ CONVERT(VARCHAR(5),@INDEX_REC) + N': Đối tượng nhận không được để trống' ErrorDesc
					RETURN '-1'
				END
				IF(ISNULL(@BANK_RECEIVE_ID, '') = '' OR ISNULL(@BRANCH_RECEIVE_NAME, '') = '')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataOutsideRec;
					DEALLOCATE XmlDataOutsideRec;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới đơn vị nhận, dòng '+ CONVERT(VARCHAR(5),@INDEX_REC) + N': Tên ngân hàng hưởng trực tiếp không được để trống' ErrorDesc
					RETURN '-1'
				END
				IF(ISNULL(@CITAD1_ID, '') = '')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataOutsideRec;
					DEALLOCATE XmlDataOutsideRec;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới đơn vị nhận, dòng '+ CONVERT(VARCHAR(5),@INDEX_REC) + N': Mã CITAD không được để trống' ErrorDesc
					RETURN '-1'
				END
				IF(ISNULL(@CHANEL_PAYMENT_ID, '') = '')
				BEGIN
					ROLLBACK TRANSACTION
					CLOSE XmlDataOutsideRec;
					DEALLOCATE XmlDataOutsideRec;
					SELECT '-1' as Result, '' REQ_PAY_ID, N'Lưới đơn vị nhận, dòng '+ CONVERT(VARCHAR(5),@INDEX_REC) + N': Kênh thanh toán không được để trống' ErrorDesc
					RETURN '-1'
				END
			END

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

			INSERT INTO TR_REQ_PAY_AUTO_TRANS_OUTSIDE_REC(REQ_TRANS_OUTSIDE_REC_ID, REQ_PAY_AUTO_ID, MAKER_ID, CREATE_DT, AUTH_STATUS, AUTH_STATUS_KT,
			CHANEL_PAYMENT_ID, CITAD1_ID, BANK_RECEIVE_ID, OBJECT_RECEIVE_ID, BRANCH_RECEIVE_ID, BRANCH_RECEIVE_NAME, ACC_NO_RECEIVE, TRADE_AMT, TRADE_DESC, SUB_BANK_RECEIVE_NAME, ENTRY_PAIR, REF_NO)
			VALUES (@p_REQ_TRANS_OUTSIDE_REC_ID, @p_REQ_PAY_AUTO_ID, @p_USER_LOGIN, GETDATE(), NULL, NULL,
			@CHANEL_PAYMENT_ID, @CITAD1_ID, @BANK_RECEIVE_ID, @OBJECT_RECEIVE_ID, @BRANCH_RECEIVE_ID, @BRANCH_RECEIVE_NAME, @ACC_NO_RECEIVE, @TRADE_AMT, @TRADE_DESC, @SUB_BANK_RECEIVE_NAME, @ENTRY_PAIR, @REF_NO)

		IF @@error<>0 GOTO ABORT;
		FETCH NEXT FROM XmlDataOutsideRec INTO  @MAKER_ID_KT, @CHANEL_PAYMENT_ID, @CITAD1_ID, @BANK_RECEIVE_ID, @OBJECT_RECEIVE_ID, 
		@BRANCH_RECEIVE_ID, @BRANCH_RECEIVE_NAME, @ACC_NO_RECEIVE, @TRADE_AMT, @TRADE_DESC, @SUB_BANK_RECEIVE_NAME, @ENTRY_PAIR, @REF_NO
		END;
		CLOSE XmlDataOutsideRec;
		DEALLOCATE XmlDataOutsideRec;
	--- END INSERT đơn vị nhận

--- INSERT PHÂN CHIA
	DECLARE @hDocBudgetAllocation INT
	EXEC sp_xml_preparedocument @hDocBudgetAllocation OUTPUT, @p_XMP_BUDGET_ALLOCATION;

	DECLARE @TYPE_BUDGET_ALLOCAITON VARCHAR(20), @PERCENT_BUDGET_ALLOCAITON DECIMAL(18,2), @AMT_BUDGET_ALLOCAITON DECIMAL(18,2), @REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20)

	DECLARE XmlDataBudgetAllocation CURSOR FOR SELECT * FROM OPENXML(@hDocBudgetAllocation, 'Root/XmlDataBudgetAllocation',2)
	WITH(REQ_PAY_AUTO_MANUFACTURER_ID VARCHAR(20), ACC_NO VARCHAR(20), ACC_NAME NVARCHAR(255), BRANCH_ID VARCHAR(20), DEP_ID NVARCHAR(255), TYPE_BUDGET_ALLOCAITON VARCHAR(20), PERCENT_BUDGET_ALLOCAITON DECIMAL(18,2), AMT_BUDGET_ALLOCAITON DECIMAL(18,2))
	OPEN XmlDataBudgetAllocation

	DELETE FROM TR_REQ_PAY_AUTO_BUDGET_ALLOCATION WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
	FETCH NEXT FROM XmlDataBudgetAllocation INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON
	WHILE @@fetch_status=0 
	BEGIN
		DECLARE @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID VARCHAR(20)
		EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_AUTO_ALLCOTION', @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID OUT;
		IF @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID = '' OR @REQ_PAY_AUTO_BUDGET_ALLOCATION_ID IS NULL GOTO ABORT;
		
		INSERT INTO TR_REQ_PAY_AUTO_BUDGET_ALLOCATION(REQ_PAY_AUTO_BUDGET_ALLOCATION_ID, REQ_PAY_AUTO_MANUFACTURER_ID, REQ_PAY_AUTO_ID, ACC_NO, ACC_NAME, BRANCH_ID, DEP_ID, TYPE_BUDGET_ALLOCAITON, PERCENT_BUDGET_ALLOCAITON, AMT_BUDGET_ALLOCAITON, MAKER_ID, CREATE_DT, RECORD_STATUS)
		VALUES (@REQ_PAY_AUTO_BUDGET_ALLOCATION_ID, @REQ_PAY_AUTO_MANUFACTURER_ID, @p_REQ_PAY_AUTO_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON, @p_MAKER_ID, GETDATE(), '1')
	
	IF @@error<>0 GOTO ABORT;
	FETCH NEXT FROM XmlDataBudgetAllocation INTO @REQ_PAY_AUTO_MANUFACTURER_ID, @ACC_NO, @ACC_NAME, @BRANCH_ID, @DEP_ID, @TYPE_BUDGET_ALLOCAITON, @PERCENT_BUDGET_ALLOCAITON, @AMT_BUDGET_ALLOCAITON
	END
	CLOSE XmlDataBudgetAllocation;
	DEALLOCATE XmlDataBudgetAllocation;

	END	-- END XAC NHAN TAM DUNG HOAC HUY PHIEU

COMMIT TRANSACTION
	IF(@p_IS_SEND_APPR ='SEND')
	BEGIN
		IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE ISNULL(PROCESS, '') NOT IN ('12', '18', '14.2', '20.2') AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))	-- NEU KHONG PHAI XAC NHAN TAM DUNG HOAC HUY PHIEU
		BEGIN
			UPDATE TR_REQ_PAYMENT_AUTO 
			SET AUTH_STATUS_KT = 'P', CREATE_DT_KT = GETDATE(), MAKER_ID_KT = @p_USER_LOGIN, CONFIRM_NOTE = @p_CONFIRM_NOTE, PROCESS = '4'
			WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
			-- UPDATE FLOW KE TOAN
			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_AUTO_ID AND TLNAME= @p_USER_LOGIN AND TYPE_JOB = 'XL'
			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_AUTO_ID AND TYPE_JOB = 'KS'
			-- INSERT VAO PL_PROCESS
			INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
			VALUES(@p_REQ_PAY_AUTO_ID,'SEND_GDV',@p_USER_LOGIN,GETDATE(), N'Giao dịch viên gửi phê duyệt',N'Giao dịch viên cập nhật thông tin')
			SELECT '1' as Result, '' REQ_PAY_ID, N'Phiếu yêu cầu thanh toán tự động số: '+(SELECT REQ_PAY_AUTO_CODE FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID)+N' đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
			RETURN '1'
		END
		ELSE IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE ISNULL(PROCESS, '') IN ('12', '14.2') AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))
		BEGIN
			UPDATE TR_REQ_PAYMENT_AUTO 
			SET CREATE_DT_KT = GETDATE(), MAKER_ID_KT = @p_USER_LOGIN, CONFIRM_NOTE = @p_CONFIRM_NOTE, PROCESS = '13'
			WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
			-- UPDATE FLOW KE TOAN
			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_AUTO_ID AND TLNAME= @p_USER_LOGIN AND TYPE_JOB = 'XL'
			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_AUTO_ID AND TYPE_JOB = 'KS'
			-- INSERT VAO PL_PROCESS
			INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
			VALUES(@p_REQ_PAY_AUTO_ID,'GDV_UNSUS',@p_USER_LOGIN,GETDATE(), N'Giao dịch viên gửi phê duyệt',N'Giao dịch viên xác nhận yêu cầu khôi phục phiếu')
			SELECT '1' as Result, '' REQ_PAY_ID, N'Phiếu yêu cầu thanh toán tự động số: '+(SELECT REQ_PAY_AUTO_CODE FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID)+N' đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
			RETURN '1'
		END
		ELSE IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_AUTO WHERE ISNULL(PROCESS, '') IN ('18', '20.2') AND REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID))
		BEGIN
			UPDATE TR_REQ_PAYMENT_AUTO 
			SET PROCESS = '19', CREATE_DT_KT = GETDATE(), MAKER_ID_KT = @p_USER_LOGIN, CONFIRM_NOTE = @p_CONFIRM_NOTE
			WHERE REQ_PAY_AUTO_ID = @p_REQ_PAY_AUTO_ID
			-- UPDATE FLOW KE TOAN
			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_AUTO_ID AND TLNAME= @p_USER_LOGIN AND TYPE_JOB = 'XL'
			UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_AUTO_ID AND TYPE_JOB = 'KS'
			-- INSERT VAO PL_PROCESS
			INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
			VALUES(@p_REQ_PAY_AUTO_ID,'GDV_DEL',@p_USER_LOGIN,GETDATE(), N'Giao dịch viên gửi phê duyệt',N'Giao dịch viên xác nhận yêu cầu hủy phiếu')
			SELECT '1' as Result, '' REQ_PAY_ID, N'Phiếu yêu cầu thanh toán tự động số: '+(SELECT REQ_PAY_AUTO_CODE FROM TR_REQ_PAYMENT_AUTO WHERE REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID)+N' đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
			RETURN '1'
		END
		ELSE
		BEGIN
			PRINT '1'
		END

		
	END

SELECT '0' as Result, @p_REQ_PAY_AUTO_ID  REQ_PAY_ID, @p_REQ_PAY_AUTO_CODE  AS REQ_PAY_CODE, N'Thêm mới thành công' ErrorDesc
RETURN '0'

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

GO


ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_AUTO_TRANSFER_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_IS_TRANSFER	VARCHAR(20)= NULL,
@p_TOP INT = NULL
AS
BEGIN -- PAGING
-- BEGIN KHAI BÁO
-- ĐƠ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) 
									AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
	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) 
								AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)

-- 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 ='') 
	
-- 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
-- END KHAI BÁO

	IF(@p_TOP IS NULL OR @p_TOP=0)
	BEGIN
-- PAGING BEGIN
		SELECT A.*, B.TLNAME AS TRANSFER_USER_RECEIVE,
		C.BRANCH_NAME, D.DEP_NAME, E.TLFullName AS MAKER_NAME, F.CONTENT AS REQ_PAY_AUTO_TYPE_NAME, G.CONTENT AS AUTH_STATUS_NAME, H.CONTENT AS AUTH_STATUS_KT_NAME
-- SELECT END
        FROM TR_REQ_PAYMENT_AUTO A
		LEFT JOIN PL_REQUEST_PROCESS_CHILD B ON A.REQ_PAY_AUTO_ID = B.REQ_ID AND B.TYPE_JOB = 'XL'
		LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT D ON A.DEP_ID = D.DEP_ID
		LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
		LEFT JOIN CM_ALLCODE F ON A.REQ_PAY_AUTO_TYPE = F.CDVAL AND F.CDNAME = 'PAY_TYPE_AUTO' AND F.CDTYPE = 'REQ_AUTO'
		LEFT JOIN CM_ALLCODE G ON A.AUTH_STATUS = G.CDVAL AND G.CDNAME = 'AUTH_STATUS' AND G.CDTYPE = 'TR_REQ_PAYMENT'
		LEFT JOIN CM_ALLCODE H ON A.AUTH_STATUS_KT = H.CDVAL AND H.CDNAME = 'AUTH_STATUS_KT' AND H.CDTYPE = 'TR_REQ_PAYMENT'
        WHERE 1=1 
		AND	(	A.AUTH_STATUS = 'A' OR A.AUTH_STATUS = 'N')
		AND A.PROCESS IN ('2', '11', '17')
		-- BEGIN FILTER
		AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
		AND	(	A.PROCESS = @p_PROCESS OR ISNULL(@p_PROCESS, '') = '')
		AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
		AND	(	A.REQ_PAY_AUTO_SERVICE_TYPE = @p_REQ_PAY_AUTO_SERVICE_TYPE OR ISNULL(@p_REQ_PAY_AUTO_SERVICE_TYPE, '') = '')
		AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
		AND	(	(	@p_IS_TRANSFER = 'Y' 
					AND	EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_AUTO_ID) 
						
				)
				OR	(	@p_IS_TRANSFER='N' 
						AND	NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_AUTO_ID )
					)
				OR @p_IS_TRANSFER IS NULL 
				OR @p_IS_TRANSFER=''
			)
		AND	(CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR @p_FRMDATE IS NULL OR @p_FRMDATE = '' OR A.CREATE_DT IS NULL)
		AND	(CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE = '' OR A.CREATE_DT IS NULL)
		--AND	(	A.BRANCH_ID = @p_BRANCH_ID OR ISNULL(@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	(	B.TLNAME = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
		-- END FILTER
		ORDER BY A.CREATE_DT DESC
-- PAGING END
    END;
    ELSE 
	BEGIN
-- PAGING BEGIN
		SELECT A.*, B.TLNAME AS TRANSFER_USER_RECEIVE,
		C.BRANCH_NAME, D.DEP_NAME, E.TLFullName AS MAKER_NAME, F.CONTENT AS REQ_PAY_AUTO_TYPE_NAME, G.CONTENT AS AUTH_STATUS_NAME, H.CONTENT AS AUTH_STATUS_KT_NAME
-- SELECT END
        FROM TR_REQ_PAYMENT_AUTO A
		LEFT JOIN PL_REQUEST_PROCESS_CHILD B ON A.REQ_PAY_AUTO_ID = B.REQ_ID AND B.TYPE_JOB = 'XL'
		LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT D ON A.DEP_ID = D.DEP_ID
		LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
		LEFT JOIN CM_ALLCODE F ON A.REQ_PAY_AUTO_TYPE = F.CDVAL AND F.CDNAME = 'PAY_TYPE_AUTO' AND F.CDTYPE = 'REQ_AUTO'
		LEFT JOIN CM_ALLCODE G ON A.AUTH_STATUS = G.CDVAL AND G.CDNAME = 'AUTH_STATUS' AND G.CDTYPE = 'TR_REQ_PAYMENT'
		LEFT JOIN CM_ALLCODE H ON A.AUTH_STATUS_KT = H.CDVAL AND H.CDNAME = 'AUTH_STATUS_KT' AND H.CDTYPE = 'TR_REQ_PAYMENT'
        WHERE 1=1 
		AND	(	A.AUTH_STATUS = 'A' OR A.AUTH_STATUS = 'N')
		AND A.PROCESS IN ('2', '11', '17')
		-- BEGIN FILTER
		AND	(	A.REQ_PAY_AUTO_TYPE = @p_REQ_PAY_AUTO_TYPE OR ISNULL(@p_REQ_PAY_AUTO_TYPE, '') = '')
		AND	(	A.PROCESS = @p_PROCESS OR ISNULL(@p_PROCESS, '') = '')
		AND	(	A.CONTRACT_ID = @p_CONTRACT_ID OR ISNULL(@p_CONTRACT_ID, '') = '')
		AND	(	A.REQ_PAY_AUTO_SERVICE_TYPE = @p_REQ_PAY_AUTO_SERVICE_TYPE OR ISNULL(@p_REQ_PAY_AUTO_SERVICE_TYPE, '') = '')
		AND	(	A.REQ_PAY_AUTO_CODE = @p_REQ_PAY_AUTO_CODE OR ISNULL(@p_REQ_PAY_AUTO_CODE, '') = '')
		AND	(	(	@p_IS_TRANSFER = 'Y' 
					AND	(	EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_AUTO_ID) 
						)
				)
				OR	(	@p_IS_TRANSFER='N' 
						AND	NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_AUTO_ID )
					)
				OR @p_IS_TRANSFER IS NULL 
				OR @p_IS_TRANSFER=''
			)
		AND	(CONVERT(DATE, A.CREATE_DT) >= CONVERT(DATE, @p_FRMDATE, 103) OR @p_FRMDATE IS NULL OR @p_FRMDATE = '' OR A.CREATE_DT IS NULL)
		AND	(CONVERT(DATE, A.CREATE_DT) <= CONVERT(DATE, @p_TODATE, 103) OR @p_TODATE IS NULL OR @p_TODATE = '' OR A.CREATE_DT IS NULL)
		--AND	(	A.BRANCH_ID = @p_BRANCH_ID OR ISNULL(@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	(	B.TLNAME = @p_MAKER_ID_KT OR ISNULL(@p_MAKER_ID_KT, '') = '')
		-- END FILTER
		ORDER BY A.CREATE_DT DESC
-- PAGING END
   END;
END -- PAGING