CREATE OR REPLACE FUNCTION dbo.fn_GetOmsPolicy
(
	"PatientID" uuid
,	"OnDate" timestamp without time zone
)
RETURNS uuid AS
$BODY$
declare	_Policy UUID;
	_validThrough TimeStamp;
Begin
	if "OnDate" is null 
	then "OnDate" := Current_Timestamp::TIMESTAMP;
	end if;
	select into 
		_Policy, _validThrough
		p.PolicyID, p.ValidThrough 
	from akuz.T_POLICY p
	where p.type_ = 536 
	and p.Patient = "PatientID" 
	and Date_Part('day', "OnDate" - p.IssueDate) >= 0
	order by p.IssueDate desc, coalesce(p.ValidThrough,'20991231') desc
	Limit 1;
	if _validThrough is null or Date_Part('day', "OnDate" - _validThrough) <= 0 
	then	return _Policy;
	end if;
	return null;
END $BODY$
  LANGUAGE plpgsql STABLE;
ALTER FUNCTION dbo.fn_getomspolicy(uuid, timestamp without time zone) OWNER TO postgres;