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

--Validation is here
	IF ((SELECT AUTH_STATUS FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) ='R')
	BEGIN
		SELECT '-1' Result,'' REQ_PAY_ID,N'Phiếu đề nghị thanh toán đang được trả về đơn vị. Vui lòng chờ đơn vị cập nhật thông tin và duyệt lại!' ErrorDesc
		RETURN '-1'
	END
	IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'P'))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được gửi phê  duyệt thành công trước đó! Vui lòng đợi KSV xử lý phiếu' ErrorDesc
		RETURN '-1'
	END
	IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'A'))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đã được phê duyệt thành công trước đó!' ErrorDesc
		RETURN '-1'
	END
	IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT = 'S'))
	BEGIN
		SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán đang được được đề xuất từ chối! Vui lòng đợi KSV xử lý phiếu' ErrorDesc
		RETURN '-1'
	END
--End Validation

BEGIN TRANSACTION	
	DECLARE @TYPE_TRANSFER VARCHAR(15), @ACC_NO VARCHAR(25), @ISSUED_DT VARCHAR(20), @ISSUED_BY NVARCHAR(250), @ACC_NAME NVARCHAR(250),@CURRENCY VARCHAR(15)= NULL,@RATE DECIMAL(18,0)
	DECLARE @INDEX INT =0

	DECLARE @hDocMeThod INT;
	EXEC sp_xml_preparedocument @hDocMeThod OUTPUT, @p_XMP_TEMP_METHOD;

------------------------------------------------------------------------- BEGIN LUOI PHUONG THUC THANH TOAN -----------------------------------------------------------------------
		IF(@p_XMP_TEMP_METHOD IS NOT NULL)
		BEGIN
			--INSERT FROM MethodCursor
			DELETE FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID = @p_REQ_PAY_ID
			--MethodCursor
			DECLARE @RECEIVE_ID varchar(15),@RECEIVE_NAME NVARCHAR(100),@REQ_PAY_REASON nvarchar(MAX),@TOTAL_AMT_METHOD DECIMAL(18,2),
			@REQ_PAY_TYPE varchar(1),@REQ_PAY_DESC NVARCHAR(MAX),@REQ_PAY_ENTRIES nvarchar(MAX),@CHECK_IN VARCHAR(15), 
			@BANKCODE_NAPAS VARCHAR(20), @BANKCODE VARCHAR(20), @BANKNAME VARCHAR(250), @SUB_ISSUED_BY NVARCHAR(250), @RECEIVER_DEBIT VARCHAR(20)
			
			DECLARE XmlDataMethod CURSOR FOR SELECT * FROM OPENXML(@hdocMethod, 'Root/XmlDataMethod',2)

			WITH(RECEIVE_ID VARCHAR(15),RECEIVE_NAME NVARCHAR(100),REQ_PAY_REASON NVARCHAR(MAX),TOTAL_AMT DECIMAL(18,0),
			REQ_PAY_TYPE VARCHAR(1),REQ_PAY_DESC NVARCHAR(MAX),REQ_PAY_ENTRIES NVARCHAR(MAX),ACC_NO VARCHAR(250), 
			ACC_NAME NVARCHAR(250),ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),CHECK_IN VARCHAR(15),TYPE_TRANSFER VARCHAR(15), 
			BANKCODE_NAPAS VARCHAR(20), BANKCODE VARCHAR(20), BANKNAME NVARCHAR(250), SUB_ISSUED_BY NVARCHAR(250), RECEIVER_DEBIT NVARCHAR(20))
			
			OPEN XmlDataMethod
			SET @INDEX = 0

			FETCH NEXT FROM XmlDataMethod INTO  @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,
			@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO, @ACC_NAME,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME, @SUB_ISSUED_BY, @RECEIVER_DEBIT
			WHILE @@fetch_status=0 
			BEGIN
			
				SET @INDEX = @INDEX +1
			----------------------- begin validate ------------------------
				IF(@ACC_NO IS NULL OR @ACC_NO = '')
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Thông tin phương thức thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': số tài khoản '+ N' không được để trống' ErrorDesc
					RETURN '-1'
				END

				IF(@REQ_PAY_TYPE = '1' AND (@ISSUED_DT = '' OR @ISSUED_DT IS NULL))
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Thông tin phương thức thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': Ngày cấp CMND '+ N' không được để trống' ErrorDesc
					RETURN '-1'
				END

				IF(@ISSUED_BY = '' OR @ISSUED_BY IS NULL)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Thông tin phương thức thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': Tên ngân hàng/nơi cấp CMND '+ N' không được để trống' ErrorDesc
					RETURN '-1'
				END

				IF(@ACC_NAME = '' OR @ACC_NAME IS NULL)
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' as Result, '' REQ_PAY_ID,''  AS REQ_PAY_CODE, N'Thông tin phương thức thanh toán, dòng ' + CONVERT(VARCHAR(5),@INDEX)+N': Tên tài khoản/người nhận '+ N' không được để trống' ErrorDesc
					RETURN '-1'
				END
			----------------------- end validate --------------------------

				IF(@REQ_PAY_TYPE<>'1')
				BEGIN
					SET @ISSUED_DT = NULL
				END
				IF(@TYPE_TRANSFER IS NULL OR @TYPE_TRANSFER ='')
				BEGIN
					SET @TYPE_TRANSFER = 'A'
				END

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

				INSERT INTO TR_REQ_PAY_METHOD(METHOD_ID, REQ_PAY_ID, EMP_ID, REQ_PAY_REASON, TOTAL_AMT, REQ_PAY_TYPE, REQ_PAY_DESC, REQ_PAY_ENTRIES, TEMP, MAKER_ID, CREATE_DT, ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT, CURRENCY, RATE, CHECK_IN, TYPE_TRANSFER, BANKCODE_NAPAS, BANKCODE, BANKNAME, SUB_ISSUED_BY, RECEIVER_DEBIT)
				VALUES (@p_REQ_PAY_METHOD_ID,@p_REQ_PAY_ID,@RECEIVE_ID,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,'',@p_MAKER_ID,GETDATE(),@ACC_NO, @ACC_NAME,@ISSUED_BY,CONVERT(DATE,@ISSUED_DT,103),@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME, @SUB_ISSUED_BY, @RECEIVER_DEBIT)
				
				IF @@error<>0 GOTO ABORT;
				FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,
				@ACC_NO, @ACC_NAME,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANSFER, @BANKCODE_NAPAS, @BANKCODE, @BANKNAME, @SUB_ISSUED_BY, @RECEIVER_DEBIT
			END
			CLOSE XmlDataMethod;
			DEALLOCATE XmlDataMethod;
		END--END LUOI PHUONG THUC THANH TOAN

	COMMIT TRANSACTION
SELECT '0' as Result, @p_REQ_PAY_ID  REQ_PAY_ID, N'Chỉnh sửa phương thức thanh toán thành công' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
		RETURN '-1'
End