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 (p.ValidThrough IS NULL OR p.ValidThrough >= @OnDate)
      AND NOT EXISTS (SELECT
                          1
                      FROM AKUZ.T_POLICY pp
                      WHERE
                            pp.PolicyID <> p.PolicyID
                        AND pp.patient = p.patient
                        AND pp.IssueDate <= @OnDate
                        AND (pp.issuedate > p.issuedate OR (pp.issuedate = p.issuedate AND
                                                            (pp.validthrough IS NULL OR pp.validthrough > p.validthrough))))
    RETURN @policy
END
GO