ALTER PROCEDURE [dbo].[BID_MASTER_Upd]
@p_BID_ID VARCHAR(15),
@p_BID_CODE	varchar(50)  = NULL,
@p_BID_YPE	varchar(1)  = NULL,
@p_PROJECT_ID	varchar(15)  = NULL,
@p_TERM_BID	nvarchar(200)  = NULL,
@p_FORM	varchar(4)  = NULL,
@p_INPUT_DT	VARCHAR(20) = NULL,
@p_EXP_DT	VARCHAR(20) = NULL,
@p_OPEN_DT	VARCHAR(20) = NULL,
@p_BUDGET	decimal(18)  = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_GUARANTEE_AMT	decimal(18)  = NULL,
@p_GUARANTEE_PER	decimal(18)  = NULL,
@p_GUARANTEE_EXP	VARCHAR(20) = 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_BID_CATEGORY VARCHAR(15)=NULL,
@p_Contractor_Det XML = NULL

AS
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@p_Contractor_Det
	DECLARE ContractorDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ContractorDetail',2)
	WITH 
	(
		ID VARCHAR(15),
		SUP_ID	varchar(15)  ,
		SEND_DT	VARCHAR(20)  ,
		OFFERING_VALUE	decimal(18),
		FORM	varchar(4),
		EXP_DT	VARCHAR(20),
		BANK	nvarchar(200),
		VOUCHER	varchar(20) ,
		PROGRESS_TIME INT,
		ISVALID varchar(1),
		IS_BID_WIN	varchar(1) ,
		NOTES nvarchar(1000)
	)
	OPEN ContractorDetail
	-- Put validation here
	
BEGIN TRANSACTION
	----
	--IF(@p_BID_CATEGORY IS NOT NULL AND @p_BID_CATEGORY <>'')
	--BEGIN
		
		IF(NOT EXISTS(SELECT BID_ID FROM dbo.BID_MASTER WHERE BID_ID=@p_BID_ID AND BID_CATEGORY =@p_BID_CATEGORY))
		BEGIN
		EXEC dbo.BID_CODE_GenKey 'BID_MASTER', @p_BID_CATEGORY, @p_BID_CODE OUTPUT 
		END
		-- Kiem tra ma ke hoach co ton tai hay chua
	IF EXISTS(SELECT * FROM BID_MASTER WHERE [BID_CODE]=@p_BID_CODE AND [BID_ID] != @p_BID_ID)
	BEGIN
		ROLLBACK TRANSACTION
		SELECT ErrorCode Result, '' BID_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'BID-00001'
		RETURN '0'
	END

	----
	UPDATE BID_MASTER SET [BID_CODE] = @p_BID_CODE, [BID_TYPE] = @p_BID_YPE, [PROJECT_ID] = @p_PROJECT_ID,[TERM_BID] = @p_TERM_BID,[FORM] = @p_FORM,[INPUT_DT] = CONVERT(DATETIME, @p_INPUT_DT, 103),[EXP_DT] = CONVERT(DATETIME, @p_EXP_DT, 103),[OPEN_DT] = CONVERT(DATETIME, @p_OPEN_DT, 103),[BUDGET] = @p_BUDGET,[TOTAL_AMT] = @p_TOTAL_AMT,[GUARANTEE_AMT] = @p_GUARANTEE_AMT,[GUARANTEE_PER] = @p_GUARANTEE_PER,[GUARANTEE_EXP] = CONVERT(DATETIME, @p_GUARANTEE_EXP, 103),[NOTES] = @p_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] = CONVERT(DATETIME, @p_APPROVE_DT, 103),BID_CATEGORY=@p_BID_CATEGORY
	WHERE  BID_ID= @p_BID_ID
	IF @@Error <> 0 GOTO ABORT
		DECLARE 
		@ID VARCHAR(15),
		@SUP_ID	varchar(15)  ,
		@SEND_DT	VARCHAR(20)  ,
		@OFFERING_VALUE	decimal(18),
		@FORM	varchar(4),
		@EXP_DT	VARCHAR(20),
		@BANK	nvarchar(200),
		@VOUCHER	varchar(20) ,
		@ISVALID varchar(1),
		@IS_BID_WIN	varchar(1) ,
		@PROGRESS_TIME INT,
		@NOTES nvarchar(1000)
		FETCH NEXT FROM ContractorDetail INTO @ID,@SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
		@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
		
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF(@ID IS NOT NULL AND @ID <>'')
			BEGIN
				UPDATE dbo.BID_CONTRACTOR_DT SET SUP_ID=@SUP_ID,OFFERING_VALUE=@OFFERING_VALUE,@SEND_DT=@SEND_DT,FORM=@FORM,EXP_DT=@EXP_DT,BANK=@BANK,
				VOUCHER=@VOUCHER,PROGRESS_TIME=@PROGRESS_TIME,ISVALID=ISVALID,IS_BID_WIN=@IS_BID_WIN,NOTES=@NOTES WHERE ID=@ID
			END
			ELSE
			BEGIN
			
				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],[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])
				VALUES(@ID ,@SUP_ID ,@P_BID_ID ,CONVERT(DATETIME, @SEND_DT, 103) ,@OFFERING_VALUE ,@FORM,
				CASE WHEN @EXP_DT IS NOT NULL AND @EXP_DT <> '' then CONVERT(DATETIME, @EXP_DT, 103) ELSE NULL END ,@BANK ,@VOUCHER ,@ISVALID,@IS_BID_WIN ,
				@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),@PROGRESS_TIME)
			END
			--IF @@ERROR <> 0 GOTO ABORT
			FETCH NEXT FROM ContractorDetail INTO @ID,@SUP_ID,@SEND_DT,@OFFERING_VALUE,@FORM,
			@EXP_DT,@BANK,@VOUCHER,@PROGRESS_TIME,@ISVALID,@IS_BID_WIN,@NOTES
		END

		CLOSE ContractorDetail
		DEALLOCATE ContractorDetail

COMMIT TRANSACTION
SELECT '0' as Result, @P_BID_ID  BID_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		CLOSE ContractorDetail
		DEALLOCATE ContractorDetail
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' BID_ID, '' ErrorDesc
		RETURN '-1'
End