DROP PROCEDURE IF EXISTS sp_BA_DloAccessPostDirection;

CREATE PROCEDURE sp_BA_DloAccessPostDirection
(
	"Xml" xml DEFAULT NULL::xml
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"QueueName" character varying DEFAULT NULL::character varying
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
Begin

	IF "Xml" is not null
	then		
		Drop Table If Exists TmpDLO_ACCESSED_POSTS_DIRECTION;
		-- Generate table #DLO_ACCESSED_POSTS_DIRECTION
		Create Temp Table TmpDLO_ACCESSED_POSTS_DIRECTION
		As
		select	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/PostCode[1]/text()', x))[1]::varchar(255)			as PostCode
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/LastName[1]/text()', x))[1]::varchar(40)			as LastName
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/FirstName[1]/text()', x))[1]::varchar(40)			as FirstName
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/FatherName[1]/text()', x))[1]::varchar(40)			as FatherName
		,	to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/BeginAccessDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')as BeginAccessDate
		,	to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/EndAccessDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as EndAccessDate
		,	to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/BeginWorkDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as BeginWorkDate
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/MedicalSpecialityCode[1]/text()', x))[1]::varchar(16) 	as MedicalSpecialityCode
		,	to_timestamp((xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/ExpulsionDate[1]/text()', x))[1]::text, 'DD.MM.YYYY')	as ExpulsionDate
		,	(xpath('/DLO_ACCESSED_POSTS/body[1]/Snils[1]/text()', x))[1]::varchar(14)			as Snils
		,	(xpath('/DLO_ACCESSED_POSTS/body[1]/PostType[1]/text()', x))[1]::varchar(4)			as PostType
		,	(xpath('/DLO_ACCESSED_POSTS/body[1]/Speciality[1]/text()', x))[1]::varchar(3)			as Speciality
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/DocCode[1]/text()', x))[1]::varchar(6)			as DocCode
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/LpuMain[1]/text()', x))[1]::varchar(6)			as LpuMain
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/DirectionState[1]/text()', x))[1]::varchar(36)			as DirectionState
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/RefusingReason[1]/text()', x))[1]::varchar(255)			as RefusingReason
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/PreviousPostCode[1]/text()', x))[1]::varchar(255)			as PreviousPostCode
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/PreviousFatherName[1]/text()', x))[1]::varchar(40)			as PreviousFatherName
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/PreviousFirstName[1]/text()', x))[1]::varchar(40)			as PreviousFirstName
		,	(xpath('/DLO_ACCESSED_POSTS_DIRECTION/body[1]/PreviousLastName[1]/text()', x))[1]::varchar(40)			as PreviousLastName
		from
		(
			select "Xml" as x
		) t;
		
		update akuz.T_DLO_ACCESSED_POSTS_DIRECTION dap
		set	DirectionState = ndap.DirectionState
		,	RefusingReason = ndap.RefusingReason
		from TmpDLO_ACCESSED_POSTS_DIRECTION ndap 					
		inner join akuz.T_LPU_MAIN lm on lm.LpuCode = ndap.LpuMain
		where (dap.PreviousPostCode = ndap.PreviousPostCode or dap.PostCode = ndap.PostCode) 
		and ndap.Snils = dap.Snils and dap.DirectionState = 0;
		-- Drop tables
		DROP TABLE If Exists TmpDLO_ACCESSED_POSTS_DIRECTION;
	END if;
End;
$$;
