UFN_GROUPGOAL_DATALIST

CREATE function dbo.UFN_GROUPGOAL_DATALIST
(
    @GROUPID uniqueidentifier,
    @KPICATALOGID uniqueidentifier
)
returns table
--with execute as caller

as 
return
(
    with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
    select 
        goal.ID,
        ISFUNDRAISINGGOAL,
        PROGRESS,
        code.DESCRIPTION TYPE,
        AMOUNT,
        STARTDATE,
        GOALDATE,
        goal.DESCRIPTION,
        DATESORT =    Case 
                        When GOALDATE >= dbo.UFN_DATE_GETEARLIESTTIME(GETDATE()) Then 1
                        When GOALDATE IS NULL Then 0
                        Else -1
                    End,
        GROUPGOALUNITCODEID,
        (
            select top 1 
                KPIINSTANCE.ID
            from 
                dbo.KPIINSTANCE 
            where 
                KPIINSTANCE.KPICATALOGID = @KPICATALOGID
            and
                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="COMMITTEEID"]/DFI:Value)[1]','varchar(36)') = cast(GOAL.GROUPID as varchar(36))
            and
                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="GROUPGOALID"]/DFI:Value)[1]','varchar(36)') = cast(GOAL.ID as varchar(36))
        ) as KPIINSTANCEID,
        NAME,
        'Fundraising' as FUNDRAISERTYPE,
        null as CAMPAIGNID,
        null as CAMPAIGNNAME,
        GOAL.BASECURRENCYID
    from dbo.GROUPGOAL as GOAL 
    left join dbo.GROUPGOALUNITCODE as CODE 
        on GOAL.GROUPGOALUNITCODEID = CODE.ID
    where 
        GROUPID = @GROUPID

    union all

    select 
        ID,
        1 as ISFUNDRAISINGGOAL,
        0 as PROGRESS,
        '' as TYPE,
        AMOUNT,
        STARTDATE,
        GOALDATE,
        '' as DESCRIPTION,
        DATESORT,
        null as GROUPGOALUNITCODEID,
        KPIINSTANCEID,
        NAME,
        'Campaign' as FUNDRAISERTYPE,
        CAMPAIGNID,
        CAMPAIGNNAME,
        BASECURRENCYID
    from dbo.UFN_COMMITTEECAMPAIGNGOAL_DATALIST(@GROUPID)
);