IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ExportFrlloRemainsDataProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ExportFrlloRemainsDataProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_ExportFrlloRemainsDataProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_ExportFrlloRemainsDataProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2023 12 04
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE PROCEDURE [rds].[sp_ExportFrlloRemainsDataProcess]
    @ExportId uniqueidentifier,
    @InfoSysCode varchar(100),
    @Region varchar(5),
    @WithoutError bit,
    @Result varchar(max) OUTPUT
AS
BEGIN
	declare @OutputTable table(Val xml)

	Insert into @OutputTable(Val)
		select  
		(
			select @InfoSysCode as 'info_sys_code'																---1
			,(
				select 
				efrd.ExportFrlloRemainsDataID								as 'document/document_id',				---2.1
				getdate()													as 'document/doc_date_time',				---2.2
				efrd.IsDelete												as 'document/stock/is_delete',			--2.3.1
				--efrd.ExportFrlloRemainsDataID								as 'document/stock/stock_id',			--2.3.2
				efrd.FrlloId												as 'document/stock/stock_ext_id',		--2.3.3
				eh.oid														as 'document/stock/med_org_oid',			--2.3.4
				--??														as 'document/stock/se_num',				--2.3.5
				--??														as 'document/stock/se_name',				--2.3.6
				@Region														as 'document/stock/region',				--2.3.7
				Substring(convert(varchar(10), efr.CreateDate, 104), 4, 7)	as 'document/stock/stock_date',			--2.3.8
				efrd.KLPCode												as 'document/stock/drug_klp_code',		--2.3.9
				efrd.MICode													as 'document/stock/med_equip_code',		--2.3.10
				efrd.SpecCode												as 'document/stock/nutrition_code',		--2.3.11
				efrd.PackQty												as 'document/stock/pack_qty',			--2.3.12
				efrd.ItemQty												as 'document/stock/item_qty',			--2.3.13
				convert(varchar(10), efrd.DrugExpirationDt, 126)			as 'document/stock/drug_expiration_dt',	--2.3.14
				efrd.DrugSeria												as 'document/stock/drug_seria'			--2.3.15
			from RDS.T_EXPORT_FRLLO_REMAINS_DATA efrd
			join RDS.T_EXPORT_FRLLO_REMAINS efr on efr.ExportFrlloRemainsID = efrd.ExportFrlloRemains
			join RDS.T_PHARMACY p on p.Code = efrd.Pharmacy
			left join EHR.T_EHR_RUS_MDR308 eh on eh.EhrRusID = p.EhrRusMdr308
			where efrd.ExportFrlloRemains = @ExportId and ex.FileNumber = efrd.FileNumber and (@WithoutError = 0 or efrd.FrlloError is null)
			for xml path(''),
			type
		) as 'documents'
		for xml path(''),
        root('root')
		)
	from RDS.T_EXPORT_FRLLO_REMAINS_DATA ex
	where ex.ExportFrlloRemains = @ExportId and (@WithoutError = 0 or ex.FrlloError is null)
	group by ex.FileNumber

    SELECT @Result = CAST((SELECT val FROM  @OutputTable FOR XML PATH(''), ROOT('root_list'), TYPE) AS VARCHAR(MAX));
END				