EOFFICE - GD4

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO

ALTER   PROCEDURE [dbo].[PL_APPOINT_CONTRACTOR_Upd]

@p_REQ_ID VARCHAR(15)=NULL,

@p_REQ_CODE	nvarchar(100)  = NULL,

@p_REQ_DT	nvarchar(20) = NULL,

@p_REQ_CONTENT NVARCHAR(4000)=NULL,

@p_SURVEY_CONTENT NVARCHAR(4000) = NULL,

@p_TR_REQUEST_DOC_ID VARCHAR(15) = NULL,

@p_SUP_ID VARCHAR(15) = NULL,

@p_PROPOSE_CONTENT NVARCHAR(4000) = NULL,

@p_RECORD_STATUS	varchar(1)  = NULL,

@p_MAKER_ID	varchar(20)  = NULL,

@p_CREATE_DT	varchar(20) = NULL,

@p_AUTH_STATUS	varchar(50)  = NULL,

@p_CHECKER_ID	varchar(20)  = NULL,

@p_APPROVE_DT	nvarchar(20) = NULL,

@p_BRANCH_ID VARCHAR(15)=NULL,

@p_DEP_ID VARCHAR(20)=NULL,

@p_PROCESS_ID VARCHAR(20) = NULL,

@p_DVDM_ID VARCHAR(20) = NULL,

@p_SIGN_USER VARCHAR(20) = NULL,

@p_BASED_CONTENT NVARCHAR(3000) = NULL,

@p_TOTAL_AMT_ETM DECIMAL(18,0) = NULL,

@p_TOTAL_AMT DECIMAL(18,0) = NULL,

@p_HAS_GDK_TC VARCHAR(1) = NULL,

@p_APPOINT_REASON NVARCHAR(4000) = NULL,

@p_CATERGORY_NAME NVARCHAR(4000) = NULL,

@p_SUP_SELECT_CONTENT NVARCHAR(4000) = NULL,

@p_REPORT_TEMPLATE_CONTENT NVARCHAR(MAX) = NULL,

@p_TYPE_PL VARCHAR(20) = NULL,

@p_PL_APPOINT_CONTRACTOR_DT XML

AS	

IF(EXISTS (SELECT * FROM PL_APPOINT_CONTRACTOR WHERE REQ_CODE = @p_REQ_CODE AND REQ_ID <> @p_REQ_ID))

BEGIN

	SELECT ErrorCode Result, '' REQ_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'REQ-00001'

	RETURN '-1'

END

IF(EXISTS (

	SELECT * FROM PL_APPOINT_CONTRACTOR WHERE REQ_ID = @p_REQ_ID 

	AND AUTH_STATUS = 'U' AND PROCESS_ID <> 'NEW' AND PROCESS_ID <> 'APPROVE'))

BEGIN

	SELECT '-1' AS Result ,'' REQ_ID, 

		N'Cập nhật thất bại. Tờ trình chỉ định thầu số :' +@p_REQ_CODE+ N' đang trong trạng thái phê duyệt' ErrorDesc

	RETURN '-1'

END

BEGIN TRANSACTION

	SET @p_AUTH_STATUS = 'E'

	IF(EXISTS (SELECT * FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID AND BRANCH_TYPE = 'HS'))

	BEGIN

		SET @p_DEP_ID = (SELECT DEP_ID FROM TL_USER WHERE TLNANME = @p_MAKER_ID AND TLSUBBRID = @p_BRANCH_ID)

	END

	ELSE

	BEGIN

		SET @p_DEP_ID = ''

	END

	UPDATE PL_APPOINT_CONTRACTOR SET

		REQ_CODE = @p_REQ_CODE,

		REQ_DT = CONVERT(DATETIME,@p_REQ_DT, 103),

		REQ_CONTENT=@p_REQ_CONTENT,

		SURVEY_CONTENT = @p_SURVEY_CONTENT,

		TR_REQUEST_DOC_ID = @p_TR_REQUEST_DOC_ID,

		SUP_ID = @p_SUP_ID,

		PROPOSE_CONTENT = @p_PROPOSE_CONTENT,

		BRANCH_ID=@p_BRANCH_ID,

		DEP_ID=@p_DEP_ID,

--		CREATE_DT = CONVERT(DATETIME, @p_CREATE_DT, 103), -- NGUYENTD 31102023_SECRETKEY: FIX LỖI CẬP NHẬT LUÔN NGÀY TẠO 

		RECORD_STATUS=@p_RECORD_STATUS,

		CHECKER_ID=@p_CHECKER_ID,

		APPROVE_DT=CONVERT(DATETIME,@p_APPROVE_DT, 103),

		AUTH_STATUS=@p_AUTH_STATUS,

		MAKER_ID=@p_MAKER_ID,

		DVDM_APP_ID=@p_DVDM_ID,

		SIGN_USER = @p_SIGN_USER,

		BASED_CONTENT=@p_BASED_CONTENT,

		TOTAL_AMT_ETM = @p_TOTAL_AMT_ETM,

		HAS_GDK_TC = @p_HAS_GDK_TC,

		PROCESS_ID = @p_PROCESS_ID,

		APPOINT_REASON = @p_APPOINT_REASON,

		CATERGORY_NAME = @p_CATERGORY_NAME,

		SUP_SELECT_CONTENT = @p_SUP_SELECT_CONTENT,

		REPORT_TEMPLATE_CONTENT = @p_REPORT_TEMPLATE_CONTENT,

		TYPE_PL = @p_TYPE_PL

	WHERE REQ_ID=@p_REQ_ID

	IF @@Error <> 0 GOTO ABORT

	PRINT 'PASS UPDATE'

	-------------------INSERT DT--------------

	DELETE FROM PL_APPOINT_CONTRACTOR_DT WHERE REQ_ID= @p_REQ_ID

	IF(@p_TOTAL_AMT > @p_TOTAL_AMT_ETM)

	BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' AS Result, '' REQ_ID, 

				N'Lưới mô tả chi tiết chọn nhà cung cấp: ' +

				N'Tổng chi phí sau khi tính toán không được vượt quá tổng chi phí dự kiến của các hạng mục mua sắm được chọn' ErrorDesc

		RETURN '-1'

	END

	Declare @hdoc INT

	EXEC sp_xml_preparedocument @hdoc Output,@p_PL_APPOINT_CONTRACTOR_DT

	DECLARE PlAppointContractorDT CURSOR FOR

	SELECT *

	FROM OPENXML(@hdoc, '/Root/PlAppointContractorDT', 2)

	WITH

	(

		--REQ_ID VARCHAR(15),

		HH_ID VARCHAR(15),

		DESCRIPTION NVARCHAR(4000),

		QUANTITY INT,

		UNIT_PRICE DECIMAL(18,0),

		VAT_AMT DECIMAL(18,2),

		TOTAL_AMT DECIMAL(18,0),

		TOTAL_AMT_ETM DECIMAL(18,0),

		TR_REQ_DOC_DT_ID VARCHAR(15),

		SUP_ID VARCHAR(15)

	)

	OPEN PlAppointContractorDT

	DECLARE 

			--@d_REQ_ID VARCHAR(15),

			@d_HH_ID VARCHAR(15),

			@d_DESCRIPTION NVARCHAR(4000),

			@d_QUANTITY INT,

			@d_UNIT_PRICE DECIMAL(18,2),

			@d_VAT_AMT DECIMAL(18,2),

			@d_TOTAL_AMT DECIMAL(18,0),

			@d_TOTAL_AMT_ETM DECIMAL(18,0),

			@d_TR_REQ_DOC_DT_ID VARCHAR(15),

			@d_SUP_ID VARCHAR(15)

	FETCH NEXT FROM PlAppointContractorDT INTO

		--@d_REQ_ID,

		@d_HH_ID,

		@d_DESCRIPTION,

		@d_QUANTITY,

		@d_UNIT_PRICE,

		@d_VAT_AMT,

		@d_TOTAL_AMT,

		@d_TOTAL_AMT_ETM,

		@d_TR_REQ_DOC_DT_ID,

		@d_SUP_ID

	WHILE @@FETCH_STATUS = 0

	BEGIN

		DECLARE @d_REQ_DT_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'PL_APPOINT_CONTRACTOR_DT', @d_REQ_DT_ID out

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

		PRINT 'PASS GEN ID DT'

		INSERT INTO PL_APPOINT_CONTRACTOR_DT

			(REQ_DT_ID, REQ_ID, HH_ID, DESCRIPTION, QUANTITY, UNIT_PRICE, VAT_AMT, 

			TOTAL_AMT, TOTAL_AMT_ETM, TR_REQ_DOC_DT_ID, SUP_ID)

		VALUES

			(@d_REQ_DT_ID, @p_REQ_ID, @d_HH_ID, @d_DESCRIPTION, @d_QUANTITY, @d_UNIT_PRICE,

			@d_VAT_AMT, @d_TOTAL_AMT, @d_TOTAL_AMT_ETM, @d_TR_REQ_DOC_DT_ID, @d_SUP_ID)

		IF @@Error <> 0 GOTO ABORT

		FETCH NEXT FROM PlAppointContractorDT INTO

			--@d_REQ_ID,

			@d_HH_ID,

			@d_DESCRIPTION,

			@d_QUANTITY,

			@d_UNIT_PRICE,

			@d_VAT_AMT,

			@d_TOTAL_AMT,

			@d_TOTAL_AMT_ETM,

			@d_TR_REQ_DOC_DT_ID,

			@d_SUP_ID

	END

	PRINT 'PASS INSERT DT'

	CLOSE PlAppointContractorDT

	DEALLOCATE PlAppointContractorDT

	DECLARE @p_MESSAGE NVARCHAR(MAX)

	--------tờ trình xin chi phí---------

	IF(@p_TYPE_PL = 'TTCP')

	BEGIN

		SET @p_MESSAGE = N'Cập nhật tờ trình xin chi phí thành công'

	END

	--------tờ trình chỉ định thầu---------

	ELSE

	BEGIN

		SET @p_MESSAGE = N'Cập nhật tờ trình chỉ định thầu thành công'

	END

COMMIT TRANSACTION

SELECT '0' as Result, @p_REQ_ID  REQ_ID,@p_REQ_CODE REQ_CODE, 

	N'Cập nhật tờ trình chỉ định thầu thành công' AS ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' AS Result ,'' REQ_ID, '' ErrorDesc

		RETURN '-1'

End

--10062025_SECRETKEY