drop procedure if exists sp_RDS_DrugRequest;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-03-14
-- Description: Выгрузка/загрузка заявок по ДЛО в(из) RDS
-- =============================================
CREATE PROCEDURE sp_RDS_DrugRequest
(	
	 "Xml" xml DEFAULT NULL::xml
    ,"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
)
	language plpgsql
as
$$
Begin
	declare
		_Result text;
		,_ContentEncoding varchar(255) := 'utf-8';
		,_Type varchar(255) := 'DRUG_REQUEST';
		,_OperationType varchar(255);
		,_OwnerID uuid := "ID";
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		if ("Xml" is null)
		then
			if ("ID" is null)
		    then 
		    	_OperationType := 'FTLoad';
		    end if;
		    elsif ("IsNew" = '1')
		    then
		    	_OperationType = 'Insert';
		    end if;
		    elsif ("IsNew" = '0' and exists (select * from PHARM.T_DRUG_REQUEST where DrugRequestID = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = '0' and not exists (select * from PHARM.T_DRUG_REQUEST where DrugRequestID = "ID"))
            then
                _OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "DRUG_REQUEST",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
            end if;

			if (_OperationType = 'FTLoad')
			then
				select
				xmlelement
				(
					name "DRUG_REQUEST",
					xmlelement
					(
						name header,
						xmlforest('FTSend' as "TYPE")
					),
					xmlelement
					(
						name body,
						xmlelement
						(
							name "DRUG_REQUEST_LIST",
							(
								select
								xmlagg
								(
									xmlelement
									(
										name "DRUG_REQUEST",
										xmlforest
										(
											 dr.DrugRequestID						as "DrugRequestID"
											,to_char(bdr.RequestDate, 'YYYY-MM-DD')	as "RequestDate"
											,bdr.Number								as "Number"
											,bdr.Active								as "Active"
											,bdr.Additional							as "Additional"
											,to_char(bdr.BeginPeriod, 'YYYY-MM-DD')	as "BeginPeriod"
											,to_char(bdr.EndPeriod, 'YYYY-MM-DD')	as "EndPeriod"
											,bdr.Name								as "Name"
											,dr.ImportFileName						as "ImportFileName"
											,bdr.Comment							as "Comment"
											,lp.LpuCode								as "LpuMain"
											,br.BranchCode							as "BranchCode"
											,drM.DrugRequestID						as "DrugRequestID"
											,dr.RequestType							as "RequestType"
											,bdr.Status								as "Status"
											,bdr.Limit								as "Limit"
											,er.CodeEHR								as "EhrRusEj36pmtjqk"
											,rb.CurrVersion							as "EhrRusEj36pmtjqkVer"
											,erVzn.IdEHR							as "EhrRusVzn17"
											,rbVzn.CurrVersion						as "EhrRusVzn17Ver"
										),
										(
											select 
											xmlagg
											(
												xmlelement
												(
													name "DRUG_DEMAND",
													xmlforest
													(
														 dd.DrugDemandID		as "DrugDemandID"
														,dd.DemandOnPeriod		as "DemandOnPeriod"
														,dd.RequestQuantity		as "RequestQuantity"
														,dd.RequestCode			as "RequestCode"
														,dd.LSFO				as "LSFO"
														,dd.Comment				as "Comment"
														,dd.DrugRequest			as "DrugRequest"
														,dd.SectionCode			as "SectionCode"
														,dd.RequestCatalog		as "RequestCatalog"
													),
													(
														select
														xmlagg
														(
															xmlelement
															(
																name "PHARMACY_DRUG_DEMAND",
																xmlforest
																(
																	pdd.PharmacyDrugDemandID 	as "PharmacyDrugDemandID"
																	,pdd.DemandOnPeriod			as "DemandOnPeriod"
																	,pdd.RequestQuantity		as "RequestQuantity"
																	,pdd.Mont1					as "Mont1"
																	,pdd.Mont2					as "Mont2"
																	,pdd.Mont3					as "Mont3"
																	,pdd.Mont4					as "Mont4"
																	,pdd.Mont5					as "Mont5"
																	,pdd.Mont6					as "Mont6"
																	,pdd.Mont7					as "Mont7"
																	,pdd.Mont8					as "Mont8"
																	,pdd.Mont9					as "Mont9"
																	,pdd.Mont10					as "Mont10"
																	,pdd.Mont11					as "Mont11"
																	,pdd.Mont12					as "Mont12"
																	,pdd.DrugDemand				as "DrugDemand"
																	,pdd.Pharmacy				as "Pharmacy"
																)
															),
															(
																select
																xmlagg
																(
																	xmlelement
																	(
																		name "PATIENT_DRUG_DEMAND",
																		xmlforest
																		(
																			pat.PatientDrugDemandID					as "PatientDrugDemandID"
																			,pat.PharmacyDrugDemand					as "PharmacyDrugDemand"
																			,pat.DemandOnPeriod						as "DemandOnPeriod"
																			,pat.RequestQuantity					as "RequestQuantity"
																			,pat.Mont1								as "Mont1"
																			,pat.Mont2								as "Mont2"
																			,pat.Mont3								as "Mont3"
																			,pat.Mont4								as "Mont4"
																			,pat.Mont5								as "Mont5"
																			,pat.Mont6								as "Mont6"
																			,pat.Mont7								as "Mont7"
																			,pat.Mont8								as "Mont8"
																			,pat.Mont9								as "Mont9"
																			,pat.Mont10								as "Mont10"
																			,pat.Mont11								as "Mont11"
																			,pat.Mont12								as "Mont12"
																			,p.Birthday								as "Birthday"
																			,p.FirstName							as "FirstName"
																			,p.FatherName							as "FatherName"
																			,p.LastName								as "LastName"
																			,eSex.Code								as "Sex"
																			,b.BenefitCode							as "BenefitCode"
																			,pat.Signa								as "Signa"
																			,worker.FIO								as "Post"
																  			,b.BenefitViews							as "BenefitsType"
																			,pat.MKB								as "MKB"
																			,pat.Comment							as "Comment"
																			,p.SNILS								as "Snils"
																			,case when p.DocSeries = '0' then null
																  			else p.DocSeries end	
																													as "DocSeries"
																			,case when p.DocNumber = '0' then null
																			else p.DocNumber end						
																													as "DocNumber"
																			,to_char(p.DocIssueDate, 'YYYY-MM-DD')	as "DocIssueDate"
																			,p.DocIssueOrg							as "DocIssueOrg"
																			,eDt.Code								as "DocType"
																			,polis.Number							as "PolicyNumber"
																			,polis.Serial							as "PolicySerial"
																		)
																	)
																)
																from AKUZ.T_PATIENT_DRUG_DEMAND pat
																join AKUZ.T_PATIENT p on p.PatientID = pat.Patient
																join VCLib.T_ENUMS eDt on eDt.EnumID = p.DocType
																join VCLib.T_ENUMS eSex on eSex.EnumID = p.Sex
																left join AKUZ.T_BENEFITS b on b.BenefitsID = pat.Benefits
																left join AKUZ.T_POST post on post.ResourceID = pat.Post
																left join AKUZ.T_WORKER worker on post.Worker = worker.WorkerID
																left join AKUZ.T_POLICY polis on polis.PolicyID = dbo.fn_GetOmsPolicy(pat.Patient, "dateTime")
																where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
															)
														)
													)
													from AKUZ.T_PHARMACY_DRUG_DEMAND pdd
													where dd.DrugDemandID = pdd.DrugDemand
												)
											)
										)
										from AKUZ.T_DRUG_DEMAND dd
										where dd.DrugRequest = dr.DrugRequestID
									)
								)
							)
							from AKUZ.T_DRUG_REQUEST dr
							join AKUZ.T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
							join AKUZ.T_LPU_MAIN lp on lp.LpuID = bdr.LpuMain
							left join AKUZ.T_BRANCH br on br.LpuID = dr.Branch
							left join AKUZ.T_DRUG_REQUEST drM on drM.DrugRequestID = bdr.DrugRequestMain
							left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = bdr.EhrRusEj36pmtjqk
							left join EHR.T_EHR_RUS_REF_BOOKS rb on rb.EhrRusRefBooksID = er.EhrRusRefBooks
							left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = bdr.EhrRusVzn17
							left join EHR.T_EHR_RUS_REF_BOOKS rbVzn on rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
							where bdr.Status = 3
						)
					)
				);
			end if;
			elsif (_OperationType <> 'Delete')
			then
				select
				xmlelement
				(
					name "DRUG_REQUEST",
					xmlelement
					(
						name header,
						xmlforest (_OperationType as "TYPE")
					),
					xmlforest
					(
						"ID"                  	as "DrugRequestID"
						,bdr.RequestDate		as "RequestDate"
						,bdr.Number				as "Number"
						,bdr.Active				as "Active"
						,bdr.Additional			as "Additional"
						,bdr.BeginPeriod		as "BeginPeriod"
						,bdr.EndPeriod			as "EndPeriod"
						,bdr.Name				as "Name"
						,dr.ImportFileName		as "ImportFileName"
						,bdr.Comment			as "Comment"
						,lp.LpuCode				as "LpuMain"
						,drM.DrugRequestID		as "DrugRequestID"
						,dr.RequestType			as "RequestType"
						,br.BranchCode			as "BranchCode"
						,bdr.Status				as "Status"
						,bdr.Limit				as "Limit"
						,er.CodeEHR				as "EhrRusEj36pmtjqk"
						,rb.CurrVersion			as "EhrRusEj36pmtjqkVer"
						,erVzn.IdEHR			as "EhrRusVzn17"
						,rbVzn.CurrVersion		as "EhrRusVzn17Ver"
					),
					(
						select
						xmlagg
						(
							name "DRUG_DEMAND",
							xmlforest
							(
								 dd.DrugDemandID		as "DrugDemandID"
								,dd.DemandOnPeriod	as "DemandOnPeriod"
								,dd.RequestQuantity	as "RequestQuantity"
								,dd.RequestCode		as "RequestCode"
								,dd.LSFO				as "LSFO"
								,dd.Comment			as "Comment"
								,dd.DrugRequest		as "DrugRequest"
								,dd.SectionCode		as "SectionCode"
								,dd.RequestCatalog	as "RequestCatalog"
							),
							(
								select
								xmlagg
								(
									xmlelement
									(
										name "PHARMACY_DRUG_DEMAND",
										xmlforest
										(
											 pdd.PharmacyDrugDemandID	as "PharmacyDrugDemandID"
											,pdd.DemandOnPeriod			as "DemandOnPeriod"
											,pdd.RequestQuantity		as "RequestQuantity"
											,pdd.Mont1					as "Mont1"
											,pdd.Mont2					as "Mont2"
											,pdd.Mont3					as "Mont3"
											,pdd.Mont4					as "Mont4"
											,pdd.Mont5					as "Mont5"
											,pdd.Mont6					as "Mont6"
											,pdd.Mont7					as "Mont7"
											,pdd.Mont8					as "Mont8"
											,pdd.Mont9					as "Mont9"
											,pdd.Mont10					as "Mont10"
											,pdd.Mont11					as "Mont11"
											,pdd.Mont12					as "Mont12"
											,pdd.DrugDemand				as "DrugDemand"
											,pdd.Pharmacy				as "Pharmacy"
										),
										(
											select
											xmlagg
											(
												xmlelement
												(
													name "PATIENT_DRUG_DEMAND",
													xmlforest
													(
													pat.PatientDrugDemandID					as "PatientDrugDemandID"
													,pat.PharmacyDrugDemand					as "PharmacyDrugDemand"
													,pat.DemandOnPeriod						as "DemandOnPeriod"
													,pat.RequestQuantity					as "RequestQuantity"
													,pat.Mont1								as "Mont1"
													,pat.Mont2								as "Mont2"
													,pat.Mont3								as "Mont3"
													,pat.Mont4								as "Mont4"
													,pat.Mont5								as "Mont5"
													,pat.Mont6								as "Mont6"
													,pat.Mont7								as "Mont7"
													,pat.Mont8								as "Mont8"
													,pat.Mont9								as "Mont9"
													,pat.Mont10								as "Mont10"
													,pat.Mont11								as "Mont11"
													,pat.Mont12								as "Mont12"
													,p.Birthday								as "Birthday"
													,p.FirstName							as "FirstName"
													,p.FatherName							as "FatherName"
													,p.LastName								as "LastName"
													,eSex.Code								as "Sex"
													,b.BenefitCode							as "BenefitCode"
													,pat.Signa								as "Signa"
													,worker.FIO								as "Post"
													,b.BenefitViews							as "BenefitsType"
													,pat.MKB								as "MKB"
													,pat.Comment							as "Comment"
													,p.SNILS								as "Snils"
													,case 
													when p.DocSeries = '0' then null
													else p.DocSeries end					as "DocSeries"
													,case 
													when p.DocNumber = '0' then null
													else p.DocNumber end					as "DocNumber"
													,to_char(p.DocIssueDate, 'YYYY-MM-DD')  as "DocIssueDate"
													,p.DocIssueOrg							as "DocIssueOrg"
													,eDt.Code								as "DocType"
													,polis.Number							as "PolicyNumber"
													,polis.Serial							as "PolicySerial"
													)
												)
											)
											from AKUZ.T_PATIENT_DRUG_DEMAND pat
											join AKUZ.T_PATIENT p on p.PatientID = pat.Patient
											join VClib.T_ENUMS eDt on eDt.EnumID = p.DocType
											join VCLib.T_ENUMS eSex on eSex.EnumID = p.Sex
											left join AKUZ.T_BENEFITS b on b.BenefitsID = pat.Benefits
											left join AKUZ.T_POST post on post.ResourceID = pat.Post
											left join AKUZ.T_WORKER worker on post.Worker = worker.WorkerID
											left join AKUZ.T_POLICY polis on polis.PolicyID = dbo.fn_GetOmsPolicy(pat.Patient, "dateTime")
											where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
										)
									)
								)
								from AKUZ.T_PHARMACY_DRUG_DEMAND pdd
								where dd.DrugDemandID = pdd.DrugDemand
							)
						)
					)
					from AKUZ.T_DRUG_DEMAND dd
					where dd.DrugRequest = dr.DrugRequestID
				)
				from AKUZ.T_DRUG_REQUEST dr
				join AKUZ.T_BASE_DRUG_REQUEST bdr on bdr.DrugRequestID = dr.DrugRequestID
				join AKUZ.T_LPU_MAIN lp on lp.LpuID = bdr.LpuMain
				left join AKUZ.T_BRANCH br on br.LpuID = dr.Branch
				left join AKUZ.T_DRUG_REQUEST drM on drM.DrugRequestID = bdr.DrugRequestMain
				left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = bdr.EhrRusEj36pmtjqk
				left join EHR.T_EHR_RUS_REF_BOOKS rb on rb.EhrRusRefBooksID = er.EhrRusRefBooks
				left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = bdr.EhrRusVzn17
				left join EHR.T_EHR_RUS_REF_BOOKS rbVzn ON rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
				into _Result
				where dr.DrugRequestID = "ID";
			end if;

			declare _LpuCode varchar(6);
			_LpuCode := select LpuMain from RDS.T_DRUG_REQUEST where DrugRequestID = "ID";

			-- Отправка
			INSERT INTO RDS.T_RABBIT_SEND_OBJECT
			    (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v4()--(<RabbitSendObjectID, uniqueidentifier,>
			      ,s.ServerName--,<QueueName, varchar(255),>
			      ,ConvertTextToBinaryZip(_Result, '')--,<Content, text,>
			      ,null--,<MessageID, varchar(255),>
			      ,null--,<Headers, varchar(255),>
			      ,_ContentEncoding--,<ContentEncoding, varchar(255),>
			      ,_Type--,<Type, varchar(255),>
			      ,"User"--,<UserID, varchar(255),>
			      ,0--,<State, int,>)
			      ,"dateTime"--,<CreateDateTime, datetime,>)
			      ,_OwnerID--,<OwnerID, varchar(255),>
			from RDS.T_SYNC_SERVERS s
			where s.ServerType <> 1 and  
			exists 
			(
				select * 
				from RDS.T_SYNC_SERVERS s1
				join RDS.T_SERVERS_LINK sl on sl.SyncServers = s1.SyncServersID
				join RDS.T_SERVERS_LINK_LPU ll on ll.ServersLinkID = sl.ServersLinkID
				join RDS.T_LPU_MAIN lp on lp.MCOD = ll.LpuMain
				where lp.MCOD = _LpuCode and s1.SyncServersID = s.SyncServersID
			);
		end if;
		elsif ("Xml" is not null)
		then
			_OperationType := (xpath('/DRUG_REQUEST/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
			_OwnerID := (xpath('/DRUG_REQUEST/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
			_Result = cast("Xml" as text); 

			drop table if exists Tmp_DRUG_REQUEST_LIST;
			create temp table Tmp_DRUG_REQUEST_LIST
			as
			select
				(xpath('DRUG_REQUEST/DrugRequestID[1]/text()', x))[1]::text::UUID								as DrugRequestID
				,to_timestamp((xpath('DRUG_REQUEST/RequestDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')			as RequestDate
				,(xpath('DRUG_REQUEST/Active[1]/text()', x))[1]::text::boolean									as SerialNumber
				,(xpath('DRUG_REQUEST/Number[1]/text()', x))[1]::text::int										as Number
				,(xpath('DRUG_REQUEST/BeginPeriod[1]/text()', x))[1]::text::int									as BeginPeriod
				,to_timestamp((xpath('DRUG_REQUEST/BeginPeriod[1]/text()', x))[1]::text, 'YYYY-MM-DD')			as BeginPeriod
				,to_timestamp((xpath('DRUG_REQUEST/EndPeriod[1]/text()', x))[1]::text, 'YYYY-MM-DD')			as EndPeriod
				,(xpath('DRUG_REQUEST/Name[1]/text()', x))[1]::text::varchar(255)								as Name
				,(xpath('DRUG_REQUEST/ImportFileName[1]/text()', x))[1]::varchar(255)							as ImportFileName
				,(xpath('DRUG_REQUEST/Comment[1]/text()', x))[1]::varchar(8000)									as Comment
				,(xpath('DRUG_REQUEST/LpuMain[1]/text()', x))[1]::varchar(6)									as LpuMain
				,(xpath('DRUG_REQUEST/DrugRequestMain[1]/text()', x))[1]::text::uuid							as DrugRequestMain
				,(xpath('DRUG_REQUEST/RequestType[1]/text()', x))[1]::text::int									as RequestType
				,(xpath('DRUG_REQUEST/Status[1]/text()', x))[1]::text::int										as Status
				,(xpath('DRUG_REQUEST/Limit[1]/text()', x))[1]::text::decimal(31, 10)							as Limit
				,(xpath('DRUG_REQUEST/BranchCode[1]/text()', x))[1]::varchar(16)								as BranchCode
				,(xpath('DRUG_REQUEST/LastEditDate[1]/text()', x))[1]::text::decimal(31, 10)					as LastEditDate
				,(xpath('DRUG_REQUEST/Additional[1]/text()', x))[1]::text::boolean								as Additional
				,(xpath('DRUG_REQUEST/EhrRusEj36pmtjqk[1]/text()', x))[1]::varchar(50)							as EhrRusEj36pmtjqk
				,(xpath('DRUG_REQUEST/EhrRusEj36pmtjqkVer[1]/text()', x))[1]::varchar(50)						as EhrRusEj36pmtjqkVer
				,(xpath('DRUG_REQUEST/EhrRusVzn17[1]/text()', x))[1]::varchar(50)								as EhrRusVzn17
				,(xpath('DRUG_REQUEST/EhrRusVzn17Ver[1]/text()', x))[1]::varchar(50)							as EhrRusVzn17Ver
			from unnest(xpath('/DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST', "Xml")) as x
            where _OperationType = 'FTSend';

			
			drop table if exists Tmp_DRUG_REQUEST;
			create temp table Tmp_DRUG_REQUEST
			as
			select
				(xpath('DRUG_REQUEST[1]/DrugRequestID[1]/text()', x))[1]::text::UUID								as DrugRequestID
				,to_timestamp((xpath('DRUG_REQUEST[1]/RequestDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')			as RequestDate
				,(xpath('DRUG_REQUEST[1]/Active[1]/text()', x))[1]::text::boolean									as SerialNumber
				,(xpath('DRUG_REQUEST[1]/Number[1]/text()', x))[1]::text::int										as Number
				,(xpath('DRUG_REQUEST[1]/BeginPeriod[1]/text()', x))[1]::text::int									as BeginPeriod
				,to_timestamp((xpath('DRUG_REQUEST[1]/BeginPeriod[1]/text()', x))[1]::text, 'YYYY-MM-DD')			as BeginPeriod
				,to_timestamp((xpath('DRUG_REQUEST[1]/EndPeriod[1]/text()', x))[1]::text, 'YYYY-MM-DD')				as EndPeriod
				,(xpath('DRUG_REQUEST[1]/Name[1]/text()', x))[1]::text::varchar(255)								as Name
				,(xpath('DRUG_REQUEST[1]/ImportFileName[1]/text()', x))[1]::varchar(255)							as ImportFileName
				,(xpath('DRUG_REQUEST[1]/Comment[1]/text()', x))[1]::varchar(8000)									as Comment
				,(xpath('DRUG_REQUEST[1]/LpuMain[1]/text()', x))[1]::varchar(6)										as LpuMain
				,(xpath('DRUG_REQUEST[1]/DrugRequestMain[1]/text()', x))[1]::text::uuid								as DrugRequestMain
				,(xpath('DRUG_REQUEST[1]/RequestType[1]/text()', x))[1]::text::int									as RequestType
				,(xpath('DRUG_REQUEST[1]/Status[1]/text()', x))[1]::text::int										as Status
				,(xpath('DRUG_REQUEST[1]/Limit[1]/text()', x))[1]::text::decimal(31, 10)							as Limit
				,(xpath('DRUG_REQUEST[1]/BranchCode[1]/text()', x))[1]::varchar(16)									as BranchCode
				,(xpath('DRUG_REQUEST[1]/LastEditDate[1]/text()', x))[1]::text::decimal(31, 10)						as LastEditDate
				,(xpath('DRUG_REQUEST[1]/Additional[1]/text()', x))[1]::text::boolean								as Additional
				,(xpath('DRUG_REQUEST[1]/EhrRusEj36pmtjqk[1]/text()', x))[1]::varchar(50)							as EhrRusEj36pmtjqk
				,(xpath('DRUG_REQUEST[1]/EhrRusEj36pmtjqkVer[1]/text()', x))[1]::varchar(50)						as EhrRusEj36pmtjqkVer
				,(xpath('DRUG_REQUEST[1]/EhrRusVzn17[1]/text()', x))[1]::varchar(50)								as EhrRusVzn17
				,(xpath('DRUG_REQUEST[1]/EhrRusVzn17Ver[1]/text()', x))[1]::varchar(50)								as EhrRusVzn17Ver
			from unnest(xpath('DRUG_REQUEST', "Xml")) as x
            where _OperationType = 'FTSend';

			Drop Table If Exists Tmp_DRUG_DEMAND_LIST;
			Create Temp Table Tmp_DRUG_DEMAND_LIST
			as
			SELECT	
				 (xpath('/DRUG_DEMAND[1]/DrugDemandID[1]/text()', x))[1]::text::UUID						as DrugDemandID
				,(xpath('/DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12,2)				as DemandOnPeriod
				,(xpath('/DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12,2)			as RequestQuantity
				,(xpath('/DRUG_DEMAND[1]/RequestCode[1]/text()', x))[1]::text::int							as RequestCode
				,(xpath('/DRUG_DEMAND[1]/LSFO[1]/text()', x))[1]::text::int									as LSFO
				,(xpath('/DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000)							as Comment
				,(xpath('/DRUG_DEMAND[1]/DrugRequest[1]/text()', x))[1]::text::UUID							as DrugRequest
				,(xpath('/DRUG_DEMAND[1]/SectionCode[1]/text()', x))[1]::varchar(255)						as SectionCode			
				,(xpath('/DRUG_DEMAND[1]/RequestCatalog[1]/text()', x))[1]::text::UUID						as RequestCatalog
			from unnest(xpath('/DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND', "Xml"::xml)) x
			where _OperationType <> 'FTSend';


			Drop Table If Exists Tmp_DRUG_DEMAND;
			Create Temp Table Tmp_DRUG_DEMAND
			as
			SELECT	
				 (xpath('/DRUG_DEMAND[1]/DrugDemandID[1]/text()', x))[1]::text::UUID								as DrugDemandID
				,(xpath('/DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12,2)						as DemandOnPeriod
				,(xpath('/DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12,2)					as RequestQuantity
				,(xpath('/DRUG_DEMAND[1]/RequestCode[1]/text()', x))[1]::text::int									as RequestCode
				,(xpath('/DRUG_DEMAND[1]/LSFO[1]/text()', x))[1]::text::int											as LSFO
				,(xpath('/DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000)									as Comment
				,(xpath('/DRUG_DEMAND[1]/DrugRequest[1]/text()', x))[1]::text::UUID									as DrugRequest
				,(xpath('/DRUG_DEMAND[1]/SectionCode[1]/text()', x))[1]::varchar(255)								as SectionCode			
				,(xpath('/DRUG_DEMAND[1]/RequestCatalog[1]/text()', x))[1]::text::UUID								as RequestCatalog
			from unnest(xpath('/DRUG_REQUEST/DRUG_DEMAND', "Xml"::xml)) x
			where _OperationType <> 'FTSend';

			drop table if exists Tmp_PHARMACY_DRUG_DEMAND_LIST;
			create temp table Tmp_PHARMACY_DRUG_DEMAND_LIST
			as
			select
				(xpath('/PHARMACY_DRUG_DEMAND[1]/PatientDrugDemandID[1]/text()', x))[1]::text::UUID					as PatientDrugDemandID
				(xpath('/PHARMACY_DRUG_DEMAND[1]/PharmacyDrugDemand[1]/text()', x))[1]::text::UUID					as PharmacyDrugDemand
				(xpath('/PHARMACY_DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12, 2)			as DemandOnPeriod
				(xpath('/PHARMACY_DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12, 2)			as RequestQuantity
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont1[1]/text()', x))[1]::text::decimal(12, 2)						as Mont1
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont2[1]/text()', x))[1]::text::decimal(12, 2)						as Mont2
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont3[1]/text()', x))[1]::text::decimal(12, 2)						as Mont3
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont4[1]/text()', x))[1]::text::decimal(12, 2)						as Mont4
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont5[1]/text()', x))[1]::text::decimal(12, 2)						as Mont5
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont6[1]/text()', x))[1]::text::decimal(12, 2)						as Mont6
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont7[1]/text()', x))[1]::text::decimal(12, 2)						as Mont7
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont8[1]/text()', x))[1]::text::decimal(12, 2)						as Mont8
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont9[1]/text()', x))[1]::text::decimal(12, 2)						as Mont9
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont10[1]/text()', x))[1]::text::decimal(12, 2)					as Mont10
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont11[1]/text()', x))[1]::text::decimal(12, 2)					as Mont11
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont12[1]/text()', x))[1]::text::decimal(12, 2)					as Mont12
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Pharmacy[1]/text()', x))[1]::text::int								as Pharmacy
				(xpath('/PHARMACY_DRUG_DEMAND[1]/DrugDemand[1]/text()', x))[1]::text::uuid							as DrugDemand
			from unnest(xpath('/DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND', "Xml"::xml)) x
			where _OperationType <> 'FTSend';
			
			drop table if exists Tmp_PHARMACY_DRUG_DEMAND;
			create temp table Tmp_PHARMACY_DRUG_DEMAND
			as
			select
				(xpath('/PHARMACY_DRUG_DEMAND[1]/PatientDrugDemandID[1]/text()', x))[1]::text::UUID					as PatientDrugDemandID
				(xpath('/PHARMACY_DRUG_DEMAND[1]/PharmacyDrugDemand[1]/text()', x))[1]::text::UUID					as PharmacyDrugDemand
				(xpath('/PHARMACY_DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12, 2)			as DemandOnPeriod
				(xpath('/PHARMACY_DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12, 2)			as RequestQuantity
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont1[1]/text()', x))[1]::text::decimal(12, 2)						as Mont1
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont2[1]/text()', x))[1]::text::decimal(12, 2)						as Mont2
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont3[1]/text()', x))[1]::text::decimal(12, 2)						as Mont3
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont4[1]/text()', x))[1]::text::decimal(12, 2)						as Mont4
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont5[1]/text()', x))[1]::text::decimal(12, 2)						as Mont5
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont6[1]/text()', x))[1]::text::decimal(12, 2)						as Mont6
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont7[1]/text()', x))[1]::text::decimal(12, 2)						as Mont7
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont8[1]/text()', x))[1]::text::decimal(12, 2)						as Mont8
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont9[1]/text()', x))[1]::text::decimal(12, 2)						as Mont9
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont10[1]/text()', x))[1]::text::decimal(12, 2)					as Mont10
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont11[1]/text()', x))[1]::text::decimal(12, 2)					as Mont11
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Mont12[1]/text()', x))[1]::text::decimal(12, 2)					as Mont12
				(xpath('/PHARMACY_DRUG_DEMAND[1]/Pharmacy[1]/text()', x))[1]::text::int								as Pharmacy
				(xpath('/PHARMACY_DRUG_DEMAND[1]/DrugDemand[1]/text()', x))[1]::text::uuid							as DrugDemand
			from unnest(xpath('/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND', "Xml"::xml)) x
			where _OperationType <> 'FTSend';

			drop table if exists Tmp_PATIENT_DRUG_DEMAND_LIST;
			create temp table Tmp_PATIENT_DRUG_DEMAND_LIST
			as
			select
				(xpath('/DRUG_DEMAND[1]/PatientDrugDemandID[1]/text()', x))[1]::text::UUID					as PatientDrugDemandID
				(xpath('/DRUG_DEMAND[1]/PharmacyDrugDemand[1]/text()', x))[1]::text::UUID					as PharmacyDrugDemand
				(xpath('/DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12, 2)				as DemandOnPeriod
				(xpath('/DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12, 2)			as RequestQuantity
				(xpath('/DRUG_DEMAND[1]/Mont1[1]/text()', x))[1]::text::decimal(12, 2)						as Mont1
				(xpath('/DRUG_DEMAND[1]/Mont2[1]/text()', x))[1]::text::decimal(12, 2)						as Mont2
				(xpath('/DRUG_DEMAND[1]/Mont3[1]/text()', x))[1]::text::decimal(12, 2)						as Mont3
				(xpath('/DRUG_DEMAND[1]/Mont4[1]/text()', x))[1]::text::decimal(12, 2)						as Mont4
				(xpath('/DRUG_DEMAND[1]/Mont5[1]/text()', x))[1]::text::decimal(12, 2)						as Mont5
				(xpath('/DRUG_DEMAND[1]/Mont6[1]/text()', x))[1]::text::decimal(12, 2)						as Mont6
				(xpath('/DRUG_DEMAND[1]/Mont7[1]/text()', x))[1]::text::decimal(12, 2)						as Mont7
				(xpath('/DRUG_DEMAND[1]/Mont8[1]/text()', x))[1]::text::decimal(12, 2)						as Mont8
				(xpath('/DRUG_DEMAND[1]/Mont9[1]/text()', x))[1]::text::decimal(12, 2)						as Mont9
				(xpath('/DRUG_DEMAND[1]/Mont10[1]/text()', x))[1]::text::decimal(12, 2)						as Mont10
				(xpath('/DRUG_DEMAND[1]/Mont11[1]/text()', x))[1]::text::decimal(12, 2)						as Mont11
				(xpath('/DRUG_DEMAND[1]/Mont12[1]/text()', x))[1]::text::decimal(12, 2)						as Mont12
				,to_timestamp((xpath('DRUG_REQUEST[1]/Birthday[1]/text()', x))[1]::text, 'YYYY-MM-DD')		as Birthday
				(xpath('/DRUG_DEMAND[1]/FirstName[1]/text()', x))[1]::varchar(40)							as FirstName
				(xpath('/DRUG_DEMAND[1]/FatherName[1]/text()', x))[1]::varchar(40)							as FatherName
				(xpath('/DRUG_DEMAND[1]/LastName[1]/text()', x))[1]::varchar(40)							as LastName
				(xpath('/DRUG_DEMAND[1]/BenefitCode[1]/text()', x))[1]::text::int							as BenefitCode
				(xpath('/DRUG_DEMAND[1]/BenefitsType[1]/text()', x))[1]::text::int							as BenefitsType
				(xpath('/DRUG_DEMAND[1]/MKB[1]/text()', x))[1]::varchar(10)									as MKB
				(xpath('/DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000)							as Comment
				(xpath('/DRUG_DEMAND[1]/Signa[1]/text()', x))[1]::varchar(256)								as Signa
				(xpath('/DRUG_DEMAND[1]/Post[1]/text()', x))[1]::varchar(256)								as Post
				(xpath('/DRUG_DEMAND[1]/Snils[1]/text()', x))[1]::varchar(14)								as Snils
				(xpath('/DRUG_DEMAND[1]/DocSeries[1]/text()', x))[1]::varchar(16)							as DocSeries
				(xpath('/DRUG_DEMAND[1]/DocNumber[1]/text()', x))[1]::varchar(16)							as DocNumber
				,to_timestamp((xpath('DRUG_REQUEST[1]/DocIssueDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')	as DocIssueDate
				(xpath('/DRUG_DEMAND[1]/DocIssueOrg[1]/text()', x))[1]::varchar(14)							as DocIssueOrg
				(xpath('/DRUG_DEMAND[1]/DocType[1]/text()', x))[1]::text::int								as DocType
				(xpath('/DRUG_DEMAND[1]/PolicyNumber[1]/text()', x))[1]::varchar(32)						as PolicyNumber
				(xpath('/DRUG_DEMAND[1]/PolicySerial[1]/text()', x))[1]::varchar(12)						as PolicySerial
				(xpath('/DRUG_DEMAND[1]/Sex[1]/text()', x))[1]::varchar(255)								as Sex
			from unnest(xpath('DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND', "Xml"::xml)) x
			where _OperationType <> 'FTLoad';
			
			drop table if exists Tmp_PATIENT_DRUG_DEMAND;
			create temp table Tmp_PATIENT_DRUG_DEMAND
			as
			select
				(xpath('/DRUG_DEMAND[1]/PatientDrugDemandID[1]/text()', x))[1]::text::UUID					as PatientDrugDemandID
				(xpath('/DRUG_DEMAND[1]/PharmacyDrugDemand[1]/text()', x))[1]::text::UUID					as PharmacyDrugDemand
				(xpath('/DRUG_DEMAND[1]/DemandOnPeriod[1]/text()', x))[1]::text::decimal(12, 2)				as DemandOnPeriod
				(xpath('/DRUG_DEMAND[1]/RequestQuantity[1]/text()', x))[1]::text::decimal(12, 2)			as RequestQuantity
				(xpath('/DRUG_DEMAND[1]/Mont1[1]/text()', x))[1]::text::decimal(12, 2)						as Mont1
				(xpath('/DRUG_DEMAND[1]/Mont2[1]/text()', x))[1]::text::decimal(12, 2)						as Mont2
				(xpath('/DRUG_DEMAND[1]/Mont3[1]/text()', x))[1]::text::decimal(12, 2)						as Mont3
				(xpath('/DRUG_DEMAND[1]/Mont4[1]/text()', x))[1]::text::decimal(12, 2)						as Mont4
				(xpath('/DRUG_DEMAND[1]/Mont5[1]/text()', x))[1]::text::decimal(12, 2)						as Mont5
				(xpath('/DRUG_DEMAND[1]/Mont6[1]/text()', x))[1]::text::decimal(12, 2)						as Mont6
				(xpath('/DRUG_DEMAND[1]/Mont7[1]/text()', x))[1]::text::decimal(12, 2)						as Mont7
				(xpath('/DRUG_DEMAND[1]/Mont8[1]/text()', x))[1]::text::decimal(12, 2)						as Mont8
				(xpath('/DRUG_DEMAND[1]/Mont9[1]/text()', x))[1]::text::decimal(12, 2)						as Mont9
				(xpath('/DRUG_DEMAND[1]/Mont10[1]/text()', x))[1]::text::decimal(12, 2)						as Mont10
				(xpath('/DRUG_DEMAND[1]/Mont11[1]/text()', x))[1]::text::decimal(12, 2)						as Mont11
				(xpath('/DRUG_DEMAND[1]/Mont12[1]/text()', x))[1]::text::decimal(12, 2)						as Mont12
				,to_timestamp((xpath('DRUG_REQUEST[1]/Birthday[1]/text()', x))[1]::text, 'YYYY-MM-DD')		as Birthday
				(xpath('/DRUG_DEMAND[1]/FirstName[1]/text()', x))[1]::varchar(40)							as FirstName
				(xpath('/DRUG_DEMAND[1]/FatherName[1]/text()', x))[1]::varchar(40)							as FatherName
				(xpath('/DRUG_DEMAND[1]/LastName[1]/text()', x))[1]::varchar(40)							as LastName
				(xpath('/DRUG_DEMAND[1]/BenefitCode[1]/text()', x))[1]::text::int							as BenefitCode
				(xpath('/DRUG_DEMAND[1]/BenefitsType[1]/text()', x))[1]::text::int							as BenefitsType
				(xpath('/DRUG_DEMAND[1]/MKB[1]/text()', x))[1]::varchar(10)									as MKB
				(xpath('/DRUG_DEMAND[1]/Comment[1]/text()', x))[1]::varchar(8000)							as Comment
				(xpath('/DRUG_DEMAND[1]/Signa[1]/text()', x))[1]::varchar(256)								as Signa
				(xpath('/DRUG_DEMAND[1]/Post[1]/text()', x))[1]::varchar(256)								as Post
				(xpath('/DRUG_DEMAND[1]/Snils[1]/text()', x))[1]::varchar(14)								as Snils
				(xpath('/DRUG_DEMAND[1]/DocSeries[1]/text()', x))[1]::varchar(16)							as DocSeries
				(xpath('/DRUG_DEMAND[1]/DocNumber[1]/text()', x))[1]::varchar(16)							as DocNumber
				,to_timestamp((xpath('DRUG_REQUEST[1]/DocIssueDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')	as DocIssueDate
				(xpath('/DRUG_DEMAND[1]/DocIssueOrg[1]/text()', x))[1]::varchar(14)							as DocIssueOrg
				(xpath('/DRUG_DEMAND[1]/DocType[1]/text()', x))[1]::text::int								as DocType
				(xpath('/DRUG_DEMAND[1]/PolicyNumber[1]/text()', x))[1]::varchar(32)						as PolicyNumber
				(xpath('/DRUG_DEMAND[1]/PolicySerial[1]/text()', x))[1]::varchar(12)						as PolicySerial
				(xpath('/DRUG_DEMAND[1]/Sex[1]/text()', x))[1]::varchar(255)								as Sex
			from unnest(xpath('DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND', "Xml"::xml)) x
			where _OperationType <> 'FTLoad';

			drop table if exists Tmp_DRUG_REQUEST_COMMENTS_LIST;
			create temp table Tmp_DRUG_REQUEST_COMMENTS_LIST
			as
			select
				(xpath('/DRUG_REQUEST_COMMENTS[1]/DrugRequestCommentsID[1]/text()', x))[1]::text::UUID			as DrugRequestCommentsID
				(xpath('/DRUG_REQUEST_COMMENTS[1]/Comment[1]/text()', x))[1]::varchar(8000)						as Comment
				(xpath('/DRUG_REQUEST_COMMENTS[1]/RequestCode[1]/text()', x))[1]::text::int						as RequestCode
				(xpath('/DRUG_REQUEST_COMMENTS[1]/Processed[1]/text()', x))[1]::text::int							as Processed
				(xpath('/DRUG_REQUEST_COMMENTS[1]/UserProcessed[1]/text()', x))[1]::text::int						as UserProcessed
				(xpath('/DRUG_REQUEST_COMMENTS[1]/Worker[1]/text()', x))[1]::varchar(500)							as Worker
				(xpath('/DRUG_REQUEST_COMMENTS[1]/DrugRequest[1]/text()', x))[1]::text::int						as DrugRequest
			from unnest(xpath('DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_REQUEST_COMMENTS', "Xml"::xml)) x
			where _OperationType = 'FTSend';
			
			drop table if exists Tmp_DRUG_REQUEST_COMMENTS;
			create temp table Tmp_DRUG_REQUEST_COMMENTS
			as
			select
				(xpath('/DRUG_REQUEST_COMMENTS[1]/DrugRequestCommentsID[1]/text()', x))[1]::text::UUID			as DrugRequestCommentsID
				(xpath('/DRUG_REQUEST_COMMENTS[1]/Comment[1]/text()', x))[1]::varchar(8000)						as Comment
				(xpath('/DRUG_REQUEST_COMMENTS[1]/RequestCode[1]/text()', x))[1]::text::int						as RequestCode
				(xpath('/DRUG_REQUEST_COMMENTS[1]/Processed[1]/text()', x))[1]::text::int							as Processed
				(xpath('/DRUG_REQUEST_COMMENTS[1]/UserProcessed[1]/text()', x))[1]::text::int						as UserProcessed
				(xpath('/DRUG_REQUEST_COMMENTS[1]/Worker[1]/text()', x))[1]::varchar(500)							as Worker
				(xpath('/DRUG_REQUEST_COMMENTS[1]/DrugRequest[1]/text()', x))[1]::text::int						as DrugRequest
			from unnest(xpath('DRUG_REQUEST/DRUG_REQUEST_COMMENTS', "Xml"::xml)) x
			where _OperationType = 'FTSend';

			if (_OperationType = 'FTSend')
			then
				update RDS.T_DRUG_REQUEST dr
			    set  dr.RequestDate			= drl.RequestDate
					,dr.LpuMain				= coalesce(lp.MCOD, tt.LpuMain)
					,dr.Active				= drl.Active
					,dr.DrugRequestMain		= drl.DrugRequestMain
					,dr.Number				= drl.Number
					,dr.RequestType			= drl.RequestType
					,dr.ImportFileName		= drl.ImportFileName
					,dr.Name				= drl.Name
					,dr.EndPeriod			= drl.EndPeriod
					,dr.BeginPeriod			= drl.BeginPeriod
					,dr.Status				= drl.Status
					,dr.Comment				= drl.Comment
					,dr.Limit				= drl.Limit
					,dr.Branch				= tt.BranchID
					,dr.LastEditDate		= coalesce(drl.LastEditDate, getdate())
					,dr.Additional			= coalesce(drl.Additional,0)
					,dr.EhrRusEj36pmtjqk	= ehr.EhrRusID
					,dr.EhrRusVzn17			= ehrVzn.EhrRusID
				from Tmp_DRUG_REQUEST_LIST drl 
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >=	drl.RequestDate))
				left join lateral
				(
					select br.BranchID, br.LpuMain 
					from RDS.T_BRANCH br 
					join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >=	drl.RequestDate))
					where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain)	limit 1
				) tt on true
				left join lateral
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
					left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer limit 1
				) ehr on true
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver limit 1
				) ehrVzn on true
				where drl.DrugRequestID = dr.DrugRequestID;

				INSERT INTO RDS.T_DRUG_REQUEST
			    (
					 DrugRequestID
					,RequestDate
					,LpuMain
					,Active
					,DrugRequestMain
					,Number
					,RequestType
					,ImportFileName
					,Name
					,EndPeriod
					,BeginPeriod
					,Status
					,Comment
					,Limit
					,Branch
					,LastEditDate
					,Additional
					,EhrRusEj36pmtjqk
					,EhrRusVzn17
				)
				SELECT 
						drl.DrugRequestID
			           ,drl.RequestDate
			           ,coalesce(lp.MCOD, tt.LpuMain)
			           ,drl.Active
			           ,drl.DrugRequestMain
			           ,drl.Number
			           ,drl.RequestType
			           ,drl.ImportFileName
			           ,drl.Name
			           ,drl.EndPeriod
			           ,drl.BeginPeriod
			           ,drl.Status
			           ,drl.Comment
			           ,drl.Limit
			           ,tt.BranchID
					   ,coalesce(drl.LastEditDate, Current_TimeStamp::TIMESTAMP)
					   ,coalesce(drl.Additional, '0')
					   ,ehr.EhrRusID
					   ,ehrVzn.EhrRusID
				FROM Tmp_DRUG_REQUEST_LIST drl
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >= drl.RequestDate))
				left join lateral
				(
					select br.BranchID, br.LpuMain 
					from RDS.T_BRANCH br 
						join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >= drl.RequestDate))
					where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain) limit 1
				) tt on true
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer limit 1
				) ehr on true
				left join lateral
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver limit 1
				) ehrVzn on true
				where not exists (select * from RDS.T_DRUG_REQUEST dr where dr.DrugRequestID = drl.DrugRequestID);

				update RDS.T_DRUG_DEMAND dd
			        set dd.DrugRequest = ddl.DrugRequest
			           ,dd.DemandOnPeriod = ddl.DemandOnPeriod
			           ,dd.RequestQuantity = ddl.RequestQuantity
			           ,dd.RequestCode = ddl.RequestCode
			           ,dd.LSFO = ddl.LSFO
			           ,dd.Comment = ddl.Comment
					   ,dd.SectionCode = ddl.SectionCode
					   ,dd.PersRequest = coalesce(ddl.PersRequest, '0')
			           ,dd.DrugApplicationListItem = ddl.RequestCatalog
				from Tmp_DRUG_DEMAND_LIST ddl 
				where ddl.DrugDemandID = dd.DrugDemandID;

				INSERT INTO RDS.T_DRUG_DEMAND
				(
					DrugDemandID
					,DrugRequest
					,DemandOnPeriod
					,RequestQuantity
					,RequestCode
					,LSFO
					,Comment
					,SectionCode
					,PersRequest
					,DrugApplicationListItem
				)
				SELECT
					 ddl.DrugDemandID
					,ddl.DrugRequest
					,ddl.DemandOnPeriod
					,ddl.RequestQuantity
					,ddl.RequestCode
					,ddl.LSFO
					,ddl.Comment
					,ddl.SectionCode
					,coalesce(ddl.PersRequest, '0')
					,ddl.RequestCatalog
				FROM Tmp_DRUG_DEMAND_LIST ddl
				where not exists (select * from RDS.T_DRUG_DEMAND dd where dd.DrugDemandID = ddl.DrugDemandID);
			

				update RDS.T_PHARMACY_DRUG_DEMAND pdd
			    set 
					 DrugDemand			= pddl.DrugDemand
					,DemandOnPeriod		= pddl.DemandOnPeriod
					,Pharmacy			= pddl.Pharmacy
					,RequestQuantity	= pddl.RequestQuantity
					,Mont1				= pddl.Mont1
					,Mont2				= pddl.Mont2
					,Mont3				= pddl.Mont3
					,Mont4				= pddl.Mont4
					,Mont5				= pddl.Mont5
					,Mont6				= pddl.Mont6
					,Mont7				= pddl.Mont7
					,Mont8				= pddl.Mont8
					,Mont9				= pddl.Mont9
					,Mont10				= pddl.Mont10
					,Mont11				= pddl.Mont11
					,Mont12				= pddl.Mont12
				from Tmp_PHARMACY_DRUG_DEMAND_LIST pddl 
				where pddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID;

				INSERT INTO RDS.T_PHARMACY_DRUG_DEMAND
			    (
					 PharmacyDrugDemandID
					,DrugDemand
					,DemandOnPeriod
					,Pharmacy
					,RequestQuantity
					,Mont1
					,Mont2
					,Mont3
					,Mont4
					,Mont5
					,Mont6
					,Mont7
					,Mont8
					,Mont9
					,Mont10
					,Mont11
					,Mont12
				)
				SELECT
					 pddl.PharmacyDrugDemandID
					,pddl.DrugDemand
					,pddl.DemandOnPeriod
					,pddl.Pharmacy
					,pddl.RequestQuantity
					,pddl.Mont1
					,pddl.Mont2
					,pddl.Mont3
					,pddl.Mont4
					,pddl.Mont5
					,pddl.Mont6
					,pddl.Mont7
					,pddl.Mont8
					,pddl.Mont9
					,pddl.Mont10
					,pddl.Mont11
					,pddl.Mont12
				FROM Tmp_PHARMACY_DRUG_DEMAND_LIST pddl
				where not exists (select * from RDS.T_PHARMACY_DRUG_DEMAND pdd where pdd.PharmacyDrugDemandID = pddl.PharmacyDrugDemandID);

				update RDS.T_PATIENT_DRUG_DEMAND pdd
				set
					 pdd.DemandOnPeriod			= pddl.DemandOnPeriod
					,pdd.PharmacyDrugDemand		= pddl.PharmacyDrugDemand
					,pdd.Comment				= pddl.Comment
					,pdd.Benefits				= (select b.BenefitsID from RDS.T_BENEFITS b where b.BenefitCode = ddl.BenefitCode and b.BenefitViews = pddl.BenefitType limit 1)
					,pdd.MKB					= pddl.MKB
					,pdd.FirstName				= pddl.FirstName
					,pdd.FatherName				= pddl.FatherName
					,pdd.LastName				= pddl.LastName
					,pdd.Birthday				= pddl.Birthday
					,pdd.RequestQuantity		= pddl.RequestQuantity
					,pdd.Mont1					= pddl.Mont1
					,pdd.Mont2					= pddl.Mont2
					,pdd.Mont3					= pddl.Mont3
					,pdd.Mont4					= pddl.Mont4
					,pdd.Mont5					= pddl.Mont5
					,pdd.Mont6					= pddl.Mont6
					,pdd.Mont7					= pddl.Mont7
					,pdd.Mont8					= pddl.Mont8
					,pdd.Mont9					= pddl.Mont9
					,pdd.Mont10					= pddl.Mont10
					,pdd.Mont11					= pddl.Mont11
					,pdd.Mont12					= pddl.Mont12
					,pdd.Signa					= pddl.Signa
					,pdd.Post					= pddl.Post
					,pdd.Snils					= pddl.Snils
					,pdd.DocSeries				= pddl.DocSeries
					,pdd.DocNumber				= pddl.DocNumber
					,pdd.DocIssueDate			= pddl.DocIssueDate
					,pdd.DocIssueOrg			= pddl.DocIssueOrg
					,pdd.DocType				= eDoc.EnumID
					,pdd.OmsNumber				= pddl.OmsNumber
					,pdd.OmsSerial				= pddl.OmsSerial
					,pdd.EhrRus5o5e88vhc6		= dul.EhrRus5o5e88vhc6
					,pdd.Sex					= eSex.EnumID
				from Tmp_PATIENT_DRUG_DEMAND_LIST pddl 
				left join VCLib.T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
				left join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex
				where pddl.PatientDrugDemandID = pdd.PatientDrugDemandID;

				INSERT INTO RDS.T_PATIENT_DRUG_DEMAND
						(
						 PatientDrugDemandID
						,DemandOnPeriod
						,PharmacyDrugDemand
						,Comment
						,Benefits
						,MKB
						,FirstName
						,FatherName
						,LastName
						,Birthday
			            ,RequestQuantity
			            ,Mont1
			            ,Mont2
			            ,Mont3
			            ,Mont4
			            ,Mont5
			            ,Mont6
			            ,Mont7
			            ,Mont8
			            ,Mont9
			            ,Mont10
			            ,Mont11
			            ,Mont12
					    ,Signa
					    ,Post
						,Snils
						,DocSeries		
						,DocNumber		
						,DocIssueDate	
						,DocIssueOrg	
						,DocType		
						,OmsNumber		
						,OmsSerial		
						,EhrRus5o5e88vhc6
						,Sex
						)
			
				SELECT		
					PatientDrugDemandID
					,DemandOnPeriod
					,PharmacyDrugDemand
					,Comment
					,(select b.BenefitsID from RDS.T_BENEFITS b where b.BenefitCode = pddl.BenefitCode and .BenefitViews = pddl.BenefitType limit 1)
					,MKB
					,FirstName
					,FatherName
					,LastName
					,Birthday
				    ,RequestQuantity
				    ,Mont1
				    ,Mont2
				    ,Mont3
				    ,Mont4
				    ,Mont5
				    ,Mont6
				    ,Mont7
				    ,Mont8
				    ,Mont9
				    ,Mont10
				    ,Mont11
				    ,Mont12
					,Signa
					,Post
					,Snils
					,pddl.DocSeries
					,pddl.DocNumber
					,pddl.DocIssueDate
					,pddl.DocIssueOrg
					,eDoc.EnumID
					,pddl.OmsNumber
					,pddl.OmsSerial
					,dul.EhrRus5o5e88vhc6
					,eSex.EnumID
				FROM Tmp_PATIENT_DRUG_DEMAND_LIST pddl
					left join VCLib.T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
					left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
					left join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex
				where not exists (select * from RDS.T_PATIENT_DRUG_DEMAND pdd where pdd.PatientDrugDemandID = pddl.PatientDrugDemandID);

				update RDS.T_DRUG_REQUEST_COMMENTS drc
					set drc.UserProcessed = rc.UserProcessed
				FROM Tmp_DRUG_REQUEST_COMMENTS_LIST rc 
				where drc.DrugRequestCommentsID = rc.DrugRequestCommentsID;

				--удаление лишних
				delete from RDS.T_PATIENT_DRUG_DEMAND pdd
				join RDS.T_PHARMACY_DRUG_DEMAND phdd on phdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand
				join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
				join Tmp_DRUG_REQUEST_LIST dr on dr.DrugRequestID = dd.DrugDemandID
				where not exists 
				(
					select * from Tmp_PATIENT_DRUG_DEMAND_LIST pddl 
					join Tmp_PHARMACY_DRUG_DEMAND_LIST phddl on phddl.PharmacyDrugDemandID = pddl.PharmacyDrugDemand
					join Tmp_DRUG_DEMAND_LIST ddl on ddl.DrugDemandID = phddl.DrugDemand
					where pddl.PatientDrugDemandID = pdd.PatientDrugDemandID and dd.DrugRequest = ddl.DrugRequest
				);

				delete pdd
				from RDS.T_PHARMACY_DRUG_DEMAND pdd
					join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = pdd.DrugDemand
					join Tmp_DRUG_REQUEST_LIST dr on dr.DrugRequestID = dd.DrugRequest
				where not exists 
				(
					select * from #PHARMACY_DRUG_DEMAND_LIST phddl 
					join Tmp_DRUG_DEMAND_LIST ddl on ddl.DrugDemandID = phddl.DrugDemand
					where phddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID and dd.DrugRequest = ddl.DrugRequest
				);

				delete dd
				from RDS.T_DRUG_DEMAND dd
					join Tmp_DRUG_REQUEST_LIST dr on dr.DrugRequestID = dd.DrugRequest
				where not exists 
				(
					select * from Tmp_DRUG_DEMAND_LIST ddl 
					where ddl.DrugDemandID = dd.DrugDemandID and dd.DrugRequest = ddl.DrugRequest
				);
			end if;
			elsif (_OperationType = 'Delete')
			then
				delete from RDS.T_PATIENT_DRUG_DEMAND pdd
					join RDS.T_PHARMACY_DRUG_DEMAND ppdd on ppdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand
					join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = ppdd.DrugDemand
					join Tmp_DRUG_REQUEST drl on drl.DrugRequestID = dd.DrugRequest;

				delete from RDS.T_PHARMACY_DRUG_DEMAND pdd
					join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = pdd.DrugDemand
					join Tmp_DRUG_REQUEST drl on drl.DrugRequestID = dd.DrugRequest;

				delete from RDS.T_DRUG_DEMAND dd
					join Tmp_DRUG_REQUEST drl on drl.DrugRequestID = dd.DrugRequest;

				delete from RDS.T_DRUG_REQUEST dr
					join #DRUG_REQUEST drl on drl.DrugRequestID = dr.DrugRequestID;
			end if;
			else
			then
				update RDS.T_DRUG_REQUEST dr
			    set 
					 dr.RequestDate = drl.RequestDate
			        ,dr.LpuMain = coalesce(lp.MCOD, tt.LpuMain)
			        ,dr.Active = drl.Active
			        ,dr.DrugRequestMain = drl.DrugRequestMain
			        ,dr.Number = drl.Number
			        ,dr.RequestType = drl.RequestType
			        ,dr.ImportFileName = drl.ImportFileName
			        ,dr.Name = drl.Name
			        ,dr.EndPeriod = drl.EndPeriod
			        ,dr.BeginPeriod = drl.BeginPeriod
			        ,dr.Status = drl.Status
			        ,dr.Comment = drl.Comment
			        ,dr.Limit = drl.Limit
			        ,dr.Branch = tt.BranchID
					,dr.LastEditDate = coalesce(drl.LastEditDate, Current_TimeStamp::TIMESTAMP)
			        ,dr.Additional = coalesce(drl.Additional, '0')
			        ,dr.EhrRusEj36pmtjqk = ehr.EhrRusID
			        ,dr.EhrRusVzn17	= ehrVzn.EhrRusID
				from Tmp_DRUG_REQUEST drl
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >= drl.RequestDate))
				left join lateral 
				(
					select br.BranchID, br.LpuMain 
					from RDS.T_BRANCH br 
						join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >= drl.RequestDate))
					where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain) limit 1
				) tt on true
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer limit 1
				) ehr on true
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver limit 1
				) ehrVzn on true
				where drl.DrugRequestID = dr.DrugRequestID;

				INSERT INTO RDS.T_DRUG_REQUEST
			    (
					DrugRequestID
					,RequestDate
					,LpuMain
					,Active
					,DrugRequestMain
					,Number
					,RequestType
					,ImportFileName
					,Name
					,EndPeriod
					,BeginPeriod
					,Status
					,Comment
					,Limit
					,Branch
					,LastEditDate
					,Additional
					,EhrRusEj36pmtjqk
					,EhrRusVzn17
				)
				SELECT 
					 drl.DrugRequestID
					,drl.RequestDate
					,coalesce(lp.MCOD, tt.LpuMain)
					,drl.Active
					,drl.DrugRequestMain
					,drl.Number
					,drl.RequestType
					,drl.ImportFileName
					,drl.Name
					,drl.EndPeriod
					,drl.BeginPeriod
					,drl.Status
					,drl.Comment
					,drl.Limit
					,tt.BranchID
					,coalesce(drl.LastEditDate, Current_TimeStamp::TIMESTAMP)
					,coalesce(drl.Additional, '0')
					,ehr.EhrRusID
					,ehrVzn.EhrRusID	
				FROM Tmp_DRUG_REQUEST drl
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >= drl.RequestDate))
				left join lateral 
				(
					select br.BranchID, br.LpuMain 
					from RDS.T_BRANCH br 
						join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >= drl.RequestDate))
					where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain) limit 1
				) tt on true
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer limit 1
				) ehr on true
				left join lateral
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver limit 1
				) ehrVzn on true
				where not exists (select * from RDS.T_DRUG_REQUEST dr where dr.DrugRequestID = drl.DrugRequestID);

				update RDS.T_DRUG_DEMAND dd 
				set 
					dd.DrugRequest = ddl.DrugRequest
			       ,dd.DemandOnPeriod = ddl.DemandOnPeriod
			       ,dd.RequestQuantity = ddl.RequestQuantity
			       ,dd.RequestCode = ddl.RequestCode
			       ,dd.LSFO = ddl.LSFO
			       ,dd.Comment = ddl.Comment
				   ,dd.SectionCode = ddl.SectionCode
				   ,dd.PersRequest = coalesce(ddl.PersRequest, '0')
				   ,dd.DrugApplicationListItem = ddl.RequestCatalog
				from Tmp_DRUG_DEMAND ddl 
				where ddl.DrugDemandID = dd.DrugDemandID;

				INSERT INTO RDS.T_DRUG_DEMAND
				(
					DrugDemandID
					,DrugRequest
					,DemandOnPeriod
					,RequestQuantity
					,RequestCode
					,LSFO
					,Comment
					,SectionCode
					,DrugApplicationListItem
					,PersRequest
				)
				SELECT
					 ddl.DrugDemandID
					,ddl.DrugRequest
					,ddl.DemandOnPeriod
					,ddl.RequestQuantity
					,ddl.RequestCode
					,ddl.LSFO
					,ddl.Comment
					,ddl.SectionCode
					,ddl.RequestCatalog
					,coalesce(ddl.PersRequest, '0')
				FROM Tmp_DRUG_DEMAND ddl
				where not exists (select * from RDS.T_DRUG_DEMAND dd where dd.DrugDemandID = ddl.[DrugDemandID])

				update RDS.T_PHARMACY_DRUG_DEMAND pdd
				set 
					 DrugDemand			= pddl.DrugDemand
			        ,DemandOnPeriod		= pddl.DemandOnPeriod
			        ,Pharmacy			= pddl.Pharmacy
			        ,RequestQuantity	= pddl.RequestQuantity
			        ,Mont1				= pddl.Mont1
			        ,Mont2				= pddl.Mont2
			        ,Mont3				= pddl.Mont3
			        ,Mont4				= pddl.Mont4
			        ,Mont5				= pddl.Mont5
			        ,Mont6				= pddl.Mont6
			        ,Mont7				= pddl.Mont7
			        ,Mont8				= pddl.Mont8
			        ,Mont9				= pddl.Mont9
			        ,Mont10				= pddl.Mont10
			        ,Mont11				= pddl.Mont11
			        ,Mont12				= pddl.Mont12
				from Tmp_PHARMACY_DRUG_DEMAND pddl 
				where pddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID;

				INSERT INTO RDS.T_PHARMACY_DRUG_DEMAND
			    (
					 PharmacyDrugDemandID
					,DrugDemand
					,DemandOnPeriod
					,Pharmacy
					,RequestQuantity
					,Mont1
					,Mont2
					,Mont3
					,Mont4
					,Mont5
					,Mont6
					,Mont7
					,Mont8
					,Mont9
					,Mont10
					,Mont11
					,Mont12
				)
				SELECT
					 pddl.PharmacyDrugDemandID
					,pddl.DrugDemand
					,pddl.DemandOnPeriod
					,pddl.Pharmacy
					,pddl.RequestQuantity
					,pddl.Mont1
					,pddl.Mont2
					,pddl.Mont3
					,pddl.Mont4
					,pddl.Mont5
					,pddl.Mont6
					,pddl.Mont7
					,pddl.Mont8
					,pddl.Mont9
					,pddl.Mont10
					,pddl.Mont11
					,pddl.Mont12
				FROM Tmp_PHARMACY_DRUG_DEMAND pddl
				where not exists (select * from RDS.T_PHARMACY_DRUG_DEMAND pdd where pdd.PharmacyDrugDemandID = pddl.PharmacyDrugDemandID);

				update RDS.T_PATIENT_DRUG_DEMAND pdd
				set
				 pdd.DemandOnPeriod		= pddl.DemandOnPeriod
				,pdd.PharmacyDrugDemand	= pddl.PharmacyDrugDemand
				,pdd.Comment			= pddl.Comment
				,pdd.Benefits			= (select b.BenefitsID from RDS.T_BENEFITS b where b.BenefitCode = ddl.BenefitCode and b.BenefitViews = pddl.BenefitType limit 1)
				,pdd.MKB				= pddl.MKB
				,pdd.FirstName			= pddl.FirstName
				,pdd.FatherName			= pddl.FatherName
				,pdd.LastName			= pddl.LastName
				,pdd.Birthday			= pddl.Birthday
				,pdd.RequestQuantity	= pddl.RequestQuantity
				,pdd.Mont1				= pddl.Mont1
				,pdd.Mont2				= pddl.Mont2
				,pdd.Mont3				= pddl.Mont3
				,pdd.Mont4				= pddl.Mont4
				,pdd.Mont5				= pddl.Mont5
				,pdd.Mont6				= pddl.Mont6
				,pdd.Mont7				= pddl.Mont7
				,pdd.Mont8				= pddl.Mont8
				,pdd.Mont9				= pddl.Mont9
				,pdd.Mont10				= pddl.Mont10
				,pdd.Mont11				= pddl.Mont11
				,pdd.Mont12				= pddl.Mont12
				,pdd.Signa				= pddl.Signa
				,pdd.Post				= pddl.Post
				,pdd.Snils				= pddl.Snils
				,pdd.DocSeries			= pddl.DocSeries
				,pdd.DocNumber			= pddl.DocNumber
				,pdd.DocIssueDate		= pddl.DocIssueDate
				,pdd.DocIssueOrg		= pddl.DocIssueOrg
				,pdd.DocType			= eDoc.EnumID
				,pdd.OmsNumber			= pddl.OmsNumber
				,pdd.OmsSerial			= pddl.OmsSerial
				,pdd.EhrRus5o5e88vhc6	= dul.EhrRus5o5e88vhc6
				,pdd.Sex				= eSex.EnumID
				from Tmp_PATIENT_DRUG_DEMAND pddl 
				left join VCLib.T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
				left join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex
				where pddl.PatientDrugDemandID = pdd.PatientDrugDemandID;

				INSERT INTO RDS.T_PATIENT_DRUG_DEMAND
				(
					 PatientDrugDemandID
					,DemandOnPeriod
					,PharmacyDrugDemand
					,Comment
					,Benefits
					,MKB
					,FirstName
					,FatherName
					,LastName
					,Birthday
					,RequestQuantity
					,Mont1
					,Mont2
					,Mont3
					,Mont4
					,Mont5
					,Mont6
					,Mont7
					,Mont8
					,Mont9
					,Mont10
					,Mont11
					,Mont12
					,Signa
					,Post
					,Snils
					,DocSeries		
					,DocNumber		
					,DocIssueDate	
					,DocIssueOrg	
					,DocType		
					,OmsNumber		
					,OmsSerial		
					,EhrRus5o5e88vhc6
					,Sex
				)
				SELECT		
					 PatientDrugDemandID
					,DemandOnPeriod
					,PharmacyDrugDemand
					,Comment
					,(select b.BenefitsID from RDS.T_BENEFITS b where b.BenefitCode = pddl.BenefitCode and b.BenefitViews = pddl.BenefitType limit 1)
					,MKB
					,FirstName
					,FatherName
					,LastName
					,Birthday
			        ,RequestQuantity
			        ,Mont1
			        ,Mont2
			        ,Mont3
			        ,Mont4
			        ,Mont5
			        ,Mont6
			        ,Mont7
			        ,Mont8
			        ,Mont9
			        ,Mont10
			        ,Mont11
			        ,Mont12
					,Signa
					,Post
					,Snils
					,pddl.DocSeries
					,pddl.DocNumber
					,pddl.DocIssueDate
					,pddl.DocIssueOrg
					,eDoc.EnumID
					,pddl.OmsNumber
					,pddl.OmsSerial
					,dul.EhrRus5o5e88vhc6
					,eSex.EnumID
				FROM Tmp_PATIENT_DRUG_DEMAND pddl
					left join VCLib.T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
					left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
					left join VCLib.T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex
				where not exists (select * from RDS.T_PATIENT_DRUG_DEMAND pdd where pdd.PatientDrugDemandID = pddl.PatientDrugDemandID);


				update RDS.T_DRUG_REQUEST_COMMENTS drc
				set drc.UserProcessed = rc.UserProcessed
				from Tmp_DRUG_REQUEST_COMMENTS rc 
				where drc.DrugRequestCommentsID = rc.DrugRequestCommentsID;

				--удаление лишних
				delete from RDS.T_PATIENT_DRUG_DEMAND pdd
					join RDS.T_PHARMACY_DRUG_DEMAND phdd on phdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand
					join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
					join Tmp_DRUG_REQUEST dr on dr.DrugRequestID = dd.DrugRequest
				where not exists 
				(
					select * from Tmp_PATIENT_DRUG_DEMAND pddl 
						join Tmp_PHARMACY_DRUG_DEMAND phddl on phddl.PharmacyDrugDemandID = pddl.PharmacyDrugDemand
						join Tmp_DRUG_DEMAND ddl on ddl.DrugDemandID = phddl.DrugDemand
					where pddl.PatientDrugDemandID = pdd.PatientDrugDemandID and dd.DrugRequest = ddl.DrugRequest
				);

				delete from RDS.T_PHARMACY_DRUG_DEMAND pdd
					join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = pdd.DrugDemand
					join Tmp_DRUG_REQUEST dr on dr.DrugRequestID = dd.DrugRequest
				where not exists 
				(
					select * from Tmp_PHARMACY_DRUG_DEMAND phddl 
						join Tmp_DRUG_DEMAND ddl on ddl.DrugDemandID = phddl.DrugDemand
					where phddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID and dd.DrugRequest = ddl.DrugRequest
				);

				delete from RDS.T_DRUG_DEMAND dd
					join Tmp_DRUG_REQUEST dr on dr.DrugRequestID = dd.DrugRequest
				where not exists 
				(
					select * from Tmp_DRUG_DEMAND ddl 
					where ddl.DrugDemandID = dd.DrugDemandID and dd.DrugRequest = ddl.DrugRequest
				);
			end if;

			drop table if exists Tmp_DRUG_REQUEST_LIST;
			drop table if exists Tmp_DRUG_REQUEST;
			drop table if exists Tmp_DRUG_DEMAND_LIST;
			drop table if exists Tmp_DRUG_DEMAND;
			drop table if exists Tmp_PHARMACY_DRUG_DEMAND_LIST;
			drop table if exists Tmp_PHARMACY_DRUG_DEMAND;
			drop table if exists Tmp_PATIENT_DRUG_DEMAND_LIST;
			drop table if exists Tmp_PATIENT_DRUG_DEMAND;
			drop table if exists Tmp_DRUG_REQUEST_COMMENTS_LIST;
			drop table if exists Tmp_DRUG_REQUEST_COMMENTS;
		end if;
	End;
End;
$$;