USP_DATALIST_REFUNDABLEITEMS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @SALESORDERID | uniqueidentifier | IN | |
| @DATETYPECODE | tinyint | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @APPUSERID | uniqueidentifier | IN | |
| @ITEMTYPECODE | tinyint | IN | |
| @PAYMENTMETHODTYPECODE | tinyint | IN | |
| @SALESMETHODID | uniqueidentifier | IN | |
| @LOWAMOUNT | money | IN | |
| @HIGHAMOUNT | money | IN | |
| @MAXROWS | int | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_REFUNDABLEITEMS
(
@CONSTITUENTID uniqueidentifier = null,
@SALESORDERID uniqueidentifier = null,
@DATETYPECODE tinyint = 0,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@APPUSERID uniqueidentifier = null,
@ITEMTYPECODE tinyint = null,
@PAYMENTMETHODTYPECODE tinyint = null,
@SALESMETHODID uniqueidentifier = null,
@LOWAMOUNT money = null,
@HIGHAMOUNT money = null,
@MAXROWS int = 500
)
as
set nocount on;
declare @RESULTS table (CONTEXTID uniqueidentifier, CONTEXTTYPE tinyint, ITEMDESCRIPTION nvarchar(255), CONSTITUENT nvarchar(255), LATESTDATE datetime, [DATE] nvarchar(max), [USER] nvarchar(255), SALESMETHOD nvarchar(100), PAYMENTMETHOD nvarchar(max), AMOUNT money);
declare @DATE date = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());
if @DATETYPECODE = 0 begin -- Today
set @STARTDATE = @DATE;
set @ENDDATE = @DATE;
end
else if @DATETYPECODE = 1 begin -- Yesterday
set @STARTDATE = dateadd(day, -1, @DATE);
set @ENDDATE = @STARTDATE;
end
else if @DATETYPECODE = 2 begin -- This week
set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 3 begin -- This month
set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 4 begin -- This quarter
set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 5 begin -- This year
set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 6 begin -- Last week
set @STARTDATE = dbo.UFN_DATE_LASTWEEK_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTWEEK_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 7 begin -- Last month
set @STARTDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 8 begin -- Last quarter
set @STARTDATE = dbo.UFN_DATE_LASTQUARTER_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTQUARTER_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 9 begin -- Last year
set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@DATE, 0);
end
else if @DATETYPECODE = 10 begin -- All dates
set @STARTDATE = null;
set @ENDDATE = null;
end
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
declare @SALESMETHODTYPECODE tinyint;
select @SALESMETHODTYPECODE = TYPECODE from dbo.SALESMETHOD where ID = @SALESMETHODID;
declare @PAYMENTMETHODTYPECODETRANSLATION tinyint =
case @PAYMENTMETHODTYPECODE
when 0 then null -- All
when 1 then 0 -- Cash
when 2 then 1 -- Check
when 3 then 2 -- Credit card
when 4 then 3 -- Debit card
when 5 then 10 -- Other
end;
-- Orders
if (@ITEMTYPECODE in (0,3)) begin
insert into @RESULTS
select top (@MAXROWS)
SALESORDER.ID as CONTEXTID,
0 as CONTEXTTYPE,
cast(SALESORDER.SEQUENCEID as nvarchar) as ITEMDESCRIPTION,
NF.NAME as CONSTITUENT,
cast(SALESORDER.TRANSACTIONDATE as date) as LATESTDATE,
cast(SALESORDER.TRANSACTIONDATE as date) as [DATE],
isnull(APPUSER.DISPLAYNAME, '') as [USER],
SALESORDER.SALESMETHODTYPE as SALESMETHOD,
dbo.UFN_SALESORDER_GETPAYMENTMETHODLIST(SALESORDER.ID) as PAYMENTMETHOD,
TOTALS.TOTAL
from
dbo.SALESORDER
left outer join
dbo.APPUSER on APPUSER.ID = SALESORDER.APPUSERID
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) as NF
outer apply
dbo.UFN_SALESORDER_TOTALS(SALESORDER.ID) as TOTALS
where
SALESORDER.STATUSCODE = 1 -- Complete orders
and SALESORDER.SALESMETHODTYPECODE <> 3 -- Exclude Group Sales
and exists (
-- Has at least one item that hasn't been fully refunded
-- Exclude orders with only unavailable tickets -- WI 180624
select *
from dbo.SALESORDERITEM
left outer join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEM.ID
where
SALESORDERITEM.SALESORDERID = SALESORDER.ID
and (
TICKET.ID is null
or (
TICKET.APPLIEDTOMEMBERSHIP = 0
and TICKET.ISREFUNDED = 0
)
)
and SALESORDERITEM.TYPECODE in (0, 1, 2, 3, 6, 14, 16) -- Ticket, Membership, Donation, Fee, Event Registration, Merchandise, Membership add-on
and SALESORDERITEM.QUANTITY > coalesce(
(
select sum(LI.QUANTITY)
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
where
FT.TYPECODE = 23 -- Refunds
and EXT.SALESORDERITEMID = SALESORDERITEM.ID
)
, 0)
)
and (@CONSTITUENTID is null or SALESORDER.CONSTITUENTID = @CONSTITUENTID)
and (@SALESORDERID is null or SALESORDER.ID = @SALESORDERID)
and (@STARTDATE is null or SALESORDER.TRANSACTIONDATE >= @STARTDATE)
and (@ENDDATE is null or SALESORDER.TRANSACTIONDATE <= @ENDDATE)
and (@APPUSERID is null or SALESORDER.APPUSERID = @APPUSERID)
and (
@PAYMENTMETHODTYPECODETRANSLATION is null
or exists (
select *
from dbo.SALESORDERPAYMENT
inner join dbo.REVENUEPAYMENTMETHOD on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
where
SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHODTYPECODETRANSLATION
)
)
and (@SALESMETHODID is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
and (@LOWAMOUNT is null or TOTALS.TOTAL >= @LOWAMOUNT)
and (@HIGHAMOUNT is null or TOTALS.TOTAL <= @HIGHAMOUNT)
order by
SALESORDER.TRANSACTIONDATE desc, SALESORDER.SEQUENCEID
option (recompile);
end
-- Event registrations
if (@ITEMTYPECODE in (0, 1)) begin
;with REGISTRANTREFUNDS_CTE as (
select
CREDITITEMEVENTREGISTRATION.REGISTRANTID,
sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as TOTAL
from
dbo.CREDITITEMEVENTREGISTRATION
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = CREDITITEMEVENTREGISTRATION.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
where
FT.TYPECODE = 23 -- Refund
group by
CREDITITEMEVENTREGISTRATION.REGISTRANTID
),
REGISTRANTREVENUE_CTE as (
select
FT.ID,
EVENTREGISTRANTPAYMENT.REGISTRANTID,
EVENTREGISTRANTPAYMENT.AMOUNT,
cast(FT.DATE as datetime) as DATE,
FT.CALCULATEDDATE,
FT.CONSTITUENTID,
FT.TYPECODE
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = LI.ID -- Don't know why payment ID is the line item
where
FT.DELETEDON is null
and LI.DELETEDON is null
and LI.TYPECODE <> 1 -- Reversal
),
REGISTRANTTOTALS_CTE as (
select
REGISTRANTREVENUE_CTE.REGISTRANTID,
sum(REGISTRANTREVENUE_CTE.AMOUNT) as TOTALPAID,
isnull(REGISTRANTREFUNDS_CTE.TOTAL, 0) as TOTALREFUNDED,
max(REGISTRANTREVENUE_CTE.DATE) as MAXPAYMENTDATE
from
REGISTRANTREVENUE_CTE
left outer join
REGISTRANTREFUNDS_CTE on REGISTRANTREFUNDS_CTE.REGISTRANTID = REGISTRANTREVENUE_CTE.REGISTRANTID
group by
REGISTRANTREVENUE_CTE.REGISTRANTID,
REGISTRANTREFUNDS_CTE.TOTAL
)
insert into @RESULTS
select distinct top (@MAXROWS)
REGISTRANTREVENUE_CTE.REGISTRANTID,
2 as CONTEXTTYPE,
EVENT.NAME as ITEMDESCRIPTION,
CONSTITUENTNAMEFORMAT.NAME as CONSTITUENT,
REGISTRANTTOTALS_CTE.MAXPAYMENTDATE as LATESTDATE,
LISTS.DATELIST as [DATE],
LISTS.APPUSERLIST as [USER],
LISTS.SALESMETHODLIST as SALESMETHOD,
LISTS.PAYMENTMETHODLIST as PAYMENTMETHOD,
REGISTRANTTOTALS_CTE.TOTALPAID - REGISTRANTTOTALS_CTE.TOTALREFUNDED as AMOUNT
from
REGISTRANTREVENUE_CTE
inner join
REGISTRANTTOTALS_CTE on REGISTRANTTOTALS_CTE.REGISTRANTID = REGISTRANTREVENUE_CTE.REGISTRANTID
inner join
dbo.REGISTRANT on REGISTRANT.ID = REGISTRANTREVENUE_CTE.REGISTRANTID
inner join
dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANTREVENUE_CTE.CONSTITUENTID) as CONSTITUENTNAMEFORMAT
outer apply
dbo.UFN_REGISTRANT_GETDATEANDPAYMENTMETHODLISTS(REGISTRANT.ID) as LISTS
where
(@STARTDATE is null or REGISTRANTREVENUE_CTE.CALCULATEDDATE >= @STARTDATE)
and (@ENDDATE is null or REGISTRANTREVENUE_CTE.CALCULATEDDATE <= @ENDDATE)
and REGISTRANTTOTALS_CTE.TOTALPAID > REGISTRANTTOTALS_CTE.TOTALREFUNDED
and (@CONSTITUENTID is null or REGISTRANTREVENUE_CTE.CONSTITUENTID = @CONSTITUENTID)
and (
(
REGISTRANTREVENUE_CTE.TYPECODE = 0 -- Payment
and (@SALESMETHODID is null or @SALESMETHODID = '3D858097-FD54-4BB8-9276-A4028E9B400C') -- Static guid for Back office
and exists (
select *
from dbo.REVENUEPAYMENTMETHOD
where
REVENUEPAYMENTMETHOD.REVENUEID = REGISTRANTREVENUE_CTE.ID
and (
(@PAYMENTMETHODTYPECODETRANSLATION is null and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0,1,2,3,10)) -- Cash, Check, Credit card, Direct debit, Other
or REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHODTYPECODETRANSLATION
)
)
)
or (
REGISTRANTREVENUE_CTE.TYPECODE = 5 -- Order
and exists (
select *
from dbo.SALESORDER
where
SALESORDER.REVENUEID = REGISTRANTREVENUE_CTE.ID
and (@SALESORDERID is null or SALESORDER.ID = @SALESORDERID)
and (@SALESMETHODID is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
and (@APPUSERID is null or SALESORDER.APPUSERID = @APPUSERID)
and (
@PAYMENTMETHODTYPECODETRANSLATION is null
or exists (
select *
from dbo.SALESORDERPAYMENT
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
where
SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHODTYPECODETRANSLATION
)
)
)
)
)
and (@LOWAMOUNT is null or (REGISTRANTTOTALS_CTE.TOTALPAID - REGISTRANTTOTALS_CTE.TOTALREFUNDED) >= @LOWAMOUNT)
and (@HIGHAMOUNT is null or (REGISTRANTTOTALS_CTE.TOTALPAID - REGISTRANTTOTALS_CTE.TOTALREFUNDED) <= @HIGHAMOUNT)
order by
LATESTDATE desc, ITEMDESCRIPTION
option (recompile);
end
-- Memberships
if (@ITEMTYPECODE in (0,2)) begin
;with REFUNDEDTICKETITEMS_CTE as (
select
LI.ID,
LI.SOURCELINEITEMID,
EXT.SALESORDERITEMID
from dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = LI.FINANCIALTRANSACTIONID
where
FINANCIALTRANSACTION.TYPECODE = 23 -- Refund
and EXT.TYPECODE = 0 -- Ticket
),
ADDONS_CTE as (
select
MEMBERSHIPTRANSACTIONID,
sum(ORGANIZATIONPURCHASEPRICE * QUANTITY) as AMOUNT
from
dbo.MEMBERSHIPADDON
group by
MEMBERSHIPTRANSACTIONID
)
insert into @RESULTS
select top (@MAXROWS)
MEMBERSHIPTRANSACTION.MEMBERSHIPID as CONTEXTID,
1 as CONTEXTTYPE,
MEMBERSHIPLEVEL.NAME + ' - ' + MEMBERSHIPTRANSACTION.ACTION as ITEMDESCRIPTION,
CONSTITUENTNAMEFORMAT.NAME as CONSTITUENT,
FT.CALCULATEDDATE as LATESTDATE,
FT.CALCULATEDDATE as [DATE],
coalesce((select APPUSER.DISPLAYNAME from dbo.APPUSER where ID = SALESORDER.APPUSERID), '') as [USER],
coalesce(SALESORDER.SALESMETHODTYPE, 'Back office') as SALESMETHOD,
case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
when 10 then -- Other
dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID)
when 9 then -- None (The case when the membership is on an order transaction)
dbo.UFN_SALESORDER_GETPAYMENTMETHODLIST(SALESORDER.ID)
else
REVENUEPAYMENTMETHOD.PAYMENTMETHOD
end as PAYMENTMETHOD,
LI.BASEAMOUNT
- (
coalesce(
(
select
sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
from
dbo.FINANCIALTRANSACTION
inner join
dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join
dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
where
FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = LI.ID
and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 5 -- Discount
and FINANCIALTRANSACTION.TYPECODE in (5, 99) -- Order, Deleted Revenue
and CREDITITEM_EXT.TYPECODE = 1 -- Membership
), 0)
)
+ coalesce([ADDONS].[AMOUNT], 0)
+ coalesce(CONTRIBUTEDREVENUE.AMOUNT, 0)
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = LI.ID
inner join
dbo.REVENUEPAYMENTMETHOD on FT.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join
dbo.UFN_MEMBERSHIP_LATESTTRANSACTIONASOF_BULK(null) as MEMBERSHIPTRANSACTION on LI.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
inner join
dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
inner join
dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
inner join
dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
left outer join
dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
left outer join
REFUNDEDTICKETITEMS_CTE as REFUNDEDITEM on REFUNDEDITEM.SOURCELINEITEMID = LI.ID
left outer join
dbo.SALESORDERITEMMEMBERSHIP on MEMBERSHIPTRANSACTION.ID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID
left outer join
REFUNDEDTICKETITEMS_CTE as REFUNDEDORDERITEM on SALESORDERITEMMEMBERSHIP.ID = REFUNDEDORDERITEM.SALESORDERITEMID
left outer join
dbo.SALESORDERITEM on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
left outer join
dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
left join
ADDONS_CTE as ADDONS on ADDONS.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(FT.CONSTITUENTID) as CONSTITUENTNAMEFORMAT
outer apply
(
select
sum(DONATIONLI.ORGAMOUNT) as AMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPLI
inner join dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLI on DONATIONLI.SOURCELINEITEMID = MEMBERSHIPLI.ID
inner join dbo.REVENUESPLIT_EXT as DONATIONEXT on DONATIONEXT.ID = DONATIONLI.ID
where MEMBERSHIPLI.ID = LI.ID
and DONATIONEXT.TYPECODE = 0
and DONATIONEXT.APPLICATIONCODE = 0
group by MEMBERSHIPLI.ID
) CONTRIBUTEDREVENUE
where
(@SALESORDERID is null or SALESORDER.ID = @SALESORDERID)
and (@APPUSERID is null or SALESORDER.APPUSERID = @APPUSERID)
and REFUNDEDITEM.ID is null
and REFUNDEDORDERITEM.ID is null
and (@CONSTITUENTID is null or FT.CONSTITUENTID = @CONSTITUENTID)
and (
(
FT.TYPECODE = 0 -- Payment
and (
(@PAYMENTMETHODTYPECODETRANSLATION is null and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE in (0,1,2,3,10)) -- Cash, Check, Credit card, Direct Debit Other
or REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHODTYPECODETRANSLATION
)
)
or (
FT.TYPECODE = 5 -- Order
and (
@PAYMENTMETHODTYPECODETRANSLATION is null
or exists (
select *
from dbo.SALESORDERPAYMENT
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
where
SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = @PAYMENTMETHODTYPECODETRANSLATION
)
)
)
)
and (@STARTDATE is null or FT.CALCULATEDDATE >= @STARTDATE)
and (@ENDDATE is null or FT.CALCULATEDDATE <= @ENDDATE)
and (
@SALESMETHODID is null
or (
@SALESMETHODID = '3D858097-FD54-4BB8-9276-A4028E9B400C' -- Static guid for Back office
and SALESORDER.ID is null
)
or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE
)
and (@LOWAMOUNT is null or LI.BASEAMOUNT >= @LOWAMOUNT)
and (@HIGHAMOUNT is null or LI.BASEAMOUNT <= @HIGHAMOUNT)
and MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 -- Annual
-- Bug #191252
and MEMBERSHIP.STATUSCODE = 0 -- Active
and MEMBERSHIP.EXPIRATIONDATE >= @DATE
and FT.DELETEDON is null
and LI.DELETEDON is null
and LI.TYPECODE <> 1 -- Reversal
order by
FT.DATE desc, MEMBERSHIPLEVEL.NAME, MEMBERSHIPTRANSACTION.ACTION
option (recompile);
end
select top(@MAXROWS)
CONTEXTID,
CONTEXTTYPE,
ITEMDESCRIPTION,
CONSTITUENT,
[DATE],
[USER],
SALESMETHOD,
PAYMENTMETHOD,
AMOUNT
from @RESULTS RESULTS
order by LATESTDATE desc, ITEMDESCRIPTION;