USP_REPORT_REVENUEDETAILGROUP

CREATE procedure dbo.USP_REPORT_REVENUEDETAILGROUP(
    @DATETYPE tinyint = null,
    @DATERANGEDISPLAY nvarchar(100) = '',
    @STARTDATE datetime = null, 
    @ENDDATE datetime = null,
    @REVENUETRANSACTIONQUERY uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @APPEALID uniqueidentifier = null,
    @DESIGNATIONID uniqueidentifier = null,
    @INCLUDEGROUPS bit = 0,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @REPORTUSERID nvarchar(128) = null,
    @CURRENCYCODE tinyint = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;
set transaction isolation level read uncommitted;

if @CURRENTAPPUSERID is null
    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

declare @NOW datetime = getdate();
declare @CURRENTDATEEARLIESTTIME datetime;
set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(@NOW);

declare @STARTDATEEARLIEST datetime = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
declare @ENDDATELATEST datetime = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

create table #REVENUEFILTER (
    ID uniqueidentifier
)
if @REVENUETRANSACTIONQUERY is not null
begin
    declare @DBOBJECTNAME nvarchar(128);
    declare @DBOBJECTTYPE smallint;

    if not exists(
        select ID 
        from dbo.IDSETREGISTER 
        where ID = @REVENUETRANSACTIONQUERY
    ) 
        raiserror('ID set does not exist in the database.', 15, 1);

    select 
        @DBOBJECTNAME = DBOBJECTNAME, 
        @DBOBJECTTYPE = OBJECTTYPE 
    from dbo.IDSETREGISTER 
    where ID = @REVENUETRANSACTIONQUERY;

    if @DBOBJECTTYPE = 1 
        set @DBOBJECTNAME = @DBOBJECTNAME + '()';
    else if @DBOBJECTTYPE = 2 
        set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUETRANSACTIONQUERY) + ''')';

    declare @SQLTOEXEC nvarchar(max);
    set @SQLTOEXEC = '
    insert into #REVENUEFILTER(
        ID
    )
    select ID
    from dbo.' + @DBOBJECTNAME + nchar(13);

    exec sp_executesql @SQLTOEXEC;
end
else
begin
    insert into #REVENUEFILTER(
        ID
    )
    select FT.ID
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.REVENUE_EXT R on R.ID = FT.ID
    where FT.DELETEDON is null and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9)
end

declare @ORIGINCODE tinyint;
select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
select
    @ORGANIZATIONCURRENCYID = ID,
    @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
    @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
from dbo.CURRENCY 
where CURRENCY.ISORGANIZATIONCURRENCY=1;

if @DESIGNATIONID is null


with CTE_CONSTITUENT as(
    select
        CONSTITUENT.ID,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        CASE CONSTITUENT.ISORGANIZATION 
            WHEN 1 THEN 
                CASE CONSTITUENT.KEYNAMEPREFIX WHEN '' THEN 
                    CONSTITUENT.KEYNAME ELSE CONSTITUENT.KEYNAMEPREFIX + ' ' +  CONSTITUENT.KEYNAME 
                END
            ELSE CASE CONSTITUENT.ISGROUP WHEN 1 THEN
              CASE CONSTITUENT.DISPLAYNAME WHEN '' THEN CONSTITUENT.KEYNAME ELSE CONSTITUENT.DISPLAYNAME END
          ELSE
              CASE CONSTITUENT.FIRSTNAME WHEN '' THEN '' ELSE CONSTITUENT.FIRSTNAME + ' ' END
              +
              CASE CONSTITUENT.MIDDLENAME WHEN '' THEN '' ELSE LEFT(CONSTITUENT.MIDDLENAME,1) + '. ' END
              + 
              CONSTITUENT.KEYNAME
          END
      END [NAME],
        GROUPCONSTITUENT.ID as GROUPHOUSEHOLDID,
        GROUPCONSTITUENT.NAME as GROUPHOUSEHOLDNAME
    from dbo.CONSTITUENT 
        inner join dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
        inner join dbo.CONSTITUENT GROUPCONSTITUENT with (nolock) on GROUPMEMBER.GROUPID = GROUPCONSTITUENT.ID
        inner join dbo.GROUPDATA on GROUPCONSTITUENT.ID = GROUPDATA.ID
        left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
        left join dbo.GROUPTYPE on GROUPDATA.GROUPTYPEID = GROUPTYPE.ID
    where (@CONSTITUENTID is null or CONSTITUENT.ID = @CONSTITUENTID)
        and (GROUPDATA.GROUPTYPECODE = 0 
            or (GROUPTYPE.INCLUDEMEMBERGIVING = 1 and @INCLUDEGROUPS = 1)
        )
        and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIESTTIME)
        and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= @CURRENTDATEEARLIESTTIME)
),
DONATIONREFUNDED_CTE as (
    select ORDERSPLIT.ID as REVENUESPLITID, CREDITLINEITEM.BASEAMOUNT as DONATIONREFUNDED
    from dbo.FINANCIALTRANSACTIONLINEITEM CREDITLINEITEM
        inner join dbo.CREDITITEM_EXT on CREDITLINEITEM.ID = CREDITITEM_EXT.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM ORDERLINEITEM on ORDERLINEITEM.ID = CREDITLINEITEM.SOURCELINEITEMID
        inner join dbo.REVENUESPLIT_EXT ORDERSPLIT on ORDERLINEITEM.ID = ORDERSPLIT.ID
    where CREDITITEM_EXT.TYPECODE = 2 and ORDERSPLIT.TYPECODE = 0 AND ORDERSPLIT.APPLICATIONCODE = 0
),
ORDERPAYMENT_CTE as (
    select ORDERPAYMENTLINEITEM.SOURCELINEITEMID as ID, coalesce(sum(ORDERPAYMENTLINEITEM.BASEAMOUNT), 0) as BASEAMOUNT, coalesce(sum(ORDERPAYMENTLINEITEM.TRANSACTIONAMOUNT), 0) as TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM ORDERPAYMENTLINEITEM
        inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = ORDERPAYMENTLINEITEM.FINANCIALTRANSACTIONID 
    where ORDERPAYMENTLINEITEM.SOURCELINEITEMID is not null
    group by ORDERPAYMENTLINEITEM.SOURCELINEITEMID
),
ORDERITEMPAYMENT_CTE as (
    select CONTRIBUTEDMEMBERSHIPLINEITEM.ID, isnull(ORDERPAYMENT_CTE.BASEAMOUNT, 0) as BASEAMOUNT, isnull(ORDERPAYMENT_CTE.TRANSACTIONAMOUNT, 0) as TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDMEMBERSHIPLINEITEM
        inner join dbo.REVENUESPLIT_EXT CONTRIBUTEDMEMBERSHIPSPLIT on CONTRIBUTEDMEMBERSHIPSPLIT.ID = CONTRIBUTEDMEMBERSHIPLINEITEM.ID
        inner join dbo.SALESORDER on CONTRIBUTEDMEMBERSHIPLINEITEM.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
        left join ORDERPAYMENT_CTE on CONTRIBUTEDMEMBERSHIPLINEITEM.ID = ORDERPAYMENT_CTE.ID
    where 
        ((CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 0 
            and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 0 
            and CONTRIBUTEDMEMBERSHIPLINEITEM.SOURCELINEITEMID is not null) 
        or 
        (CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 2 
            and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 5 
            and exists (select * from dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT DONATIONSPLIT on DONATIONLINEITEM.ID = DONATIONSPLIT.ID
                            where DONATIONSPLIT.TYPECODE = 0 and DONATIONSPLIT.APPLICATIONCODE = 0 and DONATIONLINEITEM.SOURCELINEITEMID = CONTRIBUTEDMEMBERSHIPSPLIT.ID
            )))
)



select 'http://www.blackbaud.com/REVENUETRANSACTIONID?REVENUETRANSACTIONID=' + CONVERT(nvarchar(36),REVENUE.ID) as REVENUETRANSACTIONID,
    CTE_CONSTITUENT.KEYNAME,
    CTE_CONSTITUENT.FIRSTNAME,
    CTE_CONSTITUENT.NAME,
    REVENUE.DATE,
    dbo.UDA_BUILDLIST(distinct DESIGNATION.NAME) DESIGNATIONLIST,    
    sum(case 
        when REVENUE.TRANSACTIONTYPECODE = 5 and ORDERPAYMENTFORMEMBERSHIP.ID is not null  then ORDERPAYMENTFORMEMBERSHIP.BASEAMOUNT - coalesce(DONATIONREFUNDED_CTE.DONATIONREFUNDED, 0)
        when REVENUE.TRANSACTIONTYPECODE in (0,5,7) and ORDERPAYMENTFORMEMBERSHIP.ID is null then case @CURRENCYCODE when 0 then LI.BASEAMOUNT when 2 then LI.TRANSACTIONAMOUNT else LI.ORGAMOUNT end - coalesce(DONATIONREFUNDED_CTE.DONATIONREFUNDED, 0)
        else 0.00 end) GIFT,
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then REVENUE.BALANCEINCURRENCY else 0.00 end PLEDGEBALANCE,
    CTE_CONSTITUENT.GROUPHOUSEHOLDID as GROUPHOUSEHOLDID,
    CTE_CONSTITUENT.GROUPHOUSEHOLDNAME as GROUPHOUSEHOLDNAME,
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then 1 else 0 end DISPLAYGIFTAMOUNT,
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.ISO4217 else null end [GIFTISOCURRENCYCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.CURRENCYSYMBOL else null end [GIFTCURRENCYSYMBOL],
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE else null end [GIFTCURRENCYSYMBOLDISPLAYSETTINGCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.DECIMALDIGITS else null end [GIFTDECIMALDIGITS],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.ISO4217 else null end [PLEDGEISOCURRENCYCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.CURRENCYSYMBOL else null end [PLEDGECURRENCYSYMBOL],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE else null end [PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.DECIMALDIGITS else null end [PLEDGEDECIMALDIGITS]
from dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK2(
        @ORGANIZATIONCURRENCYID,
        @ORGANIZATIONCURRENCYID,
        @DECIMALDIGITS,
        @ROUNDINGTYPECODE,
        @STARTDATEEARLIEST,
        @ENDDATELATEST,
        @ORIGINCODE,
        @CURRENCYCODE
    ) as REVENUE 
    inner join #REVENUEFILTER FILTER on FILTER.ID=REVENUE.ID
    inner join CTE_CONSTITUENT on CTE_CONSTITUENT.ID = REVENUE.CONSTITUENTID 
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = REVENUE.ID and LI.TYPECODE != 1 and LI.DELETEDON is null
    inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = LI.ID
    inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
    inner join dbo.CURRENCY CURRENCYPROPERTIES on CURRENCYPROPERTIES.ID = REVENUE.CURRENCYID
    left outer join DONATIONREFUNDED_CTE on DONATIONREFUNDED_CTE.REVENUESPLITID = REVENUESPLIT.ID
    left outer join ORDERITEMPAYMENT_CTE as ORDERPAYMENTFORMEMBERSHIP on ORDERPAYMENTFORMEMBERSHIP.ID = REVENUESPLIT.ID
where
    (
        REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,7,8)
        or (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT.APPLICATIONCODE = 0)
        or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,2,3,4,6,7,8,12,13))
        or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)
    )
    and (@APPEALID is null or REVENUE.APPEALID = @APPEALID)
    and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
    and exists (
        select 1 
        from dbo.FINANCIALTRANSACTIONLINEITEM
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID 
            and exists (
                select HASPERMISSION
                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT_EXT.ID) REVSITES
                    cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '8F250015-83FB-44d5-A467-E0A760256768', REVSITES.SITEID)
            )
    )
group by 
    REVENUE.ID, CTE_CONSTITUENT.ID, CTE_CONSTITUENT.KEYNAME, CTE_CONSTITUENT.FIRSTNAME, CTE_CONSTITUENT.NAME, 
    REVENUE.ID, REVENUE.DATE, REVENUE.TRANSACTIONTYPECODE, CTE_CONSTITUENT.GROUPHOUSEHOLDNAME, CTE_CONSTITUENT.GROUPHOUSEHOLDID, 
    CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
    CURRENCYPROPERTIES.DECIMALDIGITS, REVENUE.BASECURRENCYID, REVENUE.TRANSACTIONCURRENCYID, REVENUE.BALANCEINCURRENCY
order by 
    CTE_CONSTITUENT.KEYNAME, CTE_CONSTITUENT.FIRSTNAME, CTE_CONSTITUENT.GROUPHOUSEHOLDNAME, REVENUE.DATE

else

with CTE_CONSTITUENT as(
    select
        CONSTITUENT.ID,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.FIRSTNAME,
        CASE CONSTITUENT.ISORGANIZATION 
            WHEN 1 THEN 
                CASE CONSTITUENT.KEYNAMEPREFIX WHEN '' THEN 
                    CONSTITUENT.KEYNAME ELSE CONSTITUENT.KEYNAMEPREFIX + ' ' +  CONSTITUENT.KEYNAME 
                END
            ELSE CASE CONSTITUENT.ISGROUP WHEN 1 THEN
              CASE CONSTITUENT.DISPLAYNAME WHEN '' THEN CONSTITUENT.KEYNAME ELSE CONSTITUENT.DISPLAYNAME END
          ELSE
              CASE CONSTITUENT.FIRSTNAME WHEN '' THEN '' ELSE CONSTITUENT.FIRSTNAME + ' ' END
              +
              CASE CONSTITUENT.MIDDLENAME WHEN '' THEN '' ELSE LEFT(CONSTITUENT.MIDDLENAME,1) + '. ' END
              + 
              CONSTITUENT.KEYNAME
          END
      END [NAME],
        GROUPCONSTITUENT.ID as GROUPHOUSEHOLDID,
        GROUPCONSTITUENT.NAME as GROUPHOUSEHOLDNAME
    from dbo.CONSTITUENT 
        inner join dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
        inner join dbo.CONSTITUENT GROUPCONSTITUENT with (nolock) on GROUPMEMBER.GROUPID = GROUPCONSTITUENT.ID
        inner join dbo.GROUPDATA on GROUPCONSTITUENT.ID = GROUPDATA.ID
        left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
        left join dbo.GROUPTYPE on GROUPDATA.GROUPTYPEID = GROUPTYPE.ID
    where (@CONSTITUENTID is null or CONSTITUENT.ID = @CONSTITUENTID)
        and (GROUPDATA.GROUPTYPECODE = 0 
            or (GROUPTYPE.INCLUDEMEMBERGIVING = 1 and @INCLUDEGROUPS = 1)
        )
        and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATEEARLIESTTIME)
        and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO >= @CURRENTDATEEARLIESTTIME)
),
DONATIONREFUNDED_CTE as (
    select ORDERSPLIT.ID as REVENUESPLITID, CREDITLINEITEM.BASEAMOUNT as DONATIONREFUNDED
    from dbo.FINANCIALTRANSACTIONLINEITEM CREDITLINEITEM
        inner join dbo.CREDITITEM_EXT on CREDITLINEITEM.ID = CREDITITEM_EXT.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM ORDERLINEITEM on ORDERLINEITEM.ID = CREDITLINEITEM.SOURCELINEITEMID
        inner join dbo.REVENUESPLIT_EXT ORDERSPLIT on ORDERLINEITEM.ID = ORDERSPLIT.ID
    where CREDITITEM_EXT.TYPECODE = 2 and ORDERSPLIT.TYPECODE = 0 AND ORDERSPLIT.APPLICATIONCODE = 0
),
ORDERPAYMENT_CTE as (
    select ORDERPAYMENTLINEITEM.SOURCELINEITEMID as ID, coalesce(sum(ORDERPAYMENTLINEITEM.BASEAMOUNT), 0) as BASEAMOUNT, coalesce(sum(ORDERPAYMENTLINEITEM.TRANSACTIONAMOUNT), 0) as TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM ORDERPAYMENTLINEITEM
        inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = ORDERPAYMENTLINEITEM.FINANCIALTRANSACTIONID 
    where ORDERPAYMENTLINEITEM.SOURCELINEITEMID is not null
    group by ORDERPAYMENTLINEITEM.SOURCELINEITEMID
),
ORDERITEMPAYMENT_CTE as (
    select CONTRIBUTEDMEMBERSHIPLINEITEM.ID, isnull(ORDERPAYMENT_CTE.BASEAMOUNT, 0) as BASEAMOUNT, isnull(ORDERPAYMENT_CTE.TRANSACTIONAMOUNT, 0) as TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDMEMBERSHIPLINEITEM
        inner join dbo.REVENUESPLIT_EXT CONTRIBUTEDMEMBERSHIPSPLIT on CONTRIBUTEDMEMBERSHIPSPLIT.ID = CONTRIBUTEDMEMBERSHIPLINEITEM.ID
        inner join dbo.SALESORDER on CONTRIBUTEDMEMBERSHIPLINEITEM.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
        left join ORDERPAYMENT_CTE on CONTRIBUTEDMEMBERSHIPLINEITEM.ID = ORDERPAYMENT_CTE.ID
    where 
        ((CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 0 
            and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 0 
            and CONTRIBUTEDMEMBERSHIPLINEITEM.SOURCELINEITEMID is not null) 
        or 
        (CONTRIBUTEDMEMBERSHIPSPLIT.TYPECODE = 2 
            and CONTRIBUTEDMEMBERSHIPSPLIT.APPLICATIONCODE = 5 
            and exists (select * from dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLINEITEM
                            inner join dbo.REVENUESPLIT_EXT DONATIONSPLIT on DONATIONLINEITEM.ID = DONATIONSPLIT.ID
                            where DONATIONSPLIT.TYPECODE = 0 and DONATIONSPLIT.APPLICATIONCODE = 0 and DONATIONLINEITEM.SOURCELINEITEMID = CONTRIBUTEDMEMBERSHIPSPLIT.ID
            )))
)

select 'http://www.blackbaud.com/REVENUETRANSACTIONID?REVENUETRANSACTIONID=' + CONVERT(nvarchar(36),REVENUE.ID) as REVENUETRANSACTIONID,
    CTE_CONSTITUENT.KEYNAME,
    CTE_CONSTITUENT.FIRSTNAME,
    CTE_CONSTITUENT.NAME,
    REVENUE.DATE,
    dbo.UDA_BUILDLIST(distinct DESIGNATION.NAME) DESIGNATIONLIST,    
    sum(case 
        when REVENUE.TRANSACTIONTYPECODE = 5 and ORDERPAYMENTFORMEMBERSHIP.ID is not null  then ORDERPAYMENTFORMEMBERSHIP.BASEAMOUNT - coalesce(DONATIONREFUNDED_CTE.DONATIONREFUNDED, 0)
        when REVENUE.TRANSACTIONTYPECODE in (0,5,7) and ORDERPAYMENTFORMEMBERSHIP.ID is null then case @CURRENCYCODE when 0 then LI.BASEAMOUNT when 2 then LI.TRANSACTIONAMOUNT else LI.ORGAMOUNT end - coalesce(DONATIONREFUNDED_CTE.DONATIONREFUNDED, 0)
        else 0.00 end) GIFT,
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then 
    case when @DESIGNATIONID is null then 
              REVENUE.BALANCEINCURRENCY 
            else                           
              PLEDGESPLIT.BALANCE 
            end    
  else 0.00 
  end PLEDGEBALANCE,
    CTE_CONSTITUENT.GROUPHOUSEHOLDID as GROUPHOUSEHOLDID,
    CTE_CONSTITUENT.GROUPHOUSEHOLDNAME as GROUPHOUSEHOLDNAME,
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then 1 else 0 end DISPLAYGIFTAMOUNT,
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.ISO4217 else null end [GIFTISOCURRENCYCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.CURRENCYSYMBOL else null end [GIFTCURRENCYSYMBOL],
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE else null end [GIFTCURRENCYSYMBOLDISPLAYSETTINGCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (0,5,7) then CURRENCYPROPERTIES.DECIMALDIGITS else null end [GIFTDECIMALDIGITS],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.ISO4217 else null end [PLEDGEISOCURRENCYCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.CURRENCYSYMBOL else null end [PLEDGECURRENCYSYMBOL],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE else null end [PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE],
    case when REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,8) then CURRENCYPROPERTIES.DECIMALDIGITS else null end [PLEDGEDECIMALDIGITS]
from dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK2(
        @ORGANIZATIONCURRENCYID,
        @ORGANIZATIONCURRENCYID,
        @DECIMALDIGITS,
        @ROUNDINGTYPECODE,
        @STARTDATEEARLIEST,
        @ENDDATELATEST,
        @ORIGINCODE,
        @CURRENCYCODE
    ) as REVENUE 
    inner join #REVENUEFILTER FILTER on FILTER.ID=REVENUE.ID
    inner join CTE_CONSTITUENT on CTE_CONSTITUENT.ID = REVENUE.CONSTITUENTID 
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = REVENUE.ID and LI.TYPECODE != 1 and LI.DELETEDON is null
    inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = LI.ID
    inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
    inner join dbo.CURRENCY CURRENCYPROPERTIES on CURRENCYPROPERTIES.ID = REVENUE.CURRENCYID
    left outer join dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY_BULK_2
    (
        @ORGANIZATIONCURRENCYID,
        @ORGANIZATIONCURRENCYID,
        @DECIMALDIGITS,
        @ROUNDINGTYPECODE,
        getdate(),
        @ORIGINCODE,
        @CURRENCYCODE,
        @DESIGNATIONID,
        @STARTDATE,
        @ENDDATE,
        @STARTDATEEARLIEST,
        @ENDDATELATEST       
    ) as PLEDGESPLIT on PLEDGESPLIT.ID = LI.ID  
    left outer join DONATIONREFUNDED_CTE on DONATIONREFUNDED_CTE.REVENUESPLITID = REVENUESPLIT.ID
    left outer join ORDERITEMPAYMENT_CTE as ORDERPAYMENTFORMEMBERSHIP on ORDERPAYMENTFORMEMBERSHIP.ID = REVENUESPLIT.ID
where
    (
        REVENUE.TRANSACTIONTYPECODE in (1,3,4,6,7,8)
        or (REVENUE.TRANSACTIONTYPECODE = 5 and REVENUESPLIT.APPLICATIONCODE = 0)
        or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,2,3,4,6,7,8,12,13))
        or (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0)
    )
    and (@APPEALID is null or REVENUE.APPEALID = @APPEALID)
    and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
    and exists (
        select 1 
        from dbo.FINANCIALTRANSACTIONLINEITEM
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID 
            and exists (
                select HASPERMISSION
                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT_EXT.ID) REVSITES
                    cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '8F250015-83FB-44d5-A467-E0A760256768', REVSITES.SITEID)
            )
    )
group by 
    REVENUE.ID,PLEDGESPLIT.ID,PLEDGESPLIT.BALANCE,
  CTE_CONSTITUENT.ID, CTE_CONSTITUENT.KEYNAME, CTE_CONSTITUENT.FIRSTNAME, CTE_CONSTITUENT.NAME, 
    REVENUE.ID, REVENUE.DATE, REVENUE.TRANSACTIONTYPECODE, CTE_CONSTITUENT.GROUPHOUSEHOLDNAME, CTE_CONSTITUENT.GROUPHOUSEHOLDID, 
    CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
    CURRENCYPROPERTIES.DECIMALDIGITS, REVENUE.BASECURRENCYID, REVENUE.TRANSACTIONCURRENCYID, REVENUE.BALANCEINCURRENCY
order by 
    CTE_CONSTITUENT.KEYNAME, CTE_CONSTITUENT.FIRSTNAME, CTE_CONSTITUENT.GROUPHOUSEHOLDNAME, REVENUE.DATE


drop table #REVENUEFILTER