ALTER   PROCEDURE [dbo].[TR_REQ_DOC_XETGIA_DUOI_100M_Upd]
	@p_REQ_CODE	varchar(200) NULL,
	@p_FR_DATE varchar(30)  NULL,
	@p_CONCLUSION nvarchar(MAX) NULL,
	@p_NOTES nvarchar(MAX) NULL,
	@p_RECORD_ID varchar(15) NULL,
	@p_REQ_DOC_ID varchar(15) NULL,
	--------BAODNQ 5/3/2022 : Truyền thêm tham số---------
	@p_RECORD_STATUS VARCHAR(1) = NULL,
	@p_AUTH_STATUS VARCHAR(1) = NULL,
	@p_MAKER_ID VARCHAR(15) = NULL,
	@p_CREATE_DT VARCHAR(20) = NULL,
	@p_CHECKER_ID VARCHAR(15) = NULL,
	@p_APPROVE_DT VARCHAR(20) = NULL,
	@p_ABOUT NVARCHAR(4000) = NULL,
	@p_WIN_SUPPLIER NVARCHAR(1000) = NULL,
	@p_CURRENCY VARCHAR(15) = NULL,
	@p_TOTAL_SUPPLIERS INT = NULL,
	---------------
	@p_ADDNEW_DT xml
AS
	

	BEGIN TRANSACTION
	------------------------------------------------------------
	UPDATE TR_REQ_DOC_XETGIA_DUOI_100M SET
		[REQ_CODE]=@p_REQ_CODE,
		[FR_DATE]=CONVERT(DATETIME,@p_FR_DATE,103),
		[CONCLUSION]=@p_CONCLUSION,
		[NOTES]=@p_NOTES,
		[RECORD_STATUS] = @p_RECORD_STATUS,
		[AUTH_STATUS] = @p_AUTH_STATUS,
		[MAKER_ID] = @p_MAKER_ID,
		[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),
		[CHECKER_ID] = @p_CHECKER_ID,
		[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103),
		[ABOUT] = @p_ABOUT,
		[WIN_SUPPLIER] = @p_WIN_SUPPLIER,
		[CURRENCY] = @p_CURRENCY,
		[TOTAL_SUPPLIERS] = @p_TOTAL_SUPPLIERS

		WHERE [RECORD_ID]= @p_RECORD_ID
	DELETE TR_REQ_DOC_XETGIA_DUOI_100M_DT WHERE RECORD_MASTER_ID = @p_RECORD_ID
	DECLARE @hdoc int
	Exec sp_xml_preparedocument @hdoc Output, @p_ADDNEW_DT	
	DECLARE AddnewDT CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/ADDNEWDT',2)
	WITH 
	(
		RECORD_MASTER_ID	varchar(15),
		GOOD_NAME	nvarchar(250),
		HH_ID VARCHAR(15),
		TECH_SPECIFICATION	nvarchar(MAX),
		UNIT	nvarchar(100),
		QUANTITY	int,

		SUPPLIER_1	nvarchar(100),
		UNIT_PRICE_1	numeric(18, 2),
		TOTAL_AMT_1	numeric(18, 2),
		VAT_AMT_1 NUMERIC(18,2),
		TOTAL_AMT_AFTER_VAT_1 NUMERIC(18,2),

		SUPPLIER_2	nvarchar(100),
		UNIT_PRICE_2	numeric(18, 2),
		TOTAL_AMT_2	numeric(18, 2),
		VAT_AMT_2 NUMERIC(18,2),
		TOTAL_AMT_AFTER_VAT_2 NUMERIC(18,2),

		SUPPLIER_3	nvarchar(100),
		UNIT_PRICE_3	numeric(18, 2),
		TOTAL_AMT_3	numeric(18, 2),
		VAT_AMT_3 NUMERIC(18,2),
		TOTAL_AMT_AFTER_VAT_3 NUMERIC(18,2),

		SUPPLIER_4	nvarchar(100),
		UNIT_PRICE_4	numeric(18, 2),
		TOTAL_AMT_4	numeric(18, 2),
		VAT_AMT_4 NUMERIC(18,2),
		TOTAL_AMT_AFTER_VAT_4 NUMERIC(18,2),

		SUPPLIER_5	nvarchar(100),
		UNIT_PRICE_5	numeric(18, 2),
		TOTAL_AMT_5	numeric(18, 2),
		VAT_AMT_5 NUMERIC(18,2),
		TOTAL_AMT_AFTER_VAT_5 NUMERIC(18,2),

		NOTES	nvarchar(MAX),
		RECORD_DT_ID varchar(15),
		TR_REQ_DOC_DT_ID VARCHAR(15),
		WIN_SUPPLIER_PRICE DECIMAL(18,2)
	)
	OPEN AddnewDT

	DECLARE @RECORD_MASTER_ID	varchar(15),
		@GOOD_NAME	nvarchar(250),
		@HH_ID VARCHAR(15),
		@TECH_SPECIFICATION	nvarchar(MAX),
		@UNIT	nvarchar(100),
		@QUANTITY	int,

		@SUPPLIER_1	nvarchar(100),
		@UNIT_PRICE_1	numeric(18, 2),
		@TOTAL_AMT_1	numeric(18, 2),
		@VAT_AMT_1 NUMERIC(18,2),
		@TOTAL_AMT_AFTER_VAT_1 NUMERIC(18,2),

		@SUPPLIER_2	nvarchar(100),
		@UNIT_PRICE_2	numeric(18, 2),
		@TOTAL_AMT_2	numeric(18, 2),
		@VAT_AMT_2 NUMERIC(18,2),
		@TOTAL_AMT_AFTER_VAT_2 NUMERIC(18,2),

		@SUPPLIER_3	nvarchar(100),
		@UNIT_PRICE_3	numeric(18, 2),
		@TOTAL_AMT_3	numeric(18, 2),
		@VAT_AMT_3 NUMERIC(18,2),
		@TOTAL_AMT_AFTER_VAT_3 NUMERIC(18,2),

		@SUPPLIER_4	NVARCHAR(100),
		@UNIT_PRICE_4	NUMERIC(18, 2),
		@TOTAL_AMT_4	NUMERIC(18, 2),
		@VAT_AMT_4 NUMERIC(18,2),
		@TOTAL_AMT_AFTER_VAT_4 NUMERIC(18,2),

		@SUPPLIER_5	NVARCHAR(100),
		@UNIT_PRICE_5	NUMERIC(18, 2),
		@TOTAL_AMT_5	NUMERIC(18, 2),
		@VAT_AMT_5 NUMERIC(18,2),
		@TOTAL_AMT_AFTER_VAT_5 NUMERIC(18,2),

		@NOTES	nvarchar(MAX),
		@RECORD_DT_ID	varchar(15),
		@TR_REQ_DOC_DT_ID VARCHAR(15),
		@WIN_SUPPLIER_PRICE DECIMAL(18,2)


	FETCH NEXT FROM AddnewDT INTO 
		@RECORD_MASTER_ID, 
		@GOOD_NAME,
		@HH_ID,
		@TECH_SPECIFICATION, 
		@UNIT, 
		@QUANTITY,
		
		@SUPPLIER_1, 
		@UNIT_PRICE_1,
		@TOTAL_AMT_1,
		@VAT_AMT_1,
		@TOTAL_AMT_AFTER_VAT_1,

		@SUPPLIER_2, 
		@UNIT_PRICE_2,
		@TOTAL_AMT_2,
		@VAT_AMT_2,
		@TOTAL_AMT_AFTER_VAT_2,

		@SUPPLIER_3, 
		@UNIT_PRICE_3,
		@TOTAL_AMT_3,
		@VAT_AMT_3,
		@TOTAL_AMT_AFTER_VAT_3,

		@SUPPLIER_4,
		@UNIT_PRICE_4,
		@TOTAL_AMT_4,
		@VAT_AMT_4,
		@TOTAL_AMT_AFTER_VAT_4,

		@SUPPLIER_5,
		@UNIT_PRICE_5,
		@TOTAL_AMT_5,
		@VAT_AMT_5,
		@TOTAL_AMT_AFTER_VAT_5,

		@NOTES, 
		@RECORD_DT_ID,
		@TR_REQ_DOC_DT_ID,
		@WIN_SUPPLIER_PRICE
	
	WHILE @@FETCH_STATUS = 0	
	BEGIN			
		DECLARE @tr_ADDNEWDT_ID VARCHAR(15)
		EXEC SYS_CodeMasters_Gen 'TR_REQ_DOC_XETGIA_DUOI_100M_DT', @tr_ADDNEWDT_ID out

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

		----------Tính giá trị thành tiền trước thuế----------
		SET @TOTAL_AMT_1 = @UNIT_PRICE_1 * @QUANTITY
		SET	@TOTAL_AMT_2 = @UNIT_PRICE_2 * @QUANTITY
		SET	@TOTAL_AMT_3 = @UNIT_PRICE_3 * @QUANTITY
		SET @TOTAL_AMT_4 = @UNIT_PRICE_4 * @QUANTITY
		SET @TOTAL_AMT_5 = @UNIT_PRICE_5 * @QUANTITY

		INSERT INTO TR_REQ_DOC_XETGIA_DUOI_100M_DT
			([RECORD_DT_ID],[RECORD_MASTER_ID],[GOOD_NAME],[TECH_SPECIFICATION],[UNIT],[QUANTITY],
			[SUPPLIER_1],[UNIT_PRICE_1], [TOTAL_AMT_1], [VAT_AMT_1], [TOTAL_AMT_AFTER_VAT_1],[SUPPLIER_2],
			[UNIT_PRICE_2],[TOTAL_AMT_2], [VAT_AMT_2],[TOTAL_AMT_AFTER_VAT_2],[SUPPLIER_3],[UNIT_PRICE_3], 
			[TOTAL_AMT_3], [VAT_AMT_3],[TOTAL_AMT_AFTER_VAT_3], [SUPPLIER_4],[UNIT_PRICE_4], [TOTAL_AMT_4],
			[VAT_AMT_4],[TOTAL_AMT_AFTER_VAT_4], [SUPPLIER_5],[UNIT_PRICE_5], [TOTAL_AMT_5], [VAT_AMT_5],
			[TOTAL_AMT_AFTER_VAT_5],[NOTES], TR_REQ_DOC_DT_ID, WIN_SUPPLIER_PRICE, HH_ID)
		VALUES
			(@tr_ADDNEWDT_ID, @p_RECORD_ID, @GOOD_NAME, @TECH_SPECIFICATION, @UNIT, @QUANTITY, 
			@SUPPLIER_1, @UNIT_PRICE_1, @TOTAL_AMT_1, @VAT_AMT_1, @TOTAL_AMT_AFTER_VAT_1,@SUPPLIER_2, 
			@UNIT_PRICE_2, @TOTAL_AMT_2, @VAT_AMT_2, @TOTAL_AMT_AFTER_VAT_2, @SUPPLIER_3, @UNIT_PRICE_3, 
			@TOTAL_AMT_3, @VAT_AMT_3, @TOTAL_AMT_AFTER_VAT_3, @SUPPLIER_4, @UNIT_PRICE_4, @TOTAL_AMT_4, 
			@VAT_AMT_4,@TOTAL_AMT_AFTER_VAT_4, @SUPPLIER_5, @UNIT_PRICE_5, @TOTAL_AMT_5, @VAT_AMT_5,
			@TOTAL_AMT_AFTER_VAT_5, @NOTES, @TR_REQ_DOC_DT_ID, @WIN_SUPPLIER_PRICE, @HH_ID)

		FETCH NEXT FROM AddnewDT INTO 
			@RECORD_MASTER_ID, 
			@GOOD_NAME,
			@HH_ID,
			@TECH_SPECIFICATION, 
			@UNIT, 
			@QUANTITY,
			
			@SUPPLIER_1, 
			@UNIT_PRICE_1,
			@TOTAL_AMT_1,
			@VAT_AMT_1,
			@TOTAL_AMT_AFTER_VAT_1,

			@SUPPLIER_2, 
			@UNIT_PRICE_2,
			@TOTAL_AMT_2,
			@VAT_AMT_2,
			@TOTAL_AMT_AFTER_VAT_2,

			@SUPPLIER_3, 
			@UNIT_PRICE_3,
			@TOTAL_AMT_3,
			@VAT_AMT_3,
			@TOTAL_AMT_AFTER_VAT_3,

			@SUPPLIER_4,
			@UNIT_PRICE_4,
			@TOTAL_AMT_4,
			@VAT_AMT_4,
			@TOTAL_AMT_AFTER_VAT_4,

			@SUPPLIER_5,
			@UNIT_PRICE_5,
			@TOTAL_AMT_5,
			@VAT_AMT_5,
			@TOTAL_AMT_AFTER_VAT_5,

			@NOTES, 
			@RECORD_DT_ID,
			@TR_REQ_DOC_DT_ID,
			@WIN_SUPPLIER_PRICE
		
		PRINT @tr_ADDNEWDT_ID
	END
	CLOSE AddnewDT
	DEALLOCATE AddnewDT

COMMIT TRANSACTION
SELECT '0' as Result, @p_RECORD_ID  RECORD_ID, 
		N'Cập nhật biên bản xét giá thành công' ErrorDesc
RETURN '0'

ABORT:
BEGIN
		ROLLBACK TRANSACTION
		
		SELECT '-1' as Result, '' ADDNEW_ID, '' ErrorDesc
		RETURN '-1'
End