USP_DATALIST_SPONSORSHIPCMSDATALIST

    CREATE procedure dbo.USP_DATALIST_SPONSORSHIPCMSDATALIST(
      @CONSTITUENTID uniqueidentifier,
      @SHOWHISTORY bit = 0
  )     
  as
    set nocount on;
  select
      SPONSORSHIP.ID ID,
      case when @SHOWHISTORY = 1 then SPONSORSHIP.SPONSORSHIPCOMMITMENTID end PARENTID,
      SPONSORSHIP.ID SPONSORSHIPID,
      SPONSORSHIP.ID CONTEXTSPONSORSHIPID,
      SPONSORSHIPCOMMITMENT.COMMITMENTSEQUENCE,
      CREATETRANSACTION.TRANSACTIONSEQUENCE SPONSORSHIPSEQUENCE,
      SPONSORSHIPOPPORTUNITY.ID,
    case when SPONSORSHIPCOMMITMENT.CONSTITUENTID=REVENUE.CONSTITUENTID then
        dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)
    else
      dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) + ' (Gift Sponsorship from '+dbo.UFN_CONSTITUENT_BUILDNAME(dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(SPONSORSHIP.ID))+')'
    end as NAME,
      SPONSORSHIPPROGRAM.ID PROGRAMID,
    SPONSORSHIPPROGRAM.NAME PROGRAM,
    SPONSORSHIP.STARTDATE,
      SPONSORSHIP.ENDDATE,
      case SPONSORSHIP.STATUSCODE 
        when 1 then
          case LASTTRANSACTION.ACTIONCODE
            when 6 then 'Active (Transfer pending)'
            when 5 then 'Active (Reassigned from ' + REASSIGNEDCONSTITUENTNAME_NF.NAME + ')'
            else 'Active' end
        when 2 then
          case LASTTRANSACTION.ACTIONCODE
            when 1 then 'Transferred'
            when 2 then
              case SPONSORSHIP.ID
                when LASTTRANSACTION.CONTEXTSPONSORSHIPID then
                  case SPONSORSHIPREASON.REASONTYPECODE when 11 then 'Expired' else 'Cancelled' end
                when LASTTRANSACTION.DECLINEDSPONSORSHIPID then 'Declined' end
            when 3 then 'Terminated'
            when 5 then 'Reassigned to ' + REASSIGNEDCONSTITUENTNAME_NF.NAME
            when 7 then 
              case SPONSORSHIP.ID
                when LASTTRANSACTION.CONTEXTSPONSORSHIPID then 'Transferred'
                when LASTTRANSACTION.DECLINEDSPONSORSHIPID then 'Declined' end
            when 8 then 'Transferred' end
        else SPONSORSHIP.STATUS end STATUS,
      case when SPONSORSHIP.STATUSCODE = 2 or (SPONSORSHIP.STATUSCODE = 1 and LASTTRANSACTION.ACTIONCODE = 6) then SPONSORSHIPREASON.REASON end REASON,
      LASTTRANSACTION.ID TRANSACTIONID,
      LASTTRANSACTION.ACTIONCODE ACTIONCODE,
      dbo.UFN_SPONSORSHIP_DELETEALLOWED(SPONSORSHIP.ID) DELETEALLOWED,
      dbo.UFN_SPONSORSHIP_REACTIVATEALLOWED(SPONSORSHIP.ID) REACTIVATEALLOWED,
      REASSIGNED.CONSTITUENTID REASSIGNEDCONSTITUENTID,
      REASSIGNEDCONSTITUENTNAME_NF.NAME REASSIGNEDCONSTITUENTNAME,
      PAIDFORBY_NF.NAME PAIDFORBY,
      dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(SPONSORSHIP.ID) PAIDFORBYID,
      REVENUE.ID,
      case  @SHOWHISTORY
        when 0 then SPONSORSHIPCOMMITMENT.LOOKUPID
        else null
      end COMMITMENTID,
      case when SPONSORSHIPCOMMITMENT.CONSTITUENTID = @CONSTITUENTID then 0 else 1 end CONSTITUENTISGIFTSPONSOR,
    case when SPONSORSHIPCOMMITMENT.CONSTITUENTID=REVENUE.CONSTITUENTID then
        dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION_FIRSTNAMEONLY(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID)
    else
      dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION_FIRSTNAMEONLY(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) + ' (Gift Sponsorship from '+dbo.UFN_CONSTITUENT_BUILDNAME(dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(SPONSORSHIP.ID))+')'
    end as NAME_FIRSTNAMEONLY
  from dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPCOMMITMENT on SPONSORSHIPCOMMITMENT.ID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.ID = SPONSORSHIP.SPONSORSHIPPROGRAMID
    inner join dbo.SPONSORSHIPTRANSACTION CREATETRANSACTION on CREATETRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and CREATETRANSACTION.TRANSACTIONSEQUENCE = (select min(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MINSEQUENCE where MINSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and MINSEQUENCE.TARGETSPONSORSHIPID = SPONSORSHIP.ID)
    inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and SPONSORSHIP.ID in(MAXSEQUENCE.CONTEXTSPONSORSHIPID,MAXSEQUENCE.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID) and MAXSEQUENCE.ACTIONCODE <> 9)
    left outer join dbo.SPONSORSHIP REASSIGNED on REASSIGNED.ID = case SPONSORSHIP.STATUSCODE when 1 then LASTTRANSACTION.CONTEXTSPONSORSHIPID when 2 then LASTTRANSACTION.TARGETSPONSORSHIPID end and LASTTRANSACTION.ACTIONCODE = 5
    left outer join dbo.SPONSORSHIPREASON on SPONSORSHIPREASON.ID = LASTTRANSACTION.SPONSORSHIPREASONID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REASSIGNED.CONSTITUENTID) REASSIGNEDCONSTITUENTNAME_NF
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(SPONSORSHIP.ID)) PAIDFORBY_NF
    where SPONSORSHIPCOMMITMENT.CONSTITUENTID = @CONSTITUENTID
    and (@SHOWHISTORY = 1 or SPONSORSHIP.STATUSCODE = 1)
  union all
  select
      SPONSORSHIP.ID ID,
      case when @SHOWHISTORY = 1 then SPONSORSHIP.SPONSORSHIPCOMMITMENTID end PARENTID,
      SPONSORSHIP.ID SPONSORSHIPID,
      SPONSORSHIP.ID CONTEXTSPONSORSHIPID,
      null,
      null SPONSORSHIPSEQUENCE,
      SPONSORSHIPOPPORTUNITY.ID,
      dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) + ' (Gift Sponsorship to '+dbo.UFN_CONSTITUENT_BUILDNAME(SPONSORSHIP.CONSTITUENTID)+')' NAME,
      SPONSORSHIPPROGRAM.ID PROGRAMID,
    SPONSORSHIPPROGRAM.NAME PROGRAM,
    SPONSORSHIP.STARTDATE,
      SPONSORSHIP.ENDDATE,
      'Active' STATUS,
      null REASON,
      null TRANSACTIONID,
      null ACTIONCODE,
      dbo.UFN_SPONSORSHIP_DELETEALLOWED(SPONSORSHIP.ID) DELETEALLOWED,
      dbo.UFN_SPONSORSHIP_REACTIVATEALLOWED(SPONSORSHIP.ID) REACTIVATEALLOWED,
      null REASSIGNEDCONSTITUENTID,
      null REASSIGNEDCONSTITUENTNAME,
      PAIDFORBY_NF.NAME PAIDFORBY,
      dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(SPONSORSHIP.ID) PAIDFORBYID,
      REVENUE.ID,
      null COMMITMENTID,
    case when SPONSORSHIP.CONSTITUENTID = @CONSTITUENTID then 0 else 1 end CONSTITUENTISGIFTSPONSOR,
    dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION_FIRSTNAMEONLY(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) + ' (Gift Sponsorship to '+dbo.UFN_CONSTITUENT_BUILDNAME(SPONSORSHIP.CONSTITUENTID)+')'
  from dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.ID = SPONSORSHIP.SPONSORSHIPPROGRAMID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(SPONSORSHIP.ID)) PAIDFORBY_NF
    where  REVENUE.CONSTITUENTID = @CONSTITUENTID and SPONSORSHIP.CONSTITUENTID <> @CONSTITUENTID
    and (@SHOWHISTORY = 1 or SPONSORSHIP.STATUSCODE = 1)
    order by SPONSORSHIPSEQUENCE;