IF EXISTS (SELECT *
           FROM dbo.sysobjects
           WHERE id = OBJECT_ID(N'[dbo].[fn_GetOmsPolicy]')
             AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[fn_GetOmsPolicy]
GO

CREATE FUNCTION [dbo].fn_GetOmsPolicy(@PatientID uniqueidentifier, @OnDate datetime)
    RETURNS uniqueidentifier
AS
BEGIN
    DECLARE
        @policy uniqueidentifier,
        @validThrough datetime

    SET @OnDate = COALESCE(@OnDate, GETDATE())

    SELECT TOP 1 @policy = PolicyID,
                 @validThrough = ValidThrough
    FROM [AKUZ].T_POLICY p
    WHERE p.type_ = 536
      AND p.Patient = @PatientID
      AND p.IssueDate <= @OnDate
      AND NOT EXISTS(SELECT *
                     FROM [AKUZ].T_POLICY pp
                     WHERE pp.PolicyID <> p.PolicyID
                       AND pp.patient = p.patient
                       AND pp.IssueDate <= @OnDate
                       AND pp.IssueDate >= p.IssueDate
                       AND (pp.ValidThrough IS NULL OR pp.ValidThrough > p.ValidThrough))
    RETURN @policy
END
GO