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_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