USP_DATALIST_DESIGNATIONOPENDONORCHALLENGE

CREATE procedure dbo.USP_DATALIST_DESIGNATIONOPENDONORCHALLENGE(@DESIGNATIONID uniqueidentifier)
as
    set nocount on;

                Select 
                DONORCHALLENGE.ID,
                        DONORCHALLENGE.NAME,
                        DONORCHALLENGE.TYPE,
                        DONORCHALLENGE.DESCRIPTION,
                        DONORCHALLENGE.STARTDATE,
                        DONORCHALLENGE.ENDDATE,
            DONORCHALLENGE.EXTERNALSPONSORID,
            dbo.UFN_DONORCHALLENGESPONSORCODE_GETDESCRIPTION(DONORCHALLENGE.INTERNALSPONSORCODEID) INTERNALSPONSOR,
            DONORCHALLENGE.TOTALFUNDS,
            DONORCHALLENGE.PAYPLEDGESWITHIN,
            DONORCHALLENGE.PAYPLEDGESWITHINUNIT,
            DONORCHALLENGE.MATCHTYPE,
            DONORCHALLENGE.MATCHINGFACTOR,
            DONORCHALLENGE.MATCHTHRESHOLD,
            DONORCHALLENGE.MINGIFTAMOUNT,
            case when DONORCHALLENGE.MAXMATCHPERGIFT = 0 then NULL else DONORCHALLENGE.MAXMATCHPERGIFT end MAXMATCHPERGIFT,
            dbo.UFN_REVENUERECOGNITIONTYPECODE_GETDESCRIPTION(DONORCHALLENGE.REVENUERECOGNITIONTYPECODEID) REVENUERECOGNITIONTYPE,
            DONORCHALLENGE.PROCESSLASTRUN,
            DONORCHALLENGE.STATUSTYPE,
            COALESCE((SELECT NAME FROM dbo.SITE WHERE ID=DONORCHALLENGE.SITEID),'') AS SITENAME,
            case when DONORCHALLENGE.EXTERNALSPONSORID is null then 'Internal' else 'External' end FUNDINGSOURCE,                        

                        DONORCHALLENGE.DATEADDED,
                        DONORCHALLENGE.DATECHANGED,

                        DONORCHALLENGE.ORGANIZATIONTOTALFUNDS,
                        DONORCHALLENGE.ORGANIZATIONMATCHTHRESHOLD,
                        DONORCHALLENGE.ORGANIZATIONMINGIFTAMOUNT,
                    case when DONORCHALLENGE.ORGANIZATIONMAXMATCHPERGIFT = 0 then NULL else DONORCHALLENGE.ORGANIZATIONMAXMATCHPERGIFT end ORGANIZATIONMAXMATCHPERGIFT,
                        DESIGNATIONSITE.SITEID as SITEID,
            dbo.UFN_DONORCHALLENGE_GETREMAININGFUNDS(DONORCHALLENGE.ID) REMAININGFUNDS,
             case when EXTERNALSPONSORID is null then
                      dbo.UFN_DONORCHALLENGESPONSORCODE_GETDESCRIPTION(INTERNALSPONSORCODEID)
                  else
                      (select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(EXTERNALSPONSORID))
                  end EXTERNALSPONSOR,
            DONORCHALLENGE.BASECURRENCYID as BASECURRENCYID,
            (select top 1 ID from CURRENCY where ISORGANIZATIONCURRENCY = 1) as ORGCURRENCYID

    from dbo.DONORCHALLENGE
    left join dbo.DONORCHALLENGEDESIGNATIONMAP as DESIGNATION on DESIGNATION.DONORCHALLENGEID = DONORCHALLENGE.ID
    left join dbo.SITE as DESIGNATIONSITE on DONORCHALLENGE.SITEID = DESIGNATIONSITE.ID
    left join dbo.CURRENCY as CURRENCY on DONORCHALLENGE.BASECURRENCYID = CURRENCY.ID
    where
    DESIGNATION.DESIGNATIONID = @DESIGNATIONID and DONORCHALLENGE.STATUSTYPE like 'Open'