USP_CREDIT_REFUNDSALESORDERITEMS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ORDERID | uniqueidentifier | IN | |
| @ITEMS | xml | IN | |
| @TAXES | xml | IN | |
| @REFUNDMETHODS | xml | IN | |
| @COMMENT | nvarchar(500) | IN | |
| @CREDITREASONCODEID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @TOTAL | money | IN | |
| @TIMESTAMP | bigint | IN | |
| @ADDRESSID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_CREDIT_REFUNDSALESORDERITEMS
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@ORDERID uniqueidentifier,
@ITEMS xml = null,
@TAXES xml = null,
@REFUNDMETHODS xml = null,
@COMMENT nvarchar(500) = null,
@CREDITREASONCODEID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@TOTAL money = null, -- OBSOLETE - Maximum refundable amount (i.e. total value of the order including discounts).
@TIMESTAMP bigint = null,
@ADDRESSID uniqueidentifier = null
)
as
begin
set nocount on;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime = getdate();
declare @POSTSTATUSCODE tinyint;
--Make sure nobody else has done a refund since the form was opened.
if @TIMESTAMP <> coalesce((select TSLONG from dbo.SALESORDER where ID = @ORDERID), -1) begin
if @ORDERID is null or @ORDERID = '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 = @ORDERID;
select @POSTSTATUSCODE = FT.POSTSTATUSCODE
from dbo.SALESORDER SO
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = SO.REVENUEID
where SO.ID = @ORDERID;
end
-- Get refund items from input parameter @ITEMS
declare @CREDITITEMS table(
ID uniqueidentifier,
SALESORDERITEMID uniqueidentifier,
PRICE money,
ITEMDISCOUNTS money, -- Item-level discounts being reversed for a particular item (not included in the @ITEMS xml).
ORDERDISCOUNTS money, -- Order-level discounts being reversed for a particular item (not included in the @ITEMS xml).
AMOUNTPAID money,
AMOUNTTOREFUND money, -- This will be the BASEAMOUNT on the refund line item.
TYPECODE tinyint,
[DESCRIPTION] nvarchar(255),
EVENTREGISTRANTID uniqueidentifier,
ITEMID uniqueidentifier, -- membership transaction ID, membership addon ID, or itemized item ID (ticket/merch unit/item-level fee)
REVENUESPLITID uniqueidentifier,
CONTRIBUTEDREVENUE money
);
insert into @CREDITITEMS
select
newid(),
T.item.value('(SALESORDERITEMID)[1]','uniqueidentifier') SALESORDERITEMID,
T.item.value('(PRICE)[1]','money') PRICE,
0 ITEMDISCOUNTS,
0 ORDERDISCOUNTS,
T.item.value('(AMOUNTPAID)[1]','money') AMOUNTPAID,
T.item.value('(AMOUNTTOREFUND)[1]','money') AMOUNTTOREFUND,
T.item.value('(TYPECODE)[1]','tinyint') TYPECODE,
(select [DESCRIPTION] from dbo.SALESORDERITEM where ID = T.item.value('(SALESORDERITEMID)[1]','uniqueidentifier')) [DESCRIPTION],
case
when T.item.value('(EVENTREGISTRANTID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
then null
else
T.item.value('(EVENTREGISTRANTID)[1]','uniqueidentifier')
end EVENTREGISTRANTID,
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,
0 CONTRIBUTEDREVENUE
from @ITEMS.nodes('/ITEMS/ITEM') T(item)
where T.item.value('(INCLUDE)[1]','bit') = 1;
declare @TAXTABLE table (
TAXID uniqueidentifier, -- actually refers to SALESORDERITEM, not TAX.
[DESCRIPTION] nvarchar(200),
REVENUESPLITID uniqueidentifier,
CURRENTTAX money -- amount of tax to refund
);
insert into @TAXTABLE
select
T.item.value('(TAXID)[1]','uniqueidentifier'),
(select [DESCRIPTION] from dbo.SALESORDERITEM where ID = T.item.value('(TAXID)[1]','uniqueidentifier')),
T.item.value('(REVENUESPLITID)[1]','uniqueidentifier'),
round(T.item.value('(CURRENTTAX)[1]','money'), 2)
from @TAXES.nodes('/TAXES/ITEM') T(item)
where T.item.value('(CURRENTTAX)[1]','money') > 0;
declare @REFUNDEDITEMTOTAL money = (
(
select sum(case when TYPECODE = 5 then 0 else AMOUNTTOREFUND end)
from @CREDITITEMS
)
+ coalesce((select sum(CURRENTTAX) from @TAXTABLE),0)
);
-- As of 2013/09/20, we have decided that order-level discounts will be reversed only if everything they apply to is fully refunded.
-- In other words, if there is a discounted item that is *not* fully refunded, then we *do not* reverse order discounts.
declare @SHOULDREVERSEORDERDISCOUNTS bit = case
when (
-- the number of discounted ticket and merch items that are being fully refunded in this transaction
select count(ID)
from @CREDITITEMS CREDITITEMS
where TYPECODE in (0,14) -- Only tickets and merch can have order-level discounts (although merch might not)
and AMOUNTPAID < PRICE -- Ticket or merch item was discounted => order-level discount was applicable (this excludes non-discountable merch)
and AMOUNTTOREFUND = AMOUNTPAID -- fully refunding the item (because if any like this exist, we do NOT reverse the order-level discount)
) = (
-- the number of discounted ticket and merch items on the entire order
select count(TICKET.ID) + count(MERCH.ID)
from dbo.SALESORDERITEM SOI
left join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SOI.ID
left join dbo.SALESORDERITEMMERCHANDISEUNIT MERCH on MERCH.SALESORDERITEMMERCHANDISEID = SOI.ID
where SOI.SALESORDERID = @ORDERID
and (
coalesce(TICKET.ORDERLEVELDISCOUNTSAPPLIED, MERCH.ORDERLEVELDISCOUNTSAPPLIED) > 0
or coalesce(TICKET.ITEMLEVELDISCOUNTSAPPLIED, MERCH.ITEMLEVELDISCOUNTSAPPLIED) > 0
)
) then 1
else 0
end;
-- Determine which item-level discounts will be reversed (only if the full amount paid for the ticket/merch unit is being refunded).
update @CREDITITEMS
set
ITEMDISCOUNTS = TICKET.ITEMLEVELDISCOUNTSAPPLIED,
ORDERDISCOUNTS = case when @SHOULDREVERSEORDERDISCOUNTS = 1 then TICKET.ORDERLEVELDISCOUNTSAPPLIED else 0 end
from @CREDITITEMS CREDITITEMS
inner join dbo.TICKET on TICKET.ID = CREDITITEMS.ITEMID
where TICKET.AMOUNTPAID = CREDITITEMS.AMOUNTTOREFUND;
update @CREDITITEMS
set
ITEMDISCOUNTS = MERCHANDISE.ITEMLEVELDISCOUNTSAPPLIED,
ORDERDISCOUNTS = case when @SHOULDREVERSEORDERDISCOUNTS = 1 then MERCHANDISE.ORDERLEVELDISCOUNTSAPPLIED else 0 end
from @CREDITITEMS CREDITITEMS
inner join dbo.SALESORDERITEMMERCHANDISEUNIT MERCHANDISE on MERCHANDISE.ID = CREDITITEMS.ITEMID
where MERCHANDISE.AMOUNTPAID = CREDITITEMS.AMOUNTTOREFUND;
update @CREDITITEMS
set
ITEMDISCOUNTS = PROMO.AMOUNT
from @CREDITITEMS CREDITITEMS
inner join dbo.SALESORDERITEM MEMBERSHIPITEM on MEMBERSHIPITEM.ID = CREDITITEMS.SALESORDERITEMID
inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION PROMO on PROMO.SALESORDERITEMID = MEMBERSHIPITEM.ID
where MEMBERSHIPITEM.PRICE = CREDITITEMS.AMOUNTTOREFUND + PROMO.AMOUNT;
/******** Validate refund items and refund payments *******/
-- Error if no items were selected
if not exists (select 1 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
-- -- (original quantity - already refunded quantity - quantity we're refunding right now)
if exists(
select SALESORDERITEM.ID
from @CREDITITEMS CREDITITEMS
inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = CREDITITEMS.SALESORDERITEMID
left join dbo.CREDITITEM_EXT on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = CREDITITEM_EXT.ID
where
not CREDITITEMS.TYPECODE in (0,3,14) -- tickets, fees, and merch items each have their own ITEMID, which will be validated roughly 30 lines below
group by SALESORDERITEM.ID, SALESORDERITEM.QUANTITY
having SALESORDERITEM.QUANTITY - coalesce(sum(FTLI.QUANTITY),0) - count(CREDITITEMS.ID) < 0
) begin
raiserror('ERR_ITEMS_EXCEEDSQUANTITYAVAILABLE', 13, 1);
return 1;
end
-- Error if the amount refunding is less than zero for any of the items
if exists (select 1 from @CREDITITEMS where AMOUNTTOREFUND < 0) begin
raiserror('ERR_ITEMS_NEGATIVEAMOUNT', 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) <> @REFUNDEDITEMTOTAL begin
raiserror('ERR_REFUNDMETHODS_DIFFERENTFROMTOTALAMOUNT', 13, 1);
return 1;
end
-- Error if the refund amount is greater than the total payment amount remaining
if @REFUNDEDITEMTOTAL > (
select
FINANCIALTRANSACTION.BASEAMOUNT - coalesce((
select sum(coalesce(CREDITPAYMENT.AMOUNT, 0)) from dbo.CREDITPAYMENT where REVENUEID = FINANCIALTRANSACTION.ID
),0) -- (original amount - refunded amount)
from dbo.SALESORDER
inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
inner join dbo.FINANCIALTRANSACTION on SALESORDERPAYMENT.PAYMENTID = FINANCIALTRANSACTION.ID
where SALESORDER.ID = @ORDERID
)
begin
raiserror('ERR_REFUNDAMOUNT_EXCEEDSLIMIT', 13, 1);
return 1;
end
-- Error if an item has already been refunded
if exists (
select 1 from @CREDITITEMS ITEMS
inner join dbo.CREDITITEM_EXT on ITEMS.ITEMID = CREDITITEM_EXT.SALESORDERITEMIZEDITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = CREDITITEM_EXT.ID
where FTLI.BASEAMOUNT > 0
)
begin
raiserror('ERR_ITEMALREADYREFUNDED', 13, 1);
return 1;
end
-- Error if an itemized item has no ITEMID
if exists (
select 1
from @CREDITITEMS ITEMS
where TYPECODE in (0,14) -- ticket, merch
and ITEMID is null
union all
-- item-level fees
select 1
from @CREDITITEMS ITEMS
inner join dbo.SALESORDERITEMFEE FEEITEM on FEEITEM.ID = ITEMS.SALESORDERITEMID
where FEEITEM.APPLIESTOCODE = 1 -- item-level
and ITEMS.ITEMID is null
)
begin
raiserror('ERR_ITEMWITHOUTITEMID', 13, 1);
return 1;
end
-- Error if there are any later membership transactions
if exists (
select 1 from @CREDITITEMS MEMBERSHIPITEM
inner join dbo.MEMBERSHIPTRANSACTION REFUNDINGTRANSACTION on REFUNDINGTRANSACTION.ID = MEMBERSHIPITEM.ITEMID
outer apply (
select top 1 ID
from dbo.MEMBERSHIPTRANSACTION
where MEMBERSHIPID = REFUNDINGTRANSACTION.MEMBERSHIPID
order by DATEADDED desc
) LATESTTRANSACTION
where MEMBERSHIPITEM.ITEMID <> LATESTTRANSACTION.ID
)
begin
raiserror('ERR_ITEMS_MEMBERSHIPREFUND_HASNEWERMEMBERSHIPTRANSACTIONS', 13, 1);
return 1;
end
-- Error if there are memberships with contributed revenue that are not being fully refunded
if exists (
select 1 from @CREDITITEMS MEMBERSHIPITEM
inner join dbo.MEMBERSHIPTRANSACTION REFUNDINGTRANSACTION on REFUNDINGTRANSACTION.ID = MEMBERSHIPITEM.ITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPLI on MEMBERSHIPLI.ID = REFUNDINGTRANSACTION.REVENUESPLITID
inner join dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDLI on CONTRIBUTEDLI.SOURCELINEITEMID = MEMBERSHIPLI.ID
inner join dbo.REVENUESPLIT_EXT EXT
on (EXT.ID = CONTRIBUTEDLI.ID and EXT.TYPECODE = 0 and EXT.APPLICATIONCODE = 0 and CONTRIBUTEDLI.TYPECODE = 0) -- Standard donation line item whose source is the membership line item
group by MEMBERSHIPLI.ID, MEMBERSHIPLI.BASEAMOUNT, MEMBERSHIPITEM.AMOUNTTOREFUND, MEMBERSHIPITEM.ITEMDISCOUNTS
having MEMBERSHIPLI.BASEAMOUNT + sum(CONTRIBUTEDLI.BASEAMOUNT) <> MEMBERSHIPITEM.AMOUNTTOREFUND + MEMBERSHIPITEM.ITEMDISCOUNTS
)
begin
raiserror('ERR_ITEMS_MEMBERSHIPPARTIALREFUND_HASCONTRIBUTEDREVENUE', 13, 1);
return 1;
end
-- Error if there are combination tickets that are not being fully refunded
-- TODO: verify
if exists (
select 1
from @CREDITITEMS COMBOITEMS
inner join dbo.SALESORDERITEMTICKET TICKETS on COMBOITEMS.SALESORDERITEMID = TICKETS.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION COMBO on TICKETS.ID = COMBO.ID
where COMBOITEMS.AMOUNTPAID <> COMBOITEMS.AMOUNTTOREFUND
)
begin
raiserror('ERR_ITEMS_COMBINATIONTICKETPARTIALREFUND', 13, 1);
return 1;
end
-- Verify that the credit card we want to refund to has actually been processed
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);
-- TODO: this does not currently work!
-- If there are combination items to refund, get the same type of the tickets of all the programs in the combination.
-- (the item that is actually displayed to the user only represents a single ticket from the combo, but all need to be refunded.)
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 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
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
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,
SALESORDERITEM.PRICE,
0 as ITEMDISCOUNTS,
0 as ORDERDISCOUNTS,
0 AMOUNTPAID,
SALESORDERITEM.PRICE as AMOUNTTOREFUND,
0 as TYPECODE,
SALESORDERITEM.[DESCRIPTION],
null as EVENTREGISTRANTID,
null as ITEMID,
LI.ID as REVENUESPLITID,
0 CONTRIBUTEDREVENUE
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 -- Donation
)
begin
-- Error if there are 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
-- Recognition credits have no significant financial implications, so we'll just cut them by the same percentage as the donation.
declare @DONATIONREFUNDS table (REVENUESPLITID uniqueidentifier, PERCENTREFUNDED real);
insert into @DONATIONREFUNDS
select
SALESORDERITEMDONATION.REVENUESPLITID,
case
when SALESORDERITEMDONATION.AMOUNT = 0.0 or CREDITITEMS.AMOUNTTOREFUND = SALESORDERITEMDONATION.AMOUNT
then 1.0
else CREDITITEMS.AMOUNTTOREFUND / SALESORDERITEMDONATION.AMOUNT
end
from dbo.SALESORDERITEMDONATION
inner join @CREDITITEMS CREDITITEMS on CREDITITEMS.SALESORDERITEMID = SALESORDERITEMDONATION.ID
where CREDITITEMS.TYPECODE = 2;
-- Delete if we're refunding the entire donation
delete from dbo.REVENUERECOGNITION
where REVENUERECOGNITION.REVENUESPLITID in
(
select ITEMS.REVENUESPLITID
from @DONATIONREFUNDS ITEMS
where PERCENTREFUNDED = 1.0
);
-- Reduce if we're only refunding part of the donation.
update dbo.REVENUERECOGNITION
set AMOUNT = AMOUNT * (1.0 - DONATIONREFUNDS.PERCENTREFUNDED)
from dbo.REVENUERECOGNITION
inner join @DONATIONREFUNDS DONATIONREFUNDS on REVENUERECOGNITION.REVENUESPLITID = DONATIONREFUNDS.REVENUESPLITID
where DONATIONREFUNDS.PERCENTREFUNDED <> 1.0;
end
--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 -- Addons that were sold as part of the transaction we're refunding
left join @CREDITITEMS CREDITADDONS on CREDITADDONS.ITEMID = MEMBERSHIPADDON.ID -- Addons we're refunding right now
left join
(
select FTLI.SOURCELINEITEMID, FTLI.QUANTITY
from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
inner join dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = FTLI.ID
where CREDITITEM_EXT.TYPECODE = 16 -- refunded membership addon
) PREVIOUSLYREFUNDEDADDONREFUNDITEMS
on PREVIOUSLYREFUNDEDADDONREFUNDITEMS.SOURCELINEITEMID = MEMBERSHIPADDON.REVENUESPLITID
where MEMBERSHIPITEM.TYPECODE = 1
group by
MEMBERSHIPADDON.QUANTITY,
MEMBERSHIPITEM.ID,
MEMBERSHIPADDON.ID
having (coalesce(count(CREDITADDONS.ID),0) + coalesce(sum(PREVIOUSLYREFUNDEDADDONREFUNDITEMS.QUANTITY),0) <> MEMBERSHIPADDON.QUANTITY)
)
begin
raiserror('BBERR_ITEMS_REFUNDINGMEMBERSHIPWITHOUTADDONS', 16, 1);
return 1;
end
declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);
-- Bug #167166 - MDC
declare @TRANSACTIONDATE datetime = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());
begin try
declare @CONSTITUENTID uniqueidentifier = (select CONSTITUENTID from dbo.SALESORDER where ID = @ORDERID);
-- handle inserting the data
declare @CURRENCYID uniqueidentifier = (select ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1);
declare @PDACCOUNTSYSTEMID uniqueidentifier;
declare @ALLOWGLDISTRIBUTIONS bit;
select
@PDACCOUNTSYSTEMID = ID,
@ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS
from
dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();
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
,@REFUNDEDITEMTOTAL
,@REFUNDEDITEMTOTAL
,@REFUNDEDITEMTOTAL
,@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 = @ORDERID)
,@CURRENTAPPUSERID
,@CONSTITUENTID
,@CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
);
insert into dbo.CREDIT_EXT
(
ID
,SALESORDERID
,CREDITREASONCODEID
---- Boilerplate
,DATEADDED ,DATECHANGED ,ADDEDBYID ,CHANGEDBYID
)
values
(
@ID
,@ORDERID
,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;
begin
-- This section handles memberships and event registrations with contributed revenue.
-- The gist of what's about to happen is:
-- 1. Find the contributory line items (donation line items whose sourcelineitemid is the revenuesplitid of an item we're trying to refund).
-- 2. For discounted memberships, prorate the discount across the contributory line items and subtract it from the membership line item.
-- 3. Subtract the contributory amount from the amount to refund of the membership or event registration item.
declare @CONTRIBUTEDREVENUE table (
CONTRIBUTEDLINEITEMID uniqueidentifier,
SOURCELINEITEMID uniqueidentifier,
TYPECODE tinyint,
[DESCRIPTION] nvarchar(200),
ORIGINALAMOUNT money,
DISCOUNTS money
);
-- Find contributed revenue (for memberships and event registrations).
insert into @CONTRIBUTEDREVENUE
select
CONTRIBUTEDLI.ID CONTRIBUTEDLINEITEMID,
CONTRIBUTEDLI.SOURCELINEITEMID,
case
when CONTRIBUTEDLI_EXT.APPLICATIONCODE = 1 then 6
else 2
end TYPECODE,
CONTRIBUTEDLI.[DESCRIPTION],
CONTRIBUTEDLI.BASEAMOUNT ORIGINALAMOUNT,
null DISCOUNTS
from @CREDITITEMS CREDITITEMS
inner join dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDLI on CONTRIBUTEDLI.SOURCELINEITEMID = CREDITITEMS.REVENUESPLITID
inner join dbo.REVENUESPLIT_EXT CONTRIBUTEDLI_EXT on CONTRIBUTEDLI_EXT.ID = CONTRIBUTEDLI.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on PAYMENTLI.SOURCELINEITEMID = CONTRIBUTEDLI.ID
inner join dbo.FINANCIALTRANSACTION PAYMENTFT on PAYMENTFT.ID = PAYMENTLI.FINANCIALTRANSACTIONID
where
CONTRIBUTEDLI_EXT.TYPECODE = 0
and CONTRIBUTEDLI_EXT.APPLICATIONCODE in (0,1)
and CONTRIBUTEDLI.TYPECODE = 0
and PAYMENTFT.TYPECODE = 0;
if exists (select 1 from @CONTRIBUTEDREVENUE)
begin
-- Set the contributed amount on items with partially contributed revenue.
update @CREDITITEMS
set CONTRIBUTEDREVENUE = coalesce((select sum(ORIGINALAMOUNT) from @CONTRIBUTEDREVENUE where SOURCELINEITEMID = CI.REVENUESPLITID),0)
from @CREDITITEMS CI;
-- Prorate item-level discounts across contributed items.
-- This could be more efficient (when discount is more than contributed amount, no proration is necessary, because the full contributory amount is discounted), but that's not important.
update @CONTRIBUTEDREVENUE
set DISCOUNTS = PRORATEDAMOUNTS.AMOUNT
from @CREDITITEMS CREDITITEMS
cross apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
CREDITITEMS.CONTRIBUTEDREVENUE,
(select case when CREDITITEMS.ITEMDISCOUNTS > CREDITITEMS.CONTRIBUTEDREVENUE then CREDITITEMS.CONTRIBUTEDREVENUE else CREDITITEMS.ITEMDISCOUNTS end),
2,
(
select CONTRIBUTEDREVENUE.CONTRIBUTEDLINEITEMID ID, CONTRIBUTEDREVENUE.ORIGINALAMOUNT AMOUNT
from @CONTRIBUTEDREVENUE CONTRIBUTEDREVENUE
where CONTRIBUTEDREVENUE.SOURCELINEITEMID = CREDITITEMS.REVENUESPLITID
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
)
) PRORATEDAMOUNTS
where PRORATEDAMOUNTS.ID = CONTRIBUTEDLINEITEMID;
-- Reduce the discount amount for partially contributed items (since the discounts have been moved to the contributory line items).
-- Also reduce the refund amount to account for discounts in excess of the contributory revenue.
update @CREDITITEMS
set
AMOUNTTOREFUND = case when CONTRIBUTEDREVENUE > ITEMDISCOUNTS then PRICE - CONTRIBUTEDREVENUE else AMOUNTTOREFUND end,
ITEMDISCOUNTS = case when CONTRIBUTEDREVENUE > ITEMDISCOUNTS then 0 else ITEMDISCOUNTS - CONTRIBUTEDREVENUE end
where CONTRIBUTEDREVENUE > 0;
-- At this point:
-- -- 1. The ITEMDISCOUNTS of a partially contributed credit item will be max(0, discounted amount - contributed amount)
-- -- 2. The AMOUNTTOREFUND of a partially contributed credit item will be PRICE - max(contributed amount, discounted amount).
end
end
declare @INS table
(
ID uniqueidentifier
,CREDITID uniqueidentifier
,SALESORDERITEMID uniqueidentifier
,PRICE money
,DISCOUNTS money
,TYPECODE tinyint
,[DESCRIPTION] nvarchar(700)
,GROUPID uniqueidentifier
,GROUPTYPECODE tinyint
,SOURCELINEITEMID uniqueidentifier
,REFUNDAMOUNT money
,UNITVALUE money
,ITEMID uniqueidentifier
);
-- This may not be the most intuitive data set-up in the world (particularly the roles that UNITVALUE and DISCOUNTS play in GL creation).
-- However, the alternatives are breaking backward-compatibility of refund data or converting all existing refunds into something that makes more sense,
-- neither of which we have time to do right now.
insert into @INS
(
ID
,CREDITID
,SALESORDERITEMID
,PRICE
,DISCOUNTS
,TYPECODE
,[DESCRIPTION]
,GROUPID
,GROUPTYPECODE
,SOURCELINEITEMID
,REFUNDAMOUNT
,UNITVALUE
,ITEMID
)
select
ID,
@ID,
SALESORDERITEMID,
PRICE,
ITEMDISCOUNTS,
TYPECODE,
[DESCRIPTION],
EVENTREGISTRANTID,
case when TYPECODE = 6 then 1 else 0 end GROUPTYPECODE,
REVENUESPLITID,
AMOUNTTOREFUND + ORDERDISCOUNTS, -- Refund amount needs to include order discounts (reversed in separate line item)
AMOUNTTOREFUND + CONTRIBUTEDREVENUE + ITEMDISCOUNTS + ORDERDISCOUNTS, -- Unit value needs to include discounts and contributed revenue (for GL generation)
ITEMID
from @CREDITITEMS
union all
--taxes
select
newid(),
@ID,
TAXES.TAXID,
TAXES.CURRENTTAX PRICE,
0 DISCOUNTS,
4 TYPECODE,
(select [DESCRIPTION] from dbo.SALESORDERITEM where ID = TAXES.TAXID),
null GROUPID,
0 GROUPTYPECODE,
TAXES.REVENUESPLITID,
TAXES.CURRENTTAX,
TAXES.CURRENTTAX UNITVALUE,
null ITEMID
from @TAXTABLE TAXES
union all
-- Contributory portions of memberships and event registrations
select
newid(),
@ID,
null SALESORDERITEMID,
ORIGINALAMOUNT PRICE,
DISCOUNTS,
TYPECODE,
[DESCRIPTION],
null GROUPID,
0 GROUPTYPECODE,
CONTRIBUTEDLINEITEMID,
ORIGINALAMOUNT - DISCOUNTS REFUNDAMOUNT,
ORIGINALAMOUNT UNITVALUE,
null ITEMID
from @CONTRIBUTEDREVENUE;
-- TODO: verify that this is still correct behavior (I think it is accounted for above with DISCOUNTS = 0, but not sure)
--earned income needs to reflect the FTLI table entry, not the SOI table entry for events that have designations on fees.
update @INS
set REFUNDAMOUNT = (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.UNITVALUE
,1 QUANTITY
,I.[DESCRIPTION]
,I.REFUNDAMOUNT
,I.REFUNDAMOUNT
,I.REFUNDAMOUNT
,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
,SALESORDERITEMIZEDITEMID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
ID
,CREDITID
,DISCOUNTS
,0 FEES
,GROUPID
,GROUPTYPECODE
,SALESORDERITEMID
,TYPECODE
,case when TYPECODE in (1,16) then null else ITEMID end -- Membership and Add-on are not itemized
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @INS;
-- Update ticket status for fully refunded tickets
update dbo.TICKET
set TICKET.STATUSCODE = 2
where ID in (select ITEMID from @CREDITITEMS where TYPECODE = 0 and AMOUNTTOREFUND = AMOUNTPAID);
-- Update ticket applied to membership fields for tickets that were applied to a fully refunded membership
-- TODO: verify that applied ticket discount is reversed when partially contributory membership is refunded.
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 MEMBERSHIPCREDITITEMS on PROMOTION.SALESORDERITEMID = MEMBERSHIPCREDITITEMS.SALESORDERITEMID
where MEMBERSHIPCREDITITEMS.AMOUNTTOREFUND = (MEMBERSHIPCREDITITEMS.AMOUNTPAID - MEMBERSHIPCREDITITEMS.CONTRIBUTEDREVENUE);
-- Update on hand quantity for returned merchandise items
with MERCHANDISEREFUNDS as (
select
SOIM.MERCHANDISEPRODUCTINSTANCEID,
count(CI.ID) QUANTITYREFUNDING
from @CREDITITEMS CI
inner join dbo.SALESORDERITEMMERCHANDISE SOIM on SOIM.ID = CI.SALESORDERITEMID
where CI.AMOUNTTOREFUND = CI.AMOUNTPAID
group by SOIM.MERCHANDISEPRODUCTINSTANCEID
)
update dbo.MERCHANDISEPRODUCTINSTANCE
set ONHANDQUANTITY += MERCHANDISEREFUNDS.QUANTITYREFUNDING
from dbo.MERCHANDISEPRODUCTINSTANCE
inner join MERCHANDISEREFUNDS on MERCHANDISEREFUNDS.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID;
-- Update refunded amount for returned merchandise item
update dbo.SALESORDERITEMMERCHANDISEUNIT
set REFUNDEDAMOUNT = coalesce(REFUNDEDAMOUNT,0) + ITEMS.AMOUNTTOREFUND
from dbo.SALESORDERITEMMERCHANDISEUNIT
inner join @CREDITITEMS ITEMS on ITEMS.ITEMID = SALESORDERITEMMERCHANDISEUNIT.ID;
-- Update refunded amount for refunded ticket fees
update dbo.SALESORDERITEMTICKETFEE
set REFUNDEDAMOUNT = coalesce(REFUNDEDAMOUNT,0) + ITEMS.AMOUNTTOREFUND
from dbo.SALESORDERITEMTICKETFEE
inner join @CREDITITEMS ITEMS on ITEMS.ITEMID = SALESORDERITEMTICKETFEE.ID;
--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 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
-- Add item level discounts (via the DISCOUNTS column on the CREDITITEM_EXT records we already created)
insert into dbo.CREDITITEMITEMDISCOUNT
(
CREDITITEMID,
SALESORDERITEMITEMDISCOUNTID,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
FTLI.ID,
SALESORDERITEMITEMDISCOUNT.ID,
EXT.DISCOUNTS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.SALESORDERITEMITEMDISCOUNT
inner join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = EXT.ID
where FTLI.FINANCIALTRANSACTIONID = @ID
and EXT.DISCOUNTS > 0;
-- Add membership promotions (if the full amount paid for the membership is being returned)
insert into dbo.CREDITITEMMEMBERSHIPITEMPROMOTION
(
CREDITITEMID,
SALESORDERITEMMEMBERSHIPITEMPROMOTIONID,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select distinct
REFUNDEDMEMBERSHIPLI.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 REFUNDEDMEMBERSHIPLI on REFUNDEDMEMBERSHIPLI.ID = EXT.ID
where REFUNDEDMEMBERSHIPLI.FINANCIALTRANSACTIONID = @ID
and (
EXT.DISCOUNTS > 0 -- Unfortunately, this is 0 when the discount has moved to contributed revenue.
or exists ( -- Instead, link to the original thing we were trying to refund to determine whether we reversed discounts.
select 1
from @CONTRIBUTEDREVENUE CR
where CR.SOURCELINEITEMID = REFUNDEDMEMBERSHIPLI.SOURCELINEITEMID
and DISCOUNTS > 0
)
);
if @SHOULDREVERSEORDERDISCOUNTS = 1
begin
-- We need to store the ID's because we have two tables to insert into.
declare @ORDERDISCOUNTLINEITEMID uniqueidentifier = newid();
declare @ADJUSTABLEDISCOUNTLINEITEMID uniqueidentifier = newid();
insert into dbo.FINANCIALTRANSACTIONLINEITEM
(
ID
,FINANCIALTRANSACTIONID
,UNITVALUE
,QUANTITY
,[DESCRIPTION]
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,POSTDATE
,POSTSTATUSCODE
,SOURCELINEITEMID
,TYPECODE
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
case when ORDERDISCOUNT.ISADJUSTABLEDISCOUNT = 1 then @ADJUSTABLEDISCOUNTLINEITEMID else @ORDERDISCOUNTLINEITEMID end
,@ID
,-PRICE
,1
,[DESCRIPTION]
,-PRICE
,-PRICE
,-PRICE
,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
,null -- the original discount LI id has never been inserted here. Not changing yet.
,5
,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMORDERDISCOUNT ORDERDISCOUNT on ORDERDISCOUNT.ID = SALESORDERITEM.ID
where SALESORDERITEM.TYPECODE = 5
and SALESORDERITEM.SALESORDERID = @ORDERID;
insert into dbo.CREDITITEM_EXT
(
ID
,CREDITID
,DISCOUNTS
,FEES
,GROUPID
,GROUPTYPECODE
,SALESORDERITEMID
,TYPECODE
,SALESORDERITEMIZEDITEMID
,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
)
select
case when ORDERDISCOUNT.ISADJUSTABLEDISCOUNT = 1 then @ADJUSTABLEDISCOUNTLINEITEMID else @ORDERDISCOUNTLINEITEMID end
,@ID
,0
,0
,null
,0
,SALESORDERITEM.ID
,5
,null
,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMORDERDISCOUNT ORDERDISCOUNT on ORDERDISCOUNT.ID = SALESORDERITEM.ID
where SALESORDERITEM.TYPECODE = 5
and SALESORDERITEM.SALESORDERID = @ORDERID;
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
-- (number of members the level allows + number of extra member add-ons sold - previously refunded add-ons).
-- If there are now too many members, we drop as many as we need to.
--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(FTLI.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 FTLI on FTLI.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.MEMBERSHIPADDON on FTLI.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
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.
declare @SALESORDERITEMMEMBERSHIPID 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
exec dbo.USP_CREDIT_REFUNDSALESORDERITEMMEMBERSHIP @SALESORDERITEMMEMBERSHIPID, @CHANGEAGENTID;
fetch next from memberships_cursor
into @SALESORDERITEMMEMBERSHIPID;
end
--raiserror('blob', 13, 1);
close memberships_cursor;
deallocate memberships_cursor;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end