-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Форм выпуска препарата(TRN) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_rds_billofgoods;

CREATE PROCEDURE sp_rds_billofgoods
(
	"Xml" text DEFAULT NULL
,	"IsNew" boolean DEFAULT '1'
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"User" uuid DEFAULT NULL::uuid
,	"PATH"  character varying DEFAULT null::character varying
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'BILL_OF_GOODS';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		-- Operation types
		_INSERT varchar(6) := 'Insert';
		_UPDATE varchar(6) := 'Update';
		_DELETE varchar(6) := 'Delete';
		_FTLOAD varchar(6) := 'FTLoad';
		_sql text;
		_EntId int;
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
		
		DROP TABLE IF EXISTS TEMP_DOSE_VIEW;
		CREATE TEMP TABLE TEMP_DOSE_VIEW(
			value	varchar(1000),
			id		int
		);
		
		INSERT INTO TEMP_DOSE_VIEW(value, id)
		select REPLACE(dv.Value, ' ', '') as value, MAX(dv.Id) as Id
		from PHARM.S_DLO_DoseView dv
		group by REPLACE(dv.Value, ' ', '');
	
		IF "PATH" IS NOT NULL
		then
			DROP TABLE IF EXISTS TEMP_BILL_OF_GOODS_DOSE;
			CREATE TEMP TABLE TEMP_BILL_OF_GOODS_DOSE(
				BillOfGoodsID					uuid
				,OtherID						INT
				,Active							BIT
				,CTrn							uuid
				,DoseView						varchar(1000)
				,DoseViewRep					varchar(1000)
				,Rate							INT
				,PackQuantity					INT
				,IsVital						BIT
				,FederalID						INT
				,ExportCode						INT
				,EAN							VARCHAR(255)
				,ProductInfo					VARCHAR(512)
				,Dlo							BIT
				,RoundToWriteOff				BIT
				,FedCode						VARCHAR(50)
				,RegCode						VARCHAR(50)
				,Gtin							VARCHAR(14)
				,MedicamentsOfRoszdravnadzor	uuid
				,EhrRus2m69i03gtd				INT
				,EhrRus2m69i03gtdVer			VARCHAR(8)
				,EhrRus9a9g09e8kl				VARCHAR(52)
				,EhrRus9a9g09e8klVer			VARCHAR(8)
				,MedicationForms				uuid
				,MedicamentProducer				uuid
				,EhrRusClp14fvirj				INT
				,EhrRusClp14fvirjVer			VARCHAR(8)	
				,EhrRusH17F8UTS3N				INT
				,EhrRusH17F8UTS3NVer			VARCHAR(8)	
				,EhrRusDsg207					INT
				,EhrRusDsg207Ver				VARCHAR(8));
	
			_sql := 'insert into TEMP_BILL_OF_GOODS_DOSE(
							BillOfGoodsID					
							,OtherID						
							,Active							
							,CTrn							
							,DoseView						
							,DoseViewRep					
							,Rate							
							,PackQuantity					
							,IsVital						
							,FederalID						
							,ExportCode						
							,EAN							
							,ProductInfo					
							,Dlo						
							,RoundToWriteOff							
							,FedCode						
							,RegCode						
							,Gtin							
							,MedicamentsOfRoszdravnadzor	
							,EhrRus2m69i03gtd				
							,EhrRus2m69i03gtdVer			
							,EhrRus9a9g09e8kl				
							,EhrRus9a9g09e8klVer			
							,MedicationForms				
							,MedicamentProducer				
							,EhrRusClp14fvirj				
							,EhrRusClp14fvirjVer			
							,EhrRusH17F8UTS3N				
							,EhrRusH17F8UTS3NVer			
							,EhrRusDsg207					
							,EhrRusDsg207Ver)  
				 SELECT  BillOfGoodsID
				,OtherID
				,Active
				,CTrn
				,dv.Value
				,REPLACE(dv.Value, '' '', '''')
				,Rate
				,PackQuantity
				,IsVital
				,FederalID
				,ExportCode
				,EAN
				,ProductInfo
				,Dlo
				,RoundToWriteOff
				,FedCode
				,RegCode
				,Gtin
				,MedicamentsOfRoszdravnadzor
				,ehr.IdEHR
				,ehr.CurrVersion
				,ehr1.CodeEHR
				,ehr1.CurrVersion
				,MedicationForms
				,MedicamentProducer
				,ehr2.IdEHR	
				,ehr2.CurrVersion
				,ehr3.IdEHR	
				,ehr3.CurrVersion
				,ehr4.IdEHR	
				,ehr4.CurrVersion
				FROM dblink(''dbname=' || "PATH" 
				|| ''','
				|| ''' SELECT bgl.BillOfGoodsID
				,bgl.OtherID
				,bgl.Active
				,bgl.CTrn
				,bgl.dv.Value
				,bgl.REPLACE(dv.Value, '' '', '''')
				,bgl.Rate
				,bgl.PackQuantity
				,bgl.IsVital
				,bgl.FederalID
				,bgl.ExportCode
				,bgl.EAN
				,bgl.ProductInfo
				,bgl.Dlo
				,bgl.RoundToWriteOff
				,bgl.FedCode
				,bgl.RegCode
				,bgl.Gtin
				,bgl.MedicamentsOfRoszdravnadzor
				,ehr.IdEHR
				,ehr.CurrVersion
				,ehr1.CodeEHR
				,ehr1.CurrVersion
				,bgl.MedicationForms
				,bgl.MedicamentProducer
				,ehr2.IdEHR	
				,ehr2.CurrVersion
				,ehr3.IdEHR	
				,ehr3.CurrVersion
				,ehr4.IdEHR	
				,ehr4.CurrVersion
					FROM PHARM.T_BILL_OF_GOODS bgl
					left join PHARM.S_DLO_DoseView dv on dv.id = bgl.DoseView
			        left join EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bgl.EhrRus2m69i03gtd
			        	LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			        left join EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bgl.EhrRus9a9g09e8kl
			        	LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
			        left join EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bgl.EhrRusClp14fvirj
			        	LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
			        left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bgl.EhrRusH17F8UTS3N
			        	LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
			        left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bgl.EhrRusDsg207
			        	LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
					'') 
					AS bg(
				BillOfGoodsID					uuid
				,OtherID						INT
				,Active							BIT
				,CTrn							uuid
				,DoseView						varchar(1000)
				,DoseViewRep					varchar(1000)
				,Rate							INT
				,PackQuantity					INT
				,IsVital						BIT
				,FederalID						INT
				,ExportCode						INT
				,EAN							VARCHAR(255)
				,ProductInfo					VARCHAR(512)
				,Dlo							BIT
				,RoundToWriteOff				BIT
				,FedCode						VARCHAR(50)
				,RegCode						VARCHAR(50)
				,Gtin							VARCHAR(14)
				,MedicamentsOfRoszdravnadzor	uuid
				,EhrRus2m69i03gtd				INT
				,EhrRus2m69i03gtdVer			VARCHAR(8)
				,EhrRus9a9g09e8kl				VARCHAR(52)
				,EhrRus9a9g09e8klVer			VARCHAR(8)
				,MedicationForms				uuid
				,MedicamentProducer				uuid
				,EhrRusClp14fvirj				INT
				,EhrRusClp14fvirjVer			VARCHAR(8)	
				,EhrRusH17F8UTS3N				INT
				,EhrRusH17F8UTS3NVer			VARCHAR(8)	
				,EhrRusDsg207					INT
				,EhrRusDsg207Ver				VARCHAR(8)) ';
	
			EXECUTE _SQL;
			
			-- Первичная загрузка
			-- Загружаем новые DoseView
			INSERT INTO PHARM.S_DLO_DoseView(Value)
			SELECT Max(bgl.DoseView)
			FROM TEMP_BILL_OF_GOODS_DOSE bgl
			WHERE bgl.DoseView IS NOT NULL 
			AND NOT EXISTS(SELECT * FROM TEMP_DOSE_VIEW dv where dv.Value = bgl.DoseViewRep)
			GROUP BY bgl.DoseViewRep;

			--пересоздаем #PharmDV
			truncate table TEMP_DOSE_VIEW;
			insert into TEMP_DOSE_VIEW(Value, Id)
			select REPLACE(dv.Value, ' ', '') as value, MAX(dv.Id) as Id
			from PHARM.S_DLO_DoseView dv
			group by REPLACE(dv.Value, ' ', '');
		
			-- TRN не из справочника RDS переводим в неактивные
			UPDATE PHARM.T_BILL_OF_GOODS bf
			set Active = '0'
			where not exists(select* from Temp_BILL_OF_GOODS_DOSE bgl where bgl.BillOfGoodsID = bf.BillOfGoodsID);
		
			-- Обновляем имеющиеся TRN
			update PHARM.T_BILL_OF_GOODS bf
			set DoseView = dv.Id
			,	OtherID = bgl.OtherID
			,	IsVital = coalesce(bgl.IsVital, '0')
			,	Active = bgl.Active
			,	Rate = coalesce(bgl.Rate, 1)
			,	ExportCode = bgl.ExportCode
			,	PackQuantity = bgl.PackQuantity
			,	Name = coalesce(bgl.Name, bf.Name)
			,	FederalID = bgl.FederalID
			,	MedicamentProducer = bgl.MedicamentProducer
			,	CTrn = bgl.CTrn
			,	MedicationForms = bgl.MedicationForms
			,	ProductInfo = bgl.ProductInfo
			,	Dlo = coalesce(bgl.Dlo, '0')
			,	RoundToWriteOff = coalesce(bgl.RoundToWriteOff, '0')
			,	Gtin = bgl.Gtin
			,	EAN = bgl.EAN
			,	MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
			,	RegCode = bgl.RegCode
			,	FedCode = bgl.FedCode
			,	EhrRus2m69i03gtd = ehr.EhrRusID
			,	EhrRus9a9g09e8kl = ehr1.EhrRusID
			,	EhrRusClp14fvirj = ehr2.EhrRusID
			,	EhrRusH17F8UTS3N = ehr3.EhrRusID
			,	EhrRusDsg207	 = ehr4.EhrRusID
			from Temp_BILL_OF_GOODS_DOSE bgl
		    left join 
				PHARM.S_DLO_DoseView dv 
				on dv.Value = bgl.DoseView
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_2M69I03GTD er
					 JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
				limit 1
			) ehr on true
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_9A9G09E8KL er
					 JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = bgl.EhrRus9a9g09e8kl AND rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
				limit 1
			) ehr1 on true
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_CLP14FVIRJ er
					 JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = bgl.EhrRusClp14fvirj AND rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
				limit 1
			) ehr2 on true
			left join lateral (
				select prep.EhrRusID 
				from EHR.T_EHR_RUS_H17F8UTS3N prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
				where prep.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
				limit 1
			) ehr3 on true
			left join lateral (
				select  prep.EhrRusID 
				from EHR.T_EHR_RUS_DSG207 prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
				where prep.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
				limit 1
			) ehr4 on true
			where bgl.BillOfGoodsID = bf.BillOfGoodsID;
		
			-- Загружаем новые TRN
			INSERT INTO PHARM.T_BILL_OF_GOODS(BillOfGoodsID
				,CTrn
				,Active
				,MedicamentProducer
				,OtherID
				,MedicationForms
				,DoseView
				,Rate
				,IsVital
				,PackQuantity
				,ExportCode
				,FederalID
				,ProductInfo
				,Dlo
				,RoundToWriteOff
				,Gtin
				,EAN
				,MedicamentsOfRoszdravnadzor
				,RegCode
				,FedCode
				,EhrRus2m69i03gtd
				,EhrRus9a9g09e8kl
				,EhrRusClp14fvirj
				,EhrRusH17F8UTS3N
				,EhrRusDsg207)
			SELECT bgl.BillOfGoodsID
				,bgl.CTrn
				,bgl.Active
				,bgl.MedicamentProducer
				,bgl.OtherID
				,bgl.MedicationForms
				,dv.Id
				,coalesce(bgl.Rate, 1)
				,coalesce(bgl.IsVital,'0')
				,bgl.PackQuantity
				,bgl.ExportCode
				,bgl.FederalID
				,bgl.ProductInfo
				,coalesce(bgl.Dlo,'0')
				,coalesce(bgl.RoundToWriteOff,'0')
				,bgl.Gtin
				,bgl.EAN
				,bgl.MedicamentsOfRoszdravnadzor
				,RegCode
				,FedCode
				,ehr.EhrRusID
				,ehr1.EhrRusID
				,ehr2.EhrRusID
				,ehr3.EhrRusID
				,ehr4.EhrRusID
			, bgl.Gtin, bgl.EAN, bgl.MedicamentsOfRoszdravnadzor, bgl.RegCode, bgl.FedCode, ehr.EhrRusID, ehr1.EhrRusID, ehr2.EhrRusID
			FROM Temp_BILL_OF_GOODS_DOSE bgl
		    left join PHARM.S_DLO_DoseView dv on dv.Value = bgl.DoseView
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_2M69I03GTD er
					 JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
				limit 1
			) ehr on true
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_9A9G09E8KL er
					 JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = bgl.EhrRus9a9g09e8kl AND rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
				limit 1
			) ehr1 on true
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_CLP14FVIRJ er
					 JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = bgl.EhrRusClp14fvirj AND rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
				limit 1
			) ehr2 on true
			left join lateral (
				select prep.EhrRusID 
				from EHR.T_EHR_RUS_H17F8UTS3N prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
				where prep.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
				limit 1
			) ehr3 on true
			left join lateral (
				select  prep.EhrRusID 
				from EHR.T_EHR_RUS_DSG207 prep
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
				where prep.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
				limit 1
			) ehr4 on true
			WHERE NOT EXISTS(SELECT* FROM PHARM.T_BILL_OF_GOODS bg WHERE bg.BillOfGoodsID = bgl.BillOfGoodsID);
		ELSE
			IF "Xml" is null
			then		
				--выясняем тип операции
				if "ID" is null
				then
					_OperationType := _FTLOAD;
				elsif "IsNew" = '1'
				then
					_OperationType := _INSERT;
				elsif "IsNew" = '0' and exists(select* from pharm.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = '0' and not exists(select* from pharm.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "BILL_OF_GOODS", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;
		
				-- Обработка операции
				if _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					select 			    
					XMLELEMENT
					(
						name "BILL_OF_GOODS"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST(_FTLOAD as "TYPE")
						)
					,	XMLELEMENT
						(
							name body
						,	XMLELEMENT
							(
								name "BILL_OF_GOODS_LIST"
							,	XMLELEMENT
								(
									name "BILL_OF_GOODS"
								,	XMLFOREST
									(
										
										bg.BillOfGoodsID	as "ID"
									,	dv.Value		as "DoseView"
									,	bg.OtherID		as "OtherID"
									,	bg.IsVital		as "IsVital"
									,	bg.Active		as "Active"
									,	bg.Rate			as "Rate"
									,	bg.ExportCode		as "ExportCode"
									,	bg.PackQuantity		as "PackQuantity"
									,	bg.FederalID		as "FederalID"
									,	bg.MedicamentProducer	as "MedicamentProducer"
									,	bg.CTrn			as "CTrn"
									,	bg.MedicationForms	as "MedicationForms"
									,	bg.ProductInfo		as "ProductInfo"
									,	bg.Dlo			as "Dlo"
									,	bg.RoundToWriteOff			as "RoundToWriteOff"
									,	bg.Gtin							as "Gtin"
									,	bg.EAN							as "EAN"
									,	bg.MedicamentsOfRoszdravnadzor	as "MedicamentsOfRoszdravnadzor"
									,	bg.RegCode						as "RegCode"
									,	bg.FedCode						as "FedCode"
									,	er.IdEHR						as "EhrRus2m69i03gtd"
									,	RRB.CurrVersion					as "EhrRus2m69i03gtdVer"
									,	er1.CodeEHR						as "EhrRus9a9g09e8kl"
									,	RRB1.CurrVersion				as "EhrRus9a9g09e8klVer"
									,	er2.IdEHR						as "EhrRusClp14fvirj"
									,	RRB2.CurrVersion				as "EhrRusClp14fvirjVer"
									,	er3.IdEHR						as "EhrRusH17F8UTS3N"
									,	RRB3.CurrVersion				as "EhrRusH17F8UTS3NVer"
									,	er4.IdEHR						as "EhrRusDsg207"
									,	RRB4.CurrVersion				as "EhrRusDsg207Ver"
									)
								)
							)
						)
					)
					from PHARM.T_BILL_OF_GOODS bg
						left join PHARM.S_DLO_DoseView dv on dv.Id = bg.DoseView
						left join EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bg.EhrRus2m69i03gtd
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						left join EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bg.EhrRus9a9g09e8kl
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
						left join EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bg.EhrRusClp14fvirj
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
						left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
						left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
					into _Result;		
				elsif _OperationType <> _DELETE
				then
					-- собираем BILL_OF_GOODS
					select
					XMLELEMENT
					(
						name "BILL_OF_GOODS"
					,	XMLELEMENT
						(
							name header
						,	XMLForest
							(
								_OperationType as "TYPE"
							,	"ID" as "ID"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	XMLFOREST
							(
								dv.Value		as "DoseView"
							,	bg.OtherID		as "OtherID"
							,	bg.IsVital		as "IsVital"
							,	bg.Active		as "Active"
							,	bg.Rate			as "Rate"
							,	bg.ExportCode		as "ExportCode"
							,	bg.PackQuantity		as "PackQuantity"
							,	bg.FederalID		as "FederalID"
							,	bg.MedicamentProducer	as "MedicamentProducer"
							,	bg.CTrn			as "CTrn"
							,	bg.MedicationForms	as "MedicationForms"
							,	bg.ProductInfo		as "ProductInfo"
							,	bg.Dlo			as "Dlo"
							,	bg.RoundToWriteOff			as "RoundToWriteOff"
							,	bg.Gtin							as "Gtin"
						  	,	bg.EAN							as "EAN"
						  	,	bg.MedicamentsOfRoszdravnadzor	as "MedicamentsOfRoszdravnadzor"
						  	,	RegCode							as "RegCode"
						  	,	FedCode							as "FedCode"
							,	er.IdEHR						as "EhrRus2m69i03gtd"
							,	RRB.CurrVersion					as "EhrRus2m69i03gtdVer"
							,	er1.CodeEHR						as "EhrRus9a9g09e8kl"
							,	RRB1.CurrVersion				as "EhrRus9a9g09e8klVer"
							,	er2.IdEHR						as "EhrRusClp14fvirj"
							,	RRB2.CurrVersion				as "EhrRusClp14fvirjVer"
							,	er3.IdEHR						as "EhrRusH17F8UTS3N"
							,	RRB3.CurrVersion				as "EhrRusH17F8UTS3NVer"
							,	er4.IdEHR						as "EhrRusDsg207"
							,	RRB4.CurrVersion				as "EhrRusDsg207Ver"
							)
						)					
					)
					from Pharm.T_BILL_OF_GOODS bg
						left join pharm.S_DLO_DoseView dv on dv.Id = mf.DoseView
						left join EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bg.EhrRus2m69i03gtd
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						left join EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bg.EhrRus9a9g09e8kl
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
						left join EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bg.EhrRusClp14fvirj
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
						left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
						left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
					into _Result
					where mf.BillOfGoodsID = "ID";		    		  
				end if;
		
				-- Отправка
				INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
				select gen_random_uuid(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User", 0, "dateTime", _OwnerID;
			else
				if ("Xml" is not null)
				then        
					-- Получаем тип операции
					_OperationType := (xpath('/BILL_OF_GOODS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::text::varchar(36);
					
					-- Обработка операции
					if _OperationType = _FTLOAD
					then
						-- Generate table Tmp_BILL_OF_GOODS_LIST
						Drop Table If Exists Tmp_BILL_OF_GOODS_LIST;
						
						CREATE TEMP TABLE Tmp_BILL_OF_GOODS_LIST (
					    ID UUID PRIMARY KEY,
					    DoseView VARCHAR(1000),
					    DoseViewRep VARCHAR(1000),
					    OtherID INT,
					    IsVital BOOLEAN,
					    Active BOOLEAN,
					    Rate INT,
					    ExportCode INT,
					    PackQuantity INT,
					    Name VARCHAR(1000),
					    FederalID INT,
					    MedicamentProducer UUID,
					    CTrn UUID,
					    MedicationForms UUID,
					    ProductInfo TEXT,
					    Dlo BOOLEAN,
					    RoundToWriteOff BOOLEAN,
					    Gtin VARCHAR(4),
					    EAN VARCHAR(255),
					    MedicamentsOfRoszdravnadzor UUID,
					    RegCode VARCHAR(50),
					    FedCode VARCHAR(50),
					    EhrRus2m69i03gtd TEXT,
					    EhrRus2m69i03gtdVer VARCHAR(8),
					    EhrRus9a9g09e8kl TEXT,
					    EhrRus9a9g09e8klVer VARCHAR(8),
					    EhrRusClp14fvirj TEXT,
					    EhrRusClp14fvirjVer VARCHAR(8),
					    EhrRusH17F8UTS3N TEXT,
					    EhrRusH17F8UTS3NVer VARCHAR(8),
					    EhrRusDsg207 TEXT,
					    EhrRusDsg207Ver VARCHAR(8)
					);
						
						INSERT INTO Tmp_BILL_OF_GOODS_LIST
						SELECT	(xpath('/BILL_OF_GOODS/ID[1]/text()', t.x))[1]::text::UUID as ID
						,	(xpath('/BILL_OF_GOODS/DoseView[1]/text()', t.x))[1]::varchar(1000) as DoseView
						,	REPLACE((xpath('/BILL_OF_GOODS/DoseViewRep[1]/text()', t.x))[1]::varchar(1000), ' ', '') as DoseViewRep
						,	(xpath('/BILL_OF_GOODS/OtherID[1]/text()', t.x))[1]::text::int as OtherID
						,	(xpath('/BILL_OF_GOODS/IsVital[1]/text()', t.x))[1]::text::boolean as IsVital
						,	(xpath('/BILL_OF_GOODS/Active[1]/text()', t.x))[1]::text::boolean as Active
						,	(xpath('/BILL_OF_GOODS/Rate[1]/text()', t.x))[1]::text::int as Rate
						,	(xpath('/BILL_OF_GOODS/ExportCode[1]/text()', t.x))[1]::text::int as ExportCode
						,	(xpath('/BILL_OF_GOODS/PackQuantity[1]/text()', t.x))[1]::text::int as PackQuantity
						,	(xpath('/BILL_OF_GOODS/Name[1]/text()', t.x))[1]::varchar(1000) as Name
						,	(xpath('/BILL_OF_GOODS/FederalID[1]/text()', t.x))[1]::text::int as FederalID
						,	(xpath('/BILL_OF_GOODS/MedicamentProducer[1]/text()', t.x))[1]::text::UUID as MedicamentProducer
						,	(xpath('/BILL_OF_GOODS/CTrn[1]/text()', t.x))[1]::text::UUID as CTrn
						,	(xpath('/BILL_OF_GOODS/MedicationForms[1]/text()', t.x))[1]::text::UUID as MedicationForms
						,	(xpath('/BILL_OF_GOODS/ProductInfo[1]/text()', t.x))[1]::text as ProductInfo
						,	(xpath('/BILL_OF_GOODS/Dlo[1]/text()', t.x))[1]::text::boolean as Dlo		
						,	(xpath('/BILL_OF_GOODS/RoundToWriteOff[1]/text()', t.x))[1]::text::boolean as RoundToWriteOff				
						,	(xpath('/BILL_OF_GOODS/Gtin[1]/text()', t.x))[1]::varchar(4) as Gtin
						,	(xpath('/BILL_OF_GOODS/EAN[1]/text()', t.x))[1]::varchar(255) as EAN
						,	(xpath('/BILL_OF_GOODS/MedicamentsOfRoszdravnadzor[1]/text()', t.x))[1]::text::uuid as MedicamentsOfRoszdravnadzor
						,	(xpath('/BILL_OF_GOODS/RegCode[1]/text()', t.x))[1]::varchar(50) as RegCode
						,	(xpath('/BILL_OF_GOODS/FedCode[1]/text()', t.x))[1]::varchar(50) as FedCode
						,	(xpath('/BILL_OF_GOODS/EhrRus2m69i03gtd[1]/text()', t.x))[1]::text as EhrRus2m69i03gtd
						,	(xpath('/BILL_OF_GOODS/EhrRus2m69i03gtdVer[1]/text()', t.x))[1]::varchar(8) as EhrRus2m69i03gtdVer
						,	(xpath('/BILL_OF_GOODS/EhrRus9a9g09e8kl[1]/text()', t.x))[1]::text as EhrRus9a9g09e8kl
						,	(xpath('/BILL_OF_GOODS/EhrRus9a9g09e8klVer[1]/text()', t.x))[1]::varchar(8) as EhrRus9a9g09e8klVer
						,	(xpath('/BILL_OF_GOODS/EhrRusClp14fvirj[1]/text()', t.x))[1]::text as EhrRusClp14fvirj
						,	(xpath('/BILL_OF_GOODS/EhrRusClp14fvirjVer[1]/text()', t.x))[1]::varchar(8) as EhrRusClp14fvirjVer
						,	(xpath('/BILL_OF_GOODS/EhrRusH17F8UTS3N[1]/text()', t.x))[1]::text as EhrRusH17F8UTS3N
						,	(xpath('/BILL_OF_GOODS/EhrRusH17F8UTS3NVer[1]/text()', t.x))[1]::varchar(8) as EhrRusH17F8UTS3NVer
						,	(xpath('/BILL_OF_GOODS/EhrRusDsg207[1]/text()', t.x))[1]::text as EhrRusDsg207
						,	(xpath('/BILL_OF_GOODS/EhrRusDsg207Ver[1]/text()', t.x))[1]::varchar(8) as EhrRusDsg207Ver
						from
						(
							select	unnest(xpath('/BILL_OF_GOODS/body/BILL_OF_GOODS_LIST/BILL_OF_GOODS', "Xml"::xml)) as x
						) t
					where _OperationType = _FTLOAD;

						CREATE INDEX idx_tmp_bgl_doseviewrep ON Tmp_BILL_OF_GOODS_LIST(DoseViewRep);
						CREATE INDEX idx_tmp_bgl_ehr_fields ON Tmp_BILL_OF_GOODS_LIST (
						    EhrRus2m69i03gtd, EhrRus2m69i03gtdVer,
						    EhrRus9a9g09e8kl, EhrRus9a9g09e8klVer,
						    EhrRusClp14fvirj, EhrRusClp14fvirjVer,
						    EhrRusH17F8UTS3N, EhrRusH17F8UTS3NVer,
						    EhrRusDsg207, EhrRusDsg207Ver
						);

						ANALYZE Tmp_BILL_OF_GOODS_LIST;
						ANALYZE PHARM.T_BILL_OF_GOODS;
						ANALYZE TEMP_DOSE_VIEW;	
						ANALYZE EHR.T_EHR_RUS_2M69I03GTD;
						ANALYZE EHR.T_EHR_RUS_9A9G09E8KL;
						ANALYZE EHR.T_EHR_RUS_CLP14FVIRJ;	
						ANALYZE EHR.T_EHR_RUS_H17F8UTS3N;
						ANALYZE EHR.T_EHR_RUS_DSG207;
						
						-- Первичная загрузка
						-- Загружаем новые DoseView
						INSERT INTO pharm.S_DLO_DoseView(Value)
						SELECT Max(bgl.DoseView)
						FROM Tmp_BILL_OF_GOODS_LIST bgl
						where bgl.DoseView is not null and not exists
						(
						    select* from TEMP_DOSE_VIEW dv where dv.Value = bgl.DoseViewRep
						)
						group by bgl.DoseViewRep;

						truncate table TEMP_DOSE_VIEW;
						insert into TEMP_DOSE_VIEW(Value, Id)
						select REPLACE(dv.Value, ' ', '') as value, MAX(dv.Id) as Id
						from PHARM.S_DLO_DoseView dv
						group by REPLACE(dv.Value, ' ', '');
		
						-- TRN не из справочника RDS переводим в неактивные
						update pharm.T_BILL_OF_GOODS bf
						set Active = '0'
						where not exists
						(
							select* from Tmp_BILL_OF_GOODS_LIST bgl where bgl.ID = bf.BillOfGoodsID
						);
		
						-- Обновляем имеющиеся TRN
						update PHARM.T_BILL_OF_GOODS bf
						set 
						    DoseView = dv.Id
						   ,OtherID = bgl.OtherID
						   ,IsVital = coalesce(bgl.IsVital,'0')
						   ,Active = bgl.Active
						   ,Rate = coalesce(bgl.Rate, 1)
						   ,ExportCode = bgl.ExportCode
						   ,PackQuantity = bgl.PackQuantity
						   ,FederalID = bgl.FederalID
						   ,MedicamentProducer = bgl.MedicamentProducer
						   ,CTrn = bgl.CTrn
						   ,MedicationForms = bgl.MedicationForms
						   ,ProductInfo = bgl.ProductInfo
						   ,Dlo = coalesce(bgl.Dlo,'0')
						   ,RoundToWriteOff = coalesce(bgl.RoundToWriteOff,'0')
						   ,Gtin = bgl.Gtin
						   ,EAN = bgl.EAN
						   ,MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
						   ,RegCode = bgl.RegCode
						   ,FedCode = bgl.FedCode
						   ,EhrRus2m69i03gtd		= ehr.EhrRusID
						   ,EhrRus9a9g09e8kl		= ehr1.EhrRusID
						   ,EhrRusClp14fvirj		= ehr2.EhrRusID
						   ,EhrRusH17F8UTS3N		= ehr3.EhrRusID
						   ,EhrRusDsg207			= ehr4.EhrRusID
						from Tmp_BILL_OF_GOODS_LIST bgl 
							left join TEMP_DOSE_VIEW dv on dv.Value = bgl.DoseViewRep
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_2M69I03GTD er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
								limit 1
							) ehr on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_9A9G09E8KL er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
								limit 1
							) ehr1 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_CLP14FVIRJ er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
								limit 1
							) ehr2 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_H17F8UTS3N er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
								limit 1
							) ehr3 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_DSG207 er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
								limit 1
							) ehr4 on true
						where bgl.ID = bf.BillOfGoodsID;

						-- Загружаем новые TRN
						INSERT INTO PHARM.T_BILL_OF_GOODS
						           (
								    BillOfGoodsID
								   ,CTrn
								   ,Active
								   ,MedicamentProducer
								   ,OtherID
								   ,MedicationForms
								   ,DoseView 
								   ,Rate
								   ,IsVital 
								   ,PackQuantity 
								   ,ExportCode
								   ,FederalID
								   ,ProductInfo
								   ,Dlo
								   ,RoundToWriteOff
								   ,Gtin
								   ,EAN
								   ,MedicamentsOfRoszdravnadzor
								   ,RegCode
								   ,FedCode
								   ,EhrRus2m69i03gtd
								   ,EhrRus9a9g09e8kl
								   ,EhrRusClp14fvirj
									,EhrRusH17F8UTS3N
									,EhrRusDsg207
								   )
						SELECT
								 bgl.ID
								,bgl.CTrn
								,bgl.Active
								,bgl.MedicamentProducer
								,bgl.OtherID
								,bgl.MedicationForms
								,dv.Id
								,coalesce(bgl.Rate, 1)
								,coalesce(bgl.IsVital,'0')
								,bgl.PackQuantity
								,bgl.ExportCode
								,bgl.FederalID
								,bgl.ProductInfo
								,coalesce(bgl.Dlo,'0')
								,coalesce(bgl.RoundToWriteOff,'0')
								,bgl.Gtin
								,bgl.EAN
								,bgl.MedicamentsOfRoszdravnadzor
								,RegCode
								,FedCode
								,ehr.EhrRusID
								,ehr1.EhrRusID
								,ehr2.EhrRusID
								,ehr3.EhrRusID
								,ehr4.EhrRusID
						from Tmp_BILL_OF_GOODS_LIST bgl
							left join TEMP_DOSE_VIEW dv on dv.Value = bgl.DoseViewRep
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_2M69I03GTD er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
								limit 1
							) ehr on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_9A9G09E8KL er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
								limit 1
							) ehr1 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_CLP14FVIRJ er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
								limit 1
							) ehr2 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_H17F8UTS3N er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
								limit 1
							) ehr3 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_DSG207 er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
								limit 1
							) ehr4 on true
						where not exists
						    (
						        select *
						        from PHARM.T_BILL_OF_GOODS bg
						        where bg.BillOfGoodsID = bgl.ID
						    );
					end if;
					elsif _OperationType = _DELETE
					then			
				
					-- Generate table Tmp_BILL_OF_GOODS
					Drop Table If Exists Tmp_BILL_OF_GOODS;
					Create Temp Table Tmp_BILL_OF_GOODS
					as
					SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID as ID
					,	(xpath('/body[1]/DoseView[1]/text()', t.x2))[1]::varchar(255) as DoseView
					,	REPLACE((xpath('/body[1]/DoseViewRep[1]/text()', t.x2))[1]::varchar(1000), ' ', '') as DoseViewRep
					,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int as OtherID
					,	(xpath('/body[1]/IsVital[1]/text()', t.x2))[1]::text::boolean as IsVital
					,	(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean as Active
					,	(xpath('/body[1]/Rate[1]/text()', t.x2))[1]::text::int as Rate
					,	(xpath('/body[1]/ExportCode[1]/text()', t.x2))[1]::text::int as ExportCode
					,	(xpath('/body[1]/PackQuantity[1]/text()', t.x2))[1]::text::int as PackQuantity
					,	(xpath('/body[1]/FederalID[1]/text()', t.x2))[1]::text::int as FederalID
					,	(xpath('/body[1]/MedicamentProducer[1]/text()', t.x2))[1]::text::UUID as MedicamentProducer
					,	(xpath('/body[1]/CTrn[1]/text()', t.x2))[1]::text::UUID as CTrn
					,	(xpath('/body[1]/MedicationForms[1]/text()', t.x2))[1]::text::UUID as MedicationForms
					,	(xpath('/body[1]/ProductInfo[1]/text()', t.x2))[1]::text as ProductInfo
					,	(xpath('/body[1]/Dlo[1]/text()', t.x2))[1]::text::boolean as Dlo
					,	(xpath('/body[1]/RoundToWriteOff[1]/text()', t.x2))[1]::text::boolean as RoundToWriteOff
					,	(xpath('/body[1]/Gtin[1]/text()', t.x2))[1]::varchar(4) as Gtin
					,	(xpath('/body[1]/EAN[1]/text()', t.x2))[1]::varchar(255) as EAN
					,	(xpath('/body[1]/MedicamentsOfRoszdravnadzor[1]/text()', t.x2))[1]::text::uuid as MedicamentsOfRoszdravnadzor
					,	(xpath('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50) as RegCode
					,	(xpath('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50) as FedCode
					,	(xpath('/body[1]/EhrRus2m69i03gtd[1]/text()', t.x2))[1]::text as EhrRus2m69i03gtd
					,	(xpath('/body[1]/EhrRus2m69i03gtdVer[1]/text()', t.x2))[1]::varchar(8) as EhrRus2m69i03gtdVer
					,	(xpath('/body[1]/EhrRus9a9g09e8kl[1]/text()', t.x2))[1]::text as EhrRus9a9g09e8kl
					,	(xpath('/body[1]/EhrRus9a9g09e8klVer[1]/text()', t.x2))[1]::varchar(8) as EhrRus9a9g09e8klVer
					,	(xpath('/body[1]/EhrRusClp14fvirj[1]/text()', t.x2))[1]::text as EhrRusClp14fvirj
					,	(xpath('/body[1]/EhrRusClp14fvirjVer[1]/text()', t.x2))[1]::varchar(8) as EhrRusClp14fvirjVer
					,	(xpath('/body[1]/EhrRusH17F8UTS3N[1]/text()', t.x2))[1]::text as EhrRusH17F8UTS3N
					,	(xpath('/body[1]/EhrRusH17F8UTS3NVer[1]/text()', t.x2))[1]::varchar(8) as EhrRusH17F8UTS3NVer
					,	(xpath('/body[1]/EhrRusDsg207[1]/text()', t.x2))[1]::text as EhrRusDsg207
					,	(xpath('/body[1]/EhrRusDsg207Ver[1]/text()', t.x2))[1]::varchar(8) as EhrRusDsg207Ver
					from
					(
						select	unnest(xpath('/BILL_OF_GOODS/header', x)) as x1
						,	unnest(xpath('/BILL_OF_GOODS/body', x)) as x2
						from unnest(xpath('/BILL_OF_GOODS', "Xml"::xml)) x
					) t;
						-- Удаление
						update pharm.T_BILL_OF_GOODS bf
						set Active = '0'
						from Tmp_BILL_OF_GOODS nbg 
						where nbg.ID = bf.BillOfGoodsID;
					else
						
					-- Generate table Tmp_BILL_OF_GOODS
					Drop Table If Exists Tmp_BILL_OF_GOODS;
					Create Temp Table Tmp_BILL_OF_GOODS
					as
					SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID as ID
					,	(xpath('/body[1]/DoseView[1]/text()', t.x2))[1]::varchar(255) as DoseView
					,	REPLACE((xpath('/body[1]/DoseViewRep[1]/text()', t.x2))[1]::varchar(1000), ' ', '') as DoseViewRep
					,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int as OtherID
					,	(xpath('/body[1]/IsVital[1]/text()', t.x2))[1]::text::boolean as IsVital
					,	(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean as Active
					,	(xpath('/body[1]/Rate[1]/text()', t.x2))[1]::text::int as Rate
					,	(xpath('/body[1]/ExportCode[1]/text()', t.x2))[1]::text::int as ExportCode
					,	(xpath('/body[1]/PackQuantity[1]/text()', t.x2))[1]::text::int as PackQuantity
					,	(xpath('/body[1]/FederalID[1]/text()', t.x2))[1]::text::int as FederalID
					,	(xpath('/body[1]/MedicamentProducer[1]/text()', t.x2))[1]::text::UUID as MedicamentProducer
					,	(xpath('/body[1]/CTrn[1]/text()', t.x2))[1]::text::UUID as CTrn
					,	(xpath('/body[1]/MedicationForms[1]/text()', t.x2))[1]::text::UUID as MedicationForms
					,	(xpath('/body[1]/ProductInfo[1]/text()', t.x2))[1]::text as ProductInfo
					,	(xpath('/body[1]/Dlo[1]/text()', t.x2))[1]::text::boolean as Dlo
					,	(xpath('/body[1]/RoundToWriteOff[1]/text()', t.x2))[1]::text::boolean as RoundToWriteOff
					,	(xpath('/body[1]/Gtin[1]/text()', t.x2))[1]::varchar(4) as Gtin
					,	(xpath('/body[1]/EAN[1]/text()', t.x2))[1]::varchar(255) as EAN
					,	(xpath('/body[1]/MedicamentsOfRoszdravnadzor[1]/text()', t.x2))[1]::text::uuid as MedicamentsOfRoszdravnadzor
					,	(xpath('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50) as RegCode
					,	(xpath('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50) as FedCode
					,	(xpath('/body[1]/EhrRus2m69i03gtd[1]/text()', t.x2))[1]::text as EhrRus2m69i03gtd
					,	(xpath('/body[1]/EhrRus2m69i03gtdVer[1]/text()', t.x2))[1]::varchar(8) as EhrRus2m69i03gtdVer
					,	(xpath('/body[1]/EhrRus9a9g09e8kl[1]/text()', t.x2))[1]::text as EhrRus9a9g09e8kl
					,	(xpath('/body[1]/EhrRus9a9g09e8klVer[1]/text()', t.x2))[1]::varchar(8) as EhrRus9a9g09e8klVer
					,	(xpath('/body[1]/EhrRusClp14fvirj[1]/text()', t.x2))[1]::text as EhrRusClp14fvirj
					,	(xpath('/body[1]/EhrRusClp14fvirjVer[1]/text()', t.x2))[1]::varchar(8) as EhrRusClp14fvirjVer
					,	(xpath('/body[1]/EhrRusH17F8UTS3N[1]/text()', t.x2))[1]::text as EhrRusH17F8UTS3N
					,	(xpath('/body[1]/EhrRusH17F8UTS3NVer[1]/text()', t.x2))[1]::varchar(8) as EhrRusH17F8UTS3NVer
					,	(xpath('/body[1]/EhrRusDsg207[1]/text()', t.x2))[1]::text as EhrRusDsg207
					,	(xpath('/body[1]/EhrRusDsg207Ver[1]/text()', t.x2))[1]::varchar(8) as EhrRusDsg207Ver
					from
					(
						select	unnest(xpath('/BILL_OF_GOODS/header', x)) as x1
						,	unnest(xpath('/BILL_OF_GOODS/body', x)) as x2
						from unnest(xpath('/BILL_OF_GOODS', "Xml"::xml)) x
					) t;
						
						-- Загружаем новые DoseView
						INSERT INTO PHARM.S_DLO_DoseView (Value)
						SELECT Max(bgl.DoseView)
						FROM Tmp_BILL_OF_GOODS bgl
						where bgl.DoseView is not null and not exists
						    (
						        select *
						        from TEMP_DOSE_VIEW dv
						        where dv.Value = bgl.DoseViewRep
						    )
						group by bgl.DoseViewRep;
						
						truncate table TEMP_DOSE_VIEW;
						insert into TEMP_DOSE_VIEW(Value, Id)
						select REPLACE(dv.Value, ' ', '') as value, MAX(dv.Id) as Id
						from PHARM.S_DLO_DoseView dv
						group by REPLACE(dv.Value, ' ', '');

						-- Обновляем TRN
						update pharm.T_BILL_OF_GOODS bf
						set	DoseView = dv.Id
						   ,OtherID = bgl.OtherID
						   ,IsVital = coalesce(bgl.IsVital,'0')
						   ,Active = bgl.Active
						   ,Rate = coalesce(bgl.Rate, 1)
						   ,ExportCode = bgl.ExportCode
						   ,PackQuantity = bgl.PackQuantity
						   ,FederalID = bgl.FederalID
						   ,MedicamentProducer = bgl.MedicamentProducer
						   ,CTrn = bgl.CTrn
						   ,MedicationForms = bgl.MedicationForms
						   ,ProductInfo = bgl.ProductInfo
						   ,Dlo = coalesce(bgl.Dlo,'0')
						   ,RoundToWriteOff = coalesce(bgl.RoundToWriteOff,'0')
						   ,Gtin = bgl.Gtin
						   ,EAN = bgl.EAN
						   ,MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
						   ,RegCode = bgl.RegCode
						   ,FedCode = bgl.FedCode
						   ,EhrRus2m69i03gtd		= ehr.EhrRusID
						   ,EhrRus9a9g09e8kl		= ehr1.EhrRusID
						   ,EhrRusClp14fvirj		= ehr2.EhrRusID
						   ,EhrRusH17F8UTS3N		= ehr3.EhrRusID
						   ,EhrRusDsg207			= ehr4.EhrRusID
						from Tmp_BILL_OF_GOODS bgl 
						left join TEMP_DOSE_VIEW dv on dv.Value = bgl.DoseViewRep
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_2M69I03GTD er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
								limit 1
							) ehr on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_9A9G09E8KL er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
								limit 1
							) ehr1 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_CLP14FVIRJ er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
								limit 1
							) ehr2 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_H17F8UTS3N er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
								limit 1
							) ehr3 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_DSG207 er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
								limit 1
							) ehr4 on true
						where  bgl.ID = bf.BillOfGoodsID and _OperationType = _UPDATE;
		
						-- Загружаем новый TRN
						INSERT INTO pharm.T_BILL_OF_GOODS(BillOfGoodsID 
							,CTrn
							,Active
							,MedicamentProducer
							,OtherID
							,MedicationForms
							,DoseView 
							,Rate 
							,IsVital 
							,PackQuantity 
							,ExportCode
							,FederalID
							,ProductInfo
							,Dlo
							,RoundToWriteOff
							,Gtin
							,EAN
							,MedicamentsOfRoszdravnadzor
							,RegCode
							,FedCode
							,EhrRus2m69i03gtd
							,EhrRus9a9g09e8kl
							,EhrRusClp14fvirj
							,EhrRusH17F8UTS3N
							,EhrRusDsg207)
						SELECT bgl.ID
							,bgl.CTrn
							,bgl.Active
							,bgl.MedicamentProducer
							,bgl.OtherID
							,bgl.MedicationForms
							,dv.Id
							,coalesce(bgl.Rate, 1)
							,coalesce(bgl.IsVital,'0')
							,bgl.PackQuantity
							,bgl.ExportCode
							,bgl.FederalID
							,bgl.ProductInfo
							,coalesce(bgl.Dlo,'0')
							,coalesce(bgl.RoundToWriteOff,'0')
							,bgl.Gtin
							,bgl.EAN
							,MedicamentsOfRoszdravnadzor
							,RegCode
							,FedCode
							,ehr.EhrRusID
							,ehr1.EhrRusID
							,ehr2.EhrRusID
							,ehr3.EhrRusID
							,ehr4.EhrRusID				
						from Tmp_BILL_OF_GOODS bgl
						left join TEMP_DOSE_VIEW dv on dv.Value = bgl.DoseViewRep
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_2M69I03GTD er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
								limit 1
							) ehr on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_9A9G09E8KL er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
								limit 1
							) ehr1 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_CLP14FVIRJ er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
								limit 1
							) ehr2 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_H17F8UTS3N er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
								limit 1
							) ehr3 on true
							left join lateral (
								select er.EhrRusID 
								from EHR.T_EHR_RUS_DSG207 er
									join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
								limit 1
							) ehr4 on true
						where not exists(select* from pharm.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = bgl.ID)
						and _OperationType = _INSERT;
					end if;
					
	        		_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'BILL_OF_GOODS' limit 1);
					if exists 
					(
						select* from VCLib.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.BILL_OF_GOODS' Limit 1
					)
					then
						---- генерируем след. номер
						--perform public.sp_GenerateNumber(1916, 'PHARM.BILL_OF_GOODS');
							
						update VCLib.T_GEN_NUMBERS
						set NextNumber = (select MAX(OtherID) from PHARM.T_BILL_OF_GOODS) --+ 1
						where EntityID = _EntId
						and NumberName = 'PHARM.BILL_OF_GOODS';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES(gen_random_uuid(), _EntId, 'PHARM.BILL_OF_GOODS', coalesce((select MAX(OtherID) from PHARM.T_BILL_OF_GOODS), 1));
					end if;
						
					-- Drop tables
					DROP TABLE if exists Tmp_BILL_OF_GOODS;
					DROP TABLE if exists Tmp_BILL_OF_GOODS_LIST;
				end if;
			END if;
	End;
End;
$$;