USP_DATALIST_REVENUETRANSACTION_DETAIL_WITHCAMPAIGNS

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

  declare @ALLOWDELETE bit;
  select @ALLOWDELETE = case when count(*) > 1 then 1 else 0 end
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where FINANCIALTRANSACTION.ID = @TRANSACTIONID
    and FINANCIALTRANSACTION.DELETEDON is null;

  select 
    FINANCIALTRANSACTIONLINEITEM.ID [REVENUESPLITID],
    null as PARENTID,
    coalesce(DESIGNATION.NAME, 'No designation') as NAME,
    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,
    dbo.UFN_REVENUE_ISPOSTED(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID) ISPOSTED,
    @ALLOWDELETE ALLOWDELETE,
    FINANCIALTRANSACTION.TYPECODE,
    FINANCIALTRANSACTION.ID REVENUEID,
    isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    null,
    null,
    null,
    FINANCIALTRANSACTIONLINEITEM.ID
  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.DELETEDON is null
  and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
  and REVENUESPLIT_EXT.APPLICATIONCODE <> 10 --Order


  union all

  select 
    null,
    REVENUESPLITCAMPAIGN.REVENUESPLITID as PARENTID,
    CAMPAIGN.NAME,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    REVENUESPLITCAMPAIGN.CAMPAIGNID,
    REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID,
    CAMPAIGNPRIORITYTYPECODE.DESCRIPTION + ' - ' + CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as CAMPAIGNSUBPRIORITY,
    REVENUESPLITCAMPAIGN.ID
    from dbo.REVENUESPLITCAMPAIGN
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    inner join dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
    left outer join dbo.CAMPAIGNSUBPRIORITY on REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = CAMPAIGNSUBPRIORITY.ID
    left outer join dbo.CAMPAIGNPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID
    left outer join dbo.CAMPAIGNPRIORITYTYPECODE on CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = CAMPAIGNPRIORITYTYPECODE.ID
    left outer join dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITYNAMECODE.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @TRANSACTIONID
    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
    and REVENUESPLIT_EXT.APPLICATIONCODE <> 10 --Order


  order by NAME asc