USP_DATALIST_DESIGNATIONLEVELDONORS_2

CREATE procedure dbo.USP_DATALIST_DESIGNATIONLEVELDONORS_2 (
  @ID uniqueidentifier = null
  ,@CONSTITUENTID uniqueidentifier = null
  ,@DATERANGE tinyint = null
  ,@FROM datetime = null
  ,@TO datetime = null
  )
as
set nocount on;

if @DATERANGE is null
  set @DATERANGE = 10; --all dates


exec [dbo].[USP_RESOLVEDATEFILTER] @DATERANGE
  ,@FROM output
  ,@TO output;

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 FT.ID as ID
  ,FT.CONSTITUENTID CONSTITUENTID
  ,(
    case 
      when CONSTITUENT.ISORGANIZATION = 1
        then case CONSTITUENT.KEYNAMEPREFIX
            when ''
              then CONSTITUENT.KEYNAME
            else CONSTITUENT.KEYNAME + ', ' + CONSTITUENT.KEYNAMEPREFIX
            end
      else dbo.UFN_NAMEFORMAT_08(CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, null, null, null, null, null, null, null)
      end
    ) + ' - Lookup ID: ' + CONSTITUENT.LOOKUPID as [NAME]
  ,cast(FT.[DATE] as datetime) as [DATE]
  ,FTL.BASEAMOUNT
  ,isnull(DLR.ISORIGINALFUNDING, 0) as ISORIGINALFUNDING
  ,isnull(DLD.ISBENEFACTOR, 0) as ISBENEFACTOR
  ,isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
  ,DESIGNATIONLEVELID = @ID
from DESIGNATION_CTE as DESIGNATION
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTL on FTL.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTL.FINANCIALTRANSACTIONID
inner join dbo.CONSTITUENT on FT.CONSTITUENTID = CONSTITUENT.ID
left join dbo.DESIGNATIONLEVELDONORINFORMATION DLD on DLD.CONSTITUENTID = CONSTITUENT.ID
  and DLD.DESIGNATIONLEVELID = @ID
left join dbo.REVENUE_EXT on REVENUE_EXT.ID = FT.ID
left join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FT.PDACCOUNTSYSTEMID
left join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
left join dbo.DESIGNATIONLEVELREVENUEINFORMATION DLR on DLR.REVENUEID = FT.ID
  and DLR.DESIGNATIONLEVELID = @ID
where (
    @CONSTITUENTID is null
    or FT.CONSTITUENTID = @CONSTITUENTID
    )
  and (
    @FROM is null
    or FT.[DATE] >= @FROM
    )
  and (
    @TO is null
    or FT.[DATE] <= @TO
    )
  and (FTL.DELETEDON is null)
  and (
    (
      FT.TYPECODE in (
        1
        ,4
        ,7
        )
      or (
        (FT.TYPECODE = 0)
        and (
          REVENUESPLIT_EXT.APPLICATIONCODE in (
            0
            ,1
            ,3
            ,7
            )
          )
        )
      )
    )
order by (
    case 
      when CONSTITUENT.ISORGANIZATION = 1
        then case CONSTITUENT.KEYNAMEPREFIX
            when ''
              then CONSTITUENT.KEYNAME
            else CONSTITUENT.KEYNAME + ', ' + CONSTITUENT.KEYNAMEPREFIX
            end
      else dbo.UFN_NAMEFORMAT_08(CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, null, null, null, null, null, null, null)
      end
    )
  ,CONSTITUENT.LOOKUPID
  ,FT.date
  --,FTL.TRANSACTIONAMOUNT