ALTER   PROCEDURE [dbo].[TR_RATE_SUPPLIER_MASTER_Ins]
	@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
	)
	DECLARE @p_RATE_REQ_NO VARCHAR(100)
	-----------------TẠO SỐ PHIẾU----------------
	EXEC TR_RATE_SUPPLIER_CODE_GenKey 
		'TR_RATE_SUPPLIER_MASTER', '', '', @p_BRANCH_ID, @p_DEP_ID, @p_RATE_REQ_NO OUT
	PRINT @p_RATE_REQ_NO
	IF(EXISTS(SELECT * FROM TR_RATE_SUPPLIER_MASTER WHERE RATE_REQ_NO = @p_RATE_REQ_NO))
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, ''  RATE_ID, '' RATE_REQ_NO,
			N'Phiếu đánh giá NCC số: ' +@p_RATE_REQ_NO+ N' đã tồn tại'  ErrorDesc
		RETURN '-1'
	END

	-------INSERT MASTER--------
	DECLARE @p_RATE_ID VARCHAR(15)
	EXEC SYS_CodeMasters_Gen 'TR_RATE_SUPPLIER_MASTER' ,@p_RATE_ID OUT
	IF @p_RATE_ID IS NULL OR @p_RATE_ID = '' GOTO ABORT
	PRINT 'PASS TAO ID'

	IF(@p_BRANCH_TYPE <> 'HS')
	BEGIN
		SET @p_DEP_ID = NULL
	END

	INSERT INTO TR_RATE_SUPPLIER_MASTER
		(RATE_ID, RATE_REQ_NO, SUP_ID, RATE_FROM_DT, RATE_TO_DT, HH_ID, RECORD_STATUS, CREATE_DT, AUTH_STATUS,
		MAKER_ID, CHECKER_ID, APPROVE_DT, PROCESS_STATUS, BRANCH_ID, TOTAL_POINT, COMMENT, PROPOSE, 
		DECISION, NOTES, IS_SEND_APPR, SEND_APPR_DT, SIGN_USER, SIGN_DT, DEP_ID, CONTRACT_ID, ATTENTION_NOTES,
		BASE_POINT)
	VALUES(@p_RATE_ID, @p_RATE_REQ_NO, @p_SUP_ID, CONVERT(DATETIME, @p_RATE_FROM_DT, 103), CONVERT(DATETIME, @p_RATE_TO_DT, 103), @p_HH_ID, @p_RECORD_STATUS,
			CONVERT(DATETIME , @p_CREATE_DT, 103), @p_AUTH_STATUS, @p_MAKER_ID, @p_CHECKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103),
			@p_PROCESS_STATUS, @p_BRANCH_ID, @p_TOTAL_POINT, @p_COMMENT, @p_PROPOSE, @p_DECISION, @p_NOTES,
			@p_IS_SEND_APPR, CONVERT(DATETIME, @p_SEND_APPR_DT, 103), @p_SIGN_USER, CONVERT(DATETIME, @p_SIGN_DT, 103),
			@p_DEP_ID, @p_CONTRACT_ID, @p_ATTENTION_NOTES, @p_BASE_POINT)
	IF @@ERROR <> 0 GOTO ABORT
	PRINT 'PASS INSERT MASTER'

	----------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 'INSERT COMPLETE'

	--------------INSERT lưu bước xử lý----------
	INSERT INTO dbo.PL_REQUEST_PROCESS
		(
		    REQ_ID,
		    PROCESS_ID,
		    STATUS,
		    ROLE_USER,
		    BRANCH_ID,
		    CHECKER_ID,
		    APPROVE_DT,
		    PARENT_PROCESS_ID,
		    IS_LEAF,
		    COST_ID,
		    DVDM_ID,
		    NOTES,
		    IS_HAS_CHILD
		)
		VALUES
		(   @p_RATE_ID,        -- REQ_ID - varchar(15)
		    'NEW',        -- PROCESS_ID - varchar(10)
		    'C',        -- STATUS - varchar(5)
		    '',        -- ROLE_USER - varchar(50)
		    '',        -- BRANCH_ID - varchar(15)
		    '',        -- CHECKER_ID - varchar(15)
		    NULL,      -- APPROVE_DT - datetime
		    '',        -- PARENT_PROCESS_ID - varchar(10)
		    'N',        -- IS_LEAF - varchar(1)
		    '',        -- COST_ID - varchar(15)
		    '',        -- DVDM_ID - varchar(15)
		    N'Chờ gửi phê duyệt',       -- NOTES - nvarchar(500)
		    NULL       -- IS_HAS_CHILD - bit
		)

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