ALTER   PROCEDURE [dbo].[TR_REQ_DOC_XETGIA_DUOI_100M_Ins]
	--@p_REQ_CODE	varchar(15) 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 TRY		
	DECLARE @tr_ADDNEW_ID VARCHAR(15)
	EXEC SYS_CodeMasters_Gen 'TR_REQ_DOC_XETGIA_DUOI_100M', @tr_ADDNEW_ID out

	------------------BAODNQ 5/3/2022 Thêm tự động sinh mã REQ_CODE--------
	DECLARE @p_REQ_CODE VARCHAR(200)
	EXEC TR_REPORT_PRICE_CODE_GenKey 'TR_REPORT_PRICE', '', '', @p_REQ_CODE OUT

	-----------BAODNQ 5/3/2022 Thêm các cột vào bảng TR_REQ_DOC_XETGIA_DUOI_100M--------
	INSERT INTO TR_REQ_DOC_XETGIA_DUOI_100M ([RECORD_ID],[REQ_DOC_ID],[REQ_CODE],[FR_DATE],[CONCLUSION],
		[NOTES], [RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],
		[ABOUT], [WIN_SUPPLIER], [CURRENCY], [TOTAL_SUPPLIERS])
     VALUES (@tr_ADDNEW_ID,@p_REQ_DOC_ID,@p_REQ_CODE,CONVERT(DATETIME,@p_FR_DATE,103),@p_CONCLUSION,
		@p_NOTES, @p_RECORD_STATUS, @p_AUTH_STATUS, @p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),
		@p_CHECKER_ID,
		CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_ABOUT, @p_WIN_SUPPLIER, @p_CURRENCY, 
		@p_TOTAL_SUPPLIERS)

	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
	
	BEGIN TRANSACTION
	------------------------------------------------------------
	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, @tr_ADDNEW_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, @tr_ADDNEW_ID  ADDNEW_ID, @p_REQ_CODE REQ_CODE, 
						N'Thêm mới biên bản xét giá thành công' ErrorDesc
RETURN '0'

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

END TRY

BEGIN CATCH
		ROLLBACK TRANSACTION
		
		SELECT '-1' as Result, '' ADDNEW_ID, @p_REQ_CODE REQ_CODE,ERROR_MESSAGE() ErrorDesc
		RETURN '-1'
END CATCH