DROP FUNCTION IF EXISTS public.fn_getdemanddrugrequestlist;

CREATE OR REPLACE FUNCTION public.fn_getdemanddrugrequestlist(
    drugdemandid uuid
)
    RETURNS uuid
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_drugdemandid uuid := drugdemandid;
BEGIN
    
    RETURN (SELECT (SELECT drl.drugapplicationlistitemid
                    FROM rds.t_drug_application_list_item AS drl
                    JOIN rds.t_drug_application_list da ON da.drugapplicationlistid = drl.drugapplicationlist
                    WHERE drl.requestcode = dd.requestcode
                      AND da.begindate <= dr.beginperiod
                      AND da.enddate >= dr.endperiod
                      AND (dr.requesttype = drl.benefittype)
                      AND ((drl.sectioncode IS NULL AND dd.sectioncode IS NULL)
                        OR drl.sectioncode = dd.sectioncode)
                    LIMIT 1
                   )
            FROM rds.t_drug_demand AS dd
            JOIN rds.t_drug_request AS dr ON dd.drugrequest = dr.drugrequestid
            WHERE dd.drugdemandid = p_drugdemandid
           );

END;
$$