IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_LoadAnswerFromFrlloRemainsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_LoadAnswerFromFrlloRemainsProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_LoadAnswerFromFrlloRemainsProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_LoadAnswerFromFrlloRemainsProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2023 12 04
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_LoadAnswerFromFrlloRemainsProcess]
    @ExportId uniqueidentifier,
    @Xml xml
AS
BEGIN
	select
		t.c.value('./document_id[1]', 'int')	            as [document_id]
		,t.c.value('./status[1]', 'int')					as [status]
		,t.c.value('./stock_id[1]', 'varchar(100)')			as [stock_id]
		,err.c.value('./code[1]', 'varchar(max)')			as [code]
		,err.c.value('./description[1]', 'varchar(max)')	as [description]
	INTO #Answer1
		from @Xml.nodes('root/result/element') T(c)
	outer apply t.c.nodes('./errors/element') err(c)
	

	CREATE TABLE #TMP_UPD(
		 [id] int identity(1,1)
		,[stock_id] varchar(100)
		,[status] int
		,[error] varchar(max)
		,[ExportFrlloRemains] uniqueidentifier
		,[ExportFrlloRemainsDataID] int
	)

	DECLARE @DT TABLE(ID INT)
	
	INSERT INTO #TMP_UPD
	SELECT 
		 a.stock_id
		,a.[status]
		,(select a2.code + '-' + a2.description + '/' from #Answer1 a2 where a2.document_id = a.document_id for xml path('')) as error
		,fed.ExportFrlloRemains
		,fed.ExportFrlloRemainsDataID
	from RDS.T_EXPORT_FRLLO_REMAINS_DATA fed
	cross apply (
		select 
			a.stock_id
			, a.status
			, (select a2.code + '-' + a2.description + '/' from #Answer1 a2 where a2.document_id = a.document_id for xml path('')) as error
		    ,a.document_id
		from #Answer1 a 
			where fed.ExportFrlloRemains = @ExportId and a.document_id = fed.ExportFrlloRemainsDataID
		group by a.document_id, a.stock_id, a.status
	) a 

	
	WHILE EXISTS(SELECT 1 FROM #TMP_UPD)
	BEGIN
	
		UPDATE TOP (1000) FED
			SET fed.FrlloId = upd.stock_id,
				fed.FrlloStatus = upd.status,
				fed.FrlloError = upd.error
		OUTPUT UPD.ID INTO @DT(ID)
		FROM RDS.T_EXPORT_FRLLO_REMAINS_DATA fed
			JOIN #TMP_UPD upd ON fed.ExportFrlloRemains = upd.[ExportFrlloRemains] and upd.[ExportFrlloRemainsDataID] = fed.ExportFrlloRemainsDataID

		DELETE U
		FROM #TMP_UPD U
		WHERE EXISTS(SELECT 1 FROM @DT D WHERE D.ID = U.ID)

		DELETE @DT

	END
END				
				