DROP FUNCTION IF EXISTS dbo.fn_getomspolicy;

CREATE OR REPLACE FUNCTION dbo.fn_getomspolicy(
    patientid uuid,
    ondate timestamp
) RETURNS uuid
    LANGUAGE plpgsql
AS
$$
DECLARE
    DECLARE
    v_policy       uuid;
    v_validthrough timestamp;
BEGIN
    ondate := COALESCE(ondate, CURRENT_TIMESTAMP);
    
    SELECT policyid,
           validthrough
    INTO v_policy,
        v_validthrough
    FROM akuz.t_policy p
    WHERE p.type_ = 536
      AND p.patient = patientid
      AND p.issuedate <= 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
                        AND (pp.validthrough IS NULL OR pp.validthrough > p.validthrough)
                     )
    LIMIT 1;
    
    RETURN v_policy;
END;
$$