drop procedure if exists sp_RDS_DrugRequest;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-03-14
-- Description: Выгрузка/загрузка заявок по ДЛО в(из) RDS
-- =============================================
CREATE PROCEDURE sp_RDS_DrugRequest
(	
	 "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
)
	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 RDS.T_DRUG_REQUEST where DrugRequestID = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = '0' and not exists (select * from RDS.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(dr.RequestDate, 'YYYY-MM-DD')	as "RequestDate"
											,dr.Number								as "Number"
											,dr.Active								as "Active"
											,to_char(dr.BeginPeriod, 'YYYY-MM-DD')	as "BeginPeriod"
											,to_char(dr.EndPeriod, 'YYYY-MM-DD')	as "EndPeriod"
											,dr.Name								as "Name"
											,dr.ImportFileName						as "ImportFileName"
											,dr.Comment							    as "Comment"
											,lp.MCOD								as "LpuMain"
											,drM.DrugRequestID						as "DrugRequestID"
											,dr.RequestType							as "RequestType"
											,dr.Status								as "Status"
											,dr.Limit								as "Limit"
											,br.BranchCode							as "BranchCode"
											,dr.Additional							as "Additional"
							                ,to_char(dr.LastEditDate, 'YYYY-MM-DD')	as "LastEditDate"
											,er.CodeEHR								as "EhrRusEj36pmtjqk"
											,rb.CurrVersion							as "EhrRusEj36pmtjqkVer"
											,erVzn.IdEHR							as "EhrRusVzn17"
											,rbVzn.CurrVersion						as "EhrRusVzn17Ver"
										),
                                        (
                                            select xmlagg
                                            (
                                                xmlelement
                                                (
                                                    name "DRUG_REQUEST_COMMENTS",
                                                    xmlforest
                                                    (
                                                        drc.DrugRequestCommentsID			as "DrugRequestCommentsID"
										                ,drc.Comment						as "Comment"
										                ,drc.RequestCode					as "RequestCode"
										                ,drc.Processed						as "Processed"
										                ,drc.UserProcessed					as "UserProcessed"
										                ,idd.IdentityName					as "Worker"
										                ,drc.DrugRequest					as "DrugRequest"
                                                    )
                                                )
                                            )
                                            from RDS.T_DRUG_REQUEST_COMMENTS drc
										        join RDS.T_USERS u on u.UserID = drc.Worker
										        join VCLib.T_USERS vu on vu.UserId = u.UserId
										        join VCLib.T_IDENTITY idd on vu.Identity = idd.IdentityID
									        where drc.DrugRequest = dr.DrugRequestID 
                                        ),
										(
											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.PersRequest			as "PersRequest"
														,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"
																			,pat.Birthday							    as "Birthday"
																			,pat.FirstName							    as "FirstName"
																			,pat.FatherName							    as "FatherName"
																			,pat.LastName							    as "LastName"
																			,eSex.Code								    as "Sex"
																			,b.BenefitCode							    as "BenefitCode"
																  			,b.BenefitViews							    as "BenefitsType"
																			,pat.MKB								    as "MKB"
																			,pat.Comment							    as "Comment"
																			,pat.Signa								    as "Signa"
																			,pat.Post								    as "Post"
																			,pat.Snils								    as "Snils"
																			,case when pat.DocSeries = '0' then null
																  			else pat.DocSeries end	
																													    as "DocSeries"
																			,case when pat.DocNumber = '0' then null
																			else pat.DocNumber end						
																													    as "DocNumber"
																			,to_char(pat.DocIssueDate, 'YYYY-MM-DD')	as "DocIssueDate"
																			,pat.DocIssueOrg							as "DocIssueOrg"
																			,eDt.Code								    as "DocType"
																			,pat.Number							        as "PolicyNumber"
																			,pat.Serial							        as "PolicySerial"
																		)
																	)
																)
																from RDS.T_PATIENT_DRUG_DEMAND pat
															        left join VCLib.T_ENUMS eDt on eDt.EnumID = pat.DocType
															        left join RDS.T_BENEFITS b on b.BenefitsID = pat.Benefits
															        left join VCLib.T_ENUMS eSex on eSex.EnumID = pat.Sex
																where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
															)
														)
													)
													from RDS.T_PHARMACY_DRUG_DEMAND pdd
													where dd.DrugDemandID = pdd.DrugDemand
												)
											)
										)
										from RDS.T_DRUG_DEMAND dd
										where dd.DrugRequest = dr.DrugRequestID
									)
								)
							)
                            from RDS.T_DRUG_REQUEST dr
							    join RDS.T_LPU_MAIN lp on lp.MCOD = dr.LpuMain
							    left join RDS.T_BRANCH br on br.BranchID = dr.Branch
							    left join RDS.T_DRUG_REQUEST drM on drM.DrugRequestID = dr.DrugRequestMain
							    left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = dr.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 = dr.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"
						,dr.RequestDate		                            as "RequestDate"
						,dr.Number				                        as "Number"
						,dr.Active				                        as "Active"
						,dr.Additional			                        as "Additional"
						,dr.BeginPeriod		                            as "BeginPeriod"
						,dr.EndPeriod			                        as "EndPeriod"
						,dr.Name				                        as "Name"
						,dr.ImportFileName		                        as "ImportFileName"
						,dr.Comment			                            as "Comment"
						,lp.MCOD				                        as "LpuMain"
						,drM.DrugRequestID		                        as "DrugRequestID"
						,dr.RequestType			                        as "RequestType"
						,bdr.Status				                        as "Status"
						,bdr.Limit				                        as "Limit"
						,br.BranchCode			                        as "BranchCode"
                        ,dr.Additional                                  as "Additional"
                        ,to_char(dr.LastEditDate, 'YYYY-MM-DD')	        as "LastEditDate"
						,er.CodeEHR				                        as "EhrRusEj36pmtjqk"
						,rb.CurrVersion			                        as "EhrRusEj36pmtjqkVer"
						,erVzn.IdEHR			                        as "EhrRusVzn17"
						,rbVzn.CurrVersion		                        as "EhrRusVzn17Ver"
					),                    
                    (
                        select xmlagg
                        (
                            xmlelement
                            (
                                name "DRUG_REQUEST_COMMENTS",
                                xmlforest
                                (
                                    drc.DrugRequestCommentsID			as "DrugRequestCommentsID"
					                ,drc.Comment						as "Comment"
					                ,drc.RequestCode					as "RequestCode"
					                ,drc.Processed						as "Processed"
					                ,drc.UserProcessed					as "UserProcessed"
					                ,idd.IdentityName					as "Worker"
					                ,drc.DrugRequest					as "DrugRequest"
                                )
                            )
                        )
                        from RDS.T_DRUG_REQUEST_COMMENTS drc
					        join RDS.T_USERS u on u.UserID = drc.Worker
					        join VCLib.T_USERS vu on vu.UserId = u.UserId
					        join VCLib.T_IDENTITY idd on vu.Identity = idd.IdentityID
					    where drc.DrugRequest = dr.DrugRequestID 
                    ),
					(
						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.PersRequest		    as "PersRequest"
								,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"
													,pat.Birthday								as "Birthday"
													,pat.FirstName							    as "FirstName"
													,pat.FatherName							    as "FatherName"
													,pat.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"
													,pat.Snils								    as "Snils"
													,case 
													when pat.DocSeries = '0' then null
													else pat.DocSeries end					    as "DocSeries"
													,case 
													when pat.DocNumber = '0' then null
													else pat.DocNumber end					    as "DocNumber"
													,to_char(pat.DocIssueDate, 'YYYY-MM-DD')    as "DocIssueDate"
													,pat.DocIssueOrg							    as "DocIssueOrg"
													,eDt.Code								    as "DocType"
													,polis.Number							    as "PolicyNumber"
													,polis.Serial							    as "PolicySerial"
													)
												)
											)
											from RDS.T_PATIENT_DRUG_DEMAND pat
                                            left join VCLib.T_ENUMS eDt on eDt.EnumID = pat.DocType
											left join RDS.T_BENEFITS b on b.BenefitsID = pat.Benefits
                                            left join VCLib.T_ENUMS eSex on eSex.EnumID = pat.Sex
											where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
										)
									)
								)
								from RDS.T_PHARMACY_DRUG_DEMAND pdd
								where dd.DrugDemandID = pdd.DrugDemand
							)
						)
					)
					from RDS.T_DRUG_DEMAND dd
					where dd.DrugRequest = dr.DrugRequestID
				)
				from RDS.T_DRUG_REQUEST dr
                    join RDS.T_LPU_MAIN lp on lp.MCOD = dr.LpuMain
                    left join RDS.T_BRANCH br on br.BranchID = dr.Branch
                    left join RDS.T_DRUG_REQUEST drM on drM.DrugRequestID = dr.DrugRequestMain
                    left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = dr.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 = dr.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 gen_random_uuid()--(<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"::xml))[1]::varchar(6);
			_OwnerID := (xpath('/DRUG_REQUEST/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
			_Result = "Xml"; 

			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"::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"::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;
$$;