USP_REPORT_TOTALREVENUE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @INCLUDETAXES | bit | IN | |
| @INCLUDESECURITYDEPOSITS | bit | IN | |
| @INCLUDEUNRECEIVEDREVENUE | bit | IN | |
| @FROMDATE | datetime | IN | |
| @TODATE | datetime | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @APPUSERID | uniqueidentifier | IN | |
| @APPUSERQUERYID | uniqueidentifier | IN | |
| @SALESMETHODTYPECODE | smallint | IN | |
| @SOURCEADVANCESALES | bit | IN | |
| @SOURCEDAILYSALES | bit | IN | |
| @SOURCEGROUPSALES | bit | IN | |
| @SOURCEONLINESALES | bit | IN | |
| @SOURCEBACKOFFICE | bit | IN |
Definition
Copy
create procedure dbo.USP_REPORT_TOTALREVENUE
(
@INCLUDETAXES bit = null,
@INCLUDESECURITYDEPOSITS bit = null,
@INCLUDEUNRECEIVEDREVENUE bit = null,
@FROMDATE datetime = null,
@TODATE datetime = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@APPUSERID uniqueidentifier = null,
@APPUSERQUERYID uniqueidentifier = null,
@SALESMETHODTYPECODE smallint = null, --not used anymore
@SOURCEADVANCESALES bit = null,
@SOURCEDAILYSALES bit = null,
@SOURCEGROUPSALES bit = null,
@SOURCEONLINESALES bit = null,
@SOURCEBACKOFFICE bit = null
)
as
set nocount on;
set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE)
set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE)
declare @SalesMethodCodes table (SALESMETHODTYPECODE smallint)
insert into @SalesMethodCodes (SALESMETHODTYPECODE)
select num
from dbo.NUMBERS
where (num = 0 and (@SOURCEDAILYSALES = 1 or @SALESMETHODTYPECODE = 0))
or (num = 1 and (@SOURCEADVANCESALES = 1 or @SALESMETHODTYPECODE = 1))
or (num = 2 and (@SOURCEONLINESALES = 1 or @SALESMETHODTYPECODE = 2))
or (num = 3 and (@SOURCEGROUPSALES = 1 or @SALESMETHODTYPECODE = 3))
or (num = 4 and (@SOURCEBACKOFFICE = 1 or @SALESMETHODTYPECODE = 4))
or (num < 5 and (@SALESMETHODTYPECODE = -1))
declare @INCLUDEBACKOFFICE bit = 0;
if @SOURCEBACKOFFICE = 1 or @SALESMETHODTYPECODE = -1 or @SALESMETHODTYPECODE = 4
set @INCLUDEBACKOFFICE = 1;
declare @USERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
declare @USERGRANTEDTRANSACTIONPAGE bit = @USERISSYSADMIN,
@USERGRANTEDORDERPAGE bit = @USERISSYSADMIN,
@USERGRANTEDRESERVATIONPAGE bit = @USERISSYSADMIN,
@USERGRANTEDREFUNDPAGE bit = @USERISSYSADMIN,
@USERGRANTEDCONSTITUENTPAGE bit = @USERISSYSADMIN,
@USERGRANTEDREVENUEAPPLICATIONPAGE bit = @USERISSYSADMIN,
@USERGRANTEDREGISTRANTPAGE bit = @USERISSYSADMIN,
@USERGRANTEDMEMBERSHIPPAGE bit = @USERISSYSADMIN;
if @USERISSYSADMIN <> 1
begin
select
@USERGRANTEDTRANSACTIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'D00E6C42-2434-4D85-8A04-2323CA6BB2E7'),
@USERGRANTEDORDERPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '9C4D9D19-BB7E-4656-9B78-7EE1930C009A'),
@USERGRANTEDRESERVATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'C8E970CA-858A-4066-AD34-DC049A2A2DE7'),
@USERGRANTEDREFUNDPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'B2130DFA-51F6-4205-87E6-7D132573CB4D'),
@USERGRANTEDCONSTITUENTPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '0C836902-A398-47a0-91EB-8B66E434148E'),
@USERGRANTEDREVENUEAPPLICATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '32C309D8-E111-4DFE-B777-69206707512F'),
@USERGRANTEDREGISTRANTPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'D7A6E035-4F35-4784-BD57-B506CEAA8CE5'),
@USERGRANTEDMEMBERSHIPPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'D56E385D-AF02-464D-AD07-8C2FBE88246D');
end;
-- These will remain empty if there's no @APPUSERID or @APPUSERQUERYID specified.
declare @APPUSERFILTER dbo.UDT_GENERICID; -- Contains the IDs of appusers to filter on
declare @CHANGEAGENTFILTER dbo.UDT_GENERICID; -- Contains all CHANGAGENTIDS associated with the appusers we're filtering on. Used to filter FINANCIALTRANSACTIONS.
declare @HASAPPUSERFILTER bit = case when (@APPUSERID is not null or @APPUSERQUERYID is not null) then 1 else 0 end;
if @HASAPPUSERFILTER = 1
begin
if @APPUSERID is not null
begin
insert into @APPUSERFILTER (ID)
select @APPUSERID;
end
else if @APPUSERQUERYID is not null
begin
insert into @APPUSERFILTER (ID)
select distinct ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@APPUSERQUERYID);
end
-- Include IDs and CHANGEAGENTIDS only for the users who should be included in the final results.
insert into @CHANGEAGENTFILTER (ID)
select distinct CHANGEAGENT.ID
from @APPUSERFILTER APPUSERFILTER
inner join dbo.APPUSER on APPUSER.ID = APPUSERFILTER.ID -- Inner join to only include the users we're filtering on
inner join dbo.CHANGEAGENT on CHANGEAGENT.USERNAME = APPUSER.USERNAME;
end
if object_id('tempdb..#TEMP_DATA_TOTALREVENUEREPORT_REVENUE') is not null
drop table tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUE;
if object_id('tempdb..#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS') is not null
drop table tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS;
if object_id('tempdb..#EarnedTotal') is not null
drop table tempdb.#EarnedTotal;
if object_id('tempdb..#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS') is not null
drop table tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS;
create table #EarnedTotal (ID uniqueidentifier primary key, Total money)
insert into #EarnedTotal (ID, Total)
select FT.ID,
sum(FTLI.TRANSACTIONAMOUNT) as EarnedTotal
from dbo.FINANCIALTRANSACTION FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on FTLI.SOURCELINEITEMID = SOURCELI.ID
inner join dbo.FINANCIALTRANSACTION SOURCEFT on SOURCELI.FINANCIALTRANSACTIONID = SOURCEFT.ID
inner join dbo.SALESORDER on SOURCEFT.ID = SALESORDER.REVENUEID
where FT.TYPECODE = 0
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and SOURCEFT.TYPECODE = 5
and REVENUESPLIT_EXT.TYPECODE != 19 --not in (19,20)
and FTLI.DELETEDON is null
and FTLI.TYPECODE = 0
and SOURCELI.DELETEDON is null
and SOURCELI.TYPECODE = 0
and REVENUESPLIT_EXT.APPLICATIONCODE = 10
and SALESORDER.SALESMETHODTYPECODE = 3
and ((SOURCEFT.CALCULATEDDATE <= @TODATE and REVENUESPLIT_EXT.TYPECODE != 20) or (FTLI.POSTDATE < @TODATE and REVENUESPLIT_EXT.TYPECODE = 20))
--and (@INCLUDETAXES = 1 or REVENUESPLIT_EXT.TYPECODE <> 7)
group by FT.ID;
with ORDERPAYMENT_CTE as (
select
ORDERPAYMENTLINEITEM.SOURCELINEITEMID as ID,
isnull(sum(ORDERPAYMENTLINEITEM.BASEAMOUNT), 0) as BASEAMOUNT,
isnull(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 and
ORDERPAYMENTLINEITEM.DELETEDON is null
and (
@HASAPPUSERFILTER = 0
or SALESORDERPAYMENT.APPUSERID in (select ID from @APPUSERFILTER)
)
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
CONTRIBUTEDMEMBERSHIPLINEITEM.DELETEDON is null and
(
(
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
FINANCIALTRANSACTIONLINEITEM.ID,
REVENUESPLIT_EXT.TYPECODE,
REVENUESPLIT_EXT.APPLICATIONCODE,
case
when SALESORDER.ID is not null then 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10))
when FINANCIALTRANSACTION.TYPECODE = 1 then 'Pledge'
else REVENUESPLIT_EXT.APPLICATION
end REFERENCE,
case
when FINANCIALTRANSACTION.TYPECODE = 5 and ORDERITEMPAYMENT_CTE.ID is not null
then ORDERITEMPAYMENT_CTE.BASEAMOUNT
else
FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT - isnull([DISCOUNTS].AMOUNT,0)
end as AMOUNT,
case
when FINANCIALTRANSACTION.TYPECODE = 5 and ORDERITEMPAYMENT_CTE.ID is not null
then ORDERITEMPAYMENT_CTE.TRANSACTIONAMOUNT
else
FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
end as TRANSACTIONAMOUNT,
FINANCIALTRANSACTION.ID as FINANCIALTRANSACTIONID,
REVENUESPLIT_EXT.DESIGNATIONID,
cast(FINANCIALTRANSACTION.DATE as datetime) as TRANSACTIONDATE,
FINANCIALTRANSACTION.CONSTITUENTID,
FINANCIALTRANSACTION.TYPECODE as TRANSACTIONTYPECODE,
FINANCIALTRANSACTION.TYPE as TRANSACTIONTYPE,
SALESORDER.ID as SALESORDERID,
SALESORDER.SALESMETHODTYPECODE as SALESMETHODTYPECODE,
isnull(SALESORDER.STATUSCODE, 1) as ORDERSTATUSCODE,
REVENUESPLITORDER.PROGRAMID,
REVENUESPLITORDER.EVENTID,
REVENUESPLITORDER.EVENTLOCATIONID,
REVENUESPLITORDER.FEEID,
REVENUESPLITORDER.TAXID,
isnull(REVENUESPLITORDER.MEMBERSHIPLEVELID,REFUNDINFO.MEMBERSHIPLEVELID) as MEMBERSHIPLEVELID,
REFUNDINFO.MEMBERSHIPLEVELTERMID,
REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID,
REVENUESPLITORDER.RESOURCEID,
REVENUESPLITORDER.VOLUNTEERTYPEID
into tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUE
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.SALESORDER on SALESORDER.REVENUEID = FINANCIALTRANSACTION.ID and SALESORDER.STATUSCODE in (1,3,4)
left join dbo.REVENUESPLITORDER on REVENUESPLITORDER.ID = FINANCIALTRANSACTIONLINEITEM.ID
left join ORDERITEMPAYMENT_CTE on ORDERITEMPAYMENT_CTE.ID = FINANCIALTRANSACTIONLINEITEM.ID
left join (
select
sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as [AMOUNT],
LI.SOURCELINEITEMID
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
where LI.TYPECODE = 5 -- Discount
and FT.TYPECODE in (5, 99) -- Order, Deleted Revenue
group by LI.SOURCELINEITEMID
) as [DISCOUNTS] on
FINANCIALTRANSACTIONLINEITEM.ID = [DISCOUNTS].SOURCELINEITEMID
--Get membership info for transactions that have been deleted when refunded
--We are just using the refund tables to get the old membership info for the revenue split--we aren't working with refunds yet
left join (
select distinct
LI.SOURCELINEITEMID as REVENUESPLITID,
CREDITITEMMEMBERSHIP.MEMBERSHIPLEVELID,
CREDITITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID
from dbo.CREDITITEMMEMBERSHIP
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = CREDITITEMMEMBERSHIP.ID
inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
where FT.CALCULATEDDATE >= @FROMDATE --Can't assume refunds for these membership are between the report filter dates
and FT.TYPECODE = 23 -- Refund
) as REFUNDINFO on
REVENUESPLIT_EXT.ID = REFUNDINFO.REVENUESPLITID
where
FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT > 0
and (SALESORDER.SALESMETHODTYPECODE <> 3 or SALESORDER.SALESMETHODTYPECODE is null) --don't want to filter null values out
and FINANCIALTRANSACTION.TYPECODE not in (2,3,4,6,8,15,20)
and not (FINANCIALTRANSACTION.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 10) --omit sales order payments (Group sales payments are below)
and FINANCIALTRANSACTION.CALCULATEDDATE between @FROMDATE and @TODATE
and (
@HASAPPUSERFILTER = 0
or SALESORDER.APPUSERID in (select ID from @APPUSERFILTER)
or FINANCIALTRANSACTION.ADDEDBYID in (select ID from @CHANGEAGENTFILTER)
)
and (@INCLUDETAXES = 1 or REVENUESPLIT_EXT.TYPECODE <> 7)
and (@INCLUDEUNRECEIVEDREVENUE = 1 or FINANCIALTRANSACTION.TYPECODE <> 1) -- not in (1,4,6,8)) -- leaving this in case we change back to MG/DC claims being considered "committed"
union all --Group sales unearned and security deposit payments
select FTLI.ID, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)), FTLI.BASEAMOUNT as AMOUNT, FTLI.TRANSACTIONAMOUNT, FT.ID, null as DesignationID, cast(FT.DATE as datetime), FT.CONSTITUENTID, FT.TYPECODE as TRANSACTIONTYPECODE, FT.TYPE as TransactionType, SALESORDER.ID, isnull(SALESORDER.SALESMETHODTYPECODE,3) as SalesMethodTypeCode, isnull(SALESORDER.STATUSCODE,0) as OrderStatusCode, null as ProgramID, null as EventID, null as EventLocationID, null as FeeID, null as TaxID, null as MembershipLevelID, null as Membershipleveltermid, null as Merchandiseproductinstanceid, null as Resourceid, null as Volunteertypeid
from dbo.FINANCIALTRANSACTION FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
left join dbo.SALESORDERPAYMENT on FT.ID = SALESORDERPAYMENT.PAYMENTID
left join dbo.RESERVATIONSECURITYDEPOSITPAYMENT RSDP on FT.ID = RSDP.PAYMENTID
left join dbo.SALESORDER on isnull(SALESORDERPAYMENT.SALESORDERID, RSDP.RESERVATIONID) = SALESORDER.ID
where FT.TYPECODE = 0
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and (REVENUESPLIT_EXT.TYPECODE = 19 or (REVENUESPLIT_EXT.TYPECODE = 13 and @INCLUDESECURITYDEPOSITS = 1))
and REVENUESPLIT_EXT.APPLICATIONCODE = 10
and FTLI.DELETEDON is null
and FTLI.TYPECODE = 0
and not exists (select 1 from #EarnedTotal where ID = FT.ID)
and (
@HASAPPUSERFILTER = 0
or (
SALESORDER.APPUSERID in (select ID from @APPUSERFILTER)
and SALESORDERPAYMENT.APPUSERID in (select ID from @APPUSERFILTER)
)
or FT.ADDEDBYID in (select ID from @CHANGEAGENTFILTER)
)
union all --Earned group sales payments
select FTLI.ID, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)), FTLI.BASEAMOUNT as AMOUNT, FTLI.TRANSACTIONAMOUNT, FT.ID, null as DesignationID, cast(FT.DATE as datetime), FT.CONSTITUENTID, FT.TYPECODE as TRANSACTIONTYPECODE, FT.TYPE as TransactionType, SALESORDER.ID, SALESORDER.SALESMETHODTYPECODE, SALESORDER.STATUSCODE, REVENUESPLITORDER.PROGRAMID as ProgramID, REVENUESPLITORDER.EVENTID as EventID, REVENUESPLITORDER.EVENTLOCATIONID as EventLocationID, REVENUESPLITORDER.FEEID as FeeID, REVENUESPLITORDER.TAXID as TaxID, null as MembershipLevelID, null as membershipleveltermid, REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID as merchandiseproductinstanceid, REVENUESPLITORDER.RESOURCEID as resourceid, REVENUESPLITORDER.VOLUNTEERTYPEID as volunteertypeid
from dbo.FINANCIALTRANSACTION FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on FTLI.SOURCELINEITEMID = SOURCELI.ID
inner join dbo.FINANCIALTRANSACTION SOURCEFT on SOURCELI.FINANCIALTRANSACTIONID = SOURCEFT.ID
inner join dbo.REVENUESPLITORDER on SOURCELI.ID = REVENUESPLITORDER.ID
inner join dbo.SALESORDER on SOURCEFT.ID = SALESORDER.REVENUEID
inner join dbo.SALESORDERPAYMENT on FT.ID = SALESORDERPAYMENT.PAYMENTID
where FT.TYPECODE = 0
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and SOURCEFT.TYPECODE = 5
and FTLI.DELETEDON is null
and FTLI.TYPECODE = 0
and SOURCELI.DELETEDON is null
and SOURCELI.TYPECODE = 0
and REVENUESPLIT_EXT.APPLICATIONCODE = 10
and SALESORDER.SALESMETHODTYPECODE = 3
and REVENUESPLIT_EXT.TYPECODE != 20
and SOURCEFT.CALCULATEDDATE <= @TODATE
and (@INCLUDETAXES = 1 or REVENUESPLIT_EXT.TYPECODE <> 7)
and (
@HASAPPUSERFILTER = 0
or SALESORDERPAYMENT.APPUSERID in (select ID from @APPUSERFILTER)
or FT.ADDEDBYID in (select ID from @CHANGEAGENTFILTER)
)
union all --Unearned over
select null, 19, 10, 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)), FT.BASEAMOUNT - EARNED.Total, FT.TRANSACTIONAMOUNT - EARNED.Total, FT.ID, null as DesignationID, cast(FT.DATE as datetime), FT.CONSTITUENTID, FT.TYPECODE, FT.TYPE, SALESORDERPAYMENT.SALESORDERID, isnull(SALESORDER.SALESMETHODTYPECODE,3) as SalesMethodTypeCode, isnull(SALESORDER.STATUSCODE,0) as OrderStatusCode, null as ProgramID, null as EventID, null as EventLocationID, null as FeeID, null as TaxID, null as MembershipLevelID, null as membershipleveltermid, null as merchandiseproductinstanceid, null as resourceid, null as volunteertypeid
from dbo.FINANCIALTRANSACTION FT
inner join dbo.SALESORDERPAYMENT on FT.ID = SALESORDERPAYMENT.PAYMENTID
inner join #EarnedTotal EARNED on FT.ID = EARNED.ID
left join dbo.SALESORDER on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
where FT.TYPECODE = 0
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and SALESORDER.SALESMETHODTYPECODE = 3
and (
@HASAPPUSERFILTER = 0
or (
SALESORDER.APPUSERID in (select ID from @APPUSERFILTER)
and SALESORDERPAYMENT.APPUSERID in (select ID from @APPUSERFILTER)
)
or FT.ADDEDBYID in (select ID from @CHANGEAGENTFILTER)
)
union all --Group Sales refunds (unearned and security deposits)
select FTLI.ID, SOURCELI_EXT.TYPECODE, 10, 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)), 0 - FT.BASEAMOUNT, 0 - FT.TRANSACTIONAMOUNT, FT.ID, null as DESIGNATIONID, cast(FT.DATE as datetime), FT.CONSTITUENTID, FT.TYPECODE, FT.TYPE, SALESORDER.ID, isnull(SALESORDER.SALESMETHODTYPECODE,3) as SALESMETHODTYPECODE, isnull(SALESORDER.STATUSCODE,0) as ORDERSTATUSCODE, null as PROGRAMID, null as EVENTID, null as EVENTLOCATIONID, null as FEEID, null as TAXID, null as MEMBERSHIPLEVELID, null as MEMBERSHIPLEVELTERMID, null as MERCHANDISEPRODUCTINSTANCEID, null as RESOURCEID, null as VOLUNTEERTYPEID
from dbo.FINANCIALTRANSACTION FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on SOURCELI.ID = FTLI.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT SOURCELI_EXT on SOURCELI_EXT.ID = SOURCELI.ID
inner join dbo.CREDIT_EXT CREDIT on CREDIT.ID = FT.ID
inner join dbo.SALESORDER on SALESORDER.ID = CREDIT.SALESORDERID
where FT.TYPECODE = 23
and SOURCELI_EXT.APPLICATIONCODE = 10
and (SOURCELI_EXT.TYPECODE = 19 or (SOURCELI_EXT.TYPECODE = 13 and @INCLUDESECURITYDEPOSITS = 1)) -- security deposit and unearned
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and FTLI.DELETEDON is null
and FTLI.TYPECODE = 0
and SOURCELI.DELETEDON is null
and SOURCELI.TYPECODE = 0
and (
@HASAPPUSERFILTER = 0
or FT.APPUSERID in (select ID from @APPUSERFILTER)
or FT.ADDEDBYID in (select ID from @CHANGEAGENTFILTER)
)
union all --Earned overage kept
select FTLI.ID, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)), FTLI.BASEAMOUNT as AMOUNT, FTLI.TRANSACTIONAMOUNT, FT.ID, null as DesignationID, FTLI.POSTDATE, FT.CONSTITUENTID, SOURCEFT.TYPECODE as TRANSACTIONTYPECODE, SOURCEFT.TYPE as TransactionType, SALESORDER.ID, SALESORDER.SALESMETHODTYPECODE, SALESORDER.STATUSCODE, null as ProgramID, null as EventID, null as EventLocationID, null as FeeID, null as TaxID, null as MembershipLevelID, null as membershipleveltermid, null as merchandiseproductinstanceid, null as resourceid, null as volunteertypeid
from dbo.FINANCIALTRANSACTION FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on FTLI.SOURCELINEITEMID = SOURCELI.ID
inner join dbo.FINANCIALTRANSACTION SOURCEFT on SOURCELI.FINANCIALTRANSACTIONID = SOURCEFT.ID
inner join dbo.SALESORDER on SOURCEFT.ID = SALESORDER.REVENUEID
where FT.TYPECODE = 0
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and SOURCEFT.TYPECODE = 5
and FTLI.DELETEDON is null
and FTLI.TYPECODE = 0
and SOURCELI.DELETEDON is null
and SOURCELI.TYPECODE = 0
and REVENUESPLIT_EXT.APPLICATIONCODE = 10
and SALESORDER.SALESMETHODTYPECODE = 3
and REVENUESPLIT_EXT.TYPECODE = 20
and FTLI.POSTDATE <= @TODATE
and (
@HASAPPUSERFILTER = 0
or SALESORDER.APPUSERID in (select ID from @APPUSERFILTER)
or FT.ADDEDBYID in (select ID from @CHANGEAGENTFILTER)
);
select
ID,
TYPECODE,
APPLICATIONCODE,
REFERENCE,
AMOUNT,
TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONID,
DESIGNATIONID,
TRANSACTIONDATE,
CONSTITUENTID,
TRANSACTIONTYPECODE,
TRANSACTIONTYPE,
SALESORDERID,
SALESMETHODTYPECODE,
PROGRAMID,
EVENTID,
EVENTLOCATIONID,
FEEID,
TAXID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
MERCHANDISEPRODUCTINSTANCEID,
RESOURCEID,
VOLUNTEERTYPEID
into tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS
from ( --Splitting backoffice and sales conditions. Previous salesmethod logic in where clause had a dramatic performance impact
--Backoffice revenue
select * --These are the same columns as select above. Trying to have less clutter--make this easier to follow.
from tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUE
where (@INCLUDEBACKOFFICE = 1 and SALESORDERID is null)
union all
--Sales revenue
select ReportRecords.* --These are the same columns as select above. Trying to have less clutter--make this easier to follow.
from tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUE as ReportRecords
inner join @SalesMethodCodes as SalesCodes on ReportRecords.SALESMETHODTYPECODE = SalesCodes.SALESMETHODTYPECODE
where (
SALESORDERID is not null and
ORDERSTATUSCODE in (0,1,2,3,4,5) --kwb Added tentative (2) and cancelled (5)
)
) [REVENUE];
--Try to update membership info for installment payments
update tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS
set PROGRAMID = MEMBERSHIPPROGRAM.ID,
MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
from tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS REV
inner join INSTALLMENTPAYMENT on REV.ID = INSTALLMENTPAYMENT.PAYMENTID
inner join REVENUE_EXT as PLEDGE_REVENUE on PLEDGE_REVENUE.ID = INSTALLMENTPAYMENT.PLEDGEID
inner join FINANCIALTRANSACTIONLINEITEM on PLEDGE_REVENUE.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
inner join MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
inner join MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
where REV.PROGRAMID is null
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;
update tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS
set PROGRAMID = MEMBERSHIPPROGRAM.ID,
MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
from tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS REV
inner join RECURRINGGIFTINSTALLMENTPAYMENT on REV.FINANCIALTRANSACTIONID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
inner join RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
inner join FINANCIALTRANSACTION PLEDGE_XACTION on PLEDGE_XACTION.ID = RECURRINGGIFTINSTALLMENT.REVENUEID
inner join FINANCIALTRANSACTIONLINEITEM PLEDGE_FTLI on PLEDGE_FTLI.FINANCIALTRANSACTIONID = PLEDGE_XACTION.ID
inner join REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REV.ID
inner join MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = PLEDGE_FTLI.ID
inner join MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
inner join MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
where REV.PROGRAMID is null
and PLEDGE_FTLI.DELETEDON is null;
--Try to update the membership info for installment payment addons
update tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS
set PROGRAMID = MEMBERSHIPPROGRAM.ID,
MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
from tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS REV
join dbo.MEMBERSHIPADDON ON MEMBERSHIPADDON.REVENUESPLITID = REV.FINANCIALTRANSACTIONID
join MEMBERSHIPTRANSACTION on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
join MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
join MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
where REV.PROGRAMID is null;
create table tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS
(
ID uniqueidentifier,
TRANSACTIONDATE datetime,
CONSTITUENTID uniqueidentifier,
SALESORDERID uniqueidentifier
);
insert into tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS
select
FT.ID,
FT.DATE,
FT.CONSTITUENTID,
SO.ID SALESORDERID
from dbo.FINANCIALTRANSACTION FT
left join dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
left join dbo.SALESORDER SO on SO.ID = CREDIT_EXT.SALESORDERID
left join @SalesMethodCodes SMC on SO.SALESMETHODTYPECODE = SMC.SALESMETHODTYPECODE
where FT.TYPECODE = 23
and FT.CALCULATEDDATE between @FROMDATE and @TODATE
and (SO.SALESMETHODTYPECODE is null or SO.SALESMETHODTYPECODE <> 3) -- Group Sales refunds are in Unearned
and (
@HASAPPUSERFILTER = 0
or FT.APPUSERID in (select ID from @APPUSERFILTER) -- Refunds are the only transactions with non-null APPUSERID.
)
and (
SMC.SALESMETHODTYPECODE is not null
or (@SOURCEBACKOFFICE = 1 and SO.ID is null)
);
-- BE CAREFUL... IF YOU ADD AN OUTPUT FIELD HERE YOU MUST UPDATE THE SUBREPORT BECAUSE IT EXPECTS A CERTAIN NUMBER OF VALUES
-- ALSO UPDATE THIS FILE FOR THE SAME REASON: \Blackbaud\AppFx\Treasury\Catalog\Workcenter\PaymentsByPaymentMethod.DataList.xml
with RESERVATIONINFO_CTE as (
select
RESERVATION.ID,
dbo.UDA_BUILDLIST(GT.DESCRIPTION) GROUPTYPE,
cast(RESERVATION.ARRIVALDATE as date) ARRIVALDATE,
RESERVATION.NAME RESERVATIONNAME,
case when @USERGRANTEDRESERVATIONPAGE = 1 then 'http://www.blackbaud.com/GROUPSALESORDERID?GROUPSALESORDERID=' + CONVERT(nvarchar(36), RESERVATION.ID) else null end RESERVATIONLINK
from dbo.RESERVATION
left join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
left join dbo.GROUPSALESGROUPTYPECODE GT on GT.ID = ITINERARY.GROUPSALESGROUPTYPECODEID
group by RESERVATION.ID, RESERVATION.ARRIVALDATE, RESERVATION.NAME
), SALESORDERPAYMENTS_CTE as (
select
SOP.SALESORDERID,
dbo.UDA_BUILDLIST(distinct REVENUEPAYMENTMETHOD.PAYMENTMETHOD) PAYMENTMETHOD
from dbo.SALESORDERPAYMENT SOP
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SOP.PAYMENTID
where (
@HASAPPUSERFILTER = 0
or SOP.APPUSERID in (select ID from @APPUSERFILTER)
)
group by SOP.SALESORDERID
), REFUNDS_CTE as (
select
FT.ID CREDITID,
CP.REVENUESPLITID REFUNDEDITEMID,
SO.ID REFUNDEDORDERID,
dbo.UDA_BUILDLIST(distinct CP.PAYMENTMETHOD) PAYMENTMETHOD
from dbo.CREDITPAYMENT CP
inner join dbo.FINANCIALTRANSACTION FT on CP.CREDITID = FT.ID
left join dbo.SALESORDER SO on SO.REVENUEID = FT.PARENTID
where FT.TYPECODE = 23
group by FT.ID, CP.REVENUESPLITID, SO.ID
)
select
FILTERED.FINANCIALTRANSACTIONID,
FILTERED.SALESORDERID,
FILTERED.TRANSACTIONDATE,
case
when FILTERED.TRANSACTIONTYPECODE = 1 --in (1,3,4,6,8) --Pledge, Matching gift claim, Planned gift, Grant award, Donor challenge claim)
and @INCLUDEUNRECEIVEDREVENUE = 1
then ( --Get the balance per split designation
select
FTLI.BASEAMOUNT -
(coalesce((
select sum(ISP.AMOUNT)
from dbo.INSTALLMENTSPLITPAYMENT ISP
inner join dbo.INSTALLMENTSPLIT INS on INS.ID = ISP.INSTALLMENTSPLITID
where ISP.PLEDGEID = FILTERED.FINANCIALTRANSACTIONID and INS.DESIGNATIONID = FILTERED.DESIGNATIONID)
, 0) +
coalesce((
select sum(ISWO.AMOUNT)
from dbo.INSTALLMENTSPLITWRITEOFF ISWO
inner join dbo.INSTALLMENTSPLIT INS on INS.ID = ISWO.INSTALLMENTSPLITID
where INS.PLEDGEID = FILTERED.FINANCIALTRANSACTIONID and INS.DESIGNATIONID = FILTERED.DESIGNATIONID)
, 0))
from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
inner join dbo.REVENUESPLIT_EXT RSE on FTLI.ID = RSE.ID
where FTLI.TYPECODE = 0
and FTLI.DELETEDON is null
and FTLI.FINANCIALTRANSACTIONID = FILTERED.FINANCIALTRANSACTIONID
and RSE.DESIGNATIONID = FILTERED.DESIGNATIONID
)
else FILTERED.AMOUNT
end as AMOUNT,
FILTERED.TRANSACTIONTYPECODE,
FILTERED.TRANSACTIONTYPE,
FILTERED.APPLICATIONCODE,
FILTERED.TYPECODE REVENUETYPECODE,
CONSTITUENT.NAME CONSTITUENTNAME,
case when @USERGRANTEDCONSTITUENTPAGE = 1 then 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), CONSTITUENT.ID) else null end CONSTITUENTLINK,
PROGRAM.NAME PROGRAMNAME,
case when FILTERED.TYPECODE = 1 and FILTERED.APPLICATIONCODE = 1 then [EVENT].NAME
else null
end EVENTNAME,
TAX.NAME TAXNAME,
FEE.NAME FEENAME,
MERCHANDISEDEPARTMENT.NAME DEPARTMENTNAME,
case
when FILTERED.TYPECODE = 2 then MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME + isnull(' (' +
case MEMBERSHIPPROGRAM.PROGRAMTYPECODE
when 0 then cast(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(5)) + ' ' + MEMBERSHIPLEVELTERM.TERMLENGTH
when 1 then MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTION
when 2 then case when MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE = 0 then MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTION
else cast((select count(ID) from dbo.INSTALLMENT where REVENUEID = FILTERED.FINANCIALTRANSACTIONID) as nvarchar(5)) + ' ' + REVENUESCHEDULE.FREQUENCY
end
end
+ ')', '')
when FILTERED.TYPECODE = 18 then ADDONPROGRAM.NAME + ' - ' + ADDON.NAME
else null
end as MEMBERSHIPNAME,
DESIGNATION.NAME DESIGNATIONNAME,
EVENTLOCATION.NAME LOCATIONNAME,
[RESOURCE].NAME RESOURCENAME,
VOLUNTEERTYPE.NAME STAFFRESOURCENAME,
FILTERED.SALESMETHODTYPECODE,
null as CREDITITEMID,
case
when FILTERED.AMOUNT < 0 and @USERGRANTEDREFUNDPAGE = 1 then 'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36), REFUNDS_CTE.CREDITID)
when FILTERED.SALESMETHODTYPECODE is null and @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), FILTERED.FINANCIALTRANSACTIONID)
when FILTERED.SALESMETHODTYPECODE is not null and @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID)
else null
end TRANSACTIONLINK,
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 as SORTCONSTITUENTNAME,
case when FILTERED.TRANSACTIONTYPECODE = 1 then 'None'
when coalesce(FILTERED.SALESMETHODTYPECODE,3) <> 3 then SALESORDERPAYMENTS_CTE.PAYMENTMETHOD
else coalesce(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, REFUNDS_CTE.PAYMENTMETHOD)
end PAYMENTMETHOD,
RESINFO.GROUPTYPE,
RESINFO.ARRIVALDATE,
RESINFO.RESERVATIONNAME,
RESINFO.RESERVATIONLINK,
FILTERED.REFERENCE,
case when FILTERED.SALESORDERID is not null and @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID)
else case FILTERED.APPLICATIONCODE
when 0 then case
when FILTERED.TRANSACTIONTYPECODE <> 1 and @USERGRANTEDREVENUEAPPLICATIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEAPPLICATIONID?REVENUEAPPLICATIONID=' + CONVERT(nvarchar(36), FILTERED.ID)
when FILTERED.TRANSACTIONTYPECODE = 1 and @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), FILTERED.FINANCIALTRANSACTIONID)
else null end
when 1 then case when @USERGRANTEDREGISTRANTPAGE = 1 then 'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID=' + CONVERT(nvarchar(36), EVENTREGISTRANTPAYMENT.REGISTRANTID) else null end
when 2 then case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), CLAIMLI.FINANCIALTRANSACTIONID) else null end
when 3 then case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), RECURRINGGIFTINSTALLMENT.REVENUEID) else null end
when 4 then case when @USERGRANTEDREVENUEAPPLICATIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEAPPLICATIONID?REVENUEAPPLICATIONID=' + CONVERT(nvarchar(36), FILTERED.ID) else null end
when 5 then case when @USERGRANTEDMEMBERSHIPPAGE = 1 then 'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), MEMBERSHIPTRANSACTION.MEMBERSHIPID) else null end
when 7 then case
when @USERGRANTEDTRANSACTIONPAGE = 1 and CLAIMLI.FINANCIALTRANSACTIONID is not null then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), CLAIMLI.FINANCIALTRANSACTIONID)
when @USERGRANTEDREVENUEAPPLICATIONPAGE = 1 and CLAIMLI.FINANCIALTRANSACTIONID is null then 'http://www.blackbaud.com/REVENUEAPPLICATIONID?REVENUEAPPLICATIONID=' + CONVERT(nvarchar(36), FILTERED.ID)
else null end
when 9 then case when @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID) else null end
when 10 then case when @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID) else null end
when 11 then case when @USERGRANTEDREVENUEAPPLICATIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEAPPLICATIONID?REVENUEAPPLICATIONID=' + CONVERT(nvarchar(36), FILTERED.ID) else null end
when 13 then case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), CLAIMLI.FINANCIALTRANSACTIONID) else null end
when 16 then case when @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID) else null end
when 18 then case when @USERGRANTEDMEMBERSHIPPAGE = 1 then 'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), MEMBERSHIPADDON.MEMBERSHIPID) else null end
else null
end
end REFERENCELINK,
case
when FILTERED.APPLICATIONCODE = 2 then (select cast(DATE as datetime) from dbo.FINANCIALTRANSACTION where ID = CLAIMLI.FINANCIALTRANSACTIONID)
when FILTERED.TRANSACTIONTYPECODE = 1 then FILTERED.TRANSACTIONDATE
else null
end PLEDGEDATE
from tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS FILTERED
left join dbo.CONSTITUENT on CONSTITUENT.ID = FILTERED.CONSTITUENTID
left join dbo.PROGRAM on PROGRAM.ID = FILTERED.PROGRAMID
left join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = FILTERED.ID
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = isnull(MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,FILTERED.MEMBERSHIPLEVELID)
left join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = isnull(MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID,FILTERED.MEMBERSHIPLEVELTERMID)
left join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
left join dbo.MERCHANDISEPRODUCTINSTANCE on FILTERED.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
left join dbo.MERCHANDISEPRODUCT on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
left join dbo.MERCHANDISEDEPARTMENT on MERCHANDISEDEPARTMENT.ID = MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID
left join dbo.[RESOURCE] on FILTERED.RESOURCEID = [RESOURCE].ID
left join dbo.VOLUNTEERTYPE on FILTERED.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
left join dbo.FEE on FILTERED.FEEID = FEE.ID
left join dbo.TAX on FILTERED.TAXID = TAX.ID
left join dbo.EVENTLOCATION on FILTERED.EVENTLOCATIONID = EVENTLOCATION.ID
left join dbo.DESIGNATION on DESIGNATION.ID = FILTERED.DESIGNATIONID
left join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = FILTERED.ID
left join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
left join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
left join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = FILTERED.ID
left join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
left join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPADDON.MEMBERSHIPID
left join dbo.MEMBERSHIPPROGRAM ADDONPROGRAM on ADDONPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = FILTERED.FINANCIALTRANSACTIONID
left join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
left join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on PAYMENTLI.ID = FILTERED.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM CLAIMLI on CLAIMLI.ID = PAYMENTLI.SOURCELINEITEMID -- for Pledge payments (also Matching gift and Donor challenge, if those were included)
left join dbo.REVENUEPAYMENTMETHOD on (REVENUEPAYMENTMETHOD.REVENUEID = FILTERED.FINANCIALTRANSACTIONID and coalesce(FILTERED.SALESMETHODTYPECODE,3) = 3)
left join RESERVATIONINFO_CTE RESINFO on RESINFO.ID = FILTERED.SALESORDERID
left join SALESORDERPAYMENTS_CTE on (SALESORDERPAYMENTS_CTE.SALESORDERID = FILTERED.SALESORDERID and FILTERED.SALESMETHODTYPECODE <> 3)
left join REFUNDS_CTE on REFUNDS_CTE.CREDITID = FILTERED.FINANCIALTRANSACTIONID -- Group Sales refunds are the only ones in TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS.
left join dbo.REVENUESCHEDULE on FILTERED.FINANCIALTRANSACTIONID = REVENUESCHEDULE.ID
where FILTERED.AMOUNT <> 0
union all
-- Refunds (excluding membership, event reg, group sales)
select
FILTERED.ID CREDITID,
FILTERED.SALESORDERID,
FILTERED.TRANSACTIONDATE,
-(sum(REFUNDLI.BASEAMOUNT) - coalesce(sum(ITEMIZEDORDERDISCOUNTSREFUNDED.AMOUNT), sum(ORDERDISCOUNTS.AMOUNT), 0)) AMOUNT, -- REFUNDLI.BASEAMOUNT already has item-level discounts removed, if applicable. The "coalesce" resolves differences between new (itemized) and old (aggregate) refunds.
23 TRANSACTIONTYPECODE,
'Refund' TRANSACTIONTYPE,
null APPLICATIONCODE,
ORDERLI_EXT.TYPECODE REVENUETYPECODE,
CONSTITUENT.NAME CONSTITUENTNAME,
case when @USERGRANTEDCONSTITUENTPAGE = 1 then 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36), CONSTITUENT.ID) else null end CONSTITUENTLINK,
PROGRAM.NAME PROGRAMNAME,
null EVENTNAME,
TAX.NAME TAXNAME,
FEE.NAME FEENAME,
MERCHANDISEDEPARTMENT.NAME DEPARTMENTNAME,
null MEMBERSHIPNAME,
DESIGNATION.NAME DESIGNATIONNAME,
null LOCATIONNAME,
null RESOURCENAME,
null STAFFRESOURCENAME,
null SALESMETHODTYPECODE,
FILTERED.ID,
case when @USERGRANTEDREFUNDPAGE = 1 then 'http://www.blackbaud.com/CREDITID?CREDITID=' + convert(nvarchar(36), FILTERED.ID) else null end TRANSACTIONLINK,
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 as SORTCONSTITUENTNAME,
REFUNDS_CTE.PAYMENTMETHOD,
null GROUPTYPE,
null ARRIVALDATE,
null RESERVATIONNAME,
null RESERVATIONLINK,
'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)) as REFERENCE,
case when @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + convert(nvarchar(36), SALESORDER.ID) else null end REFERENCELINK,
null PLEDGEDATE
from tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS FILTERED
inner join REFUNDS_CTE on REFUNDS_CTE.REFUNDEDORDERID = FILTERED.SALESORDERID and REFUNDS_CTE.CREDITID = FILTERED.ID -- Only sales refunds here; Group Sales refunds are filtered out by the where clause (typecode 19)
inner join dbo.SALESORDER on SALESORDER.ID = REFUNDS_CTE.REFUNDEDORDERID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI on REFUNDLI.FINANCIALTRANSACTIONID = FILTERED.ID
inner join dbo.CREDITITEM_EXT REFUNDLI_EXT on REFUNDLI_EXT.ID = REFUNDLI.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM ORDERLI on ORDERLI.ID = REFUNDLI.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT ORDERLI_EXT on ORDERLI_EXT.ID = ORDERLI.ID
left join dbo.REVENUESPLITORDER on REVENUESPLITORDER.ID = ORDERLI.ID -- Left join because contributed revenue has no RSO
left join dbo.PROGRAM on PROGRAM.ID = REVENUESPLITORDER.PROGRAMID
left join dbo.MERCHANDISEPRODUCTINSTANCE on MERCHANDISEPRODUCTINSTANCE.ID = REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID
left join dbo.TAX on REVENUESPLITORDER.TAXID = TAX.ID
left join dbo.MERCHANDISEPRODUCT on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
left join dbo.MERCHANDISEDEPARTMENT on MERCHANDISEDEPARTMENT.ID = MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID
left join dbo.FEE on REVENUESPLITORDER.FEEID = FEE.ID
left join dbo.DESIGNATION on DESIGNATION.ID = ORDERLI_EXT.DESIGNATIONID
left join dbo.CONSTITUENT on CONSTITUENT.ID = FILTERED.CONSTITUENTID
outer apply (
select case ORDERLI_EXT.TYPECODE
when 5 then (select case when AMOUNTPAID < REFUNDLI.BASEAMOUNT then ORDERLEVELDISCOUNTSAPPLIED else 0 end from dbo.TICKET where ID = REFUNDLI_EXT.SALESORDERITEMIZEDITEMID)
when 16 then (select case when AMOUNTPAID < REFUNDLI.BASEAMOUNT then ORDERLEVELDISCOUNTSAPPLIED else 0 end from dbo.SALESORDERITEMMERCHANDISEUNIT where ID = REFUNDLI_EXT.SALESORDERITEMIZEDITEMID)
else 0
end AMOUNT
) ITEMIZEDORDERDISCOUNTSREFUNDED
outer apply (
select sum(AMOUNT) AMOUNT from dbo.SALESORDERITEMORDERDISCOUNTDETAIL where SALESORDERITEMID = REFUNDLI_EXT.SALESORDERITEMID
) ORDERDISCOUNTS
where
ORDERLI_EXT.TYPECODE not in (1,2,13,18) -- Event registration, membership, security deposit, membership add-on. No need to exclude unearned, because TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS does not contain group sales refunds.
and ORDERLI.DELETEDON is null
and (@INCLUDETAXES = 1 or ORDERLI_EXT.TYPECODE <> 7)
group by
FILTERED.ID,
FILTERED.SALESORDERID,
FILTERED.TRANSACTIONDATE,
ORDERLI_EXT.TYPECODE,
CONSTITUENT.NAME,
PROGRAM.NAME,
TAX.NAME,
FEE.NAME,
MERCHANDISEDEPARTMENT.NAME,
DESIGNATION.NAME,
CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.KEYNAMEPREFIX, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, CONSTITUENT.ISORGANIZATION,
REFUNDS_CTE.PAYMENTMETHOD,
SALESORDER.SEQUENCEID,
SALESORDER.ID
having sum(REFUNDLI.BASEAMOUNT) - coalesce(sum(ITEMIZEDORDERDISCOUNTSREFUNDED.AMOUNT), sum(ORDERDISCOUNTS.AMOUNT), 0) <> 0
union all
-- Refunds (membership)
select distinct
FILTERED.ID,
SALESORDER.ID SALESORDERID,
FILTERED.TRANSACTIONDATE,
-(LI.TRANSACTIONAMOUNT) as AMOUNT,
23 TRANSACTIONTYPECODE,
'Refund' TRANSACTIONTYPE,
null APPLICATIONCODE,
2 REVENUETYPECODE,
CONSTITUENT.NAME CONSTITUENTNAME,
case when @USERGRANTEDCONSTITUENTPAGE = 1 then 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), CONSTITUENT.ID) else null end CONSTITUENTLINK,
null PROGRAMNAME,
null EVENTNAME,
null TAXNAME,
null FEENAME,
null DEPARTMENTNAME,
MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME + isnull(' (' +
case MEMBERSHIPPROGRAM.PROGRAMTYPECODE
when 0 then cast(MEMBERSHIPLEVELTERM.TERMTIMELENGTH as nvarchar(5)) + ' ' + MEMBERSHIPLEVELTERM.TERMLENGTH
when 1 then MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTION
when 2 then case when MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE = 0 then MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTION
else cast((select count(ID) from dbo.INSTALLMENT where REVENUEID = FILTERED.ID) as nvarchar(5)) + ' ' + RS.FREQUENCY
end
end
+ ')', '') as MEMBERSHIPNAME,
null DESIGNATIONNAME,
null LOCATIONNAME,
null RESOURCENAME,
null STAFFRESOURCENAME,
null SALESMETHODTYPECODE,
LI.ID CREDITITEMID,
case when @USERGRANTEDREFUNDPAGE = 1 then 'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36), FILTERED.ID) else null end TRANSACTIONLINK,
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 as SORTCONSTITUENTNAME,
REFUNDS_CTE.PAYMENTMETHOD,
null as GROUPTYPE,
null as ARRIVALDATE,
null as RESERVATIONNAME,
null as RESERVATIONLINK,
case when SALESORDER.ID is null then 'Membership' else 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)) end REFERENCE,
case when SALESORDER.ID is null and @USERGRANTEDMEMBERSHIPPAGE = 1 then 'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), CREDITITEMMEMBERSHIP.MEMBERSHIPID)
when SALESORDER.ID is not null and @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID)
else null
end REFERENCELINK,
null PLEDGEDATE
from tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS FILTERED
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FILTERED.ID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = LI.ID
inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = CREDITITEMMEMBERSHIP.MEMBERSHIPPROGRAMID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = CREDITITEMMEMBERSHIP.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPLEVELTERM.ID = CREDITITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID
inner join REFUNDS_CTE on REFUNDS_CTE.CREDITID = FILTERED.ID
left join dbo.CONSTITUENT on CONSTITUENT.ID = FILTERED.CONSTITUENTID
left join dbo.SALESORDER on SALESORDER.ID = FILTERED.SALESORDERID
left join dbo.REVENUESCHEDULE as RS on FILTERED.ID = RS.ID
where EXT.TYPECODE = 1 -- Membership
union all
-- Refunds (event registration)
select distinct
FILTERED.ID,
SALESORDER.ID SALESORDERID,
FILTERED.TRANSACTIONDATE,
-LI.BASEAMOUNT as AMOUNT,
23 TRANSACTIONTYPECODE,
'Refund' TRANSACTIONTYPE,
null APPLICATIONCODE,
1 REVENUETYPECODE,
CONSTITUENT.NAME CONSTITUENTNAME,
case when @USERGRANTEDCONSTITUENTPAGE = 1 then 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), CONSTITUENT.ID) else null end CONSTITUENTLINK,
null PROGRAMNAME,
[EVENT].NAME EVENTNAME,
null TAXNAME,
null FEENAME,
null DEPARTMENTNAME,
null MEMBERSHIPNAME,
null DESIGNATIONNAME,
null LOCATIONNAME,
null RESOURCENAME,
null STAFFRESOURCENAME,
null SALESMETHODTYPECODE,
LI.ID CREDITITEMID,
case when @USERGRANTEDREFUNDPAGE = 1 then 'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36), FILTERED.ID) else null end TRANSACTIONLINK,
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 as SORTCONSTITUENTNAME,
REFUNDS_CTE.PAYMENTMETHOD,
null as GROUPTYPE,
null as ARRIVALDATE,
null as RESERVATIONNAME,
null as RESERVATIONLINK,
case when SALESORDER.ID is null then 'Event registration' else 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)) end REFERENCE,
case when SALESORDER.ID is null and @USERGRANTEDREGISTRANTPAGE = 1 then 'http://www.blackbaud.com/REGISTRANTID?REGISTRANTID=' + CONVERT(nvarchar(36), REGISTRANT.ID)
when SALESORDER.ID is not null and @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID)
else null
end REFERENCELINK,
null PLEDGEDATE
from tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS FILTERED
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FILTERED.ID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join REFUNDS_CTE on REFUNDS_CTE.CREDITID = FILTERED.ID
inner join dbo.CREDITITEMEVENTREGISTRATION on CREDITITEMEVENTREGISTRATION.ID = LI.ID
inner join dbo.REGISTRANT on REGISTRANT.ID = CREDITITEMEVENTREGISTRATION.REGISTRANTID
inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
left join dbo.SALESORDER on SALESORDER.ID = FILTERED.SALESORDERID
left join dbo.CONSTITUENT on CONSTITUENT.ID = FILTERED.CONSTITUENTID
union all
-- Refunds (membership add-ons)
select distinct
FILTERED.ID,
SALESORDER.ID SALESORDERID,
FILTERED.TRANSACTIONDATE,
-((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as AMOUNT,
23 TRANSACTIONTYPECODE,
'Refund' TRANSACTIONTYPE,
null APPLICATIONCODE,
18 REVENUETYPECODE,
CONSTITUENT.NAME CONSTITUENTNAME,
case when @USERGRANTEDCONSTITUENTPAGE = 1 then 'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), CONSTITUENT.ID) else null end CONSTITUENTLINK,
null PROGRAMNAME,
null EVENTNAME,
null TAXNAME,
null FEENAME,
null DEPARTMENTNAME,
MEMBERSHIPPROGRAM.NAME + ' - ' + ADDON.NAME as MEMBERSHIPNAME,
null DESIGNATIONNAME,
null LOCATIONNAME,
null RESOURCENAME,
null STAFFRESOURCENAME,
null SALESMETHODTYPECODE,
LI.ID CREDITITEMID,
case when @USERGRANTEDREFUNDPAGE = 1 then 'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36), FILTERED.ID) else null end TRANSACTIONLINK,
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 as SORTCONSTITUENTNAME,
REFUNDS_CTE.PAYMENTMETHOD,
null as GROUPTYPE,
null as ARRIVALDATE,
null as RESERVATIONNAME,
null as RESERVATIONLINK,
case when SALESORDER.ID is null then 'Membership add-on' else 'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(10)) end REFERENCE,
case when SALESORDER.ID is null and @USERGRANTEDMEMBERSHIPPAGE = 1 then 'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), MEMBERSHIPADDON.MEMBERSHIPID)
when SALESORDER.ID is not null and @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), FILTERED.SALESORDERID)
else null
end REFERENCELINK,
null PLEDGEDATE
from tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS FILTERED
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FILTERED.ID
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join REFUNDS_CTE on REFUNDS_CTE.CREDITID = FILTERED.ID
inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = LI.SOURCELINEITEMID
inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPADDON.MEMBERSHIPID
inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
left join dbo.SALESORDER on SALESORDER.ID = FILTERED.SALESORDERID
left join dbo.CONSTITUENT on CONSTITUENT.ID = FILTERED.CONSTITUENTID
where EXT.TYPECODE = 16 -- Membership add-on
drop table tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUE;
drop table tempdb.#TEMP_DATA_TOTALREVENUEREPORT_REVENUESPLITIDS;
drop table tempdb.#TMP_DATA_TOTALREVENUEREPORT_NONGSREFUNDS;
drop table #EarnedTotal;
return 0;