USP_DATALIST_GIFTSPONSORSHIPSBYFINANCIALSPONSOR

CREATE procedure dbo.USP_DATALIST_GIFTSPONSORSHIPSBYFINANCIALSPONSOR(
    @FINANCIALSPONSORID uniqueidentifier,
    @SHOWHISTORY bit = 0
)
as
    set nocount on;

    select
        isnull(GIFTENDSPONSORSHIP.ID,ACTIVESPONSORSHIP.ID) SPONSORSHIPID,
        SPONSORSHIPCOMMITMENT.CONSTITUENTID SPONSORID,
        SPONSOR_NF.NAME,
        SPONSORSHIPPROGRAM.NAME PROGRAM,
    (case when exists(select ID from dbo.SPONSORSHIPOPPORTUNITYCHILD where ID = ACTIVESPONSORSHIP.SPONSORSHIPOPPORTUNITYID) then
      'Child - ' 
     else
       'Project - '
     end) + dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(ACTIVESPONSORSHIP.SPONSORSHIPOPPORTUNITYID) OPPORTUNITY,
    coalesce(dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(ACTIVESPONSORSHIP.SPONSORSHIPLOCATIONID),dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME((select SPONSORSHIPLOCATIONID from dbo.SPONSORSHIPOPPORTUNITY where ID = ACTIVESPONSORSHIP.SPONSORSHIPOPPORTUNITYID))) as LOCATION,
        GIFTSTART.TRANSACTIONDATE STARTDATE,
        GIFTEND.TRANSACTIONDATE ENDDATE,
        SPONSORSHIPCOMMITMENT.LOOKUPID COMMITMENTID
    from dbo.SPONSORSHIPTRANSACTION GIFTSTART
    inner join dbo.SPONSORSHIPCOMMITMENT on SPONSORSHIPCOMMITMENT.ID = GIFTSTART.SPONSORSHIPCOMMITMENTID
    left join dbo.SPONSORSHIPTRANSACTION GIFTEND on GIFTEND.SPONSORSHIPCOMMITMENTID = GIFTSTART.SPONSORSHIPCOMMITMENTID and GIFTEND.TRANSACTIONSEQUENCE = (select min(MINSEQ.TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MINSEQ where MINSEQ.SPONSORSHIPCOMMITMENTID = GIFTSTART.SPONSORSHIPCOMMITMENTID and MINSEQ.TRANSACTIONSEQUENCE > GIFTSTART.TRANSACTIONSEQUENCE and MINSEQ.ACTIONCODE in(2,3,5,9))
    left join dbo.SPONSORSHIP GIFTENDSPONSORSHIP on GIFTENDSPONSORSHIP.ID = GIFTEND.CONTEXTSPONSORSHIPID
    left join dbo.SPONSORSHIP ACTIVESPONSORSHIP on ACTIVESPONSORSHIP.SPONSORSHIPCOMMITMENTID = GIFTSTART.SPONSORSHIPCOMMITMENTID and ACTIVESPONSORSHIP.STATUSCODE = 1
    left join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.ID = isnull(GIFTENDSPONSORSHIP.SPONSORSHIPPROGRAMID,ACTIVESPONSORSHIP.SPONSORSHIPPROGRAMID)
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPONSORSHIPCOMMITMENT.CONSTITUENTID) SPONSOR_NF
    where GIFTSTART.GIFTFINANCIALSPONSORID = @FINANCIALSPONSORID
    and (@SHOWHISTORY = 1 or GIFTEND.ID is null);