DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

INSERT INTO [dbo].[SYS_PREFIX] ([ID], [Prefix], [Description]) VALUES ('CON_REQUEST_DOC_DT', 'CRDT', N'Chi tiết tờ trình XDCB')

INSERT INTO [dbo].[SYS_PREFIX] ([ID], [Prefix], [Description]) VALUES ('CON_REQUEST_HH_DT', 'CRHDT', N'')

INSERT INTO [dbo].[SYS_PREFIX] ([ID], [Prefix], [Description]) VALUES ('CON_REQUEST_PROCESS_DT', 'CRPDT', N'')

INSERT INTO [dbo].[SYS_PREFIX] ([ID], [Prefix], [Description]) VALUES ('CON_REQUEST_REVIEW_DT', 'CRRDT', N'')

ALTER PROCEDURE [dbo].[CON_REQUEST_DOC_InsXml]

@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_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_ENGINEER nvarchar(500) = NULL,

@p_BRANCH_NAME_ETX nvarchar(500) = 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_SCALE NVARCHAR(1000) = NULL,

@p_UPD_DT varchar(20) = NULL,

@p_APPROVE_VALUE decimal(18) = 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 @sErrorCode VARCHAR(20)

Declare @hdoc INT

	-- THONG TIN VE TO TRINH

	Exec sp_xml_preparedocument @hdoc Output,@P_ListRequestDoc

	DECLARE ListRequestDoc CURSOR LOCAL  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

	-- CHI PHI SUA CHUA

	Exec sp_xml_preparedocument @hdoc Output,@P_ListBid

	DECLARE ListBid CURSOR LOCAL FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/ListBid',2)

	WITH 

	(

		BID_ID varchar(15),

		BID_CODE	varchar(15),

		BID_TYPE	varchar(1),

		PROJECT_ID	varchar(15),

		TERM_BID	nvarchar(200),

		FORM	varchar(4),

		INPUT_DT	VARCHAR(20),

		EXP_DT	VARCHAR(20),

		OPEN_DT	VARCHAR(20),

		BUDGET	varchar(15),

		TOTAL_AMT	decimal(18),

		GUARANTEE_AMT	decimal(18),

		GUARANTEE_PER	decimal(18),

		GUARANTEE_EXP	VARCHAR(20),

		REQUEST_ID varchar(15),

		BID_WIN nvarchar(200),

		SETTLE_AMT decimal(18),

		CONTRACT_CODE varchar(15),

		NOTES nvarchar(1000),

		TOTAL_AMT_KT	VARCHAR(20),

		CONTRACT_VALUE decimal(18,2),

		[PERCENT] decimal(18,2),

		OUTSTANDINGVALUE decimal(18,2),

		ARISE_VALUE decimal(18,2),

		PAID_VALUE decimal(18,2),

		[DESC] nvarchar(1000)

	)

	OPEN ListBid

	-- DANH SACH HO SO THI CONG (HOP DONG)

	Exec sp_xml_preparedocument @hdoc Output,@P_ListContract_DT

	DECLARE ListContract_DT CURSOR LOCAL 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)

	)

	OPEN ListContract_DT

	-- CAC DOT THANH TOAN

	Exec sp_xml_preparedocument @hdoc Output,@P_ListPayment

	DECLARE PaymentDetail CURSOR LOCAL FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)

	WITH 

	(

		[PERCENT] decimal(18,2),

		AMOUNT	decimal(18),

		BID_ID varchar(15),

		PAY_DT	varchar(20),

		PAY_STATUS	varchar(1),

		NOTES	nvarchar(1000),

		CONTRACT_CODE	varchar(15)

	)

	OPEN PaymentDetail

	-- CHI TIET DON VI THAM GIA THAU

	Exec sp_xml_preparedocument @hdoc Output,@P_ListBid_DT

	DECLARE ContractorDetail CURSOR LOCAL FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/ContractorDetail',2)

	WITH 

	(

		ID VARCHAR(15),

		BID_ID varchar(15),

		BID_CODE varchar(15),			

		SUPPLIER nvarchar(200),

		IS_BID_WIN varchar(1),

		OFFERING_VALUE	decimal(18),

		CHECK_VALUE decimal(18)

	)

	OPEN ContractorDetail

	-- CHI TIET PHAT SINH TANG GIAM

	Exec sp_xml_preparedocument @hdoc Output,@P_ListContractorArise

	DECLARE ListContractorArise CURSOR LOCAL FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/ListContractorArise',2)

	WITH 

	(

		ARISE_ID VARCHAR(15),

		BID_ID varchar(15),

		BID_CODE varchar(15),	

		SUPPLIER nvarchar(200),

		IS_BID_WIN varchar(1),	

		BEGIN_VALUE	decimal(18,2),

		CHECKED_VALUE decimal(18,2)

	)

	OPEN ListContractorArise

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

	Exec sp_xml_preparedocument @hdoc Output,@P_ListConsHHXML

	DECLARE ListHH CURSOR LOCAL 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 LOCAL 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)

	)

	OPEN ListReview

	-- TIẾN ĐỘ CÔNG TRÌNH

	Exec sp_xml_preparedocument @hdoc Output,@P_ListConsProcessXML

	DECLARE ListConsProcess CURSOR LOCAL FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/ListConsProcess',2)

	WITH 

	(

		CON_REQ_PROCESS_ID VARCHAR(15),

        DATE_REPORT VARCHAR(15),

        PERCENT_COMPLETED DECIMAL(18,2),

        PERCENT_REMAIN DECIMAL(18,2),

        DEADLINE VARCHAR(15)

	)

	OPEN ListConsProcess

BEGIN TRANSACTION

		-- THONG TIN VE TO TRINH

		-- insert zô bảng tài liệu

		---TAO BANG TAM

		DECLARE @TEMP TABLE

			(

				[KEY] varchar(15),

				[REF_ID] varchar(15),

				[TYPE] varchar(50)

			)

		-- THEM THONG TIN TO TRINH

		--KHAI BAO TOAN BO BIEN TRUOC KHI FETCH DATA

		DECLARE 

		@INDEX int = 0,

		@IS_PARENT VARCHAR(1),

		@IS_ARISE VARCHAR(1),

		@PLAN_TYPE VARCHAR(1),

		@REQ_BUDGET varchar(15),

		@REQ_YEAR_BUDGET int,

		@REQUEST_PARENT VARCHAR(15),

		@APPROVE_DT VARCHAR(20),

		@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,2),

		@BRANCH_ID	varchar(15)

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

			--25/12/2014 Anh Ai yeu cau khong kiem tra trung to trinh - mot to trinh dung cho nhieu cong trinh

			---- KIEM TRA SO TO TRINH CO TRUNG HAY KHONG

			--IF EXISTS(SELECT * FROM CON_REQUEST_DOC WHERE [REQ_CODE]= @REQ_CODE)

			--BEGIN

			--	SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'CON-0001'

			--	GOTO ABORT

			--END

			EXEC SYS_CodeMasters_Gen 'CON_REQUEST_DOC', @CON_REQUEST_DOC_ID out

			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 = @REQ_ID

					SET @P_REQUEST_ID = @CON_REQUEST_DOC_ID

				END 

			SET @REQUEST_PARENT = @P_REQUEST_ID

			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')

			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

		DECLARE @OLD_REQUEST_PARENT VARCHAR(15) = @REQUEST_PARENT

		SET @REQUEST_PARENT = (SELECT TOP(1) CON_REQUEST_DOC_ID FROM CON_REQUEST_DOC WHERE REQUEST_PARENT = @OLD_REQUEST_PARENT ORDER BY TOTAL_AMT DESC)

		UPDATE CON_REQUEST_DOC

		SET IS_PARENT = (CASE WHEN CON_REQUEST_DOC_ID = @REQUEST_PARENT THEN '1' ELSE '0' END),

		REQUEST_PARENT = @REQUEST_PARENT

		WHERE REQUEST_PARENT = @OLD_REQUEST_PARENT

		SET @p_REQUEST_ID = @REQUEST_PARENT

		-- THEM THONG TIN CONG TRINH

		DECLARE @l_CONSTRUCT_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'CON_MASTER', @l_CONSTRUCT_ID out

		IF @l_CONSTRUCT_ID='' OR @l_CONSTRUCT_ID IS NULL GOTO ABORT

		INSERT INTO CON_MASTER([IS_FINISH],[CONSTRUCT_ID],[CONSTRUCT_CODE],[CONSTRUCT_NAME],[PLAN_ID],[DIVI_ID],[CONSTRUCT_ADDR],[LENGTH],[WIDTH], [CONSTRUCT_AREA],

		[FLOORS],[FLOORS_AREA],[YEAR_EXE],[HQ_TYPE],[CONST_TYPE],[TOTAL_AMT],[COST_ESTIMATE],[COST_EXE],[COST_INCURRED],[CONST_PURPOSE],

		[DESCRIPTION],[START_DT],[END_DT],[DATE_EXE],[COMPLETION_DT],[CONSTRUCT_PROGRESS],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[CONST_ID],

		[REQUEST_ID], [BRANCH_ID], [STREET], [LOCATION], [SCALE], [ENGINEER], [BRANCH_NAME_ETX],UPD_DT, APPROVE_VALUE,TOTAL_AREA_USE,TOTAL_COST,UNIT_PRICE)

		VALUES('N',@l_CONSTRUCT_ID ,@l_CONSTRUCT_ID ,@p_CONSTRUCT_NAME ,@p_PLAN_ID ,@p_DIVI_ID ,@p_CONSTRUCT_ADDR ,@p_LENGTH, @p_WIDTH, @p_CONSTRUCT_AREA ,@p_FLOORS, @p_FLOORS_AREA,

		 @p_YEAR_EXE ,@p_HQ_TYPE ,@p_CONST_TYPE ,@p_TOTAL_AMT ,@p_COST_ESTIMATE ,@p_COST_EXE ,@p_COST_INCURRED , @p_CONST_PURPOSE, @p_DESCRIPTION ,CONVERT(DATETIME, @p_START_DT, 103) ,

		 CONVERT(DATETIME, @p_END_DT, 103) ,CONVERT(DATETIME, @p_DATE_EXE, 103) ,CONVERT(DATETIME, @p_COMPLETION_DT, 103),@p_CONSTRUCT_PROGRESS ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,

		 CONVERT(DATETIME, @p_APPROVE_DT, 103) ,@p_CONST_ID, @p_REQUEST_ID, @p_BRANCH_ID, @p_STREET, @p_LOCATION, @p_SCALE, @p_ENGINEER, @p_BRANCH_NAME_ETX , CONVERT(DATETIME, @p_UPD_DT, 103), @p_APPROVE_VALUE,@p_TOTAL_AREA_USE,@p_TOTAL_COST,@p_UNIT_PRICE)

		IF @@Error <> 0 GOTO ABORT

		-- UPDATE CONSTRUCT ID CON_REQUEST_DOC

		UPDATE CON_REQUEST_DOC 

		SET CONSTRUCT_ID = @l_CONSTRUCT_ID

		WHERE REQUEST_PARENT = @p_REQUEST_ID

		INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@l_CONSTRUCT_ID, 'CON_MASTER')

		-- CHI PHI SUA CHUA

		--KHAI BAO TOAN BO BIEN TRUOC KHI FETCH DATA

		DECLARE 

		@BID_ID varchar(15),

		@BID_CODE	varchar(15),

		@BID_TYPE	varchar(1),

		@PROJECT_ID	varchar(15),

		@TERM_BID	nvarchar(200),

		@FORM	varchar(4),

		@INPUT_DT	VARCHAR(20),

		@EXP_DT	VARCHAR(20),

		@OPEN_DT	VARCHAR(20),

		@_TOTAL_AMT	decimal(18), -- CHI PHI

		@GUARANTEE_AMT	decimal(18),

		@GUARANTEE_PER	decimal(18),

		@GUARANTEE_EXP	VARCHAR(20),

		@REQUEST_ID varchar(15),

		@BUDGET DECIMAL(18),

		@BID_WIN nvarchar(200),

		@SETTLE_AMT decimal(18),

		@_CONTRACT_CODE varchar(15),

		@NOTES nvarchar(1000),

		@TOTAL_AMT_KT decimal(18),

		@CONTRACT_VALUE decimal(18,2),

		@PERCENT decimal(18,2),

		@OUTSTANDINGVALUE decimal(18,2),

		@ARISE_VALUE decimal(18,2),

		@PAID_VALUE decimal(18,2),

		@DESC nvarchar(1000)

		--INSERT ListBid DETAIL

		FETCH NEXT FROM ListBid INTO  @BID_ID, @BID_CODE, @BID_TYPE, @PROJECT_ID, @TERM_BID,

		@FORM, @INPUT_DT, @EXP_DT, @OPEN_DT, @BUDGET, @_TOTAL_AMT, @GUARANTEE_AMT, @GUARANTEE_PER, @GUARANTEE_EXP, @REQUEST_ID,

		@BID_WIN, @SETTLE_AMT, @_CONTRACT_CODE, @NOTES,@TOTAL_AMT_KT,@CONTRACT_VALUE,@PERCENT,@OUTSTANDINGVALUE,@ARISE_VALUE,@PAID_VALUE,@DESC

		WHILE @@FETCH_STATUS = 0

		BEGIN

			DECLARE @l_BID_ID VARCHAR(15)

			EXEC SYS_CodeMasters_Gen 'BID_MASTER', @l_BID_ID out

			IF @l_BID_ID='' OR @l_BID_ID IS NULL GOTO ABORT

			-- GIANT 10/12/2021 terM_BID

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

			--INSERT INTO BID_MASTER([BID_ID],[BID_CODE],[BID_TYPE],[PROJECT_ID],[TERM_BID],[FORM],[INPUT_DT],[EXP_DT],

			--[OPEN_DT],[BUDGET],[TOTAL_AMT],[GUARANTEE_AMT],[GUARANTEE_PER],[GUARANTEE_EXP],[NOTES],

			--[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[REQUEST_ID],

			--[BID_WIN], [SETTLE_AMT],[CONTRACT_CODE],[TOTAL_AMT_KT],CONTRACT_VALUE,[PERCENT],OUTSTANDINGVALUE, ARISE_VALUE ,PAID_VALUE,[DESC])

			--VALUES(@l_BID_ID ,@BID_CODE , @BID_TYPE,@PROJECT_ID ,@TERM_BID ,@FORM ,

			--CONVERT(DATETIME, @INPUT_DT, 103) ,CONVERT(DATETIME, @EXP_DT, 103) ,

			--CONVERT(DATETIME, @OPEN_DT, 103) ,@BUDGET ,@_TOTAL_AMT ,@GUARANTEE_AMT ,

			--@GUARANTEE_PER ,CONVERT(DATETIME, @GUARANTEE_EXP, 103) ,@NOTES ,@p_RECORD_STATUS ,

			--@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,

			--CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_REQUEST_ID, @BID_WIN, @SETTLE_AMT, @_CONTRACT_CODE, @TOTAL_AMT_KT,@CONTRACT_VALUE,@PERCENT,@OUTSTANDINGVALUE,@ARISE_VALUE,@PAID_VALUE,@DESC)		

			--IF @@ERROR <> 0 GOTO ABORT

			INSERT INTO @TEMP([KEY],[REF_ID],[TYPE]) VALUES (@BID_CODE, @l_BID_ID, 'BID_MASTER')

		FETCH NEXT FROM ListBid INTO  @BID_ID, @BID_CODE, @BID_TYPE, @PROJECT_ID, @TERM_BID,

		@FORM, @INPUT_DT, @EXP_DT, @OPEN_DT, @BUDGET, @_TOTAL_AMT, @GUARANTEE_AMT, @GUARANTEE_PER, @GUARANTEE_EXP, @REQUEST_ID,

		@BID_WIN, @SETTLE_AMT, @_CONTRACT_CODE, @NOTES,@TOTAL_AMT_KT,@CONTRACT_VALUE,@PERCENT,@OUTSTANDINGVALUE,@ARISE_VALUE,@PAID_VALUE,@DESC

		END

		CLOSE ListBid

		DEALLOCATE ListBid

		-- THEM CHI TIET THONG TIN HO SO THI CONG 

		-- HOP DONG & PHU LUC HOP DONG

		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)

		-- DANH SACH HO SO THI CONG (HOP DONG)

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

		WHILE @@FETCH_STATUS = 0

		BEGIN

			-- KIEM TRA SO HOP DONG CO TRUNG HAY KHONG

			IF EXISTS(SELECT * FROM TR_CONTRACT WHERE REQUEST_ID = @P_REQUEST_ID AND [CONTRACT_CODE]= @CONTRACT_CODE AND @CONTRACT_CODE<>'' AND @CONTRACT_CODE IS NOT NULL)

			BEGIN

				SELECT ErrorCode Result, @l_CONSTRUCT_ID CONSTRUCT_ID, '' REF_ID, '' [TYPE], ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'CTRC-0001'

				GOTO ABORT

			END

			SELECT @BID_ID = REF_ID FROM @TEMP where [TYPE]='BID_MASTER' AND [KEY] = @CDT_BID_ID

			DECLARE @l_CONTRACT_ID VARCHAR(15)

			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT', @l_CONTRACT_ID out

			IF @l_CONTRACT_ID='' OR @l_CONTRACT_ID IS NULL GOTO ABORT

			--SET @CDT_INDEX = @CDT_INDEX + 1			

			IF (@CDT_IS_PARENT = '1') -- TRA VE SO TO TRINH DAU TIEN

				SET @CDT_CONTRACT_PARENT = @l_CONTRACT_ID

			INSERT INTO TR_CONTRACT([CONTRACT_ID],[CONTRACT_CODE],[CONTRACT_NAME],[CONTRACT_TYPE],[BID_ID],[SUP_ID],

			[TOTAL_AMT],[DELIVERY_DT],[FORM1],[VOUCHER_ID1],[BANK1],[AMOUNT1],

			[EXP_DT1],[RATE1],[FORM2],[VOUCHER_ID2],[BANK2],[AMOUNT2],[EXP_DT2],

			[RATE2],[REQ_DOC_ID],[REQ_DOC_CONTENT],[APPROVE_VALUE],[SIGN_DT],[CONSTRUCT_PROGRESS],

			[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT],[START_DT],[END_DT], [IS_PARENT], [REQUEST_ID], [CONTRACT_PARENT])

			VALUES(@l_CONTRACT_ID ,@CONTRACT_CODE ,@CONTRACT_NAME, @CONTRACT_TYPE ,@BID_ID ,NULL,

			@CDT_TOTAL_AMT,NULL , NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL,

			NULL ,NULL ,NULL ,NULL, NULL,NULL,NULL ,

			NULL ,@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),

			CONVERT(DATETIME,@P_START_DT,103),CONVERT(DATETIME,@P_END_DT,103), @CDT_IS_PARENT, @p_REQUEST_ID, case when @CDT_IS_PARENT = '1' then null else @CDT_CONTRACT_PARENT end)

			IF @@Error <> 0 GOTO ABORT

			INSERT INTO @TEMP([KEY],[REF_ID],[TYPE]) VALUES (@CONTRACT_CODE, @l_CONTRACT_ID, 'TR_CONTRACT')

		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

		END

		CLOSE ListContract_DT

		DEALLOCATE ListContract_DT

		-- THEM THANH TOAN HO SO THI CONG

		--insert payment detail

		DECLARE 

		@AMOUNT	decimal(18),

		@PAY_DT	varchar(20),

		@PAY_STATUS	varchar(1),

		@CONTRACT_ID varchar(15),

		@P_CONTRACT_CODE	varchar(15)

		DECLARE @l_PAY_ID VARCHAR(15), @l_PAY_BID_ID varchar(15)

		FETCH NEXT FROM PaymentDetail INTO @PERCENT, @AMOUNT, @BID_ID, @PAY_DT, @PAY_STATUS, @NOTES, @P_CONTRACT_CODE

		WHILE @@FETCH_STATUS = 0

		BEGIN

			SELECT @l_PAY_BID_ID = BID_ID FROM TR_CONTRACT WHERE CONTRACT_CODE = @P_CONTRACT_CODE AND REQUEST_ID = @p_REQUEST_ID

			SELECT @BID_ID = REF_ID FROM @TEMP where [TYPE]='BID_MASTER' AND [KEY] = @BID_ID

			SELECT @CONTRACT_ID = REF_ID FROM @TEMP where [TYPE]='TR_CONTRACT' AND [KEY] = @P_CONTRACT_CODE

			EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @l_PAY_ID out

			IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT

			INSERT INTO TR_CONTRACT_PAYMENT([PAY_AMOUNT],[PAY_DT], [PAY_ID],[CONTRACT_ID], [CONTRACT_CODE], [PAY_PHASE],[EXPECTED_DT],

			[PERCENT],[AMOUNT],[PAY_STATUS],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT], [REQUEST_ID], BID_ID)

			VALUES(@AMOUNT,(CASE WHEN @PAY_DT IS NOT NULL AND @PAY_DT <> '' then CONVERT(DATETIME, @PAY_DT, 103) ELSE NULL END),

			 @l_PAY_ID ,@CONTRACT_ID, @P_CONTRACT_CODE,  NULL ,CONVERT(DATETIME, NULL, 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), @p_REQUEST_ID, @BID_ID)

			IF @@Error <> 0 GOTO ABORT

			FETCH NEXT FROM PaymentDetail INTO @PERCENT, @AMOUNT, @BID_ID, @PAY_DT, @PAY_STATUS, @NOTES, @P_CONTRACT_CODE

		END

		CLOSE PaymentDetail

		DEALLOCATE PaymentDetail

		-- CHI PHI SUA CHUA

		DECLARE 

		@ID VARCHAR(15),

		@DT_BID_ID varchar(15),

		@DT_BID_CODE varchar(15),

		@SUPPLIER nvarchar(200),

		@IS_BIDWIN varchar(1),

		@OFFERING_VALUE	decimal(18),

		@CHECK_VALUE decimal(18)

		FETCH NEXT FROM ContractorDetail INTO @ID, @DT_BID_ID, @DT_BID_CODE, @SUPPLIER, @IS_BIDWIN, @OFFERING_VALUE, @CHECK_VALUE

		WHILE @@FETCH_STATUS = 0

		BEGIN

			-- LAY MA HO SO THAU O BANG MASTER

			SELECT @DT_BID_ID = REF_ID FROM @TEMP where [TYPE]='BID_MASTER' AND [KEY] = @DT_BID_CODE

				EXEC SYS_CodeMasters_Gen 'BID_CONTRACTOR_DT', @ID out

				IF @ID='' OR @ID IS NULL GOTO ABORT

				IF @EXP_DT = '' SET @EXP_DT = NULL

				INSERT INTO BID_CONTRACTOR_DT([ID],[SUP_ID],[BID_ID], [BID_CODE], [SEND_DT],[OFFERING_VALUE],[FORM],[EXP_DT],

				[BANK],[VOUCHER],[ISVALID],[IS_BID_WIN],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],

				[CHECKER_ID],[APPROVE_DT],[PROGRESS_TIME], [SUPPLIER], [CHECK_VALUE], [REQUEST_ID])

				VALUES(@ID , NULL ,@DT_BID_ID ,@DT_BID_CODE, NULL ,@OFFERING_VALUE ,NULL, NULL ,NULL ,NULL ,NULL,@IS_BIDWIN ,

				@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,

				CONVERT(DATETIME, @p_APPROVE_DT, 103), NULL, @SUPPLIER, @CHECK_VALUE, @P_REQUEST_ID)

				IF @@ERROR <> 0 GOTO ABORT

				INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@ID, 'BID_CONTRACTOR_DT')

			FETCH NEXT FROM ContractorDetail INTO @ID, @DT_BID_ID, @DT_BID_CODE, @SUPPLIER, @IS_BIDWIN, @OFFERING_VALUE, @CHECK_VALUE

		END

		CLOSE ContractorDetail

		DEALLOCATE ContractorDetail

		-- CHI PHI PHAT SINH TANG GIAM

		DECLARE

		@ARISE_ID VARCHAR(15),

		@BEGIN_VALUE	decimal(18,2),

		@CHECKED_VALUE decimal(18,2)

		FETCH NEXT FROM ListContractorArise into @ARISE_ID, @DT_BID_ID, @DT_BID_CODE, @SUPPLIER, @IS_BIDWIN, @BEGIN_VALUE, @CHECKED_VALUE

		WHILE @@FETCH_STATUS = 0

		BEGIN

			-- LAY MA HO SO THAU O BANG MASTER

			SELECT @DT_BID_ID = REF_ID FROM @TEMP where [TYPE]='BID_MASTER' AND [KEY] = @DT_BID_CODE

			EXEC SYS_CodeMasters_Gen 'CON_BID_CONTRACTOR_DT_ARISE', @ARISE_ID out

			IF @ARISE_ID='' OR @ARISE_ID IS NULL GOTO ABORT

			INSERT INTO CON_BID_CONTRACTOR_DT_ARISE(ARISE_ID,REQ_ID, SUPPLIER, IS_BID_WIN, BEGIN_VALUE, CHECKED_VALUE, BID_ID, BID_CODE)

			VALUES (@ARISE_ID, @P_REQUEST_ID, @SUPPLIER, @IS_BIDWIN, @BEGIN_VALUE, @CHECKED_VALUE, @DT_BID_ID ,@DT_BID_CODE)

			INSERT INTO @TEMP([REF_ID],[TYPE]) VALUES (@ARISE_ID, 'CON_BID_CONTRACTOR_DT_ARISE')

			FETCH NEXT FROM ListContractorArise into @ARISE_ID, @DT_BID_ID, @DT_BID_CODE, @SUPPLIER, @IS_BIDWIN, @BEGIN_VALUE, @CHECKED_VALUE

		END

		CLOSE ListContractorArise

		DEALLOCATE ListContractorArise

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

		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),

			@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)

			print @BID_CODE

			print @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,

				@l_CONSTRUCT_ID,

				@REQDT_ID,

				@BID_CODE

			)

			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

		-- ĐÁNH GIÁ NCC 		

		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)

		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

		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]

			)

			VALUES

			(		

				@CON_REQ_REVIEW_ID,

				@SUP_ID,

				@CONS_PROCESS,

				@MAINTENANCE,

				@REPORT_PLAN_EDIT,

				@CONS_QUALITY,

				@BEAUTY_IMAGE,

				@l_CONSTRUCT_ID,

				@QCKT_REVIEW,

				@SUP_NAME,

				@REQ_CODE,

				@REQ_ID

			)

			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

		END

		CLOSE ListReview

		DEALLOCATE ListReview

		-- TIẾN ĐỘ CÔNG TRÌNH

		DECLARE

			@CON_REQ_PROCESS_ID VARCHAR(15),

			@DATE_REPORT VARCHAR(50),

			@PERCENT_COMPLETED DECIMAL(18,2),

			@PERCENT_REMAIN DECIMAL(18,2),

			@DEADLINE VARCHAR(50)

		FETCH NEXT FROM ListConsProcess into @CON_REQ_PROCESS_ID,@DATE_REPORT, @PERCENT_COMPLETED, @PERCENT_REMAIN, @DEADLINE

		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]

			)

			VALUES

			(

				@CON_REQ_PROCESS_ID,

				CONVERT(DATETIME, @DATE_REPORT, 103),

				@PERCENT_COMPLETED,

				@PERCENT_REMAIN,

				CONVERT(DATETIME, @DEADLINE, 103),

				@l_CONSTRUCT_ID

			)

			FETCH NEXT FROM ListConsProcess INTO @CON_REQ_PROCESS_ID,@DATE_REPORT, @PERCENT_COMPLETED, @PERCENT_REMAIN, @DEADLINE

		END

		CLOSE ListConsProcess

		DEALLOCATE ListConsProcess

COMMIT TRANSACTION

BEGIN

SELECT '0' as Result, @l_CONSTRUCT_ID CONSTRUCT_ID, [REF_ID], [TYPE], '' ErrorDesc FROM @TEMP

RETURN '0'

END

ABORT:

BEGIN

		--CLOSE ListRequestDoc

		--DEALLOCATE ListRequestDoc

		--CLOSE ListBid

		--DEALLOCATE ListBid

		CLOSE ListContract_DT

		DEALLOCATE ListContract_DT

		CLOSE PaymentDetail

		DEALLOCATE PaymentDetail

		CLOSE ContractorDetail

		DEALLOCATE ContractorDetail

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, @l_CONSTRUCT_ID CONSTRUCT_ID, '' [REF_ID], '' [TYPE], @sErrorCode ErrorDesc

		RETURN '-1'

End