USP_DATALIST_DESIGNATIONLEVELDONORS

CREATE procedure dbo.USP_DATALIST_DESIGNATIONLEVELDONORS(@ID uniqueidentifier = null)
as                 
  set nocount on;

  with DESIGNATION_CTE as (
      select
          DESIGNATION.ID
      from
          dbo.DESIGNATION
      where
          DESIGNATION.DESIGNATIONLEVEL1ID = @ID or  
          DESIGNATION.DESIGNATIONLEVEL2ID = @ID or  
          DESIGNATION.DESIGNATIONLEVEL3ID = @ID or      
          DESIGNATION.DESIGNATIONLEVEL4ID = @ID or  
          DESIGNATION.DESIGNATIONLEVEL5ID = @ID
  )
  select distinct 
    CONSTITUENT.ID as ID,
    CONSTITUENT.NAME as NAME,
    LISTORDER = 0,
    null as DATE,
    null as AMOUNT,
    null as ISORIGINALFUNDING,
    isnull(DLD.ISBENEFACTOR, 0) as ISBENEFACTOR,
    cast(CONSTITUENT.ID as nvarchar(36)) + cast(@ID as nvarchar(36)) as ACTIONID,
    PARENT = null,
    null as BASECURRENCYID
  from
    DESIGNATION_CTE as DESIGNATION
    inner join dbo.REVENUESPLIT_EXT ON REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    inner join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.DESIGNATIONLEVELDONORINFORMATION DLD on DLD.CONSTITUENTID = CONSTITUENT.ID and DLD.DESIGNATIONLEVELID = @ID
  where
    (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null)
    and 
    (
      (FINANCIALTRANSACTION.TYPECODE in (1,4,7))
      or 
      (
        (FINANCIALTRANSACTION.TYPECODE = 0)
        and (REVENUESPLIT_EXT.APPLICATIONCODE in (0,1,3,7))
      )
    )

  union all

  select 
    null as ID,
    null as NAME,
    LISTORDER = 1,
    cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,
    FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
    isnull(DLR.ISORIGINALFUNDING, 0) as ISORIGINALFUNDING,
    null as ISBENEFACTOR,
    cast(FINANCIALTRANSACTION.ID as nvarchar(36)) + cast(@ID as nvarchar(36)) as ACTIONID,
    FINANCIALTRANSACTION.CONSTITUENTID PARENT,
    isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
  from
    DESIGNATION_CTE as DESIGNATION
    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    left join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
    left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
    left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
    left join dbo.DESIGNATIONLEVELREVENUEINFORMATION DLR on DLR.REVENUEID = FINANCIALTRANSACTION.ID and DLR.DESIGNATIONLEVELID = @ID
  where
    (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null)
    and 
    (
      (FINANCIALTRANSACTION.TYPECODE in (1,4,7))
      or 
      (
        (FINANCIALTRANSACTION.TYPECODE = 0)
        and (REVENUESPLIT_EXT.APPLICATIONCODE in (0,1,3,7))
      )
    )