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 (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)))
                     )
    LIMIT 1;
    
    RETURN v_policy;
END;
$$
