USP_DATALIST_REVENUETRANSACTION_DETAIL

CREATE procedure dbo.USP_DATALIST_REVENUETRANSACTION_DETAIL
(
  @TRANSACTIONID uniqueidentifier
)
as 
  set nocount on;

  select 
    FINANCIALTRANSACTIONLINEITEM.ID [REVENUESPLITID],
    case REVENUESPLIT_EXT.TYPECODE
      when 9
        then REVENUESPLIT_EXT.TYPE + ' ' + lower(REVENUESPLIT_EXT.APPLICATION)
      when 17
        then 
          case REVENUESPLIT_EXT.APPLICATIONCODE
            when 3
              then 'Sponsorship recurring additional gift'
            else
              'Sponsorship additional donation'
          end
      else
        case REVENUESPLIT_EXT.APPLICATIONCODE
          when 6
            then
              case
                when exists(select ID from dbo.PLANNEDGIFTADDITIONREVENUE where REVENUEID = FINANCIALTRANSACTION.ID)
                  then 'Planned gift addition'
                else
                  REVENUESPLIT_EXT.APPLICATION
              end
          when 1
            then
              case REVENUESPLIT_EXT.TYPECODE
                when 0
                  then REVENUESPLIT_EXT.APPLICATION + ' (charitable)'
                else
                  REVENUESPLIT_EXT.APPLICATION
              end
          else
            REVENUESPLIT_EXT.APPLICATION
        end
    end,
    FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
    dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT(FINANCIALTRANSACTIONLINEITEM.ID, 0) as GROSSAMOUNT,
    case
      when FINANCIALTRANSACTION.POSTSTATUSCODE = 2 then
        convert(bit, 1)
      else
        convert(bit, 0)
    end as ISPOSTED,
    0 as ALLOWDELETE,
    FINANCIALTRANSACTION.TYPECODE,
    coalesce(DESIGNATION.NAME, 'No designation') as DESIGNATIONNAME,
    FINANCIALTRANSACTION.ID REVENUEID,
    isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
  from
    dbo.FINANCIALTRANSACTIONLINEITEM
  inner join
    dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
  inner join
    dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
  inner join
    dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  inner join
    dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
  inner join
    dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
  left outer join
    dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
  where
    FINANCIALTRANSACTION.ID = @TRANSACTIONID and
    FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
    FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
    REVENUESPLIT_EXT.APPLICATIONCODE <> 10 --Order