ALTER   PROCEDURE [dbo].[TR_RATE_SUPPLIER_MASTER_Upd]
	@p_RATE_ID VARCHAR(15) = NULL,
	@p_RATE_REQ_NO VARCHAR(50) = NULL,
	@p_SUP_ID VARCHAR(15) = NULL,
	@p_RATE_FROM_DT VARCHAR(20) = NULL,
	@p_RATE_TO_DT VARCHAR(20) = NULL,
	@p_HH_ID VARCHAR(15) = NULL,
	@p_RECORD_STATUS VARCHAR(1) = NULL,
	@p_CREATE_DT VARCHAR(20) = NULL,
	@p_AUTH_STATUS VARCHAR(1) = NULL,
	@p_MAKER_ID VARCHAR(15) = NULL,
	@p_CHECKER_ID VARCHAR(15) = NULL,
	@p_APPROVE_DT VARCHAR(20) = NULL,
	@p_PROCESS_STATUS VARCHAR(15) = NULL,
	@p_BRANCH_ID VARCHAR(15) = NULL,
	@p_DEP_ID VARCHAR(15) = NULL,
	@p_TOTAL_POINT INT = 0,
	@p_COMMENT nvarchar(MAX) = NULL,
	@p_PROPOSE nvarchar(MAX) = NULL,
	@p_DECISION varchar(15) = NULL,
	@p_NOTES nvarchar(MAX) = NULL,
	@p_IS_SEND_APPR VARCHAR(1) = NULL,
	@p_SEND_APPR_DT VARCHAR(20) = NULL,
	@p_SIGN_USER VARCHAR(15) = NULL,
	@p_SIGN_DT VARCHAR(20) = NULL,
	@p_CONTRACT_ID VARCHAR(15) = NULL,
	@p_ATTENTION_NOTES NVARCHAR(1000) = NULL,
	@p_BASE_POINT INT = NULL,
	@p_TR_RATE_SUP_DT_XML XML = NULL

AS

IF(CONVERT(DATETIME, @p_RATE_TO_DT, 103) < CONVERT(DATETIME, @p_RATE_FROM_DT, 103))
BEGIN
	SELECT '-1' as Result, ''  RATE_ID,
		N'Thời gian đánh giá đến không được nhỏ hơn thời gian đánh giá từ' ErrorDesc
	RETURN '-1'
END
IF(ISNULL(@p_CONTRACT_ID,'')='')
BEGIN
	SELECT '-1' as Result, ''  RATE_ID,
		N'Dữ liệu nhập không hợp lệ. Hợp đồng tham chiếu không được để trống' ErrorDesc
	RETURN '-1'
END
BEGIN TRANSACTION
	
	DECLARE @p_BRANCH_TYPE VARCHAR(50) = (
		SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID
	)

	IF(EXISTS(SELECT * FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_REQ_NO = @p_RATE_REQ_NO AND RATE_ID <> @p_RATE_ID))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, ''  RATE_ID, 
			N'Mã phiếu đánh giá NCC đã tồn tại' ErrorDesc
		RETURN '-1'
	END

	------nếu BRANCH_ID <> 'HS', ko cần lấy DEP_ID
	IF(@p_BRANCH_TYPE <> 'HS')
	BEGIN
		SET @p_DEP_ID = ''
	END

	--------UPDATE MASTER-------
	UPDATE TR_RATE_SUPPLIER_MASTER SET
		RATE_REQ_NO = @p_RATE_REQ_NO,
		SUP_ID = @p_SUP_ID,
		RATE_FROM_DT = CONVERT(DATETIME,@p_RATE_FROM_DT,103),
		RATE_TO_DT = CONVERT(DATETIME,@p_RATE_TO_DT,103),
		HH_ID = @p_HH_ID,
		RECORD_STATUS = @p_RECORD_STATUS,
		CREATE_DT = CONVERT(DATETIME, @p_CREATE_DT, 103),
		AUTH_STATUS = @p_AUTH_STATUS,
		MAKER_ID = @p_MAKER_ID,
		CHECKER_ID = @p_CHECKER_ID,
		APPROVE_DT = CONVERT(DATETIME, @p_APPROVE_DT, 103),
		PROCESS_STATUS = @p_PROCESS_STATUS,
		BRANCH_ID = @p_BRANCH_ID,
		TOTAL_POINT = @p_TOTAL_POINT,
		COMMENT = @p_COMMENT,
		PROPOSE = @p_PROPOSE,
		DECISION = @p_DECISION,
		NOTES = @p_NOTES,
		IS_SEND_APPR = @p_IS_SEND_APPR,
		SEND_APPR_DT = CONVERT(DATETIME, @p_SEND_APPR_DT, 103),
		SIGN_USER = @p_SIGN_USER,
		SIGN_DT = CONVERT(DATETIME, @p_SIGN_DT, 103),
		DEP_ID = @p_DEP_ID,
		CONTRACT_ID = @p_CONTRACT_ID,
		ATTENTION_NOTES = @p_ATTENTION_NOTES,
		BASE_POINT = @p_BASE_POINT


	WHERE RATE_ID = @p_RATE_ID
	IF @@ERROR <> 0 GOTO ABORT
	PRINT 'PASS UPDATE MASTER'

	IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_RATE_ID AND STATUS = 'R' AND PROCESS_ID = 'APPNEW'))
	BEGIN
		UPDATE PL_REQUEST_PROCESS SET
			STATUS = 'U'
		WHERE REQ_ID = @p_RATE_ID AND PROCESS_ID = 'APPNEW' AND STATUS = 'R'
	END

	DELETE TR_RATE_SUPPLIER_DT WHERE RATE_ID = @p_RATE_ID
	IF @@ERROR <> 0 GOTO ABORT

	----------GET XML----------
	DECLARE @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_TR_RATE_SUP_DT_XML
	DECLARE RateSupDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hdoc, 'Root/RateSupDetail', 2)
	WITH
	(
		RATE_CRITERIA NVARCHAR(500),
		RATE_POINT INT,
		SUP_ID_1 VARCHAR(15),
		TR_CONTRACT_ID_1 VARCHAR(15),
		SUP_POINT_1 INT,
		SUP_ID_2 VARCHAR(15),
		TR_CONTRACT_ID_2 VARCHAR(15),
		SUP_POINT_2 INT,
		SUP_ID_3 VARCHAR(15),
		TR_CONTRACT_ID_3 VARCHAR(15),
		SUP_POINT_3 INT,
		SUP_ID_4 VARCHAR(15),
		TR_CONTRACT_ID_4 VARCHAR(15),
		SUP_POINT_4 INT,
		NOTES NVARCHAR(MAX)
	)
	OPEN RateSupDetail
	PRINT 'PASS OPEN CURSOR'

	DECLARE @p_RATE_CRITERIA NVARCHAR(500),
			@p_RATE_POINT INT,
			@p_SUP_ID_1 VARCHAR(15),
			@p_TR_CONTRACT_ID_1 VARCHAR(15),
			@p_SUP_POINT_1 INT,
			@p_SUP_ID_2 VARCHAR(15),
			@p_TR_CONTRACT_ID_2 VARCHAR(15),
			@p_SUP_POINT_2 INT,
			@p_SUP_ID_3 VARCHAR(15),
			@p_TR_CONTRACT_ID_3 VARCHAR(15),
			@p_SUP_POINT_3 INT,
			@p_SUP_ID_4 VARCHAR(15),
			@p_TR_CONTRACT_ID_4 VARCHAR(15),
			@p_SUP_POINT_4 INT,
			@p_NOTES_DT NVARCHAR(MAX)

	------INSERT DT---------
	DECLARE @p_RATE_DT_ID VARCHAR(15)
	FETCH NEXT FROM RateSupDetail INTO
		@p_RATE_CRITERIA,
		@p_RATE_POINT,
		@p_SUP_ID_1,
		@p_TR_CONTRACT_ID_1,
		@p_SUP_POINT_1,
		@p_SUP_ID_2,
		@p_TR_CONTRACT_ID_2,
		@p_SUP_POINT_2,
		@p_SUP_ID_3,
		@p_TR_CONTRACT_ID_3,
		@p_SUP_POINT_3,
		@p_SUP_ID_4,
		@p_TR_CONTRACT_ID_4,
		@p_SUP_POINT_4,
		@p_NOTES_DT
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC SYS_CodeMasters_Gen 'TR_RATE_SUPPLIER_DT' ,@p_RATE_DT_ID OUT
		IF @p_RATE_DT_ID IS NULL OR @p_RATE_DT_ID = '' GOTO ABORT

		INSERT INTO TR_RATE_SUPPLIER_DT
			(RATE_DT_ID, RATE_ID, RATE_CRITERIA, RATE_POINT, SUP_ID_1, TR_CONTRACT_ID_1, SUP_POINT_1,
			SUP_ID_2, TR_CONTRACT_ID_2, SUP_POINT_2, SUP_ID_3,TR_CONTRACT_ID_3, SUP_POINT_3, 
			SUP_ID_4, TR_CONTRACT_ID_4, SUP_POINT_4, NOTES)
		VALUES(@p_RATE_DT_ID, @p_RATE_ID, @p_RATE_CRITERIA, @p_RATE_POINT, @p_SUP_ID_1, @p_TR_CONTRACT_ID_1, 
				@p_SUP_POINT_1, @p_SUP_ID_2, @p_TR_CONTRACT_ID_2, @p_SUP_POINT_2, @p_SUP_ID_3, @p_TR_CONTRACT_ID_3, 
				@p_SUP_POINT_3, @p_SUP_ID_4, @p_TR_CONTRACT_ID_4, @p_SUP_POINT_4, @p_NOTES_DT)
		IF @@ERROR <> 0 GOTO ABORT
		PRINT 'PASS INSERT DT'

		----FETCH NEXT----
		FETCH NEXT FROM RateSupDetail INTO
			@p_RATE_CRITERIA,
			@p_RATE_POINT,
			@p_SUP_ID_1,
			@p_TR_CONTRACT_ID_1,
			@p_SUP_POINT_1,
			@p_SUP_ID_2,
			@p_TR_CONTRACT_ID_2,
			@p_SUP_POINT_2,
			@p_SUP_ID_3,
			@p_TR_CONTRACT_ID_3,
			@p_SUP_POINT_3,
			@p_SUP_ID_4,
			@p_TR_CONTRACT_ID_4,
			@p_SUP_POINT_4,
			@p_NOTES_DT
	END
	
	CLOSE RateSupDetail
	DEALLOCATE RateSupDetail
	PRINT 'PASS INSERT DT'

COMMIT TRANSACTION
SELECT '0' as Result, @p_RATE_ID  RATE_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
	CLOSE RateSupDetail
	DEALLOCATE RateSupDetail
	SELECT '-1' as Result, ''  RATE_ID, '' ErrorDesc
	RETURN '-1'
END