USP_DATAFORMTEMPLATE_VIEW_CREDITMEMBERSHIPVIEW
The load procedure used by the view dataform template "Credit Membership Refund View"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @ITEMS | xml | INOUT | ITEMS |
| @TOTAL | money | INOUT | Order total |
| @PAYMENTS | xml | INOUT | PAYMENTS |
| @OTHERPAYMENTMETHODS | xml | INOUT | OTHERPAYMENTMETHODS |
| @TRANSACTIONID | uniqueidentifier | INOUT | TRANSACTIONID |
| @TIMESTAMP | bigint | INOUT | TIMESTAMP |
| @CONSTITUENTID | uniqueidentifier | INOUT | |
| @CONSTITUENTNAME | nvarchar(154) | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITMEMBERSHIPVIEW
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@ITEMS xml = null output,
@TOTAL money = null output,
@PAYMENTS xml = null output,
@OTHERPAYMENTMETHODS xml = null output,
@TRANSACTIONID uniqueidentifier = null output,
@TIMESTAMP bigint = null output,
@CONSTITUENTID uniqueidentifier = null output,
@CONSTITUENTNAME nvarchar(154) = null output
)
as
set nocount on;
set @DATALOADED = 0;
set @DATALOADED = 0;
--Raise error if membership is not active
if (select [STATUSCODE] from dbo.[MEMBERSHIP] where [ID] = @ID) <> 0
begin
raiserror('The membership selected is not active. Only active memberships may be refunded.', 13, 1);
end
if (select [EXPIRATIONDATE] from dbo.[MEMBERSHIP] where [ID] = @ID) < getdate()
begin
raiserror('The membership selected has lapsed. Lapsed memberships may not be refunded.', 13, 1);
end
declare @MEMBERSHIPTRANSACTIONID uniqueidentifier =
(select top(1)
[ID]
from dbo.[MEMBERSHIPTRANSACTION]
where [MEMBERSHIPID] = @ID order by [DATEADDED] desc)
/*Allowing $0 refunds
--Raise error if there is no payment attached to the transaction
if not exists( select [REVENUESPLIT].[ID]
from dbo.[MEMBERSHIPTRANSACTION]
inner join dbo.[REVENUESPLIT]
on [MEMBERSHIPTRANSACTION].[REVENUESPLITID] = [REVENUESPLIT].[ID]
where [MEMBERSHIPTRANSACTION].[ID] = @MEMBERSHIPTRANSACTIONID)
begin
raiserror('The membership selected has $0 available for refund.', 13, 1);
return 0;
end
*/
set @TRANSACTIONID = @MEMBERSHIPTRANSACTIONID
declare @ITEMSTABLE table(
[SALESORDERITEMID] uniqueidentifier,
[DESCRIPTION] nvarchar(500),
[QUANTITY] int,
[PRICE] money,
[TOTAL] money,
[DISCOUNTS] money,
[DISCOUNTED] bit,
[TYPECODE] tinyint,
[SALESORDERID] uniqueidentifier,
[ITEMID] uniqueidentifier,
[REVENUESPLITID] uniqueidentifier,
HASCONTRIBUTEDREVENUE bit
)
declare @PAYMENTSTABLE table(
[INCLUDE] bit,
[DESCRIPTION] nvarchar(500),
[DISPLAYDESCRIPTION] nvarchar(500),
[DISPLAYSOURCE] nvarchar(100),
[SOURCENUMBER] nvarchar(100),
[SOURCETYPE] tinyint,
[REFUNDAMOUNT] money,
[AMOUNTPAID] money,
[PAYMENTTYPECODE] tinyint,
[REVENUEID] uniqueidentifier,
[REVENUESPLITID] uniqueidentifier,
[TRANSACTIONID] uniqueidentifier,
[PAYMENTDATE] datetime
)
select
@OTHERPAYMENTMETHODS =
(select
[ID],
[DESCRIPTION]
from dbo.[OTHERPAYMENTMETHODCODE]
for xml raw ('ITEM'), type, elements, root('OTHERPAYMENTMETHODS'), BINARY BASE64)
declare @SALESORDERID uniqueidentifier
select @SALESORDERID = SALESORDER.ID
from dbo.[SALESORDER]
inner join dbo.[SALESORDERITEM]
on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
inner join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
where [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID] = @MEMBERSHIPTRANSACTIONID
--If the membership was paid for on an order
--get all the times
if @SALESORDERID is not null
begin
insert into @ITEMSTABLE
select
[SALESORDERITEM].[ID] as [SALESORDERITEMID],
[SALESORDERITEM].[DESCRIPTION],
1 as [QUANTITY],
[SALESORDERITEM].[TOTAL] as [PRICE],
[SALESORDERITEM].[TOTAL] - sum(coalesce([SALESORDERITEMMEMBERSHIPITEMPROMOTION].[AMOUNT], 0)) as [TOTAL],
sum(coalesce([SALESORDERITEMMEMBERSHIPITEMPROMOTION].[AMOUNT], 0)) as [DISCOUNTS],
case count(SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID)
when 0 then 0
else 1
end as [DISCOUNTED],
[SALESORDERITEM].[TYPECODE] as [TYPECODE],
[SALESORDERITEM].[SALESORDERID],
MEMBERSHIPTRANSACTION.ID as ITEMID,
MEMBERSHIPTRANSACTION.REVENUESPLITID,
0 as HASCONTRIBUTEDREVENUE
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left outer join dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIPITEMPROMOTION].[SALESORDERITEMID]
inner join dbo.MEMBERSHIPTRANSACTION
on SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
and MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID
group by
[SALESORDERITEM].[ID],
[SALESORDERITEM].[DESCRIPTION],
[SALESORDERITEM].[TOTAL],
[SALESORDERITEM].[TYPECODE],
[SALESORDERITEM].[SALESORDERID],
[MEMBERSHIPTRANSACTION].[ID],
MEMBERSHIPTRANSACTION.REVENUESPLITID
end
--The membership was paid for outside of an order
--get all the items
else
begin
--Get membership description
declare @ORDERITEMDESCRIPTION nvarchar(255);
select @ORDERITEMDESCRIPTION = isnull(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME([MEMBERSHIP].[MEMBERSHIPPROGRAMID]) + ' - ' +
dbo.UFN_MEMBERSHIPLEVEL_GETNAME([MEMBERSHIPTRANSACTION].[MEMBERSHIPLEVELID]) + ' (' +
dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE([MEMBERSHIPTRANSACTION].[MEMBERSHIPLEVELTERMID]) + '): ', '') +
[MEMBERSHIPTRANSACTION].[ACTION]
from dbo.[MEMBERSHIPTRANSACTION]
inner join dbo.[MEMBERSHIP] on [MEMBERSHIPTRANSACTION].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
where [MEMBERSHIPTRANSACTION].[ID] = @MEMBERSHIPTRANSACTIONID
insert into @ITEMSTABLE
select
null as [SALESORDERITEMID],
@ORDERITEMDESCRIPTION as [DESCRIPTION],
1 as [QUANTITY],
[FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT] as [PRICE],
[FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT] as [TOTAL],
0 as [DISCOUNTS],
0 as [DISCOUNTED],
1 as [TYPECODE],
null as [SALESORDERID],
@TRANSACTIONID as [ITEMID],
[MEMBERSHIPTRANSACTION].[REVENUESPLITID],
0 as HASCONTRIBUTEDREVENUE
from dbo.[MEMBERSHIP]
inner join dbo.[MEMBERSHIPTRANSACTION] on [MEMBERSHIPTRANSACTION].[MEMBERSHIPID] = [MEMBERSHIPID]
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
where [MEMBERSHIP].[ID] = @ID
and [MEMBERSHIPTRANSACTION].[ID] = @MEMBERSHIPTRANSACTIONID
end
--find all the addons
insert into @ITEMSTABLE
select
[SALESORDERITEM].[ID] as [SALESORDERITEMID],
[ADDON].[NAME],
[NOTREFUNDED].[QUANTITY],
[MEMBERSHIPADDON].[ORGANIZATIONPURCHASEPRICE],
[MEMBERSHIPADDON].[ORGANIZATIONPURCHASEPRICE] * [MEMBERSHIPADDON].[QUANTITY] - isnull(REFUNDS.TOTAL, 0),
0 as [DISCOUNT],
0 as [DISCOUNTED],
16,
[SALESORDERITEM].[SALESORDERID],
[MEMBERSHIPADDON].[ID] as [ITEMID],
[MEMBERSHIPADDON].[REVENUESPLITID],
0 as HASCONTRIBUTEDREVENUE
from dbo.[MEMBERSHIPADDON]
inner join dbo.[ADDON] on [ADDON].[ID] = [MEMBERSHIPADDON].[ADDONID]
left join dbo.[SALESORDERITEMMEMBERSHIPADDON] on [SALESORDERITEMMEMBERSHIPADDON].[MEMBERSHIPADDONID] = [MEMBERSHIPADDON].[ID]
left join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIPADDON].[ID]
outer apply (
select
sum(QUANTITY) as QUANTITY,
sum(FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT) as TOTAL
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.CREDITITEM_EXT
on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
where
FINANCIALTRANSACTION.TYPECODE = 23 and --refund
CREDITITEM_EXT.TYPECODE = 16 and --membershipaddon
FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = [MEMBERSHIPADDON].[REVENUESPLITID]
) REFUNDS
cross apply (
select [MEMBERSHIPADDON].[QUANTITY] - isnull([REFUNDS].[QUANTITY], 0) as QUANTITY
) NOTREFUNDED
where
[MEMBERSHIPADDON].[MEMBERSHIPID] = @ID
and [MEMBERSHIPADDON].[MEMBERSHIPTRANSACTIONID] = @MEMBERSHIPTRANSACTIONID
and NOTREFUNDED.QUANTITY > 0
--Get the total of the membership and all the addons
select
@TOTAL = sum([TOTAL])
from @ITEMSTABLE;
--Update it if it has contributed revenue
;with CONTRIBUTEDREVENUE_CTE as
(
select
case
when sum(FTLI.TRANSACTIONAMOUNT) > 0 then
1
else
0
end HASCONTRIBUTEDREVENUE,
I.REVENUESPLITID as ID
from @ITEMSTABLE I
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.SOURCELINEITEMID = I.REVENUESPLITID
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.REVENUESPLITID
)
update @ITEMSTABLE
set HASCONTRIBUTEDREVENUE = CTE.HASCONTRIBUTEDREVENUE
from CONTRIBUTEDREVENUE_CTE CTE where CTE.ID = REVENUESPLITID
select @ITEMS =
(
select
[SALESORDERITEMID] as [SALESORDERITEMID],
[DESCRIPTION],
sum([QUANTITY]) as [QUANTITY],
0 as [QUANTITYREFUNDING],
[PRICE],
0 as [FEES],
sum([TOTAL]) as [TOTAL],
0 as [REFUNDTOTAL],
sum([DISCOUNTS]) as [DISCOUNTS],
0 as [INCLUDE],
[DISCOUNTED],
[TYPECODE],
null as [PROGRAMID],
[ITEMID],
[REVENUESPLITID],
HASCONTRIBUTEDREVENUE
from @ITEMSTABLE
group by
[DESCRIPTION],
[PRICE],
[TYPECODE],
[DISCOUNTED],
[SALESORDERITEMID],
[ITEMID],
[REVENUESPLITID],
HASCONTRIBUTEDREVENUE
order by [TYPECODE]
for xml raw ('ITEM'), type, elements, root('ITEMS'), BINARY BASE64)
--if it was paid for on an order, get the payments
if @SALESORDERID is not null
begin
--get all the payments from sales orders
insert into @PAYMENTSTABLE
select
[PAYMENTS].[INCLUDE],
[PAYMENTS].[DESCRIPTION],
case
when [PAYMENTS].[AMOUNTPAID] < [ITEMTOTAL] then
'$' + cast([PAYMENTS].[AMOUNTPAID] as nvarchar(20)) + ' ' + [PAYMENTS].[DESCRIPTION]
else
'$' + cast([ITEMTOTAL] as nvarchar(20)) + ' ' + [PAYMENTS].[DESCRIPTION]
end [DISPLAYDESCRIPTION],
[PAYMENTS].[DISPLAYSOURCE],
[PAYMENTS].[SOURCENUMBER],
[PAYMENTS].[SOURCETYPE],
[PAYMENTS].[REFUNDAMOUNT],
case
when [PAYMENTS].[AMOUNTPAID] < [ITEMTOTAL] then
[PAYMENTS].[AMOUNTPAID]
else
[ITEMTOTAL]
end AMOUNTPAID,
[PAYMENTS].[PAYMENTTYPECODE],
[PAYMENTS].[REVENUEID],
[PAYMENTS].[REVENUESPLITID],
[PAYMENTS].[TRANSACTIONID],
[PAYMENTS].[PAYMENTDATE]
from
(select
0 as [INCLUDE],
case
when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODCODEID])
else
[REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] +
coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
end [DESCRIPTION],
null as [DISPLAYSOURCE],
[SALESORDER].[SEQUENCEID] as [SOURCENUMBER],
0 as [SOURCETYPE],
0 as [REFUNDAMOUNT],
[FINANCIALTRANSACTION].[BASEAMOUNT] - coalesce((select sum(coalesce([CREDITPAYMENT].[AMOUNT], 0))
from dbo.[CREDITPAYMENT]
where [REVENUEID] = [FINANCIALTRANSACTION].[ID]), 0) as [AMOUNTPAID], -- (amount - refunds)
[REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTTYPECODE],
1 as [AUTOINCLUDED],
[FINANCIALTRANSACTION].[ID] as [REVENUEID],
null as [REVENUESPLITID],
[CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
SUM(ITEMSTABLE.TOTAL) as ITEMTOTAL,
cast([FINANCIALTRANSACTION].[DATE] as datetime) as [PAYMENTDATE]
from dbo.[SALESORDER]
inner join dbo.[SALESORDERPAYMENT] on [SALESORDERPAYMENT].[SALESORDERID] = [SALESORDER].[ID]
inner join dbo.[FINANCIALTRANSACTION] on [SALESORDERPAYMENT].[PAYMENTID] = [FINANCIALTRANSACTION].[ID]
inner join dbo.[REVENUEPAYMENTMETHOD] on [FINANCIALTRANSACTION].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[CHECKPAYMENTMETHODDETAIL] on [CHECKPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join @ITEMSTABLE [ITEMSTABLE] on [SALESORDER].[ID] = ITEMSTABLE.SALESORDERID
where [SALESORDER].[ID] = @SALESORDERID
group by [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE], [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID], [FINANCIALTRANSACTION].[ID],
[OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID], [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD], [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
[CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], [SALESORDER].[SEQUENCEID], [FINANCIALTRANSACTION].[BASEAMOUNT],
[FINANCIALTRANSACTION].[DATE]
) [PAYMENTS]
where [PAYMENTS].[AMOUNTPAID] > 0
end
--if it was paid for through the back office
--get the payments
else
begin
insert into @PAYMENTSTABLE
select
0 as [INCLUDE],
case
when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODCODEID])
else
[REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] +
coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
end [DESCRIPTION],
'$' + cast(@TOTAL as nvarchar(20)) + ' ' +
case
when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODCODEID])
else
[REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] +
coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
end [DISPLAYDESCRIPTION],
'' as [DISPLAYSOURCE],
case
when [REVENUE_EXT].[BATCHNUMBER] <> '' then [REVENUE_EXT].[BATCHNUMBER]
else convert(nvarchar(50), [FINANCIALTRANSACTION].[SEQUENCEGENERATORID])
end as [SOURCENUMBER],
case
when [REVENUE_EXT].[BATCHNUMBER] <> '' then 1
else 2
end as [SOURCETYPE], --Payment (vs. Batch or Order)
0 as [REFUNDAMOUNT],
@TOTAL as [AMOUNTPAID],
[REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTTYPECODE],
[FINANCIALTRANSACTION].[ID] [REVENUEID],
null as [REVENUESPLITID],
[CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
cast([FINANCIALTRANSACTION].[DATE] as datetime) as [PAYMENTDATE]
from dbo.[MEMBERSHIPTRANSACTION]
inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [MEMBERSHIPTRANSACTION].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[ID]
left join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
left join dbo.[REVENUE_EXT] on [FINANCIALTRANSACTION].[ID] = [REVENUE_EXT].[ID]
inner join dbo.[REVENUEPAYMENTMETHOD] on [FINANCIALTRANSACTION].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
left join dbo.[CHECKPAYMENTMETHODDETAIL] on [CHECKPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
where [MEMBERSHIPTRANSACTION].[ID] = @MEMBERSHIPTRANSACTIONID
end
select @PAYMENTS =
(select * from @PAYMENTSTABLE
for xml raw ('ITEM'), type, elements, root('PAYMENTS'), BINARY BASE64)
select @TIMESTAMP = [TSLONG]
from dbo.[MEMBERSHIPTRANSACTION]
where [ID] = @TRANSACTIONID
select
@CONSTITUENTID = MEMBER.CONSTITUENTID,
@CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(MEMBER.CONSTITUENTID)
from
dbo.MEMBERSHIP
inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
where
MEMBERSHIP.ID = @ID
and MEMBER.ISPRIMARY = 1;
set @DATALOADED = 1;
return 0;