ALTER   PROCEDURE [dbo].[TR_CONTRACT_Upd]
@p_CONTRACT_ID VARCHAR(15),
@p_CONTRACT_CODE	varchar(150)  = NULL,
@p_CONTRACT_NAME	nvarchar(200)  = NULL,
@p_CONTRACT_TYPE varchar(1) = NULL,
@p_BID_ID	varchar(15)  = NULL,
@p_SUP_ID	varchar(15)  = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_DELIVERY_DT	VARCHAR(20) = NULL,
@p_FORM1	varchar(4)  = NULL,
@p_VOUCHER_ID1	varchar(20)  = NULL,
@p_BANK1	varchar(4)  = NULL,
@p_AMOUNT1	decimal(18)  = NULL,
@p_EXP_DT1	VARCHAR(20) = NULL,
@p_RATE1	decimal(18,2)  = NULL,
@p_FORM2	varchar(4)  = NULL,
@p_VOUCHER_ID2	varchar(20)  = NULL,
@p_BANK2	varchar(4)  = NULL,
@p_AMOUNT2	decimal(18)  = NULL,
@p_EXP_DT2	VARCHAR(20) = NULL,
@p_RATE2	decimal(18,2)  = NULL,
@p_REQ_DOC_ID	nvarchar(50)  = NULL,
@p_REQ_DOC_CONTENT	nvarchar(1000)  = NULL,
@p_APPROVE_VALUE	decimal(18)  = NULL,
@p_SIGN_DT	VARCHAR(20) = NULL,
@p_CONSTRUCT_PROGRESS	decimal(18,2)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@P_START_DT NVARCHAR(20) = NULL,
@P_END_DT NVARCHAR(20) = NULL,
@P_LISTASSET XML = NULL,
@P_LISTPAYMENT XML = NULL,
@P_CON_TRACK XML = NULL,
@p_ListTrREQFile XML,
@p_IS_CLOSED VARCHAR(1) = NULL,
@p_CONT_TYPE VARCHAR(10) = NULL,
@p_CUST_ID VARCHAR(15) = NULL,
@p_BRANCH_ID VARCHAR(15) = NULL,
@p_EXP_CONTRACT INT = NULL, 
@p_DEPOSIT_AMT DECIMAL(18,0) = NULL,
@p_TYPE_PERIOD VARCHAR(15) = NULL,
@p_DEPOSIT_NOTE NVARCHAR(1000) = NULL,
-----------------------BAODNQ 25/2/2022 : Thêm cột vào bảng TR_CONTRACT-------------
@p_IS_SEND_APPR VARCHAR(1) = NULL,
@p_SEND_APPR_DT VARCHAR(20) = NULL,
@p_ADDR NVARCHAR(1000) = NULL,
@p_SUP_CODE VARCHAR(20) = NULL
AS

IF(EXISTS(SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID AND AUTH_STATUS = 'U'))
BEGIN
	SELECT '-1' as Result, @P_CONTRACT_ID  CONTRACT_ID, 
		N'Không thể chỉnh sửa. Hợp đồng ' + @p_CONTRACT_CODE + 
			N' đã được gửi yêu cầu phê duyệt' ErrorDesc
	RETURN '-1'
END

	DECLARE @sErrorCode VARCHAR(20)
	DECLARE @TEMP TABLE
			(
				[KEY] varchar(15),
				[REF_ID] varchar(15),
				[TYPE] varchar(50)
			)
	--IF (@p_CONTRACT_CODE IS NOT NULL AND @p_CONTRACT_CODE <> '')
	--BEGIN
	--	IF EXISTS(SELECT * FROM TR_CONTRACT A WHERE A.CONTRACT_CODE = @p_CONTRACT_CODE AND A.CONTRACT_ID != @p_CONTRACT_ID )
	--	BEGIN
	--		SET @sErrorCode = 'TR-00001'
	--	END
	--END
	
	----------------BAODNQ 8/11/2022 : BỎ RÀNG BUỘC TRÙNG SỐ HỢP ĐỒNG------------
	--IF(@p_CONTRACT_CODE IS NOT NULL AND @p_CONTRACT_CODE <> '')
	--BEGIN
	--	IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_CODE = @p_CONTRACT_CODE AND CONTRACT_ID <> @p_CONTRACT_ID))
	--	BEGIN
	--		DECLARE @p_EXISTS_CONTRACT_ID VARCHAR(15) = (
	--			SELECT TOP 1 CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_CODE = @p_CONTRACT_CODE AND CONTRACT_ID <> @p_CONTRACT_ID
	--		)
	--		SELECT '-1' AS Result, '' CONTRACT_ID,
	--			N'Số hợp đồng: ' + @p_CONTRACT_CODE + N' đã tồn tại trên hệ thống ứng với ID hợp đồng: ' 
	--			+ @p_EXISTS_CONTRACT_ID + N'. Vui lòng nhập số hợp đồng khác' ErrorDesc
	--		RETURN '-1'
	--	END
	--END

	IF 	@sErrorCode <> ''
	BEGIN
		SELECT '-1' as Result, ''  CONTRACT_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode
		RETURN '-1'
	END
	
	DECLARE @REF_CODE VARCHAR(15) = NULL, @PDN_TT VARCHAR(15) = NULL
	SET @REF_CODE =
	(	SELECT TOP 1 B.REQ_PAY_CODE
		FROM TR_REQ_ADVANCE_DT A
		INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID
		WHERE A.REF_ID =@p_CONTRACT_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)
	)
	--- TIM KIEM XEM PO NAY CO DUOC PHEP THANH TOAN HAY KHONG
	SET @PDN_TT =
	(	SELECT TOP 1 B.REQ_PAY_CODE
		FROM TR_REQ_ADVANCE_DT A
		INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID
		WHERE A.REF_ID =@p_CONTRACT_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)
	)
	IF(@p_IS_CLOSED ='Y' AND @REF_CODE IS NOT NULL)
	BEGIN
			--ROLLBACK TRANSACTION
			SELECT '-1' Result, @p_CONTRACT_ID AS PO_ID, N'Hợp đồng đang được thực hiện tạm ứng với số phiếu: '+@REF_CODE AS ErrorDesc 
			RETURN '-1'
	END
	IF(@p_IS_CLOSED ='Y' AND @PDN_TT IS NOT NULL)
	BEGIN
		--ROLLBACK TRANSACTION
			SELECT '-1' Result, @p_CONTRACT_ID AS PO_ID, N'Hợp đồng đang được thực hiện thanh toán với số phiếu: '+@PDN_TT AS ErrorDesc 
			RETURN '-1'
	END

	----------------BAODNQ 14/11/2022 : NẾU LOẠI HỢP ĐỒNG = DK, KO BẮT BUỘC NHẬP LƯỚI DANH SÁCH HÀNG HÓA------------------
	---------------NGƯỢC LẠI NẾU LÀ (MS, NT), BẮT BUỘC NHẬP LƯỚI DANH SÁCH HÀNG HÓA----------------
	DECLARE @p_TR_CONTRACT_DT_XML_COUNT INT
	SET @p_TR_CONTRACT_DT_XML_COUNT = (
		SELECT @p_LISTASSET.value('count(/Root/AssetDetail/GOODS_ID)', 'INT') AS 'COUNT'
	)
	PRINT @p_TR_CONTRACT_DT_XML_COUNT
	IF(@p_CONT_TYPE <> 'DK' AND @p_TR_CONTRACT_DT_XML_COUNT <= 0)
	BEGIN
		SELECT '-1' Result, '' AS CONTRACT_ID, N'Đối với hợp đồng không phải là hợp đồng định kỳ, lưới danh sách hàng hóa bắt buộc nhập' AS ErrorDesc 
		RETURN '-1'
	END
	-----------------ENDBAODNQ-------------------

	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET
	DECLARE AssetDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/AssetDetail',2)
	WITH 
	(
		CD_ID VARCHAR(15),
		GOODS_ID	varchar(15),
		UNIT_ID	varchar(15),
		QUANTITY	decimal(18),
		PRICE	decimal(18),
		NOTES	nvarchar(1000),
		GD_NAME_REAL NVARCHAR(500),
		RECURRING VARCHAR(5),
		UNIT_NAME_REAL NVARCHAR(100),
		-------------------BAODNQ 23/2/2022: Thêm cột vào bảng TR_CONTRACT_DT-------------
		RENT_PRICE DECIMAL(18,0),
		BUILDING_ID VARCHAR(15)
	)
	OPEN AssetDetail

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTPAYMENT
	DECLARE PaymentDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)
	WITH 
	(
		PAY_ID	VARCHAR(15),
		PAY_PHASE	NVARCHAR(1000),
		EXPECTED_DT	VARCHAR(20),
		[PERCENT]	decimal(18,2),
		[AMOUNT]	decimal(18),
		PAY_STATUS VARCHAR(1),
		PAY_AMOUNT DECIMAL(18),
		PAY_DT VARCHAR(20),
		NOTES	nvarchar(1000)
	)
	OPEN PaymentDetail

	Exec sp_xml_preparedocument @hdoc Output,@P_CON_TRACK
	DECLARE ConTrack CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ConTrack',2)
	WITH 
	(
		CONS_TRACK_ID	VARCHAR(15),
		FIX_DESCRIPTION	varchar(200),
		EXPECTED_DT	VARCHAR(20),
		FINISH_DT	VARCHAR(20),
		[STATUS]	VARCHAR(1),
		NOTES	nvarchar(1000)
	)
	OPEN ConTrack

	PRINT 'PASS KHOI TAO'
BEGIN TRANSACTION

		---------------BAODNQ 12/12/2022 : HOT_FIX TẠM THỜI-------------------
		--------------NẾU KO TRUYỀN SUP_ID THÌ TỪ SUP_CODE TÌM KIẾM RA SUP_ID---------
		IF((@p_SUP_ID IS NULL OR @p_SUP_ID = '') AND @p_SUP_CODE IS NOT NULL AND @p_SUP_CODE <> '')
		BEGIN
			SET @p_SUP_ID = (SELECT TOP 1 SUP_ID FROM CM_SUPPLIER WHERE SUP_CODE = @p_SUP_CODE)
		END
		--------------ENDBAODNQ 12/12/2022----------------------------

		IF(@p_CONTRACT_NAME ='' OR @p_CONTRACT_NAME IS  NULL)
		BEGIN
			ROLLBACK TRANSACTION
				SELECT '-1' Result, @p_CONTRACT_ID AS PO_ID, N'Tên hợp đồng không được để trống' AS ErrorDesc 
				RETURN '-1'
		END
		--insert master
		IF @p_DELIVERY_DT = ''  
			SET @p_DELIVERY_DT = NULL 
		IF @p_EXP_DT1 = ''
			SET @p_EXP_DT1 = NULL
		IF @p_EXP_DT2 = ''
			SET @p_EXP_DT2 = NULL
		IF @p_SIGN_DT = ''
			SET @p_SIGN_DT = NULL
		IF @p_CREATE_DT = ''
			SET @p_CREATE_DT = NULL
		IF @p_APPROVE_DT = ''
			SET @p_APPROVE_DT = NULL
		IF @P_START_DT = ''
			SET @P_START_DT = NULL
		IF @P_END_DT = ''
			SET @P_END_DT = NULL
		DECLARE @EXP_DT_OLD DATEtime, @TOTAL_AMT_OLD DECIMAL(18,0), @FROM_EXP_DATE_OLD DATEtime
		SET @FROM_EXP_DATE_OLD = (SELECT START_DT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID)
		SET @EXP_DT_OLD = (SELECT END_DT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID)
		SET @TOTAL_AMT_OLD = (SELECT TOTAL_AMT FROM TR_CONTRACT WHERE CONTRACT_ID =@p_CONTRACT_ID)
		
		IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID AND AUTH_STATUS = 'A'))
		BEGIN
			SET @p_CHECKER_ID = NULL
			SET @p_APPROVE_DT = NULL
			SET @p_SEND_APPR_DT = NULL
		END
		
		UPDATE TR_CONTRACT SET [START_DT] = (CASE WHEN @P_START_DT IS NOT NULL AND @P_START_DT <> '' then CONVERT(DATETIME,@P_START_DT,103) ELSE NULL END), 
		[END_DT] = (CASE WHEN @P_END_DT IS NOT NULL AND @P_END_DT <> '' THEN CONVERT(DATETIME,@P_END_DT,103) ELSE NULL END), [CONTRACT_CODE] = @p_CONTRACT_CODE,
		[CONTRACT_NAME] = @p_CONTRACT_NAME, [CONTRACT_TYPE] = @p_CONTRACT_TYPE ,
		[BID_ID] = @p_BID_ID,[SUP_ID] = @p_SUP_ID,[TOTAL_AMT] = @p_TOTAL_AMT,
		[DELIVERY_DT] = (CASE WHEN @p_DELIVERY_DT IS NOT NULL AND @p_DELIVERY_DT <> '' then CONVERT(DATETIME, @p_DELIVERY_DT, 103) ELSE NULL END),
		[FORM1] = @p_FORM1,[VOUCHER_ID1] = @p_VOUCHER_ID1,[BANK1] = @p_BANK1,
		[AMOUNT1] = @p_AMOUNT1,[EXP_DT1] = (CASE WHEN @p_EXP_DT1 IS NOT NULL AND @p_EXP_DT1 <> '' then CONVERT(DATETIME, @p_EXP_DT1, 103) ELSE NULL END),
		[RATE1] = @p_RATE1,[FORM2] = @p_FORM2,[VOUCHER_ID2] = @p_VOUCHER_ID2,
		[BANK2] = @p_BANK2,[AMOUNT2] = @p_AMOUNT2,[EXP_DT2] = (CASE WHEN @p_EXP_DT2 IS NOT NULL AND @p_EXP_DT2 <> '' then CONVERT(DATETIME, @p_EXP_DT2, 103) ELSE NULL END),
		[RATE2] = @p_RATE2,[REQ_DOC_ID] = @p_REQ_DOC_ID,[REQ_DOC_CONTENT] = @p_REQ_DOC_CONTENT,
		[APPROVE_VALUE] = @p_APPROVE_VALUE,
		[SIGN_DT] =  (CASE WHEN @p_SIGN_DT IS NOT NULL AND @p_SIGN_DT <> '' then CONVERT(DATETIME, @p_SIGN_DT, 103) ELSE NULL END),
		[CONSTRUCT_PROGRESS] = @p_CONSTRUCT_PROGRESS,[NOTES] = @p_NOTES,[RECORD_STATUS] = '1',
		[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = 'E',
		[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = (CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END),
		[CONT_TYPE]=@p_CONT_TYPE, CUST_ID = @p_CUST_ID,
		[IS_CLOSED] = @p_IS_CLOSED,
		BRANCH_ID=@p_BRANCH_ID,EXP_CONTRACT=@p_EXP_CONTRACT,DEPOSIT_AMT =@p_DEPOSIT_AMT,TYPE_PERIOD =@p_TYPE_PERIOD, DEPOSIT_NOTE = @p_DEPOSIT_NOTE,
		IS_SEND_APPR = @p_IS_SEND_APPR,
		SEND_APPR_DT = CONVERT(DATE, @p_SEND_APPR_DT, 103),
		[ADDRESS] = @p_ADDR
		
		WHERE  CONTRACT_ID= @p_CONTRACT_ID
		-- INSERT VAO LOG
		INSERT INTO dbo.PL_PROCESS
		(
						REQ_ID,
						PROCESS_ID,
						CHECKER_ID,
						APPROVE_DT,
						PROCESS_DESC,NOTES
		)
		VALUES
		(   @p_CONTRACT_ID,        -- REQ_ID - varchar(15)
						'UPDATE',        -- PROCESS_ID - varchar(10)
						@p_MAKER_ID,        -- CHECKER_ID - varchar(15)
						GETDATE(), -- APPROVE_DT - datetime
					   N'User: '+ @p_MAKER_ID +N' thực hiện chỉnh sửa hợp đồng. Thông tin chỉnh sửa bao gồm: '+ CHAR(10) 
					    + N'Thông tin ngày hiệu lực hợp đồng. Điều chỉnh từ ngày : ' +  ISNULL((SELECT FORMAT (@FROM_EXP_DATE_OLD, 'dd/MM/yyyy ')),N'vô thời hạn ') + N' thành ngày : '+ ISNULL(@P_START_DT,N'vô thời hạn')
					  +CHAR(10)+
					  + N'Thông tin ngày hết hiệu lực hợp đồng. Điều chỉnh từ ngày : ' +  ISNULL((SELECT FORMAT (@EXP_DT_OLD, 'dd/MM/yyyy ')),N'vô thời hạn ') + N' thành ngày : '+ ISNULL(@P_END_DT,N'vô thời hạn')
					  +CHAR(10)+
					  N'Tổng giá trị hợp đồng điều chỉnh từ: '+ ISNULL(FORMAT(@TOTAL_AMT_OLD,'#,###'),'0') + ' VND' + N' thành: '+ ISNULL(FORMAT(@p_TOTAL_AMT,'#,###'),'0') +' VND'
					  ,N'Chỉnh sửa thông tin hợp đồng'
		)
		IF @@Error <> 0 GOTO ABORT
		PRINT 'UPDATE MASTER SUCCESS'

		--insert asset detail tương đương như NỘI DUNG trong bảng contract detail
		Declare
		@CONS_TRACK_ID VARCHAR(15),
		@FIX_DESCRIPTION VARCHAR(200),
		@FINISH_DT VARCHAR(20),
		@STATUS  VARCHAR(1),
		@CD_ID VARCHAR(15),
		@PAY_ID VARCHAR(15),
		@GOODS_ID	varchar(15),
		@UNIT_ID	varchar(15),
		@QUANTITY	decimal(18),
		@PRICE	decimal(18),
		@NOTES	nvarchar(1000),
		@PAY_PHASE	NVARCHAR(1000),
		@EXPECTED_DT	VARCHAR(20),
		@PERCENT	decimal(18),
		@AMOUNT	decimal(18),
		@PAY_STATUS VARCHAR(1),
		@PAY_AMOUNT DECIMAL(18),
		@PAY_DT VARCHAR(20),
		@GD_NAME_REAL NVARCHAR(500),
		@RECURRING VARCHAR(5),
		@UNIT_NAME_REAL NVARCHAR(100),
		-------------------BAODNQ 23/2/2022: Thêm cột vào bảng TR_CONTRACT_DT-------------
		@RENT_PRICE DECIMAL(18,0),
		@BUILDING_ID VARCHAR(15)
		
		DELETE FROM TR_CONTRACT_DT WHERE CONTRACT_ID = @P_CONTRACT_ID
		FETCH NEXT FROM AssetDetail INTO @CD_ID,@GOODS_ID,@UNIT_ID,@QUANTITY,@PRICE,@NOTES,@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			--IF(LEN(@CD_ID) = 0)
			--BEGIN
			--	EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_DT', @CD_ID out
			--	IF @CD_ID='' OR @CD_ID IS NULL GOTO ABORT
			--END

			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_DT', @CD_ID out
			IF @CD_ID='' OR @CD_ID IS NULL GOTO ABORT
			--EXEC TR_CONTRACT_DT_Ins @P_CONTRACT_ID,@GOODS_ID,@UNIT_ID,@QUANTITY,@PRICE,@NOTES,@p_RECORD_STATUS,
			--@p_MAKER_ID,@p_CREATE_DT,@p_AUTH_STATUS,@p_CHECKER_ID,@p_APPROVE_DT
			--ELSE
			INSERT INTO TR_CONTRACT_DT([CD_ID],[CONTRACT_ID],[GOODS_ID],[UNIT_ID],[QUANTITY],[PRICE],[NOTES],
			[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[GD_NAME_REAL],RECURRING,UNIT_NAME_REAL, RENT_PRICE,BUILDING_ID)
			VALUES(@CD_ID ,@P_CONTRACT_ID,
			@GOODS_ID ,
			@UNIT_ID ,
			@QUANTITY ,
			@PRICE ,
			@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END),@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID)
		
			FETCH NEXT FROM AssetDetail INTO @CD_ID,@GOODS_ID,@UNIT_ID,@QUANTITY,@PRICE,@NOTES,@GD_NAME_REAL,@RECURRING,@UNIT_NAME_REAL, @RENT_PRICE, @BUILDING_ID
		END

		DELETE FROM CON_TRACK WHERE CONTRACT_ID = @P_CONTRACT_ID
		FETCH NEXT FROM ConTrack INTO @CONS_TRACK_ID,@FIX_DESCRIPTION,@EXPECTED_DT,@FINISH_DT,@STATUS,@NOTES
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			--IF(LEN(@CONS_TRACK_ID) = 0)
			--BEGIN
			--	EXEC SYS_CodeMasters_Gen 'CONS_TRACK_ID', @CONS_TRACK_ID out
			--	IF @CONS_TRACK_ID ='' OR @CONS_TRACK_ID IS NULL GOTO ABORT
			--END
			EXEC SYS_CodeMasters_Gen 'CONS_TRACK_ID', @CONS_TRACK_ID out
			IF @CONS_TRACK_ID ='' OR @CONS_TRACK_ID IS NULL GOTO ABORT
			INSERT INTO CON_TRACK([CONS_TRACK_ID],[CONTRACT_ID],[FIX_DESCRIPTION],[EXPECTED_DT],[STATUS],[FINISH_DT],[NOTES],[CREATED_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[RECORD_STATUS])
			VALUES(@CONS_TRACK_ID ,@p_CONTRACT_ID ,@FIX_DESCRIPTION ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,
			@STATUS ,CONVERT(DATETIME, @FINISH_DT, 103) ,@NOTES ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END) ,@p_RECORD_STATUS )
		
			FETCH NEXT FROM ConTrack INTO @CONS_TRACK_ID,@FIX_DESCRIPTION,@EXPECTED_DT,@FINISH_DT,@STATUS,@NOTES
		END
	--insert payment detail

	--UPDATE  TR_CONTRACT_PAYMENT SET PAY_ID = RIGHT WHERE CONTRACT_ID = @P_CONTRACT_ID
	FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@PAY_STATUS,@PAY_AMOUNT,@PAY_DT,@NOTES

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF(LEN(ISNULL(@PAY_ID,'')) = 0 OR @PAY_ID IS NULL OR @PAY_ID = '')
		BEGIN
			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @PAY_ID out
			IF @PAY_ID='' OR @PAY_ID IS NULL GOTO ABORT
		END
		--IF(EXISTS(SELECT * FROM TR_CONTRACT_PAYMENT WHERE PAY_ID =@PAY_ID))
		--BEGIN
		--	UPDATE TR_CONTRACT_PAYMENT SET PAY_ID ='X' WHERE PAY_ID =@PAY_ID
		--END
		--EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @PAY_ID out
		IF @PAY_ID='' OR @PAY_ID IS NULL GOTO ABORT
		--EXEC TR_CONTRACT_PAYMENT_Ins @p_CONTRACT_ID,@PAY_PHASE,@EXPECTED_DT,
		--@PERCENT,@AMOUNT,@NOTES,@p_RECORD_STATUS,@p_MAKER_ID,@p_CREATE_DT,@p_AUTH_STATUS,@p_CHECKER_ID,@p_APPROVE_DT
		--ELSE
		--INSERT INTO TR_CONTRACT_PAYMENT([PAY_AMOUNT],PAY_DT, [PAY_ID],[CONTRACT_ID],[PAY_PHASE],[EXPECTED_DT],[PERCENT],[AMOUNT],[PAY_STATUS],
		--[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])
		--VALUES(@PAY_AMOUNT,(CASE WHEN @PAY_DT IS NOT NULL AND @PAY_DT <> '' then CONVERT(DATETIME, @PAY_DT, 103) ELSE NULL END),@PAY_ID ,@p_CONTRACT_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,
		--@PERCENT ,@AMOUNT,@PAY_STATUS,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103),
		--@p_AUTH_STATUS ,@p_CHECKER_ID ,(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END))

		-------------BAODNQ 15/9/2022: Fix lại theo BVB_v2_PROD-----------
		-------------Nếu @PAY_ID ko tồn tại trong TR_CONTRACT_PAYMENT, INSERT mới---------
		IF(NOT EXISTS(SELECT * FROM TR_CONTRACT_PAYMENT WHERE PAY_ID = @PAY_ID))
		BEGIN
			INSERT INTO TR_CONTRACT_PAYMENT
				([PAY_AMOUNT],PAY_DT, [PAY_ID],[CONTRACT_ID],[PAY_PHASE],[EXPECTED_DT],[PERCENT],[AMOUNT],[PAY_STATUS],
				[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])
			VALUES(@PAY_AMOUNT,(CASE WHEN @PAY_DT IS NOT NULL AND @PAY_DT <> '' then CONVERT(DATETIME, @PAY_DT, 103) ELSE NULL END),@PAY_ID ,
					@p_CONTRACT_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) , @PERCENT ,@AMOUNT,@PAY_STATUS,@NOTES ,@p_RECORD_STATUS ,
					@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103), @p_AUTH_STATUS ,@p_CHECKER_ID ,
					(CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END))
		END
		ELSE
		-------------Nếu @PAY_ID đã tồn tại trong TR_CONTRACT_PAYMENT, UPDATE---------
		BEGIN
			UPDATE TR_CONTRACT_PAYMENT SET
				PAY_AMOUNT = @PAY_AMOUNT,
				PAY_DT =(CASE WHEN @PAY_DT IS NOT NULL AND @PAY_DT <> '' then CONVERT(DATETIME, @PAY_DT, 103) ELSE NULL END),
				CONTRACT_ID = @p_CONTRACT_ID,
				PAY_PHASE = @PAY_PHASE,
				EXPECTED_DT = CONVERT(DATETIME, @EXPECTED_DT, 103),
				[PERCENT] = @PERCENT,
				[AMOUNT] = @AMOUNT,
				PAY_STATUS = @PAY_STATUS,
				NOTES = @NOTES,
				RECORD_STATUS = @p_RECORD_STATUS,
				MAKER_ID = @p_MAKER_ID,
				CREATE_DT = CONVERT(DATETIME, @p_CREATE_DT, 103),
				AUTH_STATUS = @p_AUTH_STATUS,
				CHECKER_ID = @p_CHECKER_ID,
				APPROVE_DT = (CASE WHEN @p_APPROVE_DT IS NOT NULL AND @p_APPROVE_DT <> '' then CONVERT(DATETIME, @p_APPROVE_DT, 103) ELSE NULL END)
			WHERE PAY_ID = @PAY_ID
		END

	FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@PAY_STATUS,@PAY_AMOUNT,@PAY_DT,@NOTES
	END
		
	CLOSE ConTrack
	DEALLOCATE ConTrack
	CLOSE AssetDetail
	DEALLOCATE AssetDetail
	CLOSE PaymentDetail
	DEALLOCATE PaymentDetail
	IF(@p_RECORD_STATUS ='U')
	BEGIN
		UPDATE TR_CONTRACT SET AUTH_STATUS ='U' WHERE CONTRACT_ID =@p_CONTRACT_ID
	END
	--Insert into TABLE TR_REQUEST_DOC_FILE

		DECLARE @tableTrREQFile TABLE(
			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
			REQ_ID varchar(20),
			NOTES  nvarchar(200)
		)


		Declare @fdoc INT
		Exec sp_xml_preparedocument @fdoc Output,@p_ListTrREQFile

		INSERT INTO @tableTrREQFile
		SELECT *
		FROM OPENXML(@fDoc,'/Root/ListTrREQ',2)
		WITH 
		(
			TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
			REQ_ID varchar(20),
			NOTES  nvarchar(200)
		)

		--delete TR_REQUEST_DOC_FILE where TR_REQUEST_DOC_FILE_ID not in (select TR_REQUEST_DOC_FILE_ID from @tableTrREQFile) and REQ_ID = @p_CONTRACT_ID
		------------BAODNQ 11/11/2022 : DELETE TR_REQUEST_DOC_FILE----------
		DELETE FROM TR_REQUEST_DOC_FILE WHERE REQ_ID = @p_CONTRACT_ID
		DECLARE ListTrREQFile  CURSOR FOR
		SELECT * FROM @tableTrREQFile
		OPEN ListTrREQFile

		Declare 
		@TR_REQUEST_DOC_FILE_ID	varchar(20)  ,
		@IS_VIEW	bit,
		@REQ_ID varchar(20),
		@_NOTES nvarchar(200)

		FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@REQ_ID,@_NOTES
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			DECLARE @l_REQFile_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_REQUEST_DOC_FILE', @l_REQFile_ID out
			--select @l_REQFile_ID
			--select * from TR_REQUEST_DOC_FILE where [TR_REQUEST_DOC_FILE_ID]= 'TRC000000529764'
			IF @l_REQFile_ID='' OR @l_REQFile_ID IS NULL GOTO ABORT
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_CONTRACT')
			IF(select count(*) from TR_REQUEST_DOC_FILE where TR_REQUEST_DOC_FILE_ID = @TR_REQUEST_DOC_FILE_ID ) > 0
			begin
				update TR_REQUEST_DOC_FILE set IS_VIEW=isnull(@IS_VIEW,0),REQ_ID = @p_CONTRACT_ID,NOTES=@_NOTES where TR_REQUEST_DOC_FILE_ID = @TR_REQUEST_DOC_FILE_ID 
			end
			else
			begin
				INSERT INTO dbo.TR_REQUEST_DOC_FILE
				(
					 [TR_REQUEST_DOC_FILE_ID]
					,[REQ_ID]
					,[NOTES]
				)	
				VALUES
				(   
					@l_REQFile_ID
					,@p_CONTRACT_ID,
					@_NOTES
				)
			end
			
			FETCH NEXT FROM ListTrREQFile INTO @TR_REQUEST_DOC_FILE_ID,@REQ_ID,@_NOTES
		END
		CLOSE ListTrREQFile
		DEALLOCATE ListTrREQFile
		IF @@Error <> 0 GOTO ABORT

		if(select count(*) from @TEMP) = 0
		begin
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_REQFile_ID, 'TR_CONTRACT')
		end
COMMIT TRANSACTION
IF(@p_RECORD_STATUS ='U')
BEGIN
-- INSERT VAO LOG
	INSERT INTO dbo.PL_PROCESS
	(
						REQ_ID,
						PROCESS_ID,
						CHECKER_ID,
						APPROVE_DT,
						PROCESS_DESC,NOTES
					)
					VALUES
					(   @p_CONTRACT_ID,        -- REQ_ID - varchar(15)
						'SEND',        -- PROCESS_ID - varchar(10)
						@p_MAKER_ID,        -- CHECKER_ID - varchar(15)
						GETDATE(), -- APPROVE_DT - datetime
					   N'Nhân viên gửi phê duyệt phiếu thành công' ,N'Gửi phê duyệt')
	--IF(@p_CONT_TYPE ='DK')
	--BEGIN
	--	UPDATE TR_CONTRACT SET AUTH_STATUS ='A' WHERE CONTRACT_ID =@p_CONTRACT_ID
	--END
	SELECT '2' as Result, @P_CONTRACT_ID  CONTRACT_ID, N'Hợp đồng có ID: '+ @P_CONTRACT_ID +N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp có thẩm quyền phê duyệt' ErrorDesc
	RETURN '2'
	--- 
	--IF(@p_CONT_TYPE ='DK' AND CONVERT(DATE, GETDATE(),103) <='2020-08-30')
	
	
END
ELSE
BEGIN
	SELECT '0' as Result, @P_CONTRACT_ID  CONTRACT_ID, N'Hợp đồng có ID: '+ @P_CONTRACT_ID +N' đã được cập nhật dữ liệu thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc,[REF_ID], [TYPE]  from @TEMP
	RETURN '0'
END
ABORT:
BEGIN
		
		CLOSE ConTrack
		DEALLOCATE ConTrack
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT, ''  CONTRACT_ID,'' ErrorDesc
		RETURN '-1'
End


--SELECT * FROM TR_CONTRACT_DT

--SELECT * FROM TR_CONTRACT_PAYMENT

--SELECT * FROM TR_CONTRACT