DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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