REVENUESPLIT

CREATE view dbo.REVENUESPLIT with schemabinding
as
select 
  FINANCIALTRANSACTIONLINEITEM.ID
  ,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as REVENUEID
  ,REVENUESPLIT_EXT.DESIGNATIONID
  ,FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT as AMOUNT
  ,FINANCIALTRANSACTIONLINEITEM.ADDEDBYID
  ,FINANCIALTRANSACTIONLINEITEM.CHANGEDBYID
  ,FINANCIALTRANSACTIONLINEITEM.DATEADDED
  ,FINANCIALTRANSACTIONLINEITEM.DATECHANGED
  ,FINANCIALTRANSACTIONLINEITEM.TS
  ,FINANCIALTRANSACTIONLINEITEM.TSLONG
  ,REVENUESPLIT_EXT.TYPECODE
  ,REVENUESPLIT_EXT.APPLICATIONCODE
    ,case when FINANCIALTRANSACTIONLINEITEM.DELETEDON is null then 
    cast('(' +  case REVENUESPLIT_EXT.APPLICATIONCODE 
    when 0 then N'Donation' 
    when 1 then N'Event registration' 
    when 2 then N'Pledge' 
    when 3 then N'Recurring gift' 
    when 4 then N'Other' 
    when 5 then N'Membership' 
    when 6 then N'Planned gift' 
    when 7 then N'Matching gift' 
    when 8 then N'Grant award' 
    when 9 then N'Admission' 
    when 10 then N'Order' 
    when 11 then N'Miscellaneous' 
    when 12 then N'Auction purchase' 
    when 13 then N'Donor challenge' 
    when 15 then N'Event sponsorship' 
    when 16 then N'Merchandise'  
    when 17 then N'Pending gift'
    when 18 then N'Membership add-on'
    end + ') ' 
    + cast(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT as nvarchar(20)) + ' ' + coalesce(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, 'None') + ' - ' + ltrim(rtrim(CONSTITUENT.NAME)) as nvarchar(700))
    else null end as DESCRIPTION   
  ,case when FINANCIALTRANSACTIONLINEITEM.DELETEDON is null then isnull(REX.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) else null end as BASECURRENCYID
  ,FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT as ORGANIZATIONAMOUNT
  ,FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
  ,FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
  ,case when FINANCIALTRANSACTIONLINEITEM.DELETEDON is null then FINANCIALTRANSACTION.TRANSACTIONCURRENCYID else null end as TRANSACTIONCURRENCYID
  ,FINANCIALTRANSACTION.BASEEXCHANGERATEID,
  REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS, REVENUESPLIT_EXT.REVENUESPLITBUSINESSUNITOVERRIDECODEID,
  case REVENUESPLIT_EXT.APPLICATIONCODE 
    when 0 then N'Donation' 
    when 1 then N'Event registration' 
    when 2 then N'Pledge' 
    when 3 then N'Recurring gift' 
    when 4 then N'Other' 
    when 5 then N'Membership' 
    when 6 then N'Planned gift' 
    when 7 then N'Matching gift' 
    when 8 then N'Grant award' 
    when 9 then N'Admission' 
    when 10 then N'Order' 
    when 11 then N'Miscellaneous' 
    when 12 then N'Auction purchase' 
    when 13 then N'Donor challenge' 
    when 15 then N'Event sponsorship' 
    when 16 then N'Merchandise'  
    when 17 then N'Pending gift'
    when 18 then N'Membership add-on'
    end as APPLICATION,    
  cast(case REVENUESPLIT_EXT.TYPECODE
    when 0 then N'Gift' 
    when 1 then N'Event registration' 
    when 2 then N'Membership' 
    when 3 then N'Grant award' 
    when 4 then N'Other' 
    when 5 then N'Ticket' 
    when 6 then N'Fee' 
    when 7 then N'Tax' 
    when 8 then N'Miscellaneous' 
    when 9 then N'Sponsorship' 
    when 10 then N'Supply/Equipment Resource' 
    when 11 then N'Staffing Resource' 
    when 12 then N'Auction purchase' 
    when 13 then N'Security deposit' 
    when 14 then N'Facility' 
    when 15 then N'Event sponsorship' 
    when 16 then N'Merchandise'  
    when 17 then N'Sponsorship additional gift'
    when 18 then N'Membership add-on'
    end as nvarchar(27))  as TYPE
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT REX on REX.ID = FINANCIALTRANSACTION.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
left outer join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1