-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника Медикаменты Росздравнадзора в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS DBO.sp_RDS_MedicamentsOfRoszdravnadzor;

DROP PROCEDURE IF EXISTS APS.sp_RDS_MedicamentsOfRoszdravnadzor;

CREATE PROCEDURE APS.sp_RDS_MedicamentsOfRoszdravnadzor
(
	"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) := 'MEDICAMENT_OF_ROSZDRAVNADZOR';
		_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;
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

        IF _PATH IS NOT NULL
        then
			DROP TABLE IF EXISTS Temp_C_MNN;
			CREATE TEMP TABLE Temp_C_MNN
        	(
        		 MedicamentsOfRoszdravnadzorID	uuid
        		,DrugID							INT
        		,TradeNmR						VARCHAR(1000)
        		,InnR							VARCHAR(1000)
        		,PackNx							BIGINT
        		,DosageR						VARCHAR(255)
        		,PackQn							INT
        		,Pack							VARCHAR(1000)
        		,DrugFmNmRS						VARCHAR(1000)
        		,Segment						VARCHAR(255)
        		,Year							INT
        		,Month							INT
        		,Series							VARCHAR(255)
        		,TotDrugQn						DECIMAL
        		,MnfPrice						DECIMAL
        		,PrcPrice						DECIMAL
        		,RtlPrice						DECIMAL
        		,Funds							DECIMAL
        		,VendorID						VARCHAR(255)
        		,Remark							VARCHAR(1000)
        		,SrcOrg							VARCHAR(255)
        		,EAN							VARCHAR(14)
        		,MaxMnfPrice					DECIMAL
        		,ExpiTermR						VARCHAR(500)
        		,ClNm							VARCHAR(1000)
        		,MnfNm							VARCHAR(1000)
        		,PckNm							VARCHAR(1000)
        		,RegNr							VARCHAR(100)
        		,RegDate						DATETIME
        		,Active							boolean
        	);

            _sql := 'INSERT INTO tempdb..TMP_T_MEDICAMENTS_OF_ROSZDRAVNADZOR
        			SELECT
        				 MedicamentsOfRoszdravnadzorID
        				,DrugID
        				,TradeNmR
        				,InnR
        				,PackNx
        				,DosageR
        				,PackQn
        				,Pack
        				,DrugFmNmRS
        				,Segment
        				,Year
        				,Month
        				,Series
        				,TotDrugQn
        				,MnfPrice
        				,PrcPrice
        				,RtlPrice
        				,Funds
        				,VendorID
        				,Remark
        				,SrcOrg
        				,EAN
        				,MaxMnfPrice
        				,ExpiTermR
        				,ClNm
        				,MnfNm
        				,PckNm
        				,RegNr
        				,RegDate
        				,Active
        			FROM dblink(''dbname=' || "PATH" 
				|| ''', SELECT 
                         mor.MedicamentsOfRoszdravnadzorID
        				,mor.DrugID
        				,mor.TradeNmR
        				,mor.InnR
        				,mor.PackNx
        				,mor.DosageR
        				,mor.PackQn
        				,mor.Pack
        				,mor.DrugFmNmRS
        				,mor.Segment
        				,mor.Year
        				,mor.Month
        				,mor.Series
        				,mor.TotDrugQn
        				,mor.MnfPrice
        				,mor.PrcPrice
        				,mor.RtlPrice
        				,mor.Funds
        				,mor.VendorID
        				,mor.Remark
        				,mor.SrcOrg
        				,mor.EAN
        				,mor.MaxMnfPrice
        				,mor.ExpiTermR
        				,mor.ClNm
        				,mor.MnfNm
        				,mor.PckNm
        				,mor.RegNr
        				,mor.RegDate
        				,mor.Active
                    FROM PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR mor
                    '') AS tt(
                    MedicamentsOfRoszdravnadzorID	uuid
        		                ,DrugID							INT
        		                ,TradeNmR						VARCHAR(1000)
        		                ,InnR							VARCHAR(1000)
        		                ,PackNx							BIGINT
        		                ,DosageR						VARCHAR(255)
        		                ,PackQn							INT
        		                ,Pack							VARCHAR(1000)
        		                ,DrugFmNmRS						VARCHAR(1000)
        		                ,Segment						VARCHAR(255)
        		                ,Year							INT
        		                ,Month							INT
        		                ,Series							VARCHAR(255)
        		                ,TotDrugQn						DECIMAL
        		                ,MnfPrice						DECIMAL
        		                ,PrcPrice						DECIMAL
        		                ,RtlPrice						DECIMAL
        		                ,Funds							DECIMAL
        		                ,VendorID						VARCHAR(255)
        		                ,Remark							VARCHAR(1000)
        		                ,SrcOrg							VARCHAR(255)
        		                ,EAN							VARCHAR(14)
        		                ,MaxMnfPrice					DECIMAL
        		                ,ExpiTermR						VARCHAR(500)
        		                ,ClNm							VARCHAR(1000)
        		                ,MnfNm							VARCHAR(1000)
        		                ,PckNm							VARCHAR(1000)
        		                ,RegNr							VARCHAR(100)
        		                ,RegDate						DATETIME
        		                ,Active							boolean)';

            EXECUTE _sql;
        
        	UPDATE PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR s 
        	SET	Active = '0'
        	WHERE NOT EXISTS
        	    (
        	        select *
        	        from TMP_T_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
        	        where nmf.MedicamentsOfRoszdravnadzorID = s.MedicamentsOfRoszdravnadzorID
        	    );
        
                    -- Обновляем имеющиеся 
        	 update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
        	 set  
        		  DrugID = nmf.DrugID
        	     ,TradeNmR = nmf.TradeNmR
        	     ,InnR = nmf.InnR
        	     ,PackNx = nmf.PackNx
        	     ,DosageR = nmf.DosageR
        	     ,PackQn = nmf.PackQn
        	     ,Pack = nmf.Pack
        	     ,DrugFmNmRS = nmf.DrugFmNmRS
        	     ,Segment = nmf.Segment
        	     ,Year = nmf.Year
        	     ,Month = nmf.Month
        	     ,Series = nmf.Series
        	     ,TotDrugQn = nmf.TotDrugQn
        	     ,MnfPrice = nmf.MnfPrice
        	     ,PrcPrice = nmf.PrcPrice
        	     ,RtlPrice = nmf.RtlPrice
        	     ,Funds = nmf.Funds
        	     ,VendorID = nmf.VendorID
        	     ,Remark = nmf.Remark
        	     ,SrcOrg = nmf.SrcOrg
        	     ,EAN = nmf.EAN
        	     ,MaxMnfPrice = nmf.MaxMnfPrice
        	     ,ExpiTermR = nmf.ExpiTermR
        	     ,ClNm = nmf.ClNm
        	     ,MnfNm = nmf.MnfNm
        	     ,PckNm = nmf.PckNm
        	     ,RegNr = nmf.RegNr
        	     ,RegDate = nmf.RegDate
        	     ,Active = coalesce(nmf.Active,'1')
        	 from TMP_T_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf 
             where nmf.MedicamentsOfRoszdravnadzorID = m.MedicamentsOfRoszdravnadzorID
        	 and m.Active = '1';
        	
        	 -- Загружаем новые
        	 INSERT INTO PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR
        	(MedicamentsOfRoszdravnadzorID
        		,DrugID
        		,TradeNmR
        		,InnR
        		,PackNx
        		,DosageR
        		,PackQn
        		,Pack
        		,DrugFmNmRS
        		,Segment
        		,Year
        		,Month
        		,Series
        		,TotDrugQn
        		,MnfPrice
        		,PrcPrice
        		,RtlPrice
        		,Funds
        		,VendorID
        		,Remark
        		,SrcOrg
        		,EAN
        		,MaxMnfPrice
        		,ExpiTermR
        		,ClNm
        		,MnfNm
        		,PckNm
        		,RegNr
        		,RegDate
        		,Active)
        	select
        		 nmf.MedicamentsOfRoszdravnadzorID
        		,nmf.DrugID
        		,nmf.TradeNmR
        		,nmf.InnR
        		,nmf.PackNx
        		,nmf.DosageR
        		,nmf.PackQn
        		,nmf.Pack
        		,nmf.DrugFmNmRS
        		,nmf.Segment
        		,nmf.Year
        		,nmf.Month
        		,nmf.Series
        		,nmf.TotDrugQn
        		,nmf.MnfPrice
        		,nmf.PrcPrice
        		,nmf.RtlPrice
        		,nmf.Funds
        		,nmf.VendorID
        		,nmf.Remark
        		,nmf.SrcOrg
        		,nmf.EAN
        		,nmf.MaxMnfPrice
        		,nmf.ExpiTermR
        		,nmf.ClNm
        		,nmf.MnfNm
        		,nmf.PckNm
        		,nmf.RegNr
        		,nmf.RegDate
        		,coalesce(nmf.Active,'1')
            FROM TMP_T_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
            WHERE NOT EXISTS
            (
                SELECT *
        			FROM PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                WHERE m.MedicamentsOfRoszdravnadzorID = nmf.MedicamentsOfRoszdravnadzorID
            );
        
        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_MEDICAMENTS_OF_ROSZDRAVNADZOR c where c.MedicamentsOfRoszdravnadzorID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = '0' and not exists(select* from Pharm.T_MEDICAMENTS_OF_ROSZDRAVNADZOR c where c.MedicamentsOfRoszdravnadzorID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "MEDICAMENT_OF_ROSZDRAVNADZOR", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;
        
                -- Обработка операции
                IF (_OperationType = _FTLOAD)
                then
                    -- Первичная загрузка
                    _Result :=
					XMLELEMENT
					(
						name "MEDICAMENT_OF_ROSZDRAVNADZOR"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST
							(
								_FTLOAD as "TYPE"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	xmlelement
							(
								name "MEDICAMENTS_OF_ROSZDRAVNADZOR"
							,	xmlelement
								(
									name "MEDICAMENT_OF_ROSZDRAVNADZOR"
								,	(
										select	
										xmlagg
										(
											xmlforest
											(
											 m.MedicamentsOfRoszdravnadzorID		as "ID"
        							        ,m.DrugID			                    as "DrugID"
        							        ,m.TradeNmR			                    as "TradeNmR"
        							        ,m.InnR				                    as "InnR"
        							        ,m.PackNx			                    as "PackNx"
        							        ,m.DosageR			                    as "DosageR"
        							        ,m.PackQn			                    as "PackQn"
        							        ,m.Pack				                    as "Pack"
        							        ,m.DrugFmNmRS		                    as "DrugFmNmRS"
        							        ,m.Segment			                    as "Segment"
        							        ,m.Year			                        as "Year"
        							        ,m.Month			                    as "Month"
        							        ,m.Series			                    as "Series"
        							        ,m.TotDrugQn		                    as "TotDrugQn"
        							        ,m.MnfPrice			                    as "MnfPrice"
        							        ,m.PrcPrice			                    as "PrcPrice"
        							        ,m.RtlPrice			                    as "RtlPrice"
        							        ,m.Funds			                    as "Funds"
        							        ,m.VendorID			                    as "VendorID"
        							        ,m.Remark			                    as "Remark"
        							        ,m.SrcOrg			                    as "SrcOrg"
        							        ,m.EAN				                    as "EAN"
        							        ,m.ExpiTermR		                    as "ExpiTermR"
        							        ,m.ClNm				                    as "ClNm"
        							        ,m.MnfNm			                    as "MnfNm"
        							        ,m.PckNm			                    as "PckNm"
        							        ,m.RegNr			                    as "RegNr"
        							        ,m.MaxMnfPrice		                    as "MaxMnfPrice"
        							        ,to_char(m.RegDate, 'YYYY-MM-DD')	    as "RegDate"
        							        ,m.Active			                    as "Active"
											)
										)
										from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                                        where m.Active = '1'
									)
								)
							)
						)
					);
                ELSIF(_OperationType <> _DELETE)
                then
                    -- собираем C_MNN
					select 
					XMLELEMENT
					(
						name "MEDICAMENT_OF_ROSZDRAVNADZOR"
					,	XMLFOREST
						(
							XMLFOREST
							(
								_OperationType as "TYPE"
							,	"ID" as "ID"
							) as header
						)
					,	XMLFOREST
						(
							XMLFOREST
							(
							     m.DrugID			as "DrugID"
        						,m.TradeNmR			as "TradeNmR"
        						,m.InnR				as "InnR"
        						,m.PackNx			as "PackNx"
        						,m.DosageR			as "DosageR"
        						,m.PackQn			as "PackQn"
        						,m.Pack				as "Pack"
        						,m.DrugFmNmRS		as "DrugFmNmRS"
        						,m.Segment			as "Segment"
        						,m.Year			    as "Year"
        						,m.Month			as "Month"
        						,m.Series			as "Series"
        						,m.TotDrugQn		as "TotDrugQn"
        						,m.MnfPrice			as "MnfPrice"
        						,m.PrcPrice			as "PrcPrice"
        						,m.RtlPrice			as "RtlPrice"
        						,m.Funds			as "Funds"
        						,m.VendorID			as "VendorID"
        						,m.Remark			as "Remark"
        						,m.SrcOrg			as "SrcOrg"
        						,m.EAN				as "EAN"
        						,m.ExpiTermR		as "ExpiTermR"
        						,m.ClNm				as "ClNm"
        						,m.MnfNm			as "MnfNm"
        						,m.PckNm			as "PckNm"
        						,m.RegNr			as "RegNr"
        						,m.MaxMnfPrice		as "MaxMnfPrice"
        						,to_char(m.RegDate, 'YYYY-MM-DD') as "RegDate"
        						,m.Active			as "Active"
							) as body
						)
					)
					into _Result
                    from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    where m.MedicamentsOfRoszdravnadzorID = _ID;

                END if;
        
                -- Отправка
				INSERT INTO APS.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 
                -- Получаем тип операции
				_OperationType := (xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
        
                -- Generate table TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR
				Drop Table If Exists TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR;
				Create Temp Table TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR
				as
                select (xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/ID[1]/text()', x))[1]::text::UUID						as ID
        			  ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/DrugID[1]/text()', t.x))[1]::text::int								as DrugID
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/TradeNmR[1]/text()', x))[1]::varchar(1000)					as TradeNmR
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/InnR[1]/text()', x))[1]::varchar(1000)						as InnR
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/PackNx[1]/text()', t.x))[1]::text::bigint							as PackNx
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/DosageR[1]/text()', x))[1]::varchar(255)					as DosageR
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/PackQn[1]/text()', t.x))[1]::text::int								as PackQn
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Pack[1]/text()', x))[1]::varchar(1000)						as Pack
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/DrugFmNmRS[1]/text()', x))[1]::varchar(1000)				as DrugFmNmRS
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Segment[1]/text()', x))[1]::varchar(255)					as Segment
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Year[1]/text()', t.x))[1]::text::int								as Year
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Month[1]/text()', t.x))[1]::text::int								as Month
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Series[1]/text()', x))[1]::varchar(255)						as Series
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/TotDrugQn[1]/text()', t.x))[1]::text::decimal(12,2)					as TotDrugQn
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/MnfPrice[1]/text()', t.x))[1]::text::decimal(12,2)					as MnfPrice
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/PrcPrice[1]/text()', t.x))[1]::text::decimal(12,2)					as PrcPrice
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/RtlPrice[1]/text()', t.x))[1]::text::decimal(12,2)					as RtlPrice
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Funds[1]/text()', t.x))[1]::text::decimal(12,2)						as Funds
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/VendorID[1]/text()', x))[1]::varchar(255)					as VendorID
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Remark[1]/text()', x))[1]::varchar(1000)					as Remark
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/SrcOrg[1]/text()', x))[1]::varchar(255)						as SrcOrg
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/EAN[1]/text()', x))[1]::varchar(14)							as EAN
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/MaxMnfPrice[1]/text()', t.x))[1]::text::decimal(12,2)				as MaxMnfPrice
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/ExpiTermR[1]/text()', x))[1]::varchar(500)					as ExpiTermR
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/ClNm[1]/text()', x))[1]::varchar(255)						as ClNm
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/MnfNm[1]/text()', x))[1]::varchar(1000)						as MnfNm
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/PckNm[1]/text()', x))[1]::varchar(1000)						as PckNm
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/RegNr[1]/text()', x))[1]::varchar(100)						as RegNr
        			  ,to_timestamp((xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/RegDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')     as RegDate
                      ,(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/Active[1/text()', t.x))[1]::text::boolean								as Active
				from unnest(xpath('MEDICAMENT_OF_ROSZDRAVNADZOR/body/MEDICAMENTS_OF_ROSZDRAVNADZOR/MEDICAMENT_OF_ROSZDRAVNADZOR', "Xml"::xml)) as x
				where _OperationType = _FTLOAD;			
        
                -- Generate table TMP_MEDICAMENT_OF_ROSZDRAVNADZOR
				Drop Table If Exists TMP_MEDICAMENT_OF_ROSZDRAVNADZOR;
				Create Temp Table TMP_MEDICAMENT_OF_ROSZDRAVNADZOR
				as
                select (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID            as ID
        			  ,(xpath('/body[1]/DrugID[1]/text()', t.x2))[1]::text::int								as DrugID
                      ,(xpath('/body[1]/TradeNmR[1]/text()', x2))[1]::varchar(1000)					as TradeNmR
                      ,(xpath('/body[1]/InnR[1]/text()', x2))[1]::varchar(1000)						as InnR
                      ,(xpath('/body[1]/PackNx[1]/text()', t.x2))[1]::text::bigint							as PackNx
                      ,(xpath('/body[1]/DosageR[1]/text()', x2))[1]::varchar(255)					as DosageR
                      ,(xpath('/body[1]/PackQn[1]/text()', t.x2))[1]::text::int								as PackQn
                      ,(xpath('/body[1]/Pack[1]/text()', x2))[1]::varchar(1000)						as Pack
                      ,(xpath('/body[1]/DrugFmNmRS[1]/text()', x2))[1]::varchar(1000)				as DrugFmNmRS
                      ,(xpath('/body[1]/Segment[1]/text()', x2))[1]::varchar(255)					as Segment
                      ,(xpath('/body[1]/Year[1]/text()', t.x2))[1]::text::int								as Year
                      ,(xpath('/body[1]/Month[1]/text()', t.x2))[1]::text::int								as Month
                      ,(xpath('/body[1]/Series[1]/text()', x2))[1]::varchar(255)						as Series
                      ,(xpath('/body[1]/TotDrugQn[1]/text()', t.x2))[1]::text::decimal(12,2)					as TotDrugQn
                      ,(xpath('/body[1]/MnfPrice[1]/text()', t.x2))[1]::text::decimal(12,2)					as MnfPrice
                      ,(xpath('/body[1]/PrcPrice[1]/text()', t.x2))[1]::text::decimal(12,2)					as PrcPrice
                      ,(xpath('/body[1]/RtlPrice[1]/text()', t.x2))[1]::text::decimal(12,2)					as RtlPrice
                      ,(xpath('/body[1]/Funds[1]/text()', t.x2))[1]::text::decimal(12,2)						as Funds
                      ,(xpath('/body[1]/VendorID[1]/text()', x2))[1]::varchar(255)					as VendorID
                      ,(xpath('/body[1]/Remark[1]/text()', x2))[1]::varchar(1000)					as Remark
                      ,(xpath('/body[1]/SrcOrg[1]/text()', x2))[1]::varchar(255)						as SrcOrg
                      ,(xpath('/body[1]/EAN[1]/text()', x2))[1]::varchar(14)							as EAN
                      ,(xpath('/body[1]/MaxMnfPrice[1]/text()', t.x2))[1]::text::decimal(12,2)				as MaxMnfPrice
                      ,(xpath('/body[1]/ExpiTermR[1]/text()', x2))[1]::varchar(500)					as ExpiTermR
                      ,(xpath('/body[1]/ClNm[1]/text()', x2))[1]::varchar(255)						as ClNm
                      ,(xpath('/body[1]/MnfNm[1]/text()', x2))[1]::varchar(1000)						as MnfNm
                      ,(xpath('/body[1]/PckNm[1]/text()', x2))[1]::varchar(1000)						as PckNm
                      ,(xpath('/body[1]/RegNr[1]/text()', x2))[1]::varchar(100)						as RegNr
                      ,(xpath('/body[1]/Active[1/text()', t.x2))[1]::text::boolean								as Active
        			  ,to_timestamp((xpath('/RegDate[1]/text()', x2))[1]::text, 'YYYY-MM-DD')     as RegDate
                from
					(
						select	unnest(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/header', x)) as x1
						,	unnest(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/body', x)) as x2
						from unnest(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR', "Xml"::xml)) x
					) t
				where _OperationType <> _FTLOAD;
        
                -- Обработка операции
                if (_OperationType = _FTLOAD)
                then
                    update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR s 
                    set Active = 0
                    where not exists
                        (
                            select *
                            from TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
                            where nmf.ID = s.MedicamentsOfRoszdravnadzorID
                        );
        
                    -- Обновляем имеющиеся 
                    update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    set  DrugID = nmf.DrugID
                        ,TradeNmR = nmf.TradeNmR
                        ,InnR = nmf.InnR
                        ,PackNx = nmf.PackNx
                        ,DosageR = nmf.DosageR
                        ,PackQn = nmf.PackQn
                        ,Pack = nmf.Pack
                        ,DrugFmNmRS = nmf.DrugFmNmRS
                        ,Segment = nmf.Segment
                        ,Year = nmf.Year
                        ,Month = nmf.Month
                        ,Series = nmf.Series
                        ,TotDrugQn = nmf.TotDrugQn
                        ,MnfPrice = nmf.MnfPrice
                        ,PrcPrice = nmf.PrcPrice
                        ,RtlPrice = nmf.RtlPrice
                        ,Funds = nmf.Funds
                        ,VendorID = nmf.VendorID
                        ,Remark = nmf.Remark
                        ,SrcOrg = nmf.SrcOrg
                        ,EAN = nmf.EAN
                        ,MaxMnfPrice = nmf.MaxMnfPrice
                        ,ExpiTermR = nmf.ExpiTermR
                        ,ClNm = nmf.ClNm
                        ,MnfNm = nmf.MnfNm
                        ,PckNm = nmf.PckNm
                        ,RegNr = nmf.RegNr
                        ,RegDate = nmf.RegDate
                        ,Active = coalesce(nmf.Active,'1')
                    from TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
                    where nmf.ID = m.MedicamentsOfRoszdravnadzorID and m.Active = '1';
        
                    -- Загружаем новые
                    INSERT INTO PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR
                   (MedicamentsOfRoszdravnadzorID
                   ,DrugID
                   ,TradeNmR
                   ,InnR
                   ,PackNx
                   ,DosageR
                   ,PackQn
                   ,Pack
                   ,DrugFmNmRS
                   ,Segment
                   ,Year
                   ,Month
                   ,Series
                   ,TotDrugQn
                   ,MnfPrice
                   ,PrcPrice
                   ,RtlPrice
                   ,Funds
                   ,VendorID
                   ,Remark
                   ,SrcOrg
                   ,EAN
                   ,MaxMnfPrice
                   ,ExpiTermR
                   ,ClNm
                   ,MnfNm
                   ,PckNm
                   ,RegNr
                   ,RegDate
        		   ,Active)
        		select
        			nmf.ID
                   ,nmf.DrugID
                   ,nmf.TradeNmR
                   ,nmf.InnR
                   ,nmf.PackNx
                   ,nmf.DosageR
                   ,nmf.PackQn
                   ,nmf.Pack
                   ,nmf.DrugFmNmRS
                   ,nmf.Segment
                   ,nmf.Year
                   ,nmf.Month
                   ,nmf.Series
                   ,nmf.TotDrugQn
                   ,nmf.MnfPrice
                   ,nmf.PrcPrice
                   ,nmf.RtlPrice
                   ,nmf.Funds
                   ,nmf.VendorID
                   ,nmf.Remark
                   ,nmf.SrcOrg
                   ,nmf.EAN
                   ,nmf.MaxMnfPrice
                   ,nmf.ExpiTermR
                   ,nmf.ClNm
                   ,nmf.MnfNm
                   ,nmf.PckNm
                   ,nmf.RegNr
                   ,nmf.RegDate
        		   ,coalesce(nmf.Active,1)
                    from TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
                    where not exists
                        (
                            select *
                            from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                            where m.MedicamentsOfRoszdravnadzorID = nmf.ID
                        );
                elsif(_OperationType = _DELETE)
                then
                    update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    set Active = '0'
                    from TMP_MEDICAMENT_OF_ROSZDRAVNADZOR nmf 
                    where m.MedicamentsOfRoszdravnadzorID = nmf.ID;
                else
                    -- update
                    update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    set  DrugID = nmf.DrugID
                        ,TradeNmR = nmf.TradeNmR
                        ,InnR = nmf.InnR
                        ,PackNx = nmf.PackNx
                        ,DosageR = nmf.DosageR
                        ,PackQn = nmf.PackQn
                        ,Pack = nmf.Pack
                        ,DrugFmNmRS = nmf.DrugFmNmRS
                        ,Segment = nmf.Segment
                        ,Year = nmf.Year
                        ,Month = nmf.Month
                        ,Series = nmf.Series
                        ,TotDrugQn = nmf.TotDrugQn
                        ,MnfPrice = nmf.MnfPrice
                        ,PrcPrice = nmf.PrcPrice
                        ,RtlPrice = nmf.RtlPrice
                        ,Funds = nmf.Funds
                        ,VendorID = nmf.VendorID
                        ,Remark = nmf.Remark
                        ,SrcOrg = nmf.SrcOrg
                        ,EAN = nmf.EAN
                        ,MaxMnfPrice = nmf.MaxMnfPrice
                        ,ExpiTermR = nmf.ExpiTermR
                        ,ClNm = nmf.ClNm
                        ,MnfNm = nmf.MnfNm
                        ,PckNm = nmf.PckNm
                        ,RegNr = nmf.RegNr
                        ,RegDate = nmf.RegDate
                        ,Active = coalesce(nmf.Active,'1')
                    from TMP_MEDICAMENT_OF_ROSZDRAVNADZOR nmf
                    where nmf.ID = m.MedicamentsOfRoszdravnadzorID and _OperationType = _UPDATE and m.Active = '1';
        
                    -- insert
                    INSERT INTO PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR
                   (MedicamentsOfRoszdravnadzorID
                   ,DrugID
                   ,TradeNmR
                   ,InnR
                   ,PackNx
                   ,DosageR
                   ,PackQn
                   ,Pack
                   ,DrugFmNmRS
                   ,Segment
                   ,Year
                   ,Month
                   ,Series
                   ,TotDrugQn
                   ,MnfPrice
                   ,PrcPrice
                   ,RtlPrice
                   ,Funds
                   ,VendorID
                   ,Remark
                   ,SrcOrg
                   ,EAN
                   ,MaxMnfPrice
                   ,ExpiTermR
                   ,ClNm
                   ,MnfNm
                   ,PckNm
                   ,RegNr
                   ,RegDate
        		   ,Active)
        		select
        			nmf.ID
                   ,nmf.DrugID
                   ,nmf.TradeNmR
                   ,nmf.InnR
                   ,nmf.PackNx
                   ,nmf.DosageR
                   ,nmf.PackQn
                   ,nmf.Pack
                   ,nmf.DrugFmNmRS
                   ,nmf.Segment
                   ,nmf.Year
                   ,nmf.Month
                   ,nmf.Series
                   ,nmf.TotDrugQn
                   ,nmf.MnfPrice
                   ,nmf.PrcPrice
                   ,nmf.RtlPrice
                   ,nmf.Funds
                   ,nmf.VendorID
                   ,nmf.Remark
                   ,nmf.SrcOrg
                   ,nmf.EAN
                   ,nmf.MaxMnfPrice
                   ,nmf.ExpiTermR
                   ,nmf.ClNm
                   ,nmf.MnfNm
                   ,nmf.PckNm
                   ,nmf.RegNr
                   ,nmf.RegDate
        		   ,coalesce(nmf.Active,'1')
                    from TMP_MEDICAMENT_OF_ROSZDRAVNADZOR nmf
                    where not exists
                        (
                            select *
                            from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                            where m.MedicamentsOfRoszdravnadzorID = nmf.ID
                        )
                            and
                        _OperationType = _INSERT;
                END if;
        
                update PHARM.T_BILL_OF_GOODS bg
                set bg.MedicamentsOfRoszdravnadzor = null
                from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR mor 
                where mor.MedicamentsOfRoszdravnadzorID = bg.MedicamentsOfRoszdravnadzor and mor.Active = '0';
        
                -- Drop tables
                DROP TABLE TMP_MEDICAMENT_OF_ROSZDRAVNADZOR;
                DROP TABLE TMP_MEDICAMENTS_OF_ROSZDRAVNADZOR;
			END if;
		end if;
	End;
End;
$$;