UFN_REFUND_GETPRORATEDSPLITS
Returns prorated splits for a supplied refund.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CREDITID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_REFUND_GETPRORATEDSPLITS(@CREDITID uniqueidentifier)
returns table as
return
with ORDERDISCOUNTS_CTE as (
select ID, AMOUNT
from dbo.UFN_SPLITS_GETPRORATEDSPLITS(
(
select sum(((LI.QUANTITY * LI.UNITVALUE) - [CREDITITEM].DISCOUNTS))
from dbo.CREDITITEM_EXT CREDITITEM
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM.ID
where
CREDITID = @CREDITID and
((LI.QUANTITY * LI.UNITVALUE) - [CREDITITEM].DISCOUNTS) > 0 and
exists (select 1
from dbo.[SALESORDERITEMORDERDISCOUNTDETAIL]
where [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [CREDITITEM].[SALESORDERITEMID])
),
(
select sum(abs((LI.QUANTITY * LI.UNITVALUE) - [CREDITITEM].DISCOUNTS))
from dbo.CREDITITEM_EXT CREDITITEM
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM.ID
inner join dbo.SALESORDERITEMORDERDISCOUNT on CREDITITEM.SALESORDERITEMID = SALESORDERITEMORDERDISCOUNT.ID
where CREDITITEM.CREDITID = @CREDITID
),
(
select distinct
B.ID as ID,
((LI.QUANTITY * LI.UNITVALUE) - B.DISCOUNTS) as AMOUNT
from dbo.CREDITITEM_EXT as B
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = B.ID
inner join dbo.[SALESORDERITEMORDERDISCOUNTDETAIL]
on [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [B].[SALESORDERITEMID]
where
B.CREDITID = @CREDITID
for xml raw('ITEM'),TYPE,ELEMENTS,root('AMOUNTSTOPRORATE'),binary base64
)
)
),
CONTRIBUTEDREVENUE_CTE as (
select
REFUNDMEMBERSHIPLI.ID,
sum(DONATIONLI.ORGAMOUNT) as AMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDMEMBERSHIPLI
inner join dbo.CREDITITEM_EXT REFUNDMEMBERSHIPEXT on REFUNDMEMBERSHIPEXT.ID = REFUNDMEMBERSHIPLI.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM MEMBERSHIPLI on MEMBERSHIPLI.ID = REFUNDMEMBERSHIPLI.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT MEMBERSHIPEXT on MEMBERSHIPEXT.ID = MEMBERSHIPLI.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM DONATIONLI on DONATIONLI.SOURCELINEITEMID = MEMBERSHIPLI.ID
inner join dbo.REVENUESPLIT_EXT DONATIONEXT on DONATIONEXT.ID = DONATIONLI.ID
where REFUNDMEMBERSHIPLI.FINANCIALTRANSACTIONID = @CREDITID
and (REFUNDMEMBERSHIPEXT.TYPECODE = 1 or REFUNDMEMBERSHIPEXT.TYPECODE = 6)
and ((MEMBERSHIPEXT.TYPECODE = 2 and MEMBERSHIPEXT.APPLICATIONCODE = 5) or (MEMBERSHIPEXT.TYPECODE = 1 and MEMBERSHIPEXT.APPLICATIONCODE = 1))
and ((DONATIONEXT.TYPECODE = 0 and DONATIONEXT.APPLICATIONCODE = 0) or (DONATIONEXT.TYPECODE = 0 and DONATIONEXT.APPLICATIONCODE = 1))
group by REFUNDMEMBERSHIPLI.ID
),
SOURCEITEMS_CTE as (
select distinct coalesce(SALESORDERITEMTICKET.PROGRAMID, [EVENT].PROGRAMID) as PROGRAMID,
case CREDITITEM.TYPECODE
when 1 then -- Membership
LI.SOURCELINEITEMID
else
null
end as MEMBERSHIPREVENUESPLITID,
SALESORDERITEMFEE.FEEID,
SALESORDERITEMTAX.TAXID,
COALESCE(SALESORDERITEMTICKET.EVENTID, REGISTRANT.EVENTID) AS EVENTID,
REGISTRANT.ID AS REGISTRANTID,
CREDITITEM.CREDITID,
CREDITITEM.ID as CREDITITEMID,
case
when ISNULL(CONTRIBUTEDREVENUE.AMOUNT, 0) > CREDITITEM.DISCOUNTS then --the discount is only taken out of the contributed revenue
CREDITITEMMINUSDICOUNT.TOTAL - CONTRIBUTEDREVENUE.AMOUNT
when SALESORDERITEM.ID is null and SOURCELIEXT.TYPECODE = 0 and SOURCELIEXT.APPLICATIONCODE = 0 then --this is the contributed revenue and we want it to be whatever the payment was
(select
sum(SOURCEPAYMENTLI.ORGAMOUNT)
from dbo.FINANCIALTRANSACTIONLINEITEM SOURCEPAYMENTLI
inner join dbo.FINANCIALTRANSACTION SOURCEPAYMENTFT on SOURCEPAYMENTFT.ID = SOURCEPAYMENTLI.FINANCIALTRANSACTIONID
where SOURCEPAYMENTLI.SOURCELINEITEMID = SOURCELIEXT.ID
and SOURCEPAYMENTFT.TYPECODE = 0)
else
[CREDITITEMMINUSDICOUNT].[TOTAL] - credititem.discounts
end TOTAL,
FT.TRANSACTIONAMOUNT as CREDITAMOUNT,
CREDIT_EXT.SALESORDERID,
SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPADDONID,
SOURCELIEXT.DESIGNATIONID,
LI.SOURCELINEITEMID
from dbo.CREDITITEM_EXT CREDITITEM
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
left join dbo.SALESORDERITEM ON SALESORDERITEM.ID = CREDITITEM.SALESORDERITEMID
left join dbo.SALESORDERITEMTICKET ON SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
left join dbo.SALESORDERITEMTAX ON SALESORDERITEM.ID = SALESORDERITEMTAX.TAXITEMID
left join dbo.SALESORDERITEMEVENTREGISTRATION ON SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
left join dbo.REGISTRANT ON SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
left join dbo.SALESORDERITEMFEE ON SALESORDERITEM.ID = SALESORDERITEMFEE.ID
left join dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEM.ID = SALESORDERITEMMERCHANDISE.ID
left join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.ID = SALESORDERITEM.ID
left join CONTRIBUTEDREVENUE_CTE CONTRIBUTEDREVENUE on CONTRIBUTEDREVENUE.ID = LI.ID
inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = CREDITITEM.CREDITID
inner join dbo.REVENUESPLIT_EXT SOURCELIEXT on SOURCELIEXT.ID = LI.SOURCELINEITEMID
cross apply (select (LI.QUANTITY * LI.UNITVALUE) - coalesce((select AMOUNT from ORDERDISCOUNTS_CTE OD where OD.ID = CREDITITEM.ID), 0) as [TOTAL]) [CREDITITEMMINUSDICOUNT]
where
CREDITITEM.CREDITID = @CREDITID and
[CREDITITEMMINUSDICOUNT].[TOTAL] > 0 and
CREDIT_EXT.SALESORDERID is not null
) ,
SOURCESPLITS_CTE as (
select
FTLI.ID as REVENUESPLITID,
FTLI.FINANCIALTRANSACTIONID,
REVENUESPLIT_EXT.TYPE +'-Order-'+ convert(nvarchar(50), SALESORDER.SEQUENCEID) as TYPE,
CREDITPAYMENT.AMOUNT as REFUNDPAYMENTTOTAL,
SUM(SOURCEITEMS.TOTAL) as REFUNDSPLITAMOUNT,
SOURCEITEMS.CREDITAMOUNT as CREDITAMOUNT,
CREDITPAYMENT.ID as CREDITPAYMENTID,
REVENUESPLIT_EXT.TYPECODE,
[CREDITPAYMENT].[PAYMENTMETHODCODE],
SOURCEITEMS.CREDITITEMID
from SOURCEITEMS_CTE AS SOURCEITEMS
inner join dbo.CREDITPAYMENT ON SOURCEITEMS.CREDITID = CREDITPAYMENT.CREDITID
inner join dbo.SALESORDER on SOURCEITEMS.SALESORDERID = SALESORDER.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on SALESORDER.REVENUEID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
inner join dbo.REVENUESPLITORDER on FTLI.ID = REVENUESPLITORDER.ID
left outer join dbo.EVENTREGISTRANTPAYMENT on FTLI.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
where
(
(REVENUESPLITORDER.PROGRAMID = SOURCEITEMS.PROGRAMID and REVENUESPLITORDER.EVENTID is null)
or (REVENUESPLITORDER.EVENTID = SOURCEITEMS.EVENTID and SOURCEITEMS.REGISTRANTID is null)
or (EVENTREGISTRANTPAYMENT.REGISTRANTID = SOURCEITEMS.REGISTRANTID and REVENUESPLIT_EXT.TYPECODE = 1)
)
--OR (REVENUESPLITORDER.MEMBERSHIPLEVELID = SOURCEITEMS.MEMBERSHIPLEVELID)
or (FTLI.ID = SOURCEITEMS.MEMBERSHIPREVENUESPLITID)
or (REVENUESPLITORDER.FEEID = SOURCEITEMS.FEEID)
or (REVENUESPLITORDER.TAXID = SOURCEITEMS.TAXID)
or (REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID = SOURCEITEMS.MERCHANDISEPRODUCTINSTANCEID)
or (REVENUESPLITORDER.MEMBERSHIPADDONID = SOURCEITEMS.MEMBERSHIPADDONID)
or (REVENUESPLITORDER.DESIGNATIONID = SOURCEITEMS.DESIGNATIONID and REVENUESPLITORDER.ID = SOURCEITEMS.SOURCELINEITEMID)
group by FTLI.ID, FTLI.FINANCIALTRANSACTIONID, CREDITPAYMENT.AMOUNT, SOURCEITEMS.CREDITAMOUNT, CREDITPAYMENT.ID, REVENUESPLIT_EXT.TYPE, SALESORDER.SEQUENCEID, REVENUESPLIT_EXT.TYPECODE, CREDITPAYMENT.PAYMENTMETHODCODE, SOURCEITEMS.CREDITITEMID
--back office event registrations.
union all
select ORIGLI.ID as REVENUESPLITID,
ORIGLI.FINANCIALTRANSACTIONID,
REVENUESPLIT_EXT.TYPE,
CREDITPAYMENT.AMOUNT AS REFUNDPAYMENTTOTAL,
FT.TRANSACTIONAMOUNT AS REFUNDSPLITAMOUNT, -- 2013-7-12 SW: formerly, using CREDITPAYMENT amount here caused payments to be prorated according to each other's values. Similar change below for membership may be necessary if we ever allow multiple backoffice payments toward a single membership.
FT.TRANSACTIONAMOUNT AS CREDITAMOUNT,
CREDITPAYMENT.ID AS CREDITPAYMENTID,
REVENUESPLIT_EXT.TYPECODE,
[CREDITPAYMENT].[PAYMENTMETHODCODE],
REFUNDLI.ID [CREDITITEMID]
from dbo.CREDIT_EXT CREDIT
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CREDIT.ID
inner join dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = CREDIT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM ORIGLI on ORIGLI.ID = CREDITPAYMENT.REVENUESPLITID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = ORIGLI.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI on REFUNDLI.SOURCELINEITEMID = ORIGLI.ID
where
CREDIT.SALESORDERID is null
and CREDIT.ID = @CREDITID
and REVENUESPLIT_EXT.TYPECODE = 1
--Security deposit refund
union all
select
FTLI.ID as REVENUESPLITID,
FTLI.FINANCIALTRANSACTIONID,
REVENUESPLIT_EXT.TYPE,
CP.AMOUNT AS REFUNDPAYMENTTOTAL,
CP.AMOUNT AS REFUNDSPLITAMOUNT,
FT.TRANSACTIONAMOUNT AS CREDITAMOUNT,
CP.ID AS CREDITPAYMENTID,
REVENUESPLIT_EXT.TYPECODE,
[CP].[PAYMENTMETHODCODE],
LI.ID [CREDITITEMID]
from dbo.CREDIT_EXT CREDIT
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CREDIT.ID
inner join dbo.CREDITPAYMENT CP ON CREDIT.ID = CP.CREDITID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on CP.REVENUESPLITID = FTLI.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.SOURCELINEITEMID = FTLI.ID
where
REVENUESPLIT_EXT.TYPECODE = 13 and CREDIT.ID = @CREDITID
-- Donation refund
union all
select
FTLI.ID as REVENUESPLITID,
FTLI.FINANCIALTRANSACTIONID,
REVENUESPLIT_EXT.TYPE,
CP.AMOUNT AS REFUNDPAYMENTTOTAL,
LI.TRANSACTIONAMOUNT AS REFUNDSPLITAMOUNT,
FT.TRANSACTIONAMOUNT AS CREDITAMOUNT,
CP.ID AS CREDITPAYMENTID,
REVENUESPLIT_EXT.TYPECODE,
[CP].[PAYMENTMETHODCODE],
CREDITITEM.ID CREDITITEMID
from dbo.CREDITITEM_EXT CREDITITEM
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
inner join dbo.CREDITPAYMENT CP on CREDITITEM.CREDITID = CP.CREDITID
inner join dbo.SALESORDERITEM on CREDITITEM.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.SALESORDERITEMDONATION on SALESORDERITEMDONATION.ID = SALESORDERITEM.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on SALESORDERITEMDONATION.REVENUESPLITID = FTLI.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
where
REVENUESPLIT_EXT.TYPECODE = 0 and CREDITITEM.CREDITID = @CREDITID
--back office membership and membership add-ons.
--these work differently because we cannot populate the revenuesplitid
--on the credit payment table (the credit payment
--is not associated with just one revenue split)
union all
select
FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID as REVENUESPLITID,
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID as REVENUEID,
CREDITITEM_EXT.TYPE,
FINANCIALTRANSACTION.ORGAMOUNT AS REFUNDPAYMENTTOTAL,
FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT AS REFUNDSPLITAMOUNT,
FINANCIALTRANSACTION.ORGAMOUNT AS CREDITAMOUNT,
CREDITPAYMENT.ID AS CREDITPAYMENTID,
CREDITITEM_EXT.TYPECODE,
CREDITPAYMENT.PAYMENTMETHODCODE,
FINANCIALTRANSACTIONLINEITEM.ID CREDITITEMID
from dbo.CREDIT_EXT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = CREDIT_EXT.ID
inner join dbo.CREDITITEM_EXT ON CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = CREDIT_EXT.ID
inner join dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = CREDIT_EXT.ID
where
CREDIT_EXT.SALESORDERID is null
and CREDIT_EXT.ID = @CREDITID
and (CREDITITEM_EXT.TYPECODE = 16 or CREDITITEM_EXT.TYPECODE = 1)
and CREDITITEM_EXT.SALESORDERITEMID is null
),
DISTRIBUTIONS_CTE as (
select
CREDITAMOUNTS.CREDITPAYMENTID,
DISTRIBUTIONS.ID as REVENUESPLITID,
DISTRIBUTIONS.AMOUNT,
DISTRIBUTIONS.CREDITITEMID
from (
select distinct
CREDITPAYMENTID, CREDITAMOUNT, REFUNDPAYMENTTOTAL
from SOURCESPLITS_CTE
) as CREDITAMOUNTS
outer apply (
select
ID, AMOUNT, CREDITITEMID
from dbo.UFN_SPLITS_GETPRORATEDSPLITS(CREDITAMOUNTS.CREDITAMOUNT, CREDITAMOUNTS.REFUNDPAYMENTTOTAL,
(
select
SOURCESPLITS_CTE.REVENUESPLITID as ID,
SOURCESPLITS_CTE.REFUNDSPLITAMOUNT as AMOUNT,
SOURCESPLITS_CTE.CREDITITEMID
from SOURCESPLITS_CTE
where SOURCESPLITS_CTE.CREDITPAYMENTID = CREDITAMOUNTS.CREDITPAYMENTID
for xml raw('ITEM'), type, elements, root('AMOUNTSTOPRORATE'), binary base64
)
)
) as DISTRIBUTIONS
)
select distinct
SPLITS.REVENUESPLITID,
SPLITS.CREDITPAYMENTID,
'Refund-'+SPLITS.TYPE as REFERENCE,
DISTRIBUTIONS_CTE.AMOUNT,
[SPLITS].[TYPECODE] as [REVENUETYPECODE],
[SPLITS].[PAYMENTMETHODCODE],
SPLITS.CREDITITEMID
from
SOURCESPLITS_CTE as SPLITS
inner join
DISTRIBUTIONS_CTE on DISTRIBUTIONS_CTE.CREDITPAYMENTID = SPLITS.CREDITPAYMENTID and DISTRIBUTIONS_CTE.CREDITITEMID = SPLITS.CREDITITEMID
inner join
dbo.CREDITITEM_EXT on SPLITS.CREDITITEMID = CREDITITEM_EXT.ID
where CREDITITEM_EXT.CREDITID = @CREDITID