USP_DATAFORMTEMPLATE_VIEW_DAILYSALESORDER_2
The load procedure used by the view dataform template "Daily Sales Order View Data Form 2"
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 |
| @SUBTOTAL | money | INOUT | Subtotal |
| @TAXES | money | INOUT | Taxes |
| @TOTAL | money | INOUT | Total |
| @TENDERED | money | INOUT | Tendered |
| @CHANGEDUE | money | INOUT | Change due |
| @BALANCE | money | INOUT | Balance |
| @SALESORDERID | uniqueidentifier | INOUT | ID |
| @AVAILABLEPAYMENTMETHODS | xml | INOUT | Payment Types |
| @CONSTITUENTID | uniqueidentifier | INOUT | Patron |
| @ORDERNUMBER | nvarchar(10) | INOUT | ORDERNUMBER |
| @MEMBERID | uniqueidentifier | INOUT | MEMBERID |
| @QUICKBUTTONCONTEXTID | uniqueidentifier | INOUT | QUICKBUTTONCONTEXTID |
| @PATRONMEMBERSHIPCOUNT | int | INOUT | PATRONMEMBERSHIPCOUNT |
| @MEMBERSHIPID | uniqueidentifier | INOUT | |
| @ISTAXEXEMPT | bit | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALESORDER_2
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@ITEMS xml = null output,
@SUBTOTAL money = null output,
@TAXES money = null output,
@TOTAL money = null output,
@TENDERED money = null output,
@CHANGEDUE money = null output,
@BALANCE money = null output,
@SALESORDERID uniqueidentifier = null output,
@AVAILABLEPAYMENTMETHODS xml = null output,
@CONSTITUENTID uniqueidentifier = null output,
@ORDERNUMBER nvarchar(10) = null output,
@MEMBERID uniqueidentifier = null output,
@QUICKBUTTONCONTEXTID uniqueidentifier = null output,
@PATRONMEMBERSHIPCOUNT integer = null output,
@MEMBERSHIPID uniqueidentifier = null output,
@ISTAXEXEMPT bit = null output
)
as
set nocount on;
declare @SALESMETHODID uniqueidentifier;
select
@DATALOADED = 1,
@SALESORDERID = @ID,
@CONSTITUENTID = CONSTITUENTID,
@ORDERNUMBER = SEQUENCEID,
@SALESMETHODID = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(SALESMETHODTYPECODE)
from dbo.SALESORDER
where ID = @ID;
-- Order same as itemized receipt
-- 0 - Ticket
-- 10 - Combination
-- 20 - Event Registration
-- 30 - Membership
-- 40 - Merchandise
-- 50 - Donation
-- 60 - Discounts
-- 70 - Fees
-- 80 - Taxes
-- 85 - Delivery method
-- 90 - Payments
set @ITEMS =
(select ID, QUANTITY, DESCRIPTION, TOTAL, TYPECODE, SALESORDERITEMTYPECODE, PAYMENTID, DISCOUNTITEMID, EVENTID, REGISTRANTID, TICKETCOMBINATIONID, PRICETYPE, PRICETYPECODEID, PROGRAMNAME, TICKETSAPPLIED
from (
select
[SALESORDERITEM].[ID],
case [SALESORDERITEM].[TYPECODE]
when 6 then 0
else [SALESORDERITEM].[QUANTITY]
end as [QUANTITY],
case
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null then
case
when [PROGRAM].[ISDAILYADMISSION] = 1 then [PROGRAM].[NAME]
else [EVENT].[NAME] + ' - ' + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
end
when [SALESORDERITEM].[TYPECODE] = 0 then [SALESORDERITEM].[DESCRIPTION] + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
when [SALESORDERITEM].[TYPECODE] = 6 then REGISTRANTNAMEFORMAT.NAME + ' - ' + [SALESORDERITEM].[DESCRIPTION]
else [SALESORDERITEM].[DESCRIPTION]
end as [DESCRIPTION],
case
when TYPECODE = 1 then -- membership
[SALESORDERITEM].[TOTAL] + (
select isnull(sum(ADDONITEMS.TOTAL), 0)
from dbo.SALESORDERITEM ADDONITEMS
inner join dbo.SALESORDERITEMMEMBERSHIPADDON SOIMA on ADDONITEMS.ID = SOIMA.ID
where ADDONITEMS.TYPECODE = 16 and SOIMA.SALESORDERITEMMEMBERSHIPID = SALESORDERITEM.ID
)
else
[SALESORDERITEM].[TOTAL]
end as [TOTAL],
0 TYPECODE,
case
when exists(select 1 from dbo.[SALESORDERITEMTICKETREGISTRANT] as [SOITR] where [SOITR].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]) then 254
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null then 253
when [SALESORDERITEM].[TYPECODE] = 1 then (select [TYPECODE] from dbo.[SALESORDERITEMMEMBERSHIP] where [ID] = [SALESORDERITEM].[ID])
else [SALESORDERITEM].[TYPECODE]
end as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
null as [DISCOUNTITEMID],
[SALESORDERITEMTICKET].[EVENTID],
[REGISTRANT].[ID] as [REGISTRANTID],
[SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID],
PRICETYPECODE.DESCRIPTION as [PRICETYPE],
[SALESORDERITEMTICKET].[PRICETYPECODEID],
[COMBINATION].[NAME] as [PROGRAMNAME], --Only used for combos right now
0 as [TICKETSAPPLIED],
--below fields are used for sorting
case
when TYPECODE = 0 then --ticket
0
when TYPECODE = 2 then --donation
50
when TYPECODE = 6 then --event registration
20
else
0 --should not occur
end as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE],
case
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null then [COMBINATION].[NAME]
when [SALESORDERITEM].[TYPECODE] = 0 then [SALESORDERITEM].[DESCRIPTION] + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
when [SALESORDERITEM].[TYPECODE] = 6 then REGISTRANTNAMEFORMAT.NAME + ' - ' + [SALESORDERITEM].[DESCRIPTION]
else [SALESORDERITEM].[DESCRIPTION]
end as [ORDERSORTFIELD1],
case
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null then
case
when [PROGRAM].[ISDAILYADMISSION] = 1 then [PROGRAM].[NAME]
else [EVENT].[NAME] + ' - ' + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
end
else cast([SALESORDERITEM].[ID] as nvarchar(36))
end as [ORDERSORTFIELD2]
from dbo.[SALESORDERITEM]
left join dbo.[SALESORDERITEMTICKET]
on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
left join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
left join dbo.[REGISTRANT]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
left join dbo.[SALESORDERITEMTICKETCOMBINATION]
on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
left join dbo.[COMBINATION]
on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
left join dbo.[EVENT]
on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
left join dbo.[PROGRAM]
on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.PRICETYPECODE
on PRICETYPECODE.ID = SALESORDERITEMTICKET.PRICETYPECODEID
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) as REGISTRANTNAMEFORMAT
where [SALESORDERITEM].[SALESORDERID] = @ID
and [SALESORDERITEM].[TYPECODE] in (0,2,6)
union all
-- Membership
select
[SALESORDERITEM].[ID],
[SALESORDERITEM].[QUANTITY],
[SALESORDERITEM].[DESCRIPTION],
[SALESORDERITEM].[TOTAL],
0 TYPECODE,
[SALESORDERITEM].[TYPECODE] as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
null as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null [PRICETYPECODEID],
null as [PROGRAMNAME],
case
when exists (select 1 from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION where SALESORDERITEMID = SALESORDERITEM.ID and MEMBERSHIPPROMOID is null) then
1
else
0
end as [TICKETSAPPLIED], --only used for memberships right now
--below fields are used for sorting
30 [ORDERITEMTYPE],
[SALESORDERITEM].[TYPECODE] as [SUBORDERITEMTYPE],
case
when [SALESORDERITEM].[TYPECODE] = 1 then cast([SALESORDERITEM].[ID] as nvarchar(36))
else cast([SALESORDERITEMMEMBERSHIPADDON].[SALESORDERITEMMEMBERSHIPID] as nvarchar(36))
end as [ORDERSORTFIELD1],
'' as [ORDERSORTFIELD2]
from dbo.[SALESORDERITEM]
left join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left join dbo.[SALESORDERITEMMEMBERSHIPADDON]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIPADDON].[ID]
where [SALESORDERITEM].[SALESORDERID] = @ID
and [SALESORDERITEM].[TYPECODE] in (1, 16)
union all
-- Merchandise
select
[SOI].[ID],
[SOI].[QUANTITY],
[MPI].[ITEMDETAILS],
[SOI].[TOTAL],
9 as TYPECODE,
[SOI].[TYPECODE] as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
null as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
40 as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE],
null as [ORDERSORTFIELD1],
null as [ORDERSORTFIELD2]
from dbo.SALESORDERITEM SOI
inner join dbo.SALESORDERITEMMERCHANDISE SOIM on SOIM.ID = SOI.ID
inner join dbo.MERCHANDISEPRODUCTINSTANCE MPI on SOIM.MERCHANDISEPRODUCTINSTANCEID = MPI.ID
where SOI.SALESORDERID = @ID
union all
select
[SALESORDERITEMITEMDISCOUNT].[DISCOUNTID] as [ID],
case when [DISCOUNT].[DISCOUNTTYPECODE] = 2 then
null
else
[LIMIT].[NUMBEROFDISCOUNTSPERORDER]
end as [QUANTITY],
[DISCOUNTNAME] as [DESCRIPTION],
-SUM([SALESORDERITEMITEMDISCOUNT].[AMOUNT]) AS [TOTAL],
1 [TYPECODE],
null as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
[SALESORDERMANUALDISCOUNT].[ID] as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
60 as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE],
null as [ORDERSORTFIELD1],
null as [ORDERSORTFIELD2]
from dbo.[SALESORDERITEMITEMDISCOUNT]
inner join dbo.[SALESORDERITEM] on
[SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
inner join dbo.DISCOUNT on
[DISCOUNT].[ID] = [SALESORDERITEMITEMDISCOUNT].[DISCOUNTID]
left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] [LIMIT] on
[LIMIT].[DISCOUNTID] = [SALESORDERITEMITEMDISCOUNT].[DISCOUNTID] and
[LIMIT].[SALESORDERID] = @ID
left join dbo.[SALESORDERMANUALDISCOUNT] on
[SALESORDERMANUALDISCOUNT].[DISCOUNTID] = [SALESORDERITEMITEMDISCOUNT].[DISCOUNTID] and
[SALESORDERMANUALDISCOUNT].[SALESORDERID] = [SALESORDERITEM].[SALESORDERID]
where SALESORDERITEM.SALESORDERID = @ID
group by [SALESORDERITEMITEMDISCOUNT].[DISCOUNTID], [DISCOUNTNAME], [LIMIT].[NUMBEROFDISCOUNTSPERORDER], [SALESORDERMANUALDISCOUNT].[ID], [DISCOUNT].[DISCOUNTTYPECODE]
union all
--applied tickets
select
[SALESORDERITEMMEMBERSHIPITEMPROMOTION].[ID] AS [ID],
null as [QUANTITY],
[PROMOTIONNAME] as [DESCRIPTION],
-[AMOUNT] AS [TOTAL],
case
when SALESORDERITEMMEMBERSHIPITEMPROMOTION.MEMBERSHIPPROMOID is null then 7
else 5
end as [TYPECODE],
252 as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
[SALESORDERITEMMEMBERSHIPITEMPROMOTION].[SALESORDERITEMID] as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
30 as [ORDERITEMTYPE],
60 as [SUBORDERITEMTYPE],
[SALESORDERITEM].[DESCRIPTION] as [ORDERSORTFIELD1],
cast([SALESORDERITEM].[ID] as nvarchar(36)) as [ORDERSORTFIELD2]
from dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION]
inner join dbo.[SALESORDERITEM] on
[SALESORDERITEMMEMBERSHIPITEMPROMOTION].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
where SALESORDERITEM.SALESORDERID = @ID
union all
select
[SALESORDERITEMORDERDISCOUNT].[DISCOUNTID],
null as [QUANTITY],
[DESCRIPTION] as [DESCRIPTION],
-[TOTAL] AS [TOTAL],
case
when [SALESORDERITEMORDERDISCOUNT].[DISCOUNTID] is null then
4
else
1
end as TYPECODE,
null as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
case
when [SALESORDERITEMORDERDISCOUNT].[DISCOUNTID] is null then
[SALESORDERITEM].ID
else
[SALESORDERMANUALDISCOUNT].[ID]
end as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
60 as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE],
null as [ORDERSORTFIELD1],
null as [ORDERSORTFIELD2]
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMORDERDISCOUNT] on
[SALESORDERITEMORDERDISCOUNT].[ID] = [SALESORDERITEM].[ID]
left join dbo.[SALESORDERMANUALDISCOUNT] on
[SALESORDERMANUALDISCOUNT].[DISCOUNTID] = [SALESORDERITEMORDERDISCOUNT].[DISCOUNTID] and
[SALESORDERMANUALDISCOUNT].[SALESORDERID] = [SALESORDERITEM].[SALESORDERID]
where [SALESORDERITEM].[SALESORDERID] = @ID
union all
--Membership Promos
select
[SALESORDERITEMMEMBERSHIPPROMO].[MEMBERSHIPPROMOID],
null as [QUANTITY],
[DESCRIPTION] as [DESCRIPTION],
-[TOTAL] AS [TOTAL],
5 TYPECODE,
SALESORDERITEM.TYPECODE as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
[SALESORDERITEMMEMBERSHIPPROMO].[ID] as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
60 as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE] ,
null as [ORDERSORTFIELD1],
null as [ORDERSORTFIELD2]
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMMEMBERSHIPPROMO] on
[SALESORDERITEMMEMBERSHIPPROMO].[ID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @ID and
[SALESORDERITEMMEMBERSHIPPROMO].[INUSE] = 1
union all
select
[SALESORDERITEMFEE].[FEEID],
1,
[SALESORDERITEM].[DESCRIPTION],
sum([TOTAL]) as [TOTAL],
2 TYPECODE,
null as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
null as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
70 as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE],
null as [ORDERSORTFIELD1],
null as [ORDERSORTFIELD2]
from dbo.[SALESORDERITEM]
inner join dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
where SALESORDERID = @ID
and SALESORDERITEM.TYPECODE = 3
group by SALESORDERITEMFEE.FEEID, SALESORDERITEM.DESCRIPTION
union all
select
SALESORDERPAYMENT.PAYMENTID AS [ID],
1 as [QUANTITY],
case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
when 2 then
dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ': ' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' - ' + CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE
when 10 then
dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID)
else
REVENUEPAYMENTMETHOD.PAYMENTMETHOD
end as [DESCRIPTION],
-SALESORDERPAYMENT.AMOUNTTENDERED AS [TOTAL],
3 TYPECODE,
null as [SALESORDERITEMTYPECODE],
SALESORDERPAYMENT.ID as [PAYMENTID],
null as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
80 as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE],
null as [ORDERSORTFIELD1],
null as [ORDERSORTFIELD2]
from dbo.SALESORDERPAYMENT
inner join dbo.REVENUEPAYMENTMETHOD on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
left join dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
left join dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
where SALESORDERPAYMENT.SALESORDERID = @ID
union all
--Delivery method
select
[SALESORDER].[DELIVERYMETHODID] as [ID],
0 as [QUANTITY],
'Delivery method - ' + dbo.UFN_DELIVERYMETHOD_GETNAME([SALESORDER].[DELIVERYMETHODID]) as [DESCRIPTION],
0 as [TOTAL],
20 as [TYPECODE],
null as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
null as [DISCOUNTITEMID],
null as [EVENTID],
null as [REGISTRANTID],
null as [TICKETCOMBINATIONID],
null as [PRICETYPE],
null as [PRICETYPECODEID],
null as [PROGRAMNAME],
0 as [TICKETSAPPLIED],
85 as [ORDERITEMTYPE],
0 as [SUBORDERITEMTYPE],
null as [ORDERSORTFIELD1],
null as [ORDERSORTFIELD2]
from dbo.[SALESORDER]
where [SALESORDER].[ID] = @ID
and [SALESORDER].[SALESMETHODTYPECODE] in (1, 2) --Advanced, Online Sales
) as DETAILS
order by ORDERITEMTYPE, coalesce([ORDERSORTFIELD1], [DESCRIPTION]), coalesce([ORDERSORTFIELD2], cast([ID] as nvarchar(36))), SUBORDERITEMTYPE
for xml raw ('ITEM'), type, elements, root('ITEMS'), BINARY BASE64
);
select
@SUBTOTAL = SUBTOTAL,
@TOTAL = TOTAL,
@TAXES = TAXES,
@TENDERED = AMOUNTTENDERED,
@CHANGEDUE = CHANGEDUE,
@BALANCE = BALANCE
from
dbo.UFN_SALESORDER_TOTALS(@ID);
-- This code is reused by the advance sales view form.
-- Makes sure that it doesn't hardcode the sales method.
set @AVAILABLEPAYMENTMETHODS = (
select PAYTYPES.PAYMENTTYPECODE
from dbo.UFN_SALESMETHODPAYMENTMETHOD_GETPAYMENTTYPES(@SALESMETHODID) PAYTYPES
for xml raw ('ITEM'), type, elements, root('AVAILABLEPAYMENTMETHODS'), BINARY BASE64
);
select top(1)
@MEMBERID = MEMBER.ID,
@PATRONMEMBERSHIPCOUNT = (
select count(*)
from dbo.MEMBER
where CONSTITUENTID = @CONSTITUENTID
and ISDROPPED = 0
),
@MEMBERSHIPID = MEMBERSHIP.ID
from dbo.MEMBER
inner join dbo.MEMBERSHIP
on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
inner join dbo.MEMBERSHIPLEVEL
on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
inner join dbo.MEMBERSHIPPROGRAM
on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
where
MEMBER.CONSTITUENTID = @CONSTITUENTID and
MEMBER.ISDROPPED = 0 and
getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE) and
MEMBERSHIP.STATUSCODE = 0
order by
MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS asc;
set @ISTAXEXEMPT = dbo.UFN_SALESORDER_ISTAXEXEMPT(@ID);
return 0;