DROP FUNCTION IF EXISTS dbo.fn_GetDmsPolicy;

CREATE FUNCTION dbo.fn_GetDmsPolicy("PatientID" UUID, "OnDate" TimeStamp, "InsuranceCompanyID" UUID)
	RETURNS UUID AS
 $$
	DECLARE	
	_policy uuid;
	_validThrough TimeStamp;
 Begin
	if "OnDate" is null 
	then "OnDate" := current_timestamp::timestamp;
	end if;	   
	select into 
		_policy, _validThrough
		polis.PolicyID, polis.ValidThrough
	from akuz.T_POLICY polis
	inner join 
		akuz.T_DMS_POLICY dms_pol 
		On polis.PolicyID = dms_pol.PolicyID
	inner join 
		akuz.T_CONTRACT cont 
		On dms_pol.DmsContract = cont.ContractID
	where polis.type_ = 518 
	and polis.Patient = "PatientID"
	and Date_Part('day', "OnDate" - polis.IssueDate) >= 0 
	and ( "InsuranceCompanyID" IS NULL OR cont.Organization = "InsuranceCompanyID" )
	order by polis.IssueDate desc
	Limit 1;
	  
	if _validThrough is null or Date_Part('day', "OnDate" - _validThrough) <= 0 
	then return _policy;
	end if;

	return null;
END $$
language plpgsql stable;