ALTER   PROCEDURE [dbo].[CON_REQUEST_DOC_UpdXml]
@p_CONSTRUCT_ID varchar(15) = NULL,
@p_CONSTRUCT_CODE	varchar(15)  = NULL,
@p_CONSTRUCT_NAME	nvarchar(100)  = NULL,
@p_PLAN_ID	varchar(15)  = NULL,
@p_CONST_ID	varchar(15)  = NULL,
@p_DIVI_ID	varchar(15)  = NULL,
@p_CONSTRUCT_ADDR	nvarchar(200)  = NULL,
@p_LENGTH	decimal(18, 2)	= NULL,
@p_WIDTH	decimal(18, 2)	= NULL,
@p_CONSTRUCT_AREA	decimal(18)  = NULL,
@p_FLOORS	int	= NULL,
@p_FLOORS_AREA	decimal(18, 2)	= NULL,
@p_TOTAL_AREA_USE	decimal(18, 2)	= NULL, -- GIANT
@p_UNUSED_AREA	decimal(18, 2)	= NULL,
@p_TOTAL_COST	decimal(18, 2)	= NULL,-- GIANT
@p_UNIT_PRICE	decimal(18, 2)	= NULL,-- GIANT
@p_YEAR_EXE	varchar(4)  = NULL,
@p_CONST_TYPE	varchar(15)  = NULL,
@p_HQ_TYPE	varchar(15)  = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_COST_ESTIMATE	decimal(18)  = NULL,
@p_COST_EXE	decimal(18)  = NULL,
@p_COST_INCURRED	decimal(18)  = NULL,
@p_CONST_PURPOSE varchar(15) = NULL,
@p_DESCRIPTION	nvarchar(2000)  = NULL,
@p_START_DT	VARCHAR(20) = NULL,
@p_END_DT	VARCHAR(20) = NULL,
@p_DATE_EXE	VARCHAR(20) = NULL,
@p_COMPLETION_DT	VARCHAR(20) = NULL,
@p_CONSTRUCT_PROGRESS	decimal(18)  = NULL,
@p_RECORD_STATUS	varchar(1)  = '1',
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(1)  = 'N',
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_REQUEST_ID VARCHAR(15) = NULL,
@p_BRANCH_ID VARCHAR(15) = NULL,
@p_STREET NVARCHAR(200) = NULL,
@p_LOCATION VARCHAR(15) = NULL,
@p_UPD_DT varchar(20) = NULL,
@p_SCALE NVARCHAR(1000) = NULL,
@p_ENGINEER nvarchar(500) = NULL,
@p_APPROVE_VALUE decimal(18) = NULL,
@p_BRANCH_NAME_ETX nvarchar(500) = NULL,
@P_ListRequestDoc XML = NULL, -- THONG TIN VE TO TRINH
@P_ListBid XML = NULL, -- CHI PHI SUA CHUA
@P_ListContract_DT XML = NULL, -- DANH SACH HO SO THI CONG
@P_ListPayment XML = NULL, -- CAC DOT THANH TOAN
@P_ListBid_DT XML = NULL, -- DANH SACH CHI TIET DON VI THAM GIA THAU
@P_ListContractorArise XML = NULL, -- DANH SACH CHI TIET PHAT SINH TANG GIAM
@P_ListReviewXML XML = NULL, -- ĐÁNH GIÁ NCC
@P_ListConsProcessXML XML = NULL, -- TIẾN ĐỘ CÔNG TRÌNH
@P_ListConsHHXML XML = NULL -- DANH SÁCH HÀNG HÓA
AS
--Validation is here
/*
DECLARE @ERRORSYS NVARCHAR(15) = '' 
  IF ( NOT EXISTS ( SELECT * FROM CON_REQUEST_DOC WHERE ))
	 SET @ERRORSYS = ''
IF @ERRORSYS <> '' 
BEGIN
	SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '0'
END 
*/

DECLARE @OLD_REQUEST_ID VARCHAR(15) = @P_REQUEST_ID
DECLARE @sErrorCode VARCHAR(20),@DEP_CREATE VARCHAR(15) = NULL
SELECT @DEP_CREATE = DEP_ID FROM TL_USER WHERE TLNANME = @p_MAKER_ID 

Declare @hdoc INT

	-- THONG TIN VE TO TRINH
	Exec sp_xml_preparedocument @hdoc Output,@P_ListRequestDoc
	DECLARE @TABLE_REQ_DOC TABLE (
		CON_REQUEST_DOC_ID varchar(15),
		REQ_ID	varchar(15),
		REQ_CODE	nvarchar(100),
		REQ_NAME	nvarchar(200),
		REQ_DT	VARCHAR(20),
		REQ_CONTENT	nvarchar(1000),
		CONSTRUCT_ID	varchar(15),
		TOTAL_AMT	decimal(18),
		BRANCH_ID	varchar(15),
		PLAN_TYPE VARCHAR(1),
		IS_PARENT VARCHAR(1),
		IS_ARISE VARCHAR(1),
		BUDGET varchar(15),
		YEAR_BUDGET int,
		REQUEST_PARENT VARCHAR(15),
		APPROVE_DT VARCHAR(20)
)
INSERT INTO @TABLE_REQ_DOC SELECT *
	FROM OPENXML(@hDoc,'/Root/ListRequestDoc',2)
	WITH 
	(
		CON_REQUEST_DOC_ID varchar(15),
		REQ_ID	varchar(15),
		REQ_CODE	nvarchar(100),
		REQ_NAME	nvarchar(200),
		REQ_DT	VARCHAR(20),
		REQ_CONTENT	nvarchar(1000),
		CONSTRUCT_ID	varchar(15),
		TOTAL_AMT	decimal(18),
		BRANCH_ID	varchar(15),
		PLAN_TYPE VARCHAR(1),
		IS_PARENT VARCHAR(1),
		IS_ARISE VARCHAR(1),
		BUDGET varchar(15),
		YEAR_BUDGET int,
		REQUEST_PARENT VARCHAR(15),
		APPROVE_DT VARCHAR(20)

	)



	DECLARE ListRequestDoc CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ListRequestDoc',2)
	WITH 
	(
		CON_REQUEST_DOC_ID varchar(15),
		REQ_ID	varchar(15),
		REQ_CODE	nvarchar(100),
		REQ_NAME	nvarchar(200),
		REQ_DT	VARCHAR(20),
		REQ_CONTENT	nvarchar(1000),
		CONSTRUCT_ID	varchar(15),
		TOTAL_AMT	decimal(18),
		BRANCH_ID	varchar(15),
		PLAN_TYPE VARCHAR(1),
		IS_PARENT VARCHAR(1),
		IS_ARISE VARCHAR(1),
		BUDGET VARCHAR(15),
		YEAR_BUDGET INT,
		REQUEST_PARENT VARCHAR(15),
		APPROVE_DT VARCHAR(20)

	)
	OPEN ListRequestDoc

	-- DANH SACH HO SO THI CONG (HOP DONG)
	Exec sp_xml_preparedocument @hdoc Output,@P_ListContract_DT
	DECLARE ListContract_DT CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ListContract_DT',2)
	WITH 
	(
		CONTRACT_ID varchar(15),
		CONTRACT_CODE	varchar(15),
		[CONTRACT_NAME]	nvarchar(200),
		CONTRACT_TYPE	varchar(15),
		BID_ID	nvarchar(200),
		TOTAL_AMT DECIMAL(18),
		IS_PARENT varchar(1),
		REQUEST_ID varchar(15),
		REQDT_ID VARCHAR(15)
	)
	OPEN ListContract_DT

	-- DANH SÁCH HÀNG HÓA
	Exec sp_xml_preparedocument @hdoc Output,@P_ListConsHHXML
	DECLARE ListHH CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ListHH',2)
	WITH 
	(
		CON_REQ_HH_ID VARCHAR(15),
		REQ_CODE VARCHAR(50),
		REQ_ID VARCHAR(15),
		SUP_ID VARCHAR(15),
		SELLTEMENT_AMT DECIMAL(18,2),
		[DESCRIPTION] NVARCHAR(4000), -- KHOONG SU DUNG
		HH_ID VARCHAR(15),
		ESTIMATES_AMT DECIMAL(18,2),
		BRANCH_DO VARCHAR(15),
		CONS_DOCUMENT_ID VARCHAR(15),
		BID_ID VARCHAR(15),
		BID_CODE VARCHAR(50),
		REQDT_ID VARCHAR(15)
	)
	OPEN ListHH
	-- ĐÁNH GIÁ NCC
	Exec sp_xml_preparedocument @hdoc Output,@P_ListReviewXML
	DECLARE ListReview CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ListReview',2)
	WITH 
	(
		CON_REQ_REVIEW_ID VARCHAR(15),
		SUP_ID VARCHAR(15),
		QCKT_REVIEW NVARCHAR(4000),
		CONS_PROCESS VARCHAR(15),
		MAINTENANCE VARCHAR(15),
		REPORT_PLAN_EDIT VARCHAR(15),
		CONS_QUALITY VARCHAR(15),
		BEAUTY_IMAGE VARCHAR(15),
		REQ_ID VARCHAR(15),
		REQ_CODE VARCHAR(50),
		SUP_NAME NVARCHAR(1000),
		HANGHOA_ID VARCHAR(15)
	)
	OPEN ListReview

	-- TIẾN ĐỘ CÔNG TRÌNH
	Exec sp_xml_preparedocument @hdoc Output,@P_ListConsProcessXML
	DECLARE ListConsProcess CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ListConsProcess',2)
	WITH 
	(
		CON_REQ_PROCESS_ID VARCHAR(15),
        DATE_REPORT VARCHAR(50),
        PERCENT_COMPLETED DECIMAL(18,2),
        PERCENT_REMAIN DECIMAL(18,2),
        DEADLINE VARCHAR(50),
		ITEM_JOB NVARCHAR(MAX),
		NOTES NVARCHAR(MAX),
		DEADLINE_CON VARCHAR(50)
	)
	OPEN ListConsProcess



BEGIN TRANSACTION

		-- insert zô bảng tài liệu
		---TAO BANG TAM
		DECLARE @TEMP TABLE
		(
			[KEY] varchar(15),
			[REF_ID] varchar(15),
			[TYPE] varchar(50)
		)

		-- UPDATE BANG THONG TIN VE TO TRINH
		DELETE CON_REQUEST_DOC WHERE REQUEST_PARENT = @p_REQUEST_ID
		DECLARE 
		@INDEX int = 0,
		@CON_REQUEST_DOC_ID varchar(15),
		@REQ_ID	varchar(15),
		@REQ_CODE	nvarchar(100),
		@REQ_NAME	nvarchar(200),
		@REQ_DT	VARCHAR(20),
		@REQ_CONTENT	nvarchar(1000),
		@CONSTRUCT_ID	varchar(15),
		@TOTAL_AMT	decimal(18),
		@BRANCH_ID	varchar(15),
		@PLAN_TYPE VARCHAR(1),
		@IS_PARENT VARCHAR(1),
		@IS_ARISE VARCHAR(1),
		@REQ_BUDGET VARCHAR(15),
		@REQ_YEAR_BUDGET int,
		@REQUEST_PARENT VARCHAR(15),
		@APPROVE_DT VARCHAR(20),
		@DOC_REQUEST_TOTAL_AMT DECIMAL(18,2) = 0

		--INSERT ListBid DETAIL
		FETCH NEXT FROM ListRequestDoc INTO @CON_REQUEST_DOC_ID, @REQ_ID, @REQ_CODE, @REQ_NAME, @REQ_DT, @REQ_CONTENT,
		@CONSTRUCT_ID, @TOTAL_AMT, @BRANCH_ID, @PLAN_TYPE, @IS_PARENT,@IS_ARISE, @REQ_BUDGET, @REQ_YEAR_BUDGET, @REQUEST_PARENT, @APPROVE_DT
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF(@CON_REQUEST_DOC_ID IS NULL OR @CON_REQUEST_DOC_ID = '')
			BEGIN
				EXEC SYS_CodeMasters_Gen 'CON_REQUEST_DOC', @CON_REQUEST_DOC_ID out
			END

			IF @CON_REQUEST_DOC_ID='' OR @CON_REQUEST_DOC_ID IS NULL GOTO ABORT
			SET @INDEX = @INDEX + 1
			IF (@INDEX = 1) -- TRA VE SO TO TRINH DAU TIEN
			BEGIN
				SET @P_REQUEST_ID = @CON_REQUEST_DOC_ID
			END 
			
			SET @REQUEST_PARENT = @P_REQUEST_ID
			PRINT @REQUEST_PARENT
			INSERT INTO CON_REQUEST_DOC([CON_REQUEST_DOC_ID],[REQ_ID], [REQ_CODE], [REQ_NAME], [REQ_DT], [REQ_CONTENT], [CONSTRUCT_ID],
			[TOTAL_AMT], [NOTES], [RECORD_STATUS], [MAKER_ID], [CREATE_DT], [AUTH_STATUS], [CHECKER_ID], 
			[APPROVE_DT], [BRANCH_ID], [PLAN_TYPE], [IS_PARENT] , [BUDGET], [YEAR_BUDGET], [REQUEST_PARENT], [IS_ARISE])
			VALUES(@CON_REQUEST_DOC_ID,@REQ_ID, @REQ_CODE, @REQ_NAME, CONVERT(DATETIME, @REQ_DT, 103), @REQ_CONTENT, @CONSTRUCT_ID,
			@TOTAL_AMT, '', @p_RECORD_STATUS, @P_MAKER_ID, CONVERT(DATETIME,  @P_CREATE_DT , 103), @P_AUTH_STATUS, @P_CHECKER_ID, 
			 CONVERT(DATETIME, @APPROVE_DT, 103), @BRANCH_ID, @PLAN_TYPE, @IS_PARENT, @REQ_BUDGET, @REQ_YEAR_BUDGET, @REQUEST_PARENT, @IS_ARISE)
			
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@CON_REQUEST_DOC_ID, 'CON_REQUEST_DOC')
			SET @DOC_REQUEST_TOTAL_AMT = @DOC_REQUEST_TOTAL_AMT + @TOTAL_AMT

			PRINT @@ERROR
			IF @@ERROR <> 0 GOTO ABORT

		FETCH NEXT FROM ListRequestDoc INTO  @CON_REQUEST_DOC_ID, @REQ_ID, @REQ_CODE, @REQ_NAME, @REQ_DT, @REQ_CONTENT,
		@CONSTRUCT_ID, @TOTAL_AMT, @BRANCH_ID, @PLAN_TYPE, @IS_PARENT,@IS_ARISE, @REQ_BUDGET, @REQ_YEAR_BUDGET, @REQUEST_PARENT, @APPROVE_DT
		END
		CLOSE ListRequestDoc
		DEALLOCATE ListRequestDoc

		-- THEM THONG TIN CONG TRINH
		IF(@p_TOTAL_COST > @DOC_REQUEST_TOTAL_AMT AND EXISTS(SELECT * FROM @TABLE_REQ_DOC))
		BEGIN
			DECLARE @ERRORSYS NVARCHAR(200) = N'Tổng chi phí xây dựng không lớn hơn tổng giá trị các tờ trình ('+ cast(format(@DOC_REQUEST_TOTAL_AMT, 'N', 'en-US') AS VARCHAR(20)) +')'
			ROLLBACK TRANSACTION
			SELECT '-1' as Result, @ERRORSYS ErrorDesc
			RETURN '-1'
		END	
		DECLARE @IS_FINISH VARCHAR(1) = 'N'

		UPDATE CON_MASTER SET [IS_FINISH] = @IS_FINISH, [CONSTRUCT_CODE] = @p_CONSTRUCT_CODE,[CONSTRUCT_NAME] = @p_CONSTRUCT_NAME,[PLAN_ID] = @p_PLAN_ID,[DIVI_ID] = @p_DIVI_ID,[CONSTRUCT_ADDR] = @p_CONSTRUCT_ADDR,[LENGTH] = @p_LENGTH,[WIDTH] = @p_WIDTH,
		[CONSTRUCT_AREA] = @p_CONSTRUCT_AREA, [FLOORS] = @p_FLOORS, [FLOORS_AREA] = @p_FLOORS_AREA, [YEAR_EXE] = @p_YEAR_EXE,[CONST_TYPE] = @p_CONST_TYPE, [HQ_TYPE] = @p_HQ_TYPE,[TOTAL_AMT] = @p_TOTAL_AMT,[COST_ESTIMATE] = @p_COST_ESTIMATE,[COST_EXE] = @p_COST_EXE,
		[COST_INCURRED] = @p_COST_INCURRED, [CONST_PURPOSE] = @p_CONST_PURPOSE, [DESCRIPTION] = @p_DESCRIPTION,[START_DT] = CONVERT(DATETIME, @p_START_DT, 103),[END_DT] = CONVERT(DATETIME, @p_END_DT, 103),[DATE_EXE] = CONVERT(DATETIME, @p_DATE_EXE, 103),[COMPLETION_DT] = CONVERT(DATETIME,
		 @p_COMPLETION_DT, 103),[CONSTRUCT_PROGRESS] = @p_CONSTRUCT_PROGRESS,[RECORD_STATUS] = @p_RECORD_STATUS,[MAKER_ID] = @p_MAKER_ID--,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103)
		 ,[DEP_CREATE] = @DEP_CREATE,[AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, 
		 @p_APPROVE_DT, 103),[CONST_ID] = @p_CONST_ID, [REQUEST_ID] = @p_REQUEST_ID, [BRANCH_ID] = @P_BRANCH_ID, 
		 [STREET] = @P_STREET, [LOCATION] = @P_LOCATION, [SCALE] = @P_SCALE, [ENGINEER] = @p_ENGINEER, [BRANCH_NAME_ETX] = @p_BRANCH_NAME_ETX, UPD_DT = CONVERT(DATETIME, @p_UPD_DT, 103), APPROVE_VALUE = @p_APPROVE_VALUE,
		TOTAL_AREA_USE = @p_TOTAL_AREA_USE,TOTAL_COST = @p_TOTAL_COST,UNIT_PRICE=  @p_UNIT_PRICE,UNUSED_AREA = @p_UNUSED_AREA
		WHERE  CONSTRUCT_ID = @p_CONSTRUCT_ID
		
		INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@p_CONSTRUCT_ID, 'CON_MASTER')
		IF @@Error <> 0 GOTO ABORT

		-- DANH SÁCH HÀNG HÓA

		DELETE CON_REQUEST_HH_DT WHERE CONSTRUCT_ID = @p_CONSTRUCT_ID

		DECLARE
			@CON_REQ_HH_ID VARCHAR(15),
			@SUP_ID VARCHAR(15),
			@SELLTEMENT_AMT DECIMAL(18,2),
			@DESCRIPTION NVARCHAR(4000),
			@HH_ID VARCHAR(15),
			@ESTIMATES_AMT DECIMAL(18,2),
			@BRANCH_DO VARCHAR(15),
			@CONS_DOCUMENT_ID VARCHAR(15),
			@BID_ID varchar(15),
			@BID_CODE VARCHAR(15),
			@REQDT_ID VARCHAR(15)
		FETCH NEXT FROM ListHH INTO @CON_REQ_HH_ID,@REQ_CODE, @REQ_ID, @SUP_ID, @SELLTEMENT_AMT, @DESCRIPTION, @HH_ID, @ESTIMATES_AMT,@BRANCH_DO,@CONS_DOCUMENT_ID,@BID_ID,@BID_CODE,@REQDT_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC SYS_CodeMasters_Gen 'CON_REQUEST_HH_DT', @CON_REQ_HH_ID out
			IF @CON_REQ_HH_ID='' OR @CON_REQ_HH_ID IS NULL GOTO ABORT

			SET @DESCRIPTION = (SELECT [DESCRIPTION] FROM PL_REQUEST_DOC_DT WHERE REQDT_ID = @REQDT_ID)

			INSERT INTO [dbo].[CON_REQUEST_HH_DT]
			(
			   [CON_REQ_HH_ID],
			   [SUP_ID],
			   [SELLTEMENT_AMT],
			   [DESCRIPTION],
			   [HH_ID],
			   [REQ_ID],
			   [ESTIMATES_AMT],
			   [BRANCH_DO],
			   [CONS_DOCUMENT_ID],
			   [BID_ID],
			   [REQ_CODE],
			   [CONSTRUCT_ID],
			   [REQDT_ID],
			   [BID_CODE]
			)
			VALUES
			(
				@CON_REQ_HH_ID,
				@SUP_ID,
				@SELLTEMENT_AMT,
				@DESCRIPTION,
				@HH_ID,
				@REQ_ID,
				@ESTIMATES_AMT,
				@BRANCH_DO,
				@CONS_DOCUMENT_ID,
				@BID_ID,
				@REQ_CODE,
				@p_CONSTRUCT_ID,
				@REQDT_ID,
				@BID_CODE
			)

			INSERT INTO @TEMP([KEY],[REF_ID],[TYPE]) VALUES (@REQDT_ID, @CON_REQ_HH_ID, 'CON_REQUEST_HH_DT')


			FETCH NEXT FROM ListHH INTO @CON_REQ_HH_ID,@REQ_CODE, @REQ_ID, @SUP_ID, @SELLTEMENT_AMT, @DESCRIPTION, @HH_ID, @ESTIMATES_AMT,@BRANCH_DO,@CONS_DOCUMENT_ID,@BID_ID,@BID_CODE,@REQDT_ID
		END
		CLOSE ListHH
		DEALLOCATE ListHH

		-- UPDATE DANH SÁCH HỒ SƠ THI CÔNG
		-- HOP DONG & PHU LUC HOP DONG

		DELETE TR_CONTRACT WHERE REQUEST_ID = @OLD_REQUEST_ID

		DECLARE 
		@CDT_INDEX int = 0,
		@CDT_CONTRACT_ID varchar(15),
		@CONTRACT_CODE	varchar(15),
		@CONTRACT_NAME	nvarchar(200),
		@CONTRACT_TYPE	varchar(1),
		@CDT_BID_ID	nvarchar(200),
		@CDT_TOTAL_AMT decimal(18),
		@CDT_IS_PARENT varchar(1),
		@CDT_REQUEST_ID varchar(15),
		@CDT_CONTRACT_PARENT varchar(15),
		@CDT_REQDT_ID VARCHAR(15)
		
		--INSERT ListContract_DT DETAIL
		FETCH NEXT FROM ListContract_DT INTO  @CDT_CONTRACT_ID, @CONTRACT_CODE, @CONTRACT_NAME, @CONTRACT_TYPE,
		@CDT_BID_ID, @CDT_TOTAL_AMT, @CDT_IS_PARENT, @CDT_REQUEST_ID, @CDT_REQDT_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN

			DECLARE @l_CON_REQUEST_CONTRACT_ID VARCHAR(15), @CDT_CON_REQ_HH_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'CON_REQUEST_CONTRACT', @l_CON_REQUEST_CONTRACT_ID out
			IF @l_CON_REQUEST_CONTRACT_ID='' OR @l_CON_REQUEST_CONTRACT_ID IS NULL GOTO ABORT
			
			SET @CDT_CON_REQ_HH_ID = (SELECT REF_ID FROM @TEMP WHERE [KEY] = @CDT_REQDT_ID)

			INSERT INTO CON_REQUEST_CONTRACT([CON_REQUEST_CONTRACT_ID],[CON_REQ_HH_ID],[CONTRACT_ID],[TOTAL_AMT],[REQDT_ID])
			VALUES (@l_CON_REQUEST_CONTRACT_ID,@CDT_CON_REQ_HH_ID,@CDT_CONTRACT_ID,@CDT_TOTAL_AMT,@CDT_REQDT_ID)
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_CON_REQUEST_CONTRACT_ID, 'CON_REQUEST_CONTRACT')

			IF @@Error <> 0 GOTO ABORT


		FETCH NEXT FROM ListContract_DT INTO  @CDT_CONTRACT_ID, @CONTRACT_CODE, @CONTRACT_NAME, @CONTRACT_TYPE,
		@CDT_BID_ID, @CDT_TOTAL_AMT, @CDT_IS_PARENT, @CDT_REQUEST_ID, @CDT_REQDT_ID
		END
		CLOSE ListContract_DT
		DEALLOCATE ListContract_DT

		-- ĐÁNH GIÁ NCC 
		DELETE CON_REQUEST_REVIEW_DT WHERE CONSTRUCT_ID = @p_CONSTRUCT_ID

		DECLARE
			@CON_REQ_REVIEW_ID VARCHAR(15),
			@QCKT_REVIEW NVARCHAR(4000),
			@CONS_PROCESS VARCHAR(15),
			@MAINTENANCE VARCHAR(15),
			@REPORT_PLAN_EDIT VARCHAR(15),
			@CONS_QUALITY VARCHAR(15),
			@BEAUTY_IMAGE VARCHAR(15),
			@SUP_NAME NVARCHAR(1000),
			@HANGHOA_ID VARCHAR(15)
		FETCH NEXT FROM ListReview into @CON_REQ_REVIEW_ID,@SUP_ID, @QCKT_REVIEW, @CONS_PROCESS, @MAINTENANCE, @REPORT_PLAN_EDIT, @CONS_QUALITY, @BEAUTY_IMAGE, @REQ_ID, @REQ_CODE, @SUP_NAME, @HANGHOA_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC SYS_CodeMasters_Gen 'CON_REQUEST_REVIEW_DT', @CON_REQ_REVIEW_ID out
			IF @CON_REQ_REVIEW_ID='' OR @CON_REQ_REVIEW_ID IS NULL GOTO ABORT

			INSERT INTO [dbo].[CON_REQUEST_REVIEW_DT]
			(
				[CON_REQ_REVIEW_ID],
				[SUP_ID],
				[CONS_PROCESS],
				[MAINTENANCE],
				[REPORT_PLAN_EDIT],
				[CONS_QUALITY],
				[BEAUTY_IMAGE],
				[CONSTRUCT_ID],
				[QCKT_REVIEW],
				[SUP_NAME],
				[REQ_CODE],
				[REQ_ID],
				[HANGHOA_ID]
			)
			VALUES
			(		
				@CON_REQ_REVIEW_ID,
				@SUP_ID,
				@CONS_PROCESS,
				@MAINTENANCE,
				@REPORT_PLAN_EDIT,
				@CONS_QUALITY,
				@BEAUTY_IMAGE,
				@p_CONSTRUCT_ID,
				@QCKT_REVIEW,
				@SUP_NAME,
				@REQ_CODE,
				@REQ_ID,
				@HANGHOA_ID
			)
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@CON_REQ_REVIEW_ID, 'CON_REQUEST_REVIEW_DT')

			FETCH NEXT FROM ListReview INTO @CON_REQ_REVIEW_ID,@SUP_ID, @QCKT_REVIEW, @CONS_PROCESS, @MAINTENANCE, @REPORT_PLAN_EDIT, @CONS_QUALITY, @BEAUTY_IMAGE, @REQ_ID, @REQ_CODE, @SUP_NAME, @HANGHOA_ID
		END
		CLOSE ListReview
		DEALLOCATE ListReview
		-- TIẾN ĐỘ CÔNG TRÌNH

		DELETE CON_REQUEST_PROCESS_DT WHERE CONSTRUCT_ID = @p_CONSTRUCT_ID


		DECLARE
			@CON_REQ_PROCESS_ID VARCHAR(15),
			@DATE_REPORT VARCHAR(50),
			@PERCENT_COMPLETED DECIMAL(18,2),
			@PERCENT_REMAIN DECIMAL(18,2),
			@DEADLINE VARCHAR(50),
			@ITEM_JOB NVARCHAR(MAX),
			@NOTES NVARCHAR(MAX),
			@DEADLINE_CON VARCHAR(50)
		FETCH NEXT FROM ListConsProcess into @CON_REQ_PROCESS_ID,@DATE_REPORT, @PERCENT_COMPLETED, @PERCENT_REMAIN, @DEADLINE, @ITEM_JOB, @NOTES, @DEADLINE_CON
		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC SYS_CodeMasters_Gen 'CON_REQUEST_PROCESS_DT', @CON_REQ_PROCESS_ID out
			IF @CON_REQ_PROCESS_ID='' OR @CON_REQ_PROCESS_ID IS NULL GOTO ABORT

			INSERT INTO [dbo].[CON_REQUEST_PROCESS_DT]
			(
				[CON_REQ_PROCESS_ID],
				[DATE_REPORT],
				[PERCENT_COMPLETED],
				[PERCENT_REMAIN],
				[DEADLINE],
				[CONSTRUCT_ID],
				[ITEM_JOB],
				[NOTES],
				[DEADLINE_CON]
			)
			VALUES
			(
				@CON_REQ_PROCESS_ID,
				CONVERT(DATETIME, @DATE_REPORT, 103),
				@PERCENT_COMPLETED,
				@PERCENT_REMAIN,
				CASE WHEN @DEADLINE IS NULL OR @DEADLINE = '' THEN NULL ELSE CONVERT(DATETIME, @DEADLINE, 103) END,
				@p_CONSTRUCT_ID,
				@ITEM_JOB,
				@NOTES,
				CASE WHEN @DEADLINE_CON IS NULL OR @DEADLINE_CON = '' THEN NULL ELSE CONVERT(DATETIME, @DEADLINE_CON, 103) END
			)
			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@CON_REQ_PROCESS_ID, 'CON_REQUEST_PROCESS_DT')

			FETCH NEXT FROM ListConsProcess INTO @CON_REQ_PROCESS_ID,@DATE_REPORT, @PERCENT_COMPLETED, @PERCENT_REMAIN, @DEADLINE, @ITEM_JOB, @NOTES, @DEADLINE_CON
		END
		CLOSE ListConsProcess
		DEALLOCATE ListConsProcess
-- start hieuhm 09/11/2022 thêm lịch sử xử lí
		INSERT INTO dbo.PL_PROCESS
					(
					    REQ_ID,
					    PROCESS_ID,
					    CHECKER_ID,
					    APPROVE_DT,
					    PROCESS_DESC,
					    NOTES
					)
					VALUES
					(   @p_CONSTRUCT_ID,       
						'UPDATE',
					    @p_MAKER_ID,        
					    GETDATE(), 
					    N'Cập nhật công trình thành công' ,      
					    N'Đơn vị cập nhật công trình'       
					)
-- end hieuhm 09/11/2022 thêm lịch sử xử lí
COMMIT TRANSACTION
SELECT '0' as Result, @p_REQUEST_ID REQ_ID, [REF_ID], [TYPE], '' ErrorDesc FROM @TEMP
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		CLOSE ListRequestDoc
		DEALLOCATE ListRequestDoc
		CLOSE ListBid
		DEALLOCATE ListBid
		CLOSE ListContract_DT
		DEALLOCATE ListContract_DT
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail
		CLOSE ContractorDetail
		DEALLOCATE ContractorDetail		
		SELECT ErrorCode Result, @p_REQUEST_ID REQ_ID, '' [REF_ID], '' [TYPE], ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode
		RETURN '-1'
End
ABORT1:
BEGIN
		ROLLBACK TRANSACTION
		CLOSE ListRequestDoc
		DEALLOCATE ListRequestDoc
		
		SELECT ErrorCode Result, @p_REQUEST_ID REQ_ID, '' [REF_ID], '' [TYPE],ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode
		RETURN '-1'
End
ABORT2:
BEGIN
		ROLLBACK TRANSACTION
		CLOSE ListContract_DT
		DEALLOCATE ListContract_DT
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail
		CLOSE ContractorDetail
		DEALLOCATE ContractorDetail		
		SELECT ErrorCode Result, @p_REQUEST_ID REQ_ID, '' [REF_ID], '' [TYPE], ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode
		RETURN '-1'
End