UFN_GROUPGOAL_DATALIST_WITHCURRENCY

Returns table of group goals for the group and ISFUNDRAISINGGOAL criteria using the specified currency to convert monetary values.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@GROUPID	uniqueidentifier	IN
@KPICATALOGID	uniqueidentifier	IN
@CURRENCYID	uniqueidentifier	IN
Definition

Copy


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

            as 
            return
            (
                with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
                select 
                    goal.ID,
                    ISFUNDRAISINGGOAL,
                    PROGRESS,
                    code.DESCRIPTION TYPE,
                    case when ISFUNDRAISINGGOAL = 1 then dbo.UFN_GROUPGOAL_GETAMOUNTINCURRENCY(GOAL.ID, @CURRENCYID)
                                            else AMOUNT end as 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
                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
                from dbo.UFN_COMMITTEECAMPAIGNGOAL_DATALIST_WITHCURRENCY(@GROUPID, @CURRENCYID)
            );