USP_CREDIT_ADDREFUND
Creates a refund.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CONTEXTID | uniqueidentifier | IN | |
| @COMMENT | nvarchar(500) | IN | |
| @ITEMS | xml | IN | |
| @CREDITREASONCODEID | uniqueidentifier | IN | |
| @REFUNDAMOUNT | money | IN | |
| @TRANSACTIONDATE | datetime | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @REFUNDMETHODS | xml | IN | |
| @TAXES | xml | IN | |
| @TOTAL | money | IN | |
| @ORDERDISCOUNTS | money | IN | |
| @CONTEXTTYPE | tinyint | IN | |
| @TRANSACTIONID | uniqueidentifier | IN | |
| @TIMESTAMP | bigint | IN | |
| @ADDRESSID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_CREDIT_ADDREFUND
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CONTEXTID uniqueidentifier,
@COMMENT nvarchar(500) = null,
@ITEMS xml = null,
@CREDITREASONCODEID uniqueidentifier = null,
@REFUNDAMOUNT money = null,
@TRANSACTIONDATE datetime = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@REFUNDMETHODS xml = null,
@TAXES xml = null,
@TOTAL money = null,
@ORDERDISCOUNTS money = null,
@CONTEXTTYPE tinyint = null, -- 0 is order, 1 is membership, 2 is event registration
@TRANSACTIONID uniqueidentifier = null,
@TIMESTAMP bigint = null,
@ADDRESSID uniqueidentifier = null
)
as
set nocount on;
if @ID is null
set @ID = newid()
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @POSTSTATUSCODE tinyint
--Make sure nobody else has done a refund since the form was opened.
if @CONTEXTTYPE = 0 begin --it's an order
if @TIMESTAMP <> coalesce((select [TSLONG] from dbo.[SALESORDER] where [ID] = @TRANSACTIONID), -1) begin
if @TRANSACTIONID is null or @TRANSACTIONID = '00000000-0000-0000-0000-000000000000'
raiserror('ERR_TRANSACTIONID_ORDERNOTSELECTED', 13, 1);
else
raiserror('ERR_CREDITITEMS_CONFLICTINGREFUND', 13, 1);
return 1;
end
else begin
update dbo.[SALESORDER] set [DATECHANGED] = @CURRENTDATE, [CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @TRANSACTIONID;
select @POSTSTATUSCODE = FT.POSTSTATUSCODE
from dbo.SALESORDER SO
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = SO.REVENUEID
where SO.ID = @TRANSACTIONID;
end
end
-- it's a membership refund
else if @CONTEXTTYPE = 1 begin
if @TIMESTAMP <> coalesce((select [TSLONG] from dbo.[MEMBERSHIPTRANSACTION] where [ID] = @TRANSACTIONID), -1) begin
if @TRANSACTIONID is null or @TRANSACTIONID = '00000000-0000-0000-0000-000000000000'
raiserror('ERR_TRANSACTIONID_MEMBERSHIPNOTSELECTED', 13, 1);
else
raiserror('ERR_ITEMS_ORDERREFUND_HASNEWERMEMBERSHIPTRANSACTIONS', 13, 1);
return 1;
end
else begin
update dbo.[MEMBERSHIPTRANSACTION] set [DATECHANGED] = getdate(), [CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @TRANSACTIONID
select @POSTSTATUSCODE = LI.POSTSTATUSCODE
from dbo.MEMBERSHIPTRANSACTION MT
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = MT.REVENUESPLITID
where MT.ID = @TRANSACTIONID;
end
end
-- it's an event registraion refund
else if @CONTEXTTYPE = 2 begin
if @TIMESTAMP <> coalesce((select [TSLONG] from dbo.[REGISTRANT] where [ID] = @TRANSACTIONID), -1) begin
if @TRANSACTIONID is null or @TRANSACTIONID = '00000000-0000-0000-0000-000000000000'
raiserror('ERR_TRANSACTIONID_EVENTREGISTRATIONNOTSELECTED', 13, 1);
else
raiserror('ERR_CREDITITEMS_CONFLICTINGEVENTREGISTRAIONREFUND', 13, 1);
return 1;
end
else begin
update dbo.[REGISTRANT] set [DATECHANGED] = getdate(), [CHANGEDBYID] = @CHANGEAGENTID
where [ID] = @TRANSACTIONID
select @POSTSTATUSCODE = LI.POSTSTATUSCODE
from dbo.EVENTREGISTRANTPAYMENT RP
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = RP.PAYMENTID
where RP.REGISTRANTID = @TRANSACTIONID;
end
end
--A special note about REVENUESPLITID:
--This is the FINANCIALTRANSACITONLINEITEM.SOURCELINEITEMID for a given refund ftli.
--Previously we only stored this for back office memberships and back office event registrations,
--however there is nothing preventing us from storing this info in the future for items that we can.
--Perhaps someday a dev will magically even find time (given time even?) to update client data
--so this is stored for all items. For now though, it's only for back office membership,
--back office event registrations, and all membership addons.
--Please update this comment if you change this.
-- Get refund items from input parameter @ITEMS
declare @CREDITITEMS table(
[ID] uniqueidentifier,
[SALESORDERITEMID] uniqueidentifier,
[QUANTITYREFUNDING] int,
[QUANTITY] int,
[PRICE] money,
[FEES] money,
[DISCOUNTS] money,
[TYPECODE] int,
[DESCRIPTION] nvarchar(255),
[REFUNDINGTOTAL] money,
[GROUPID] uniqueidentifier,
[GROUPTYPECODE] tinyint,
[ITEMID] uniqueidentifier,
[REVENUESPLITID] uniqueidentifier
);
insert into @CREDITITEMS
select
newid(),
case when T.item.value('(SALESORDERITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
null
else
T.item.value('(SALESORDERITEMID)[1]','uniqueidentifier')
end [SALESORDERITEMID],
T.item.value('(QUANTITYREFUNDING)[1]','decimal(10, 4)'),
T.item.value('(QUANTITY)[1]','decimal(10, 4)'),
T.item.value('(PRICE)[1]','money'),
round( T.item.value('(FEES)[1]','money') * T.item.value('(QUANTITYREFUNDING)[1]','decimal(10, 4)'), 2),
T.item.value('(DISCOUNTS)[1]','money'),
T.item.value('(TYPECODE)[1]','integer'),
case @CONTEXTTYPE
when 0 then
(select [DESCRIPTION] from dbo.[SALESORDERITEM] where [ID] = T.item.value('(SALESORDERITEMID)[1]','uniqueidentifier'))
when 1 then
T.item.value('(DESCRIPTION)[1]','nvarchar(100)')
end,
T.item.value('(REFUNDINGTOTAL)[1]','money'),
case when T.item.value('(GROUPID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
null
else
T.item.value('(GROUPID)[1]','uniqueidentifier')
end [GROUPID],
T.item.value('(GROUPTYPECODE)[1]','tinyint'),
case when T.item.value('(ITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
null
else
T.item.value('(ITEMID)[1]','uniqueidentifier')
end [ITEMID],
case when T.item.value('(REVENUESPLITID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
null
else
T.item.value('(REVENUESPLITID)[1]','uniqueidentifier')
end [REVENUESPLITID]
from @ITEMS.nodes('/ITEMS/ITEM') T(item)
--Error if the quantity refunding is less than zero for any of the items
if exists (select SALESORDERITEMID from @CREDITITEMS where QUANTITYREFUNDING < 0) begin
raiserror('ERR_ITEMS_NEGATIVEQUANTITY', 13, 1);
return 1;
end
--Now that we've checked to make sure no negative quantities have been entered,
--get rid of all the ones that weren't selected to return
delete from @CREDITITEMS
where [QUANTITYREFUNDING] <= 0
-- If there are combination items to refund,
-- get the same type of the tickets of all the programs in the combination.
if exists (
select [ITEMS].[SALESORDERITEMID]
from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEMTICKET] [TICKETS] on [ITEMS].[SALESORDERITEMID] = [TICKETS].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [COMBO] on [TICKETS].[ID] = [COMBO].[ID]
) begin
-- Update the price, fees, of the item already in @CREDITITEMS
-- the price returned from input parameter is the price for the whole combo,
-- not the price for the individual program.
update @CREDITITEMS set
[PRICE] = [TICKETS].[PRICE],
[FEES] = dbo.UFN_SALESORDERITEM_GETFEES([ITEMS].[SALESORDERITEMID]) * [ITEMS].[QUANTITYREFUNDING]
from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEMTICKET] [TICKETS] on [ITEMS].[SALESORDERITEMID] = [TICKETS].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [COMBO] on [TICKETS].[ID] = [COMBO].[ID]
--update price for applying tickets to membership
update @CREDITITEMS set
[PRICE] = [SALESORDERITEM].[PRICE],
[FEES] = dbo.UFN_SALESORDERITEM_GETFEES([ITEMS].[SALESORDERITEMID]) * [ITEMS].[QUANTITYREFUNDING]
from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEM] on [ITEMS].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
inner join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIPITEMPROMOTION].[SALESORDERITEMID]
insert into @CREDITITEMS
select
newid() as [ID],
[SALESORDERITEM].[ID],
(
select [QUANTITYREFUNDING]
from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEMTICKET] [TICKETS] on [ITEMS].[SALESORDERITEMID] = [TICKETS].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [COMBO] on [TICKETS].[ID] = [COMBO].[ID]
where
[TICKETS].[PRICETYPECODEID] = [SALESORDERITEMTICKET].[PRICETYPECODEID] and
[COMBO].[COMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] and
[COMBO].[TICKETCOMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID]
) as [QUANTITYREFUNDING],
(
select [QUANTITY]
from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEMTICKET] [TICKETS] on [ITEMS].[SALESORDERITEMID] = [TICKETS].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [COMBO] on [TICKETS].[ID] = [COMBO].[ID]
where
[TICKETS].[PRICETYPECODEID] = [SALESORDERITEMTICKET].[PRICETYPECODEID] and
[COMBO].[COMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] and
[COMBO].[TICKETCOMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID]
) as [QUANTITY],
[SALESORDERITEM].[PRICE],
dbo.UFN_SALESORDERITEM_GETFEES([SALESORDERITEM].[ID]) * (
select [QUANTITYREFUNDING]
from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEMTICKET] [TICKETS] on [ITEMS].[SALESORDERITEMID] = [TICKETS].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [COMBO] on [TICKETS].[ID] = [COMBO].[ID]
where
[TICKETS].[PRICETYPECODEID] = [SALESORDERITEMTICKET].[PRICETYPECODEID] and
[COMBO].[COMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] and
[COMBO].[TICKETCOMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID]
) as [FEES],
0 as [DISCOUNTS],
0 as [TYPECODE],
[SALESORDERITEM].[DESCRIPTION],
0 as [REFUNDINGTOTAL],
null as [GROUPID],
0 as [GROUPTYPECODE],
null as [ITEMID],
LI.ID as [REVENUESPLITID]
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
inner join dbo.REVENUESPLITORDER on REVENUESPLITORDER.ID = LI.ID
where
[SALESORDERITEM].[ID] not in (select [SALESORDERITEMID] from @CREDITITEMS)
and [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] in (
select [COMBO].[TICKETCOMBINATIONID]
from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEMTICKET] [TICKETS] on [ITEMS].[SALESORDERITEMID] = [TICKETS].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [COMBO] on [TICKETS].[ID] = [COMBO].[ID]
)
and [SALESORDERITEMTICKET].[PRICETYPECODEID] in (
select [TICKETS].[PRICETYPECODEID] from @CREDITITEMS [ITEMS]
inner join dbo.[SALESORDERITEMTICKET] [TICKETS] on [ITEMS].[SALESORDERITEMID] = [TICKETS].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [COMBO] on [TICKETS].[ID] = [COMBO].[ID]
where
[COMBO].[COMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] and
[COMBO].[TICKETCOMBINATIONID] = [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID]
)
and (
REVENUESPLITORDER.PROGRAMID = SALESORDERITEMTICKET.PROGRAMID
and (SALESORDERITEMTICKET.EVENTID is null or SALESORDERITEMTICKET.EVENTID = REVENUESPLITORDER.EVENTID)
)
end
if exists
(
select 1
from @CREDITITEMS ITEMS
where ITEMS.TYPECODE = 2
) begin
-- check for matching gifts
if exists
(
select 1
from dbo.REVENUEMATCHINGGIFT
inner join @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item) on
T.item.value('(REVENUEID)[1]', 'uniqueidentifier') = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
) begin
raiserror('ERR_REFUNDMETHODS_DONATIONMATCHINGGIFTS', 13, 1);
return 1;
end
delete
from dbo.REVENUERECOGNITION
where REVENUERECOGNITION.REVENUESPLITID in
(
select
SOID.REVENUESPLITID
from dbo.SALESORDERITEMDONATION SOID
inner join @CREDITITEMS ITEMS on
ITEMS.SALESORDERITEMID = SOID.ID
)
end
/********Validate refund items*******/
--Error if no items were selected
if not exists (select [SALESORDERITEMID] from @CREDITITEMS) begin
raiserror('ERR_ITEMS_NONESELECTED', 13, 1);
return 1;
end
--Error if the quantity refunding for any item is greater than the quantity available
if exists(select [SALESORDERITEMID] from @CREDITITEMS where [QUANTITYREFUNDING] > [QUANTITY]) begin
raiserror('ERR_ITEMS_EXCEEDSQUANTITYAVAILABLE', 13, 1);
return 1;
end
--Error if the payments do not add up to the refund total
if coalesce((select sum(T.item.value('(AMOUNTREFUNDING)[1]','money')) from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)),0) <> @REFUNDAMOUNT begin
raiserror('ERR_REFUNDMETHODS_DIFFERENTFROMTOTALAMOUNT', 13, 1);
return 1;
end
--Error if the refund amount is greater than the order total
if @REFUNDAMOUNT > @TOTAL begin
raiserror('ERR_REFUNDAMOUNT_EXCEEDSLIMIT', 13, 1);
return 1;
end
declare @PAYMENTGROUPID uniqueidentifier = null;
select top(1)
@PAYMENTGROUPID = [PAYMENTGROUPID]
from (
select
T.item.value('(AMOUNTREFUNDING)[1]','money') as [AMOUNTPAID],
T.item.value('(PAYMENTGROUPID)[1]','uniqueidentifier') as [PAYMENTGROUPID],
T.item.value('(PAYMENTGROUPMAXAMOUNT)[1]','money') as [PAYMENTGROUPMAXAMOUNT]
from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
) [PAYMENTS]
group by [PAYMENTGROUPID], [PAYMENTGROUPMAXAMOUNT]
having sum([AMOUNTPAID]) > [PAYMENTGROUPMAXAMOUNT]
if (@PAYMENTGROUPID is not null) and (@PAYMENTGROUPID <> '00000000-0000-0000-0000-000000000000') begin
declare @PAYMENTSLIST nvarchar(1000)
declare @PAYMENTGROUPMAXAMOUNT nvarchar(25)
select
@PAYMENTSLIST = dbo.UDA_BUILDLIST(T.item.value('(DESCRIPTION)[1]','nvarchar(1000)'))
from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
where T.item.value('(PAYMENTGROUPID)[1]','uniqueidentifier') = @PAYMENTGROUPID
select top(1)
@PAYMENTGROUPMAXAMOUNT = convert(nvarchar(25), T.item.value('(PAYMENTGROUPMAXAMOUNT)[1]','money'))
from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
where T.item.value('(PAYMENTGROUPID)[1]','uniqueidentifier') = @PAYMENTGROUPID
set @PAYMENTSLIST = 'The original transaction includes multiple payment methods, so you cannot return the full transaction amount as one refund payment. You may return up to $' + @PAYMENTGROUPMAXAMOUNT + ' among the following payments: ' + @PAYMENTSLIST + '.'
raiserror(@PAYMENTSLIST, 13, 1);
return 1;
end
if exists(
select
CREDITCARDPAYMENTMETHODDETAIL.ID
from dbo.CREDITCARDPAYMENTMETHODDETAIL
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID in (
select
case when T.item.value('(REVENUEID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
then (select FINANCIALTRANSACTIONID from dbo.FINANCIALTRANSACTIONLINEITEM where ID = T.item.value('(REVENUESPLITID)[1]','uniqueidentifier'))
else T.item.value('(REVENUEID)[1]','uniqueidentifier')
end
from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
where T.item.value('(PAYMENTTYPECODE)[1]','integer') = 2
)
and CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID is null
)
raiserror('BBERR_REFUNDCREDITCARDPAYMENT_MUSTBEPROCESSED', 16, 1);
-- 256291: Removing refund form's 60-day limit. Not all processors have a 60-day limit.
-- Instead, we will error on the form if the first credit card payment refund fails and rollback this procedure.
-- We must complete the refund after the first credit card is processed because we cannot reverse the refund
-- That means, if a refund has more than one payment, it's possible to complete a refund and have 'unrefundable' payments (which was also the case before this change)
--if exists(
-- select
-- CREDITCARDPAYMENTMETHODDETAIL.ID
-- from dbo.CREDITCARDPAYMENTMETHODDETAIL
-- inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
-- where REVENUEPAYMENTMETHOD.REVENUEID in (
-- select
-- case when T.item.value('(REVENUEID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
-- then (select REVENUEID from dbo.REVENUESPLIT where ID = T.item.value('(REVENUESPLITID)[1]','uniqueidentifier'))
-- else T.item.value('(REVENUEID)[1]','uniqueidentifier')
-- end
-- from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
-- where T.item.value('(PAYMENTTYPECODE)[1]','integer') = 2
-- )
-- and dateadd(day, 60, CREDITCARDPAYMENTMETHODDETAIL.DATEADDED) <= convert(date, getdate())
--)
-- raiserror('BBERR_REFUNDCREDITCARDPAYMENT_TRANSACTIONTOOOLD', 16, 1);
--Throw an error if we're refunding a membership, but all the add-ons
--have not been refunded.
if exists(
select
MEMBERSHIPITEM.ID
from @CREDITITEMS MEMBERSHIPITEM
inner join dbo.MEMBERSHIPADDON
on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPITEM.ITEMID
left join @CREDITITEMS CREDITADDONS
on CREDITADDONS.ITEMID = MEMBERSHIPADDON.ID
left join
(
select FTLI.SOURCELINEITEMID, FTLI.QUANTITY
from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.CREDITITEM_EXT
on CREDITITEM_EXT.ID = FTLI.ID
where FINANCIALTRANSACTION.TYPECODE = 23 --refund
and CREDITITEM_EXT.TYPECODE = 16 --membershipaddon
) PREVIOUSLYREFUNDEDADDONREFUNDITEMS
on PREVIOUSLYREFUNDEDADDONREFUNDITEMS.SOURCELINEITEMID = MEMBERSHIPADDON.REVENUESPLITID
where MEMBERSHIPITEM.TYPECODE = 1
and MEMBERSHIPITEM.QUANTITYREFUNDING = 1
group by
CREDITADDONS.ID,
CREDITADDONS.QUANTITY,
CREDITADDONS.QUANTITYREFUNDING,
MEMBERSHIPADDON.QUANTITY,
MEMBERSHIPITEM.ID,
MEMBERSHIPADDON.ID
having (CREDITADDONS.ID is not null and CREDITADDONS.QUANTITY <> CREDITADDONS.QUANTITYREFUNDING)
or (CREDITADDONS.ID is null and sum(coalesce(PREVIOUSLYREFUNDEDADDONREFUNDITEMS.QUANTITY, 0)) <> MEMBERSHIPADDON.QUANTITY)
)
raiserror('BBERR_ITEMS_REFUNDINGMEMBERSHIPWITHOUTADDONS', 16, 1);
declare @CURRENTDATETIMEOFFSET datetimeoffset
set @CURRENTDATETIMEOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1)
-- Bug #167166 - MDC
set @TRANSACTIONDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());
if cast(@TRANSACTIONDATE as date) < (select cast(S.TRANSACTIONDATE as date) from dbo.SALESORDER S where S.ID = @CONTEXTID) begin
raiserror('ERR_REFUNDDATE_BEFORE_ORDERDATE', 13, 1);
return 1;
end
declare @LATESTTRANSACTIONID uniqueidentifier
declare @MEMBERSHIPID uniqueidentifier
begin try
-- JustinMe 10/21/2009 Bug#61958
-- get the constituent id from either sales order or membership revenue
declare @CONSTITUENTID uniqueidentifier
select @CONSTITUENTID =
case @CONTEXTTYPE
when 0 then (select [CONSTITUENTID] from dbo.[SALESORDER] where [ID] = @CONTEXTID)
when 1 then
(
select
top 1 FINANCIALTRANSACTION.CONSTITUENTID
from dbo.MEMBERSHIPTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @CONTEXTID
order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE, MEMBERSHIPTRANSACTION.DATEADDED desc
)
else
(select [REGISTRANT].[CONSTITUENTID] from dbo.[REGISTRANT] where [ID] = @CONTEXTID)
end
-- LeeCh, 12/04/2009 Bug#69430
-- Get sales order ID for refunded item if it is in a sales order
declare @SALESORDERID uniqueidentifier
declare @MEMBERSHIPREFUNDHASMEMBERSHIP bit = 1;
if @CONTEXTTYPE = 0 begin
set @SALESORDERID = @CONTEXTID
end
else begin
if @CONTEXTTYPE = 1 begin
select @SALESORDERID = [SALESORDER].[ID]
from @CREDITITEMS [CREDITITEMS]
inner join dbo.[SALESORDERITEM]
on [CREDITITEMS].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
inner join dbo.[SALESORDER]
on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
--We only want to refund the membership if it was selected
--(you can select just add-ons)
if exists(select ID from @CREDITITEMS where TYPECODE = 16 and QUANTITYREFUNDING > 0) and not exists (select ID from @CREDITITEMS where TYPECODE = 1 and QUANTITYREFUNDING > 0)
set @MEMBERSHIPREFUNDHASMEMBERSHIP = 0;
end
else
begin
declare @EVENTREGISTRATIONREVENUEID uniqueidentifier
select top 1 @EVENTREGISTRATIONREVENUEID = (T.item.value('(REVENUEID)[1]','uniqueidentifier')) from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
if @EVENTREGISTRATIONREVENUEID <> '00000000-0000-0000-0000-000000000000'
begin
select top 1 @SALESORDERID = [SALESORDERPAYMENT].[SALESORDERID]
from dbo.[SALESORDERPAYMENT]
where [PAYMENTID] = @EVENTREGISTRATIONREVENUEID
end
end
end
-- handle inserting the data
declare @CURRENCYID uniqueidentifier;
select @CURRENCYID = ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1;
declare @PDACCOUNTSYSTEMID uniqueidentifier;
declare @ALLOWGLDISTRIBUTIONS bit;
select
@PDACCOUNTSYSTEMID = ID,
@ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS
from
dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();
declare @INS table(
ID uniqueidentifier
,CREDITID uniqueidentifier
,SALESORDERITEMID uniqueidentifier
,QUANTITY int
,PRICE money
,FEES money
,DISCOUNTS money
,TYPECODE tinyint
,DESCRIPTION nvarchar(700)
,GROUPID uniqueidentifier
,GROUPTYPECODE tinyint
,SOURCELINEITEMID uniqueidentifier
,CONTRIBUTEDREVENUE money
,ISCONTRIBUTEDREVENUE bit
,TOTALAMOUNT money)
insert into dbo.FINANCIALTRANSACTION(
ID
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,TRANSACTIONCURRENCYID
,TYPECODE
,PDACCOUNTSYSTEMID
,DESCRIPTION
,DATE
,POSTDATE
,POSTSTATUSCODE
,PARENTID
,APPUSERID
,CONSTITUENTID
---- Boilerplate
,DATEADDED ,DATECHANGED ,ADDEDBYID ,CHANGEDBYID)
values(
@ID
,@REFUNDAMOUNT
,@REFUNDAMOUNT
,@REFUNDAMOUNT
,@CURRENCYID
,23 --Refund
,@PDACCOUNTSYSTEMID
,@COMMENT
,@TRANSACTIONDATE
,case when @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 then cast(@TRANSACTIONDATE as date) else null end
,case when @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 then 1 else 3 end
,(select REVENUEID from dbo.SALESORDER where ID = @SALESORDERID)
,@CURRENTAPPUSERID
,@CONSTITUENTID
,@CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
)
insert into dbo.CREDIT_EXT(
ID
,SALESORDERID
,CREDITREASONCODEID
---- Boilerplate
,DATEADDED ,DATECHANGED ,ADDEDBYID ,CHANGEDBYID)
values (
@ID
,@SALESORDERID
,case @CREDITREASONCODEID
when '00000000-0000-0000-0000-000000000000' then null
else @CREDITREASONCODEID
end
,@CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
)
insert into dbo.CREDITPAYMENT
(
[ID],
[CREDITID],
[APPUSERID],
[CREDITPAYMENTDATEWITHTIMEOFFSET],
[AMOUNT],
[PAYMENTMETHODCODE],
[OTHERPAYMENTMETHODCODEID],
[REVENUEID],
[REVENUESPLITID],
[REFUNDPROCESSED],
[STATUS],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
T.item.value('(CREDITPAYMENTID)[1]','uniqueidentifier'),
@ID,
@CURRENTAPPUSERID,
@CURRENTDATETIMEOFFSET,
T.item.value('(AMOUNTREFUNDING)[1]','money'),
T.item.value('(PAYMENTTYPECODE)[1]','tinyint'),
case when T.item.value('(PAYMENTTYPECODE)[1]','tinyint') = 10 then
T.item.value('(OTHERPAYMENTMETHODCODEID)[1]','uniqueidentifier')
else
null
end,
case when T.item.value('(REVENUEID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
null
else
T.item.value('(REVENUEID)[1]','uniqueidentifier')
end,
case when T.item.value('(REVENUESPLITID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then
null
else
T.item.value('(REVENUESPLITID)[1]','uniqueidentifier')
end,
case T.item.value('(PAYMENTTYPECODE)[1]','tinyint')
when 2 then 0
else 1
end,
T.item.value('(STATUS)[1]','nvarchar(255)'),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item);
if @ADDRESSID = '00000000-0000-0000-0000-000000000000'
set @ADDRESSID = null;
insert into dbo.CREDITPAYMENTCHECKPAYMENTMETHODDETAIL
(
[ID],
[ADDRESSID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
T.item.value('(CREDITPAYMENTID)[1]','uniqueidentifier'),
@ADDRESSID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
where
T.item.value('(PAYMENTTYPECODE)[1]','tinyint') = 1;
--If it's an order refund
if @CONTEXTTYPE = 0 begin
insert into @INS(
ID
,CREDITID
,SALESORDERITEMID
,QUANTITY
,PRICE
,FEES
,DISCOUNTS
,TYPECODE
,DESCRIPTION
,GROUPID
,GROUPTYPECODE
,SOURCELINEITEMID)
select distinct
[ID],
@ID,
[SALESORDERITEMID],
[QUANTITYREFUNDING],
[PRICE],
[FEES],
[DISCOUNTS],
[TYPECODE],
[DESCRIPTION],
[GROUPID],
[GROUPTYPECODE],
[REVENUESPLITID]
from @CREDITITEMS
where
[SALESORDERITEMID] is not null
--taxes
union all
select
newid(),
@ID,
T.item.value('(TAXID)[1]','uniqueidentifier'),
1,
round(T.item.value('(CURRENTTAX)[1]','money'), 2),
0,
0,
4,
(select [DESCRIPTION] from dbo.[SALESORDERITEM] where [ID] = T.item.value('(TAXID)[1]','uniqueidentifier')),
null,
0,
T.item.value('(REVENUESPLITID)[1]','uniqueidentifier')
from @TAXES.nodes('/TAXES/ITEM') T(item)
where T.item.value('(CURRENTTAX)[1]','money') > 0
--item level fees
union all
select
newid(),
@ID,
FEES.ID,
1,
FEES.AMOUNT,
0,
0,
3,
CREDITITEMS.DESCRIPTION,
null,
0,
REVENUESPLIT.ID
from
@CREDITITEMS as CREDITITEMS
cross apply
dbo.UFN_SPLITS_GETPRORATEDSPLITS(
(
select
coalesce(sum(SALESORDERITEM.TOTAL), 0) - coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS), 0)
from dbo.SALESORDERITEMFEE
inner join dbo.SALESORDERITEM on SALESORDERITEMFEE.ID = SALESORDERITEM.ID
left outer join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
where SALESORDERITEMFEE.SALESORDERITEMID = CREDITITEMS.SALESORDERITEMID
),
CREDITITEMS.FEES,
(
select
SALESORDERITEM.ID as ID,
SALESORDERITEM.TOTAL - coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) , 0) as AMOUNT
from dbo.SALESORDERITEMFEE
inner join SALESORDERITEM on SALESORDERITEMFEE.ID = SALESORDERITEM.ID
left outer join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
where SALESORDERITEMFEE.SALESORDERITEMID = CREDITITEMS.SALESORDERITEMID
group by SALESORDERITEM.ID, SALESORDERITEM.TOTAL
FOR XML RAW('ITEM'),TYPE,ELEMENTS,root('AMOUNTSTOPRORATE'),binary base64
)
) as FEES
left outer join dbo.SALESORDERITEMFEE
on FEES.ID = SALESORDERITEMFEE.ID
left outer join dbo.SALESORDERITEM
on SALESORDERITEMFEE.ID = SALESORDERITEM.ID
left outer join dbo.SALESORDER
on SALESORDER.ID = SALESORDERITEM.SALESORDERID
left outer join dbo.FINANCIALTRANSACTION REVENUE
on REVENUE.ID = SALESORDER.REVENUEID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
left outer join dbo.REVENUESPLITORDER
on REVENUESPLITORDER.ID = REVENUESPLIT.ID
where CREDITITEMS.FEES > 0
and FEES.AMOUNT > 0
and SALESORDER.ID = @SALESORDERID
and REVENUESPLITORDER.FEEID = SALESORDERITEMFEE.FEEID
--Contributed revenue
insert into @INS(
ID
,CREDITID
,SALESORDERITEMID
,QUANTITY
,PRICE
,FEES
,DISCOUNTS
,TYPECODE
,DESCRIPTION
,GROUPID
,GROUPTYPECODE
,SOURCELINEITEMID
,ISCONTRIBUTEDREVENUE
,TOTALAMOUNT)
select
newid(),
@ID,
null, --SALESORDERITEMID
1, --QUANTITYREFUNDING
FTLI.ORGAMOUNT,
0, --FEES
case when FTLI.ORGAMOUNT >= sum(isnull(I.DISCOUNTS, 0)) then sum(isnull(I.DISCOUNTS, 0)) else FTLI.ORGAMOUNT end,--DISCOUNTS
case
when REVENUESPLIT_EXT.APPLICATIONCODE = 1 then 6
else 2
end, --TYPECODE
FTLI.DESCRIPTION, --DESCRIPTION
null, --GROUPID
0, --GROUPTYPECODE
FTLI.ID,
1,
sum(PAYMENTLI.ORGAMOUNT)
from FINANCIALTRANSACTIONLINEITEM FTLI
inner join REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
inner join @INS I on I.SOURCELINEITEMID = FTLI.SOURCELINEITEMID
--I.SOURCELINEITEM is the membership source FTLI for the refunds, FTLI's SOURCELINEITEM is the membership FTLI that caused the donation FTLI
inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on PAYMENTLI.SOURCELINEITEMID = FTLI.ID
inner join dbo.FINANCIALTRANSACTION PAYMENTFT on PAYMENTFT.ID = PAYMENTLI.FINANCIALTRANSACTIONID
where revenuesplit_ext.TYPECODE = 0 and (REVENUESPLIT_EXT.APPLICATIONCODE = 0 or REVENUESPLIT_EXT.APPLICATIONCODE = 1)
and FTLI.TYPECODE = 0
and PAYMENTFT.TYPECODE = 0
group by FTLI.ID, FTLI.DESCRIPTION, FTLI.ORGAMOUNT, REVENUESPLIT_EXT.APPLICATIONCODE
--update the contributed revenue amount for memberships
;with CTE_CONTRIBUTEDREVENUE as
(
select
sum(FTLI.TRANSACTIONAMOUNT)AMOUNT,
I.SOURCELINEITEMID,
SUM(isnull(I.DISCOUNTS, 0)) DISCOUNTS
from @INS I
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.SOURCELINEITEMID = I.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
where REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 0 --only donations
and FTLI.TYPECODE = 0 --only standard ft line items
group by I.SOURCELINEITEMID
)
update @INS
set I.CONTRIBUTEDREVENUE = CT.AMOUNT
,I.DISCOUNTS = isnull(I.DISCOUNTS, 0) - CT.DISCOUNTS
from @INS I
inner join CTE_CONTRIBUTEDREVENUE CT on CT.SOURCELINEITEMID = I.SOURCELINEITEMID
--update final amount with discounts and contributed revenue
update @INS
set TOTALAMOUNT =
case
when isnull(CONTRIBUTEDREVENUE, 0) > DISCOUNTS then --the discount is only taken out of the contributed revenue
(PRICE * QUANTITY) - isnull(CONTRIBUTEDREVENUE, 0)
when ISCONTRIBUTEDREVENUE = 1 then --this is the contributed revenue and the appropriate amount was set when it was inserted into @INS
TOTALAMOUNT
else
(PRICE * QUANTITY) - isnull(DISCOUNTS, 0)
end
--earned income needs to reflect the FTLI table entry, not the SOI table entry for events that have designations on fees.
update @INS
set TOTALAMOUNT = (select TRANSACTIONAMOUNT from dbo.FINANCIALTRANSACTIONLINEITEM LI where LI.ID = I.SOURCELINEITEMID)
from
@INS I
inner join
dbo.SALESORDERITEM SOI on SOI.ID = I.SALESORDERITEMID
inner join
dbo.SALESORDERITEMEVENTREGISTRATION ER on ER.ID = SOI.ID
inner join
dbo.REGISTRANT R on R.ID = ER.REGISTRANTID
inner join
dbo.EVENT E on E.ID = R.EVENTID
where
E.DESIGNATIONSONFEES = 1;
insert into dbo.FINANCIALTRANSACTIONLINEITEM(
ID
,FINANCIALTRANSACTIONID
,UNITVALUE
,QUANTITY
,DESCRIPTION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,POSTDATE
,POSTSTATUSCODE
,TYPECODE
,SOURCELINEITEMID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
I.ID
,I.CREDITID
,I.PRICE
,I.QUANTITY
,I.DESCRIPTION
,TOTALAMOUNT
,TOTALAMOUNT
,TOTALAMOUNT
,FT.POSTDATE
,FT.POSTSTATUSCODE
,CASE I.TYPECODE
WHEN 3 then 7 --Fee
WHEN 4 then 8 --Tax
WHEN 5 then 5 --Discount
ELSE 0 end
,I.SOURCELINEITEMID
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @INS I
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = I.CREDITID;
insert into dbo.CREDITITEM_EXT(
ID
,CREDITID
,DISCOUNTS
,FEES
,GROUPID
,GROUPTYPECODE
,SALESORDERITEMID
,TYPECODE
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
I.ID
,I.CREDITID
,I.DISCOUNTS
,I.FEES
,I.GROUPID
,I.GROUPTYPECODE
,I.SALESORDERITEMID
,I.TYPECODE
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @INS I;
--update ticket status for returned tickets
with TICKETIDS_CTE as(
select
[TICKET].[ID],
row_number() over(partition by [TICKET].[SALESORDERITEMTICKETID] order by [TICKET].[ID]) as [ROWNUMBER],
[CREDITITEMS].[QUANTITYREFUNDING]
from dbo.[TICKET]
inner join @CREDITITEMS [CREDITITEMS] on
[TICKET].[SALESORDERITEMTICKETID] = [CREDITITEMS].[SALESORDERITEMID]
where
[TICKET].[SALESORDERITEMTICKETID] = [CREDITITEMS].[SALESORDERITEMID] and
[TICKET].[STATUSCODE] in (0, 1) and
[TICKET].[APPLIEDTOMEMBERSHIP] = 0
)
update
dbo.[TICKET]
set
[TICKET].[STATUSCODE] = 2
from
dbo.[TICKET]
inner join
TICKETIDS_CTE as [TICKETIDS] on [TICKET].[ID] = [TICKETIDS].[ID]
where
[TICKETIDS].[ROWNUMBER] <= [TICKETIDS].[QUANTITYREFUNDING]
-- Update on hand quantity for returned merchandise items
update dbo.[MERCHANDISEPRODUCTINSTANCE]
set [ONHANDQUANTITY] = [ONHANDQUANTITY] + [CI].[QUANTITYREFUNDING]
from @CREDITITEMS [CI]
inner join dbo.[SALESORDERITEM] [SOI] on [SOI].[ID] = [CI].[SALESORDERITEMID]
inner join dbo.[SALESORDERITEMMERCHANDISE] [SOIM] on [SOIM].[ID] = [SOI].[ID]
where [MERCHANDISEPRODUCTINSTANCE].[ID] = [SOIM].[MERCHANDISEPRODUCTINSTANCEID]
--update ticket applied to membership field
update
dbo.[TICKET]
set
[TICKET].[APPLIEDTOMEMBERSHIP] = 0,
[TICKET].[APPLIEDTOMEMBERSHIPSALESORDERID] = null
from dbo.[TICKET]
inner join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMTICKET] [APPLIEDTICKETS] on
[TICKET].[ID] = [APPLIEDTICKETS].[TICKETID]
inner join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM] [APPLIEDITEM] on
APPLIEDTICKETS.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMID = [APPLIEDITEM].[ID]
inner join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION] [PROMOTION] on
[APPLIEDITEM].[SALESORDERITEMMEMBERSHIPITEMPROMOTIONID] = [PROMOTION].[ID]
inner join @CREDITITEMS [CREDITITEMS] on
[PROMOTION].[SALESORDERITEMID] = [CREDITITEMS].[SALESORDERITEMID]
--Add credit membership items
insert into dbo.[CREDITITEMMEMBERSHIP]
(
[ID],
[MEMBERSHIPID],
[MEMBERSHIPPROGRAMID],
[MEMBERSHIPPROGRAMNAME],
[MEMBERSHIPLEVELID],
[MEMBERSHIPLEVELTERMID],
[MEMBERSHIPLEVELTYPECODEID],
[NUMBEROFCHILDREN],
[COMMENTS],
[ISGIFT],
[GIVENBYID],
[EXPIRATIONDATE],
[MEMBERS],
[ACTIONCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[CREDITITEMS].[ID],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPLEVELID],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPLEVELTERMID],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID],
[SALESORDERITEMMEMBERSHIP].[NUMBEROFCHILDREN],
[SALESORDERITEMMEMBERSHIP].[COMMENTS],
[SALESORDERITEMMEMBERSHIP].[ISGIFT],
[SALESORDERITEMMEMBERSHIP].[GIVENBYID],
[SALESORDERITEMMEMBERSHIP].[EXPIRATIONDATE],
(
select dbo.UFN_CONSTITUENT_BUILDNAME([SOIM].[CONSTITUENTID]) [MEMBERNAME]
from dbo.[SALESORDERITEMMEMBER] as [SOIM]
inner join dbo.[SALESORDERITEMMEMBERSHIP] as [SOIMS]
on [SOIM].[SALESORDERITEMMEMBERSHIPID] = [SOIMS].[ID]
inner join dbo.[MEMBER]
on [MEMBER].[CONSTITUENTID] = [SOIM].[CONSTITUENTID]
and [MEMBER].[MEMBERSHIPID] = [SOIMS].[MEMBERSHIPID]
where [SALESORDERITEMMEMBERSHIP].[ID] = [SOIM].[SALESORDERITEMMEMBERSHIPID] and [MEMBER].[ISDROPPED] = 0
for xml raw('ITEM'),type,elements,root('MEMBERSHIPMEMBERS'),binary base64
),
coalesce(
[MEMBERSHIPTRANSACTION].[ACTIONCODE],
(select [MEMBERSHIPTRANSACTION].[ACTIONCODE] from dbo.[MEMBERSHIPTRANSACTION] where [ID] = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID([SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID]))
),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @CREDITITEMS as [CREDITITEMS]
inner join dbo.[SALESORDERITEM]
on [CREDITITEMS].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
inner join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left join dbo.[MEMBERSHIPTRANSACTION]
on [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID] = [MEMBERSHIPTRANSACTION].[ID]
--Add credit event registration items
insert into dbo.[CREDITITEMEVENTREGISTRATION]
(
[ID],
[REGISTRANTID],
[EVENTNAME],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[CREDITITEMS].[ID],
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID],
[SALESORDERITEMEVENTREGISTRATION].[EVENTNAME],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @CREDITITEMS as [CREDITITEMS]
inner join dbo.[SALESORDERITEM]
on [CREDITITEMS].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
--Order level discounts
--order discounts are 'all or none'... we don't currently
--refund part of an order discount
if @ORDERDISCOUNTS > 0 begin
insert into dbo.[CREDITITEM]
(
[CREDITID],
[SALESORDERITEMID],
[QUANTITY],
[PRICE],
[FEES],
[DISCOUNTS],
[TYPECODE],
[DESCRIPTION],
[GROUPID],
[GROUPTYPECODE],
[REVENUESPLITID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select distinct
@ID,
[ID],
[QUANTITY],
-[PRICE],
0,
0,
[SALESORDERITEM].[TYPECODE],
[SALESORDERITEM].[DESCRIPTION],
null,
0,
null [REVENUESPLITID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[SALESORDERITEM] where [TYPECODE] = 5 and [SALESORDERID] = @CONTEXTID
end
--Add item level discounts
insert into dbo.CREDITITEMITEMDISCOUNT
(
CREDITITEMID,
SALESORDERITEMITEMDISCOUNTID,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
LI.ID,
SALESORDERITEMITEMDISCOUNT.ID,
SALESORDERITEMITEMDISCOUNT.AMOUNT, --Right now, the entire discount has to be returned
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.SALESORDERITEMITEMDISCOUNT
inner join
dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
where
LI.FINANCIALTRANSACTIONID = @ID;
--Add item membership promotions
insert into dbo.CREDITITEMMEMBERSHIPITEMPROMOTION
(
CREDITITEMID,
SALESORDERITEMMEMBERSHIPITEMPROMOTIONID,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
LI.ID,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT, --Right now, the entire promotion has to be returned
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
inner join
dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
where
LI.FINANCIALTRANSACTIONID = @ID;
update LI set
QUANTITY = CREDITITEMTICKETCOUNTS.NUMOFTICKETS,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.FINANCIALTRANSACTIONLINEITEM as LI
inner join
dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
inner join
dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
inner join (
select
CREDITITEMTICKET.CREDITITEMID,
count(*) as NUMOFTICKETS
from dbo.CREDITITEMTICKET
group by CREDITITEMTICKET.CREDITITEMID
) as CREDITITEMTICKETCOUNTS on CREDITITEMTICKETCOUNTS.CREDITITEMID = LI.ID
left outer join
dbo.SALESORDER on SALESORDER.REVENUEID = FT.PARENTID
where
@CONTEXTID in (SALESORDER.ID, CREDIT_EXT.SALESORDERID);
end
--If it's a membership refund
else if @CONTEXTTYPE = 1 begin
--Error if there are any later membership transactions
set @LATESTTRANSACTIONID =
(select top(1)
[ID]
from dbo.[MEMBERSHIPTRANSACTION]
where [MEMBERSHIPID] = @MEMBERSHIPID order by [DATEADDED] desc)
if @TRANSACTIONID <> @LATESTTRANSACTIONID begin
raiserror('ERR_ITEMS_MEMBERSHIPREFUND_HASNEWERMEMBERSHIPTRANSACTIONS', 13, 1);
end
insert into @INS(
ID
,CREDITID
,SALESORDERITEMID
,QUANTITY
,PRICE
,FEES
,DISCOUNTS
,TYPECODE
,DESCRIPTION
,GROUPID
,GROUPTYPECODE
,SOURCELINEITEMID)
select
[ID],
@ID,
[SALESORDERITEMID],
[QUANTITYREFUNDING],
case
when DISCOUNTS > 0 or TYPECODE <> 1 then -- cannot partially refund discounted membership
[PRICE] -- so we want the full price of the membership, no the discounted price
else -- if it's possibly a partial refund, we want just the partial portion
[REFUNDINGTOTAL]
end [PRICE],
0 as [FEES],
coalesce([DISCOUNTS],0),
[TYPECODE],
[DESCRIPTION],
null as [GROUPID],
0 as [GROUPTYPECODE],
[REVENUESPLITID]
from @CREDITITEMS [CREDITITEMS]
where [QUANTITYREFUNDING] > 0
--Add contributed revenue
insert into @INS(
ID
,CREDITID
,SALESORDERITEMID
,QUANTITY
,PRICE
,FEES
,DISCOUNTS
,TYPECODE
,DESCRIPTION
,GROUPID
,GROUPTYPECODE
,SOURCELINEITEMID
,ISCONTRIBUTEDREVENUE
,TOTALAMOUNT)
select
newid(),
@ID,
null, --SALESORDERITEMID
1, --QUANTITYREFUNDING
FTLI.ORGAMOUNT,
0, --FEES
case when FTLI.ORGAMOUNT >= sum(isnull(I.DISCOUNTS, 0)) then sum(isnull(I.DISCOUNTS, 0)) else FTLI.ORGAMOUNT end,--DISCOUNTS
2, --TYPECODE
FTLI.DESCRIPTION, --DESCRIPTION
null, --GROUPID
0, --GROUPTYPECODE
FTLI.ID,
1,
sum(PAYMENTLI.ORGAMOUNT)
from FINANCIALTRANSACTIONLINEITEM FTLI
inner join REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
inner join @INS I on I.SOURCELINEITEMID = FTLI.SOURCELINEITEMID
--I.SOURCELINEITEM is the membership source FTLI for the refunds, FTLI's SOURCELINEITEM is the membership FTLI that caused the donation FTLI
inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on PAYMENTLI.SOURCELINEITEMID = FTLI.ID
inner join dbo.FINANCIALTRANSACTION PAYMENTFT on PAYMENTFT.ID = PAYMENTLI.FINANCIALTRANSACTIONID
where revenuesplit_ext.TYPECODE = 0 and revenuesplit_ext.APPLICATIONCODE = 0
and FTLI.TYPECODE = 0
and PAYMENTFT.TYPECODE = 0
group by FTLI.ID, FTLI.DESCRIPTION, FTLI.ORGAMOUNT
--update the contributed revenue amount for memberships
;with CTE_CONTRIBUTEDREVENUE as
(
select
sum(FTLI.TRANSACTIONAMOUNT)AMOUNT,
I.SOURCELINEITEMID,
SUM(isnull(I.DISCOUNTS, 0)) DISCOUNTS
from @INS I
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.SOURCELINEITEMID = I.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
where REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 0 --only donations
and FTLI.TYPECODE = 0 --only standard ft line items
group by I.SOURCELINEITEMID
)
update @INS
set I.CONTRIBUTEDREVENUE = CT.AMOUNT
,I.DISCOUNTS = isnull(I.DISCOUNTS, 0) - CT.DISCOUNTS
from @INS I
inner join CTE_CONTRIBUTEDREVENUE CT on CT.SOURCELINEITEMID = I.SOURCELINEITEMID
--update final amount with discounts and contributed revenue
update @INS
set TOTALAMOUNT =
case
when SALESORDERITEMID is not null and isnull(CONTRIBUTEDREVENUE, 0) > DISCOUNTS then --the discount is only taken out of the contributed revenue
(PRICE * QUANTITY) - isnull(CONTRIBUTEDREVENUE, 0)
when SALESORDERITEMID is not null and ISCONTRIBUTEDREVENUE = 1 then --this is the contributed revenue and the appropriate amount was set when it was inserted into @INS
TOTALAMOUNT
else
(PRICE * QUANTITY) - isnull(DISCOUNTS, 0)
end
insert into dbo.FINANCIALTRANSACTIONLINEITEM(
ID
,FINANCIALTRANSACTIONID
,UNITVALUE
,QUANTITY
,DESCRIPTION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,POSTDATE
,POSTSTATUSCODE
,TYPECODE
,SOURCELINEITEMID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
I.ID
,I.CREDITID
,I.PRICE
,I.QUANTITY
,I.DESCRIPTION
,TOTALAMOUNT
,TOTALAMOUNT
,TOTALAMOUNT
,FT.POSTDATE
,FT.POSTSTATUSCODE
,CASE I.TYPECODE
WHEN 3 then 7 --Fee
WHEN 4 then 8 --Tax
WHEN 5 then 5 --Discount
ELSE 0 end
,SOURCELINEITEMID
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @INS I
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = I.CREDITID;
insert into dbo.CREDITITEM_EXT(
ID
,CREDITID
,DISCOUNTS
,FEES
,GROUPID
,GROUPTYPECODE
,SALESORDERITEMID
,TYPECODE
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
I.ID
,I.CREDITID
,I.DISCOUNTS
,I.FEES
,I.GROUPID
,I.GROUPTYPECODE
,I.SALESORDERITEMID
,I.TYPECODE
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @INS I;
insert into dbo.[CREDITITEMMEMBERSHIP]
(
[ID],
[MEMBERSHIPID],
[MEMBERSHIPPROGRAMID],
[MEMBERSHIPPROGRAMNAME],
[MEMBERSHIPLEVELID],
[MEMBERSHIPLEVELTERMID],
[MEMBERSHIPLEVELTYPECODEID],
[NUMBEROFCHILDREN],
[COMMENTS],
[ISGIFT],
[GIVENBYID],
[EXPIRATIONDATE],
[MEMBERS],
[ACTIONCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[CREDITITEMS].[ID],
[MEMBERSHIPTRANSACTION].[MEMBERSHIPID],
[MEMBERSHIP].[MEMBERSHIPPROGRAMID],
(select [NAME] from dbo.[MEMBERSHIPPROGRAM] where [ID] = [MEMBERSHIP].[MEMBERSHIPPROGRAMID]),
[MEMBERSHIPTRANSACTION].[MEMBERSHIPLEVELID],
[MEMBERSHIPTRANSACTION].[MEMBERSHIPLEVELTERMID],
[MEMBERSHIPTRANSACTION].[MEMBERSHIPLEVELTYPECODEID],
[MEMBERSHIPTRANSACTION].[NUMBEROFCHILDREN],
[MEMBERSHIPTRANSACTION].[COMMENTS],
[MEMBERSHIPTRANSACTION].[ISGIFT],
[MEMBERSHIPTRANSACTION].[DONORID],
[MEMBERSHIPTRANSACTION].[EXPIRATIONDATE],
(
select dbo.[UFN_CONSTITUENT_BUILDNAME]([MEMBER].[CONSTITUENTID]) [MEMBERNAME]
from dbo.[MEMBER]
where [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
for xml raw('ITEM'),type,elements,root('MEMBERSHIPMEMBERS'),binary base64
),
[MEMBERSHIPTRANSACTION].[ACTIONCODE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.[MEMBERSHIPTRANSACTION]
inner join dbo.[MEMBERSHIP]
on [MEMBERSHIPTRANSACTION].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
inner join @CREDITITEMS [CREDITITEMS] on [CREDITITEMS].[ITEMID] = [MEMBERSHIPTRANSACTION].[ID]
where [MEMBERSHIPTRANSACTION].[ID] = @TRANSACTIONID
--update ticket applied to membership field
update
dbo.[TICKET]
set
[TICKET].[APPLIEDTOMEMBERSHIP] = 0,
[TICKET].[APPLIEDTOMEMBERSHIPSALESORDERID] = null
from dbo.[TICKET]
inner join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMTICKET] [APPLIEDTICKETS] on
[TICKET].[ID] = [APPLIEDTICKETS].[TICKETID]
inner join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM] [APPLIEDITEM] on
[APPLIEDTICKETS].[SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMID] = [APPLIEDITEM].[ID]
inner join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION] [PROMOTION] on
[APPLIEDITEM].[SALESORDERITEMMEMBERSHIPITEMPROMOTIONID] = [PROMOTION].[ID]
inner join @CREDITITEMS [CREDITITEMS] on
[PROMOTION].[SALESORDERITEMID] = [CREDITITEMS].[SALESORDERITEMID]
--Add item membership promotions
insert into dbo.CREDITITEMMEMBERSHIPITEMPROMOTION
(
CREDITITEMID,
SALESORDERITEMMEMBERSHIPITEMPROMOTIONID,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
LI.ID,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT, --Right now, the entire promotion has to be returned
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
inner join
dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
where
LI.FINANCIALTRANSACTIONID = @ID
end
--If it's an event registration refund
else if @CONTEXTTYPE = 2 begin
delete from @INS
--Save to the credit item structure
insert into @INS(
ID
,CREDITID
,SALESORDERITEMID
,QUANTITY
,PRICE
,FEES
,DISCOUNTS
,TYPECODE
,DESCRIPTION
,GROUPID
,GROUPTYPECODE
,SOURCELINEITEMID)
select
newid(), -- 2013-07-10 SW: account for multiple backoffice payment methods
@ID,
[CREDITPAYMENTS].[SALESORDERITEMID],
1 as [QUANTITY],
coalesce([CREDITPAYMENTS].[AMOUNT],0), -- switch to refund amount
0 as [FEES],
0 as [DISCOUNTS],
6 as [TYPECODE],
dbo.UFN_CONSTITUENT_BUILDNAME([REGISTRANT].[CONSTITUENTID]) + ' - ' + EVENT.NAME,
[CREDITITEMS].[GROUPID],
coalesce([CREDITPAYMENTS].[GROUPTYPECODE],1),
[CREDITPAYMENTS].[REVENUESPLITID]
from @CREDITITEMS [CREDITITEMS]
inner join dbo.[REGISTRANT]
on [CREDITITEMS].[GROUPID] = [REGISTRANT].[ID]
inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
outer apply (
--Backoffice payments
select
[CREDITPAYMENT].[AMOUNT],
1 as [GROUPTYPECODE],
null as [SALESORDERITEMID]
,[CREDITPAYMENT].[REVENUESPLITID]
from dbo.[CREDITPAYMENT]
inner join dbo.[EVENTREGISTRANTPAYMENT] on [CREDITPAYMENT].[REVENUESPLITID] = [EVENTREGISTRANTPAYMENT].[PAYMENTID]
where [CREDITPAYMENT].[REVENUESPLITID] is not null
and [CREDITPAYMENT].[CREDITID] = @ID
--Order payments
union all
select
sum([CREDITPAYMENT].[AMOUNT]) AMOUNT,
[CREDITITEMS].[GROUPTYPECODE],
[SALESORDERITEM].[ID] as [SALESORDERITEMID],
PAYMENTSPLIT.ID
from dbo.[CREDITPAYMENT]
inner join dbo.[SALESORDERPAYMENT] on [SALESORDERPAYMENT].[PAYMENTID] = [CREDITPAYMENT].[REVENUEID]
inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[SALESORDERID] = [SALESORDERPAYMENT].[SALESORDERID]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
inner join dbo.[REGISTRANT] on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
left join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on PAYMENTSPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
left join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = PAYMENTSPLIT.ID
left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
where [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = @TRANSACTIONID
and [CREDITPAYMENT].[CREDITID] = @ID
and FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID
and REVENUESPLIT_EXT.APPLICATIONCODE = 1 and REVENUESPLIT_EXT.TYPECODE = 1
group by [SALESORDERITEM].[ID], PAYMENTSPLIT.ID
) [CREDITPAYMENTS]
--Add contributed revenue
insert into @INS(
ID
,CREDITID
,SALESORDERITEMID
,QUANTITY
,PRICE
,FEES
,DISCOUNTS
,TYPECODE
,DESCRIPTION
,GROUPID
,GROUPTYPECODE
,SOURCELINEITEMID
,ISCONTRIBUTEDREVENUE
,TOTALAMOUNT)
select
newid(),
@ID,
null, --SALESORDERITEMID
1, --QUANTITYREFUNDING
FTLI.ORGAMOUNT,
0, --FEES
0,--DISCOUNTS
6, --TYPECODE
FTLI.DESCRIPTION, --DESCRIPTION
null, --GROUPID
0, --GROUPTYPECODE
FTLI.ID,
1,
sum(PAYMENTLI.ORGAMOUNT)
from FINANCIALTRANSACTIONLINEITEM FTLI
inner join REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
inner join @INS I on I.SOURCELINEITEMID = FTLI.SOURCELINEITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on PAYMENTLI.SOURCELINEITEMID = FTLI.ID
inner join dbo.FINANCIALTRANSACTION PAYMENTFT on PAYMENTFT.ID = PAYMENTLI.FINANCIALTRANSACTIONID
where revenuesplit_ext.TYPECODE = 0 and revenuesplit_ext.APPLICATIONCODE = 1
and FTLI.TYPECODE = 0
and PAYMENTFT.TYPECODE = 0
group by FTLI.ID, FTLI.DESCRIPTION, FTLI.ORGAMOUNT
--update the contributed revenue amount for event registration
;with CTE_CONTRIBUTEDREVENUE as
(
select
sum(FTLI.TRANSACTIONAMOUNT)AMOUNT,
I.SOURCELINEITEMID
from @INS I
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.SOURCELINEITEMID = I.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
where REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 1 --only donations
and FTLI.TYPECODE = 0 --only standard ft line items
group by I.SOURCELINEITEMID
)
update @INS
set I.CONTRIBUTEDREVENUE = CT.AMOUNT
from @INS I
inner join CTE_CONTRIBUTEDREVENUE CT on CT.SOURCELINEITEMID = I.SOURCELINEITEMID
--update final amount with discounts and contributed revenue
update @INS
set TOTALAMOUNT =
case
when SALESORDERITEMID is not null then
(PRICE * QUANTITY) - isnull(CONTRIBUTEDREVENUE, 0)
else
TOTALAMOUNT
end
--earned income needs to reflect the FTLI table entry, not the SOI table entry for events that have designations on fees.
update @INS
set TOTALAMOUNT = (select TRANSACTIONAMOUNT from dbo.FINANCIALTRANSACTIONLINEITEM LI where LI.ID = I.SOURCELINEITEMID)
from
@INS I
inner join
dbo.SALESORDERITEM SOI on SOI.ID = I.SALESORDERITEMID
inner join
dbo.SALESORDERITEMEVENTREGISTRATION ER on ER.ID = SOI.ID
inner join
dbo.REGISTRANT R on R.ID = ER.REGISTRANTID
inner join
dbo.EVENT E on E.ID = R.EVENTID
where
E.DESIGNATIONSONFEES = 1;
insert into dbo.FINANCIALTRANSACTIONLINEITEM(
ID
,FINANCIALTRANSACTIONID
,UNITVALUE
,QUANTITY
,DESCRIPTION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,POSTDATE
,POSTSTATUSCODE
,TYPECODE
,SOURCELINEITEMID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
I.ID
,I.CREDITID
,I.PRICE
,I.QUANTITY
,I.DESCRIPTION
,isnull(TOTALAMOUNT, (I.PRICE * I.QUANTITY) - isnull(I.DISCOUNTS, 0))
,isnull(TOTALAMOUNT, (I.PRICE * I.QUANTITY) - isnull(I.DISCOUNTS, 0))
,isnull(TOTALAMOUNT, (I.PRICE * I.QUANTITY) - isnull(I.DISCOUNTS, 0))
,FT.POSTDATE
,FT.POSTSTATUSCODE
,CASE I.TYPECODE
WHEN 3 then 7 --Fee
WHEN 4 then 8 --Tax
WHEN 5 then 5 --Discount
ELSE 0 end
,I.SOURCELINEITEMID
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @INS I
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = I.CREDITID;
insert into dbo.CREDITITEM_EXT(
ID
,CREDITID
,DISCOUNTS
,FEES
,GROUPID
,GROUPTYPECODE
,SALESORDERITEMID
,TYPECODE
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
I.ID
,I.CREDITID
,I.DISCOUNTS
,I.FEES
,I.GROUPID
,I.GROUPTYPECODE
,I.SALESORDERITEMID
,I.TYPECODE
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @INS I;
--Save to the credit item event registration structure
insert into dbo.CREDITITEMEVENTREGISTRATION
(
ID,
REGISTRANTID,
EVENTNAME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
ID, -- 2013-07-10 SW: @INS.ID is not the same as @CREDITITEMS.ID
GROUPID,
DESCRIPTION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @INS
end
--If there are extra-member type add-ons, we might need to drop some members.
if exists (
select
FINANCIALTRANSACTIONLINEITEM.ID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.MEMBERSHIPADDON
on MEMBERSHIPADDON.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
inner join dbo.ADDON
on ADDON.ID = MEMBERSHIPADDON.ADDONID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
and ADDON.ADDONTYPECODE = 1
) begin
declare @MEMBERSTODROP dbo.UDT_GENERICID;
--To figure out how many to drop, we have to calculate the total number of members allowed.
--That is the number of members the level allows, plus the number of extra member
--add-ons sold, minus previously refunded add-ons. Once we know this, we see if there's
--a difference between the number of members allowed and the current number of members.
--If there's no difference, we drop a number of members equal to the number of extra member
--add-ons we're refunding. If there is a difference, we take that into account and drop fewer.
--Note: we can assume that there will only ever be one membershiptransaction
--in the refund form for each membership
;with CTE_ADDONINFO as
(
select
MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID,
sum(FINANCIALTRANSACTIONLINEITEM.QUANTITY) as NUMBEROFADDONSREFUNDING,
(
select
count(MEMBER.ID)
from dbo.MEMBERSHIPTRANSACTION
inner join dbo.MEMBERSHIP
on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
inner join dbo.MEMBER
on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
where MEMBERSHIPTRANSACTION.ID = MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
and MEMBER.ISDROPPED = 0
) as TOTALNUMBEROFMEMBERS,
(
select
MEMBERSHIPLEVEL.MEMBERSALLOWED + sum(MA.QUANTITY)
from dbo.MEMBERSHIPTRANSACTION
inner join dbo.MEMBERSHIP
on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
inner join dbo.MEMBERSHIPLEVEL
on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIPADDON MA
on MA.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
inner join dbo.ADDON
on addon.ID = MA.ADDONID
where MEMBERSHIPTRANSACTION.ID = MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
and ADDON.ADDONTYPECODE = 1
group by MEMBERSHIPTRANSACTION.ID, MEMBERSHIPLEVEL.MEMBERSALLOWED
) as NUMBERMEMBERSALLOWED, --level + add-ons sold. We'll subtract out previously refunded ones in the next cte.
(
select
coalesce(sum(FINANCIALTRANSACTIONLINEITEM.QUANTITY), 0)
from dbo.MEMBERSHIPTRANSACTION
inner join dbo.MEMBERSHIPADDON MA
on MA.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
inner join dbo.ADDON
on addon.ID = MA.ADDONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = MA.REVENUESPLITID
inner join dbo.FINANCIALTRANSACTION FT
on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FT.ID
inner join dbo.CREDITITEM_EXT
on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
where MEMBERSHIPTRANSACTION.ID = MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
and ADDON.ADDONTYPECODE = 1
and FT.TYPECODE = 23 --refund
and CREDITITEM_EXT.TYPECODE = 16 --membershipaddon
and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID <> @ID
) as NUMBERPREVIOUSLYREFUNDED
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.id
inner join dbo.MEMBERSHIPADDON
on FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = MEMBERSHIPADDON.REVENUESPLITID
inner join dbo.ADDON
on ADDON.ID = MEMBERSHIPADDON.ADDONID
where FINANCIALTRANSACTION.ID = @ID
and ADDON.ADDONTYPECODE = 1
group by MEMBERSHIPTRANSACTIONID, MEMBERSHIPADDON.MEMBERSHIPID
),
CTE_MEMBERINFO as
(
select
MEMBER.ID as MEMBERID,
row_number() over (partition by MEMBERSHIPTRANSACTION.ID order by MEMBER.DATEADDED desc) as ROWNUMBER,
ADDONINFO.NUMBEROFADDONSREFUNDING - (ADDONINFO.NUMBERMEMBERSALLOWED - ADDONINFO.NUMBERPREVIOUSLYREFUNDED - ADDONINFO.TOTALNUMBEROFMEMBERS) as NUMBERTODROP
from CTE_ADDONINFO as ADDONINFO
inner join dbo.MEMBERSHIPTRANSACTION
on MEMBERSHIPTRANSACTION.ID = addoninfo.MEMBERSHIPTRANSACTIONID
inner join dbo.MEMBER
on member.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
where MEMBER.ISPRIMARY = 0
and MEMBER.ISDROPPED = 0
)
insert into @MEMBERSTODROP (ID)
select MEMBERINFO.MEMBERID
from CTE_MEMBERINFO MEMBERINFO
where MEMBERINFO.ROWNUMBER <= MEMBERINFO.NUMBERTODROP;
update dbo.MEMBER set
ISDROPPED = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID in (select ID from @MEMBERSTODROP);
update dbo.MEMBERSHIPCARD set
STATUSCODE = 2, -- Cancelled
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
MEMBERID in (select ID from @MEMBERSTODROP)
and STATUSCODE <> 2; -- Cancelled
end
if @ALLOWGLDISTRIBUTIONS=1 and isnull(@POSTSTATUSCODE, 1) != 3 begin
--create GL Distributions
exec dbo.USP_REFUND_CREATEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE
exec dbo.USP_REFUND_CREATEDISCOUNTGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE
end
--Refunding memberships. This happens after GL since refunding the membership might delete the last membership transaction and that transaction is the path to the revenue split for sales order item memberships
if @CONTEXTTYPE = 0 begin
--Refund all memberships in the items grid
declare @SALESORDERITEMMEMBERSHIPID uniqueidentifier;
declare @MEMBERSHIPTRANSACTIONID uniqueidentifier
declare memberships_cursor cursor LOCAL FAST_FORWARD for
select [SALESORDERITEMMEMBERSHIP].[ID]
from @CREDITITEMS [CREDITITEMS]
inner join dbo.[SALESORDERITEMMEMBERSHIP]
on [CREDITITEMS].[SALESORDERITEMID] = [SALESORDERITEMMEMBERSHIP].[ID];
open memberships_cursor
fetch next from memberships_cursor
into @SALESORDERITEMMEMBERSHIPID
while @@FETCH_STATUS = 0 begin
select
@MEMBERSHIPID = [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID],
@MEMBERSHIPTRANSACTIONID = [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID]
from dbo.[SALESORDERITEMMEMBERSHIP]
where [SALESORDERITEMMEMBERSHIP].[ID] = @SALESORDERITEMMEMBERSHIPID
set @LATESTTRANSACTIONID =
(select top(1)
[ID]
from dbo.[MEMBERSHIPTRANSACTION]
where [MEMBERSHIPID] = @MEMBERSHIPID order by [DATEADDED] desc)
-- Before 6.14.10 ... The membership was $0 and no split was created for it
if @MEMBERSHIPTRANSACTIONID is null begin
--See if the last membership transaction was a drop. If it wasn't a drop and it was also for $0, assume the last one is the one for this order
--Worst that can happen: We cancel a membership the patron wanted to cancel anyway, but we don't mark the sales order item that was used in the last transaction as having been refunded
if exists (
select 1
from dbo.[MEMBERSHIPTRANSACTION]
where
[MEMBERSHIPTRANSACTION].[ID] = @LATESTTRANSACTIONID and
[MEMBERSHIPTRANSACTION].[ACTIONCODE] <> 4 and
[MEMBERSHIPTRANSACTION].[REVENUESPLITID] is null
) begin
set @MEMBERSHIPTRANSACTIONID = @LATESTTRANSACTIONID
end
end
--End no membership transaction handling (pre-6.14.10)
--Error if there are any later membership transactions
if @MEMBERSHIPTRANSACTIONID is null or (@MEMBERSHIPTRANSACTIONID <> @LATESTTRANSACTIONID) begin
raiserror('ERR_ITEMS_ORDERREFUND_HASNEWERMEMBERSHIPTRANSACTIONS', 13, 1);
end
exec dbo.USP_CREDIT_REFUNDMEMBERSHIP @ID = @MEMBERSHIPID, @CHANGEAGENTID = @CHANGEAGENTID, @MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID
fetch next from memberships_cursor
into @SALESORDERITEMMEMBERSHIPID
end
close memberships_cursor
deallocate memberships_cursor
end
else if @CONTEXTTYPE = 1 and @MEMBERSHIPREFUNDHASMEMBERSHIP = 1 begin
set @MEMBERSHIPID = (select [MEMBERSHIPID] from dbo.[MEMBERSHIPTRANSACTION] where ID = @TRANSACTIONID)
exec dbo.USP_CREDIT_REFUNDMEMBERSHIP @ID = @MEMBERSHIPID, @CHANGEAGENTID = @CHANGEAGENTID, @MEMBERSHIPTRANSACTIONID = @TRANSACTIONID
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;