USP_DATALIST_SPONSORSHIP

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

    -- Commitments

    select
      SPONSORSHIPCOMMITMENT.ID,
      null PARENTID,
      null SPONSORSHIPID,
      isnull(LASTTRANSACTION.TARGETSPONSORSHIPID,LASTTRANSACTION.CONTEXTSPONSORSHIPID) CONTEXTSPONSORSHIPID,
      SPONSORSHIPCOMMITMENT.COMMITMENTSEQUENCE,
      -1 SPONSORSHIPSEQUENCE,
      null SPONSORSHIPOPPORTUNITYID,
      null NAME,
    null PROGRAMID,
      '' PROGRAM,
      (select min(STARTDATE) from dbo.SPONSORSHIP where SPONSORSHIP.SPONSORSHIPCOMMITMENTID = SPONSORSHIPCOMMITMENT.ID) STARTDATE,
      case when LASTTRANSACTION.ACTIONCODE in(2,3,5) then LASTSPONSORSHIP.ENDDATE end ENDDATE,
      case when LASTTRANSACTION.ACTIONCODE in(2,3) or (LASTTRANSACTION.ACTIONCODE = 5 and LASTSPONSORSHIP.ENDDATE is not null) then 'Inactive'
           when LASTTRANSACTION.ACTIONCODE = 6 then 'Active (Transfer pending)'
           else 'Active' end STATUS,
      '' REASON,
      LASTTRANSACTION.ID TRANSACTIONID,
      LASTTRANSACTION.ACTIONCODE ACTIONCODE,
      0 DELETEALLOWED,
      dbo.UFN_SPONSORSHIP_REACTIVATEALLOWED(isnull(LASTTRANSACTION.TARGETSPONSORSHIPID,LASTTRANSACTION.CONTEXTSPONSORSHIPID)) REACTIVATEALLOWED,
      null REASSIGNEDCONSTITUENTID,
      '' REASSIGNEDCONSTITUENTNAME,
      PAIDFORBY_NF.NAME PAIDFORBY,
      dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(LASTSPONSORSHIP.ID) PAIDFORBYID,
      REVENUE.ID REVENUEID,
      SPONSORSHIPCOMMITMENT.LOOKUPID COMMITMENTID
    from dbo.SPONSORSHIPCOMMITMENT
    inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIPCOMMITMENT.ID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIPCOMMITMENT.ID and MAXSEQUENCE.ACTIONCODE <> 9)
    left outer join dbo.SPONSORSHIP LASTSPONSORSHIP on LASTSPONSORSHIP.SPONSORSHIPCOMMITMENTID = SPONSORSHIPCOMMITMENT.ID and ((LASTTRANSACTION.ACTIONCODE = 5 and LASTSPONSORSHIP.ID in(LASTTRANSACTION.TARGETSPONSORSHIPID,LASTTRANSACTION.CONTEXTSPONSORSHIPID)) or (LASTTRANSACTION.ACTIONCODE <> 5 and LASTSPONSORSHIP.ID = isnull(LASTTRANSACTION.TARGETSPONSORSHIPID,LASTTRANSACTION.CONTEXTSPONSORSHIPID)))
    left outer join dbo.REVENUESPLIT on REVENUESPLIT.ID = LASTSPONSORSHIP.REVENUESPLITID
    left outer join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(dbo.UFN_SPONSORSHIP_GIFTFINANCIALSPONSORID(LASTSPONSORSHIP.ID)) PAIDFORBY_NF
    where SPONSORSHIPCOMMITMENT.CONSTITUENTID = @CONSTITUENTID
    and @SHOWHISTORY = 1
    union all
    -- Sponsorships

    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,
      dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) 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
  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.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID) 
            or (SPONSORSHIP.ID = MAXSEQUENCE.CONTEXTSPONSORSHIPID and MAXSEQUENCE.DECLINEDSPONSORSHIPID is null) )
          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
    left outer join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    left outer join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    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)
    order by COMMITMENTSEQUENCE, SPONSORSHIPSEQUENCE;