USP_DATALIST_SPONSORSHIPTRANSACTIONHISTORY

CREATE procedure dbo.USP_DATALIST_SPONSORSHIPTRANSACTIONHISTORY(@CONTEXTID uniqueidentifier)
as
    set nocount on;

    select ST.ID,
           case @CONTEXTID
             when ST.CONTEXTSPONSORSHIPID then
                case ST.ACTIONCODE
                  when 4 then 'Reactivate'
                  when 6 then 'Initiate transfer'
                  when 9 then 'Edit'
                  else 'Inactivate'
                end
             when ST.TARGETSPONSORSHIPID then
                case ST.ACTIONCODE
                  when 8 then 'Activate'
                  else 'Create'
                end
             when ST.DECLINEDSPONSORSHIPID then 'Decline'
           end EVENT,
           case ST.ACTIONCODE
             when 9 then ST.ACTION + ' to ' + (select NAME
                                               from dbo.CONSTITUENT
                                               where CONSTITUENT.ID = isnull(ST.GIFTFINANCIALSPONSORID,S.CONSTITUENTID))
             when 2 then case STR.REASONTYPECODE when 11 then 'Cancel expired' else ST.ACTION end
             else ST.ACTION
           end ACTION,
           STR.REASON as REASON,
           ST.TRANSACTIONDATE as DATE
    from dbo.SPONSORSHIP S
    inner join dbo.SPONSORSHIPTRANSACTION ST on ST.SPONSORSHIPCOMMITMENTID = S.SPONSORSHIPCOMMITMENTID and S.ID in(ST.CONTEXTSPONSORSHIPID,ST.TARGETSPONSORSHIPID,ST.DECLINEDSPONSORSHIPID)
    left join SPONSORSHIPREASON STR ON STR.ID = ST.SPONSORSHIPREASONID
    where S.ID = @CONTEXTID
    order by ST.TRANSACTIONSEQUENCE