USP_DATAFORMTEMPLATE_VIEW_DAILYSALESORDER
The load procedure used by the view dataform template "Daily Sales Order View Data Form"
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. |
| @CALCULATORINPUT | nvarchar(9) | INOUT | CALCULATORINPUT |
| @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 |
| @MEMBERSHIPS | xml | INOUT | MEMBERSHIPS |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALESORDER
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@CALCULATORINPUT nvarchar(9) = null 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,
@MEMBERSHIPS xml = null output
)
as
set nocount on;
declare @CURRENTDATE datetime = getdate();
declare @SALESMETHODTYPECODE tinyint;
select
@DATALOADED = 1,
@CONSTITUENTID = [CONSTITUENTID],
@SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE
from dbo.[SALESORDER]
where [ID] = @ID
set @CALCULATORINPUT = '';
-- 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
select @ITEMS =
(select ID, QUANTITY, DESCRIPTION, TOTAL, TYPECODE, SALESORDERITEMTYPECODE, PAYMENTID, DISCOUNTITEMID, EVENTID, REGISTRANTID, TICKETCOMBINATIONID, PRICETYPE, PRICETYPECODEID, PROGRAMNAME, TICKETSAPPLIED
from (
select distinct
[SALESORDERITEM].[ID],
case [SALESORDERITEM].[TYPECODE]
when 6 then 0
else [SALESORDERITEM].[QUANTITY]
end as [QUANTITY],
case
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null and [PROGRAM].[ISDAILYADMISSION] = 1 then [PROGRAM].[NAME]
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null and [PROGRAM].[ISDAILYADMISSION] = 0 then [EVENT].[NAME] + ' - ' + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
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 dbo.UFN_CONSTITUENT_BUILDNAME([REGISTRANT].[CONSTITUENTID]) + ' - ' + [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 [PROGRAM].[ISPREREGISTERED] = 1 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],
coalesce([COMBINATION].[ID], [SALESORDERITEMTICKET].[EVENTID], [SALESORDERITEMTICKET].[PROGRAMID]) as [EVENTID],
[REGISTRANT].[ID] as [REGISTRANTID],
[SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID],
dbo.UFN_PRICETYPECODE_GETDESCRIPTION([SALESORDERITEMTICKET].[PRICETYPECODEID]) 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 dbo.UFN_CONSTITUENT_BUILDNAME([REGISTRANT].[CONSTITUENTID]) + ' - ' + [SALESORDERITEM].[DESCRIPTION]
else [SALESORDERITEM].[DESCRIPTION]
end as [ORDERSORTFIELD1],
case
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null and [PROGRAM].[ISDAILYADMISSION] = 1 then [PROGRAM].[NAME]
when [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] is not null and [PROGRAM].[ISDAILYADMISSION] = 0 then [EVENT].[NAME] + ' - ' + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
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]
where [SALESORDERITEM].[SALESORDERID] = @ID
and [SALESORDERITEM].[TYPECODE] in (0,2,6)
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
-- Membership
select distinct
[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))
when [SALESORDERITEM].[TYPECODE] = 16 then 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
select
[SALESORDERITEMITEMDISCOUNT].[DISCOUNTID] as [ID],
case when [DISCOUNT].[DISCOUNTTYPECODE] = 2 then
null
else
[LIMIT].[NUMBEROFDISCOUNTSPERORDER]
end as [QUANTITY],
[DISCOUNTNAME] as [DESCRIPTION],
-1 * 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],
-1 * [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],
-1 * [TOTAL] AS [TOTAL],
case
when [SALESORDERITEMORDERDISCOUNT].[DISCOUNTID] is null then
4
else
1
end
TYPECODE,
null as [SALESORDERITEMTYPECODE],
null as [PAYMENTID],
case
when [SALESORDERITEMORDERDISCOUNT].[DISCOUNTID] is null then
[SALESORDERITEM].ID
else
[SALESORDERMANUALDISCOUNT].[ID]
end
[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],
-1 * [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],
-1 * 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)
set @SALESORDERID = @ID;
set @AVAILABLEPAYMENTMETHODS =
(select PAYTYPES.PAYMENTTYPECODE from
dbo.SALESMETHOD
CROSS APPLY dbo.UFN_SALESMETHODPAYMENTMETHOD_GETPAYMENTTYPES(SALESMETHOD.ID) PAYTYPES
WHERE SALESMETHOD.TYPECODE = @SALESMETHODTYPECODE
for xml raw ('ITEM'), type, elements, root('AVAILABLEPAYMENTMETHODS'), BINARY BASE64
);
set @MEMBERSHIPS =
(
select
[MEMBERSHIP].[ID] as [ID],
'' + dbo.UFN_MEMBERSHIPPROGRAM_GETNAME([MEMBERSHIP].[MEMBERSHIPPROGRAMID]) + ' - ' +
dbo.UFN_MEMBERSHIPLEVEL_GETNAME([MEMBERSHIP].[MEMBERSHIPLEVELID]) +
(case
when [MEMBERSHIP].[EXPIRATIONDATE] is null then
''
else
' (' + dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE([MEMBERSHIP].[MEMBERSHIPLEVELTERMID]) + ')'
end) +
(case
when [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID] is null then
''
else
': ' + dbo.UFN_MEMBERSHIPLEVELTYPECODE_GETDESCRIPTION([MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID])
end)
as [DESCRIPTION],
case when [MEMBERSHIP].[STATUSCODE] = 1 then [MT].[TRANSACTIONDATE] else [MEMBERSHIP].[EXPIRATIONDATE] end [MEMBERSHIPEXPIRATION],
case when @CURRENTDATE > [MEMBERSHIP].[EXPIRATIONDATE] then 1
else 0 end [ISLAPSED],
case when [MEMBERSHIP].[STATUSCODE] = 1 then 1
else 0 end [ISCANCELED],
case [MEMBERSHIP].[STATUSCODE]
when 2 then
1
else
0
end [ISPENDING],
convert( nvarchar(1000),
(select [CONSTITUENT].[NAME] as div
from dbo.[MEMBER]
inner join dbo.[CONSTITUENT]
on [MEMBER].[CONSTITUENTID] = [CONSTITUENT].[ID]
where [MEMBERSHIPID] = [MEMBERSHIP].[ID] and
[ISDROPPED] = 0
order by [MEMBER].[ISPRIMARY] desc
for xml raw (''), type, elements, BINARY BASE64)) as [OTHERMEMBERS]
from dbo.[MEMBER]
inner join dbo.[MEMBERSHIP] on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
inner join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID]
left join dbo.[MEMBERSHIPTRANSACTION] [MT] on ([MEMBERSHIP].[ID] = [MT].[MEMBERSHIPID] and [MT].[ACTIONCODE] = 4) and ([MT].[ID] in (select top 1 [ID] from dbo.[MEMBERSHIPTRANSACTION] [MT2] where [MT2].[ACTIONCODE] = 4 and [MT2].[MEMBERSHIPID] = [MEMBERSHIP].[ID] order by [MT2].[TRANSACTIONDATE] desc))
where
[MEMBER].[CONSTITUENTID] = @CONSTITUENTID and
[MEMBER].[ISDROPPED] = 0
for xml raw ('ITEM'), type, elements, root('MEMBERSHIPS'), BINARY BASE64
);
return 0;