USP_DATALIST_SALESREPORT
Returns list of sales revenue by sales order item type.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @FROM | datetime | IN | from |
| @TO | datetime | IN | to |
| @SALESMETHODID | uniqueidentifier | IN | Sales method |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_SALESREPORT
(
@FROM datetime = null,
@TO datetime = null,
@SALESMETHODID uniqueidentifier = null
)
with recompile
as
set nocount on;
declare @SALESMETHODTYPECODE tinyint = null;
select @SALESMETHODTYPECODE = [TYPECODE]
from dbo.[SALESMETHOD]
where [ID] = @SALESMETHODID;
set @FROM = dbo.UFN_DATE_GETEARLIESTTIME(@FROM)
set @TO = dbo.UFN_DATE_GETLATESTTIME(@TO)
--Note, since users want to see the report in order of Admission, Events, and Memberships... SALESORDERITEM.TYPECODEs are overridden for sorting
--Admission = 0, Events = 5, Membership = 10, Donations = 15, Resources = 20, Volunteers = 25, Fees = 30, Facility rentals = 35, Merchandise = 40 (Numbers separated to provide room for future item types)
-- Group order discount details under the item types they are discounting
--Select program totals (with order-level discounts)
select
[SALESORDERITEMTYPECODE],
[PROGRAMNAME],
[QUANTITY],
[GROSS],
[DISCOUNT],
[REFUND],
[ISORDERDISCOUNT],
[CATEGORY]
from
(select
case
when [SALESORDERITEM].[TYPECODE] = 0 then 0
when [SALESORDERITEM].[TYPECODE] = 5 then
case DISCOUNTEDITEM.TYPECODE
when 0 then 0
when 14 then 40
end
--when [SALESORDERITEM].[TYPECODE] = 6 then 5 event registration are broken out below
when [SALESORDERITEM].[TYPECODE] = 1 then 10
when [SALESORDERITEM].[TYPECODE] = 2 then 15
when [SALESORDERITEM].[TYPECODE] in (8,9) then 20
when [SALESORDERITEM].[TYPECODE] in (10,11) then 25
when [SALESORDERITEM].[TYPECODE] = 3 then 30
when [SALESORDERITEM].[TYPECODE] = 7 then 35
when [SALESORDERITEM].[TYPECODE] = 14 then 40
end as [SALESORDERITEMTYPECODE],
case [SALESORDERITEM].[TYPECODE]
when 0 then [SALESORDERITEMTICKET].[PROGRAMNAME]
when 1 then [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME]
when 2 then [SALESORDERITEMDONATION].[DESIGNATIONNAME]
when 3 then [SALESORDERITEMFEE].[FEENAME]
when 5 then [SALESORDERITEMORDERDISCOUNT].[DISCOUNTNAME]
when 7 then [SALESORDERITEMFACILITY].[EVENTLOCATIONNAME]
when 8 then [SALESORDERITEMITINERARYRESOURCE].[RESOURCENAME]
when 9 then [SALESORDERITEMITINERARYITEMRESOURCE].[RESOURCENAME]
when 10 then [SALESORDERITEMITINERARYSTAFFRESOURCE].[VOLUNTEERTYPENAME]
when 11 then [SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[VOLUNTEERTYPENAME]
when 14 then [MERCHANDISEDEPARTMENT].[NAME]
end as [PROGRAMNAME],
case [SALESORDERITEM].[TYPECODE]
when 5 then 0
else coalesce(sum([SALESORDERITEM].[QUANTITY]),0)
end as [QUANTITY],
case [SALESORDERITEM].[TYPECODE]
when 5 then 0
else coalesce(sum([SALESORDERITEM].[TOTAL]),0)
end as [GROSS],
case [SALESORDERITEM].[TYPECODE]
when 1 then ( select
coalesce(sum([SOIMIP].[AMOUNT]),0)
from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION SOIMIP
inner join dbo.SALESORDERITEM SOI2 on SOIMIP.SALESORDERITEMID = SOI2.ID
inner join dbo.SALESORDERITEMMEMBERSHIP SOIM2 on SOI2.ID = SOIM2.ID
where
SOIM2.MEMBERSHIPPROGRAMNAME = SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMNAME and
SOI2.SALESORDERID in ( select ID
from dbo.SALESORDER SO2
where
([SO2].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
[SO2].[TRANSACTIONDATE] between @FROM and @TO and
[SO2].[STATUSCODE] = 1 ) )
when 5 then isnull(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0)
else coalesce(sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]),0)
end as [DISCOUNT],
0 as [REFUND],
case [SALESORDERITEM].[TYPECODE]
when 5 then 1
else 0
end as [ISORDERDISCOUNT],
case [SALESORDERITEM].[TYPECODE]
when 0 then [SALESORDERITEMTICKET].[PROGRAMCATEGORYNAME]
when 8 then [SALESORDERITEMITINERARYRESOURCE].[RESOURCECATEGORYNAME]
when 9 then [SALESORDERITEMITINERARYITEMRESOURCE].[RESOURCECATEGORYNAME]
else null
end as [CATEGORY]
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDER]
on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
--Tickets
left join dbo.[SALESORDERITEMTICKET]
on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
--Item level discounts
left join dbo.[SALESORDERITEMITEMDISCOUNT]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID]
--Memberships
left join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
--Donations
left join dbo.[SALESORDERITEMDONATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
--Order discounts
left join dbo.[SALESORDERITEMORDERDISCOUNT]
on [SALESORDERITEM].[ID] = [SALESORDERITEMORDERDISCOUNT].[ID]
--Resources (for the purposes of this report, combine itinerary and itinerary item resources)
left join dbo.[SALESORDERITEMITINERARYRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYRESOURCE].[SALESORDERITEMID]
left join dbo.[SALESORDERITEMITINERARYITEMRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMRESOURCE].[SALESORDERITEMID]
--Staffing Resources (for the purposes of this report, combine itinerary and itinerary item resources)
left join dbo.[SALESORDERITEMITINERARYSTAFFRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYSTAFFRESOURCE].[SALESORDERITEMID]
left join dbo.[SALESORDERITEMITINERARYITEMSTAFFRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[SALESORDERITEMID]
--Fees
left join dbo.[SALESORDERITEMFEE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
--Facility Rentals
left join dbo.[SALESORDERITEMFACILITY]
on [SALESORDERITEM].[ID] = [SALESORDERITEMFACILITY].ID
--Merchandise
left join dbo.[SALESORDERITEMMERCHANDISE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join dbo.[MERCHANDISEDEPARTMENT]
on [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
-- Order level discounts
left outer join
dbo.SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMORDERDISCOUNTID = SALESORDERITEM.ID
left outer join
dbo.SALESORDERITEM DISCOUNTEDITEM on DISCOUNTEDITEM.ID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
--ADD MORE SALESORDERITEMS HERE--
where
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
[SALESORDER].[TRANSACTIONDATE] between @FROM and @TO and
[SALESORDER].[STATUSCODE] = 1 and
([SALESORDERITEM].[TYPECODE] <> 4 and [SALESORDERITEM].[TYPECODE] <> 6) --Report does not include taxes, event registrations are below due to complex refunds
group by
[SALESORDERITEM].[TYPECODE],
[SALESORDERITEMTICKET].[PROGRAMNAME],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME],
[SALESORDERITEMDONATION].[DESIGNATIONNAME],
[SALESORDERITEMFEE].[FEENAME],
[SALESORDERITEMORDERDISCOUNT].[DISCOUNTNAME],
[SALESORDERITEMITINERARYRESOURCE].[RESOURCENAME],
[SALESORDERITEMITINERARYITEMRESOURCE].[RESOURCENAME],
[SALESORDERITEMITINERARYSTAFFRESOURCE].[VOLUNTEERTYPENAME],
[SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[VOLUNTEERTYPENAME],
[SALESORDERITEMTICKET].[PROGRAMCATEGORYNAME],
[SALESORDERITEMITINERARYRESOURCE].[RESOURCECATEGORYNAME],
[SALESORDERITEMITINERARYITEMRESOURCE].[RESOURCECATEGORYNAME],
[SALESORDERITEMFACILITY].[EVENTLOCATIONNAME],
[MERCHANDISEDEPARTMENT].[NAME],
SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT,
DISCOUNTEDITEM.TYPECODE
--In-order Refunds (except for registrations)
union all
select
case
when [SALESORDERITEM].[TYPECODE] = 0 then 0
when [SALESORDERITEM].[TYPECODE] = 1 then 10
when [SALESORDERITEM].[TYPECODE] = 3 then 30
when [SALESORDERITEM].[TYPECODE] = 14 then 40
when [SALESORDERITEM].[TYPECODE] = 5 then
case [DISCOUNTEDITEM].[TYPECODE]
when 0 then 0
when 14 then 40
end
end as [SALESORDERITEMTYPECODE],
case [SALESORDERITEM].[TYPECODE]
when 0 then [SALESORDERITEMTICKET].[PROGRAMNAME]
when 1 then [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME]
when 3 then [SALESORDERITEMFEE].[FEENAME]
when 5 then [SALESORDERITEMORDERDISCOUNT].[DISCOUNTNAME]
when 14 then [MERCHANDISEDEPARTMENT].[NAME]
end as [PROGRAMNAME],
case [SALESORDERITEM].[TYPECODE]
when 5 then 0
else
-1 * coalesce(sum(FINANCIALTRANSACTIONLINEITEM.QUANTITY),0)
end as [QUANTITY],
0 as [GROSS],
0 as [DISCOUNT],
case [SALESORDERITEM].[TYPECODE]
when 5 then -1 * isnull(sum([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT]), 0)
else coalesce(sum((FINANCIALTRANSACTIONLINEITEM.QUANTITY * FINANCIALTRANSACTIONLINEITEM.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS),0)
end [REFUND],
case [SALESORDERITEM].[TYPECODE]
when 5 then 1
else 0
end as [ISORDERDISCOUNT],
case [SALESORDERITEM].[TYPECODE]
when 0 then [SALESORDERITEMTICKET].[PROGRAMCATEGORYNAME]
else null
end as [CATEGORY]
from
dbo.FINANCIALTRANSACTION
inner join
dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join
dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = CREDITITEM_EXT.SALESORDERITEMID
inner join dbo.[SALESORDER]
on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
--Tickets
left join dbo.[SALESORDERITEMTICKET]
on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
--Memberships
left join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
--Fees
left join dbo.[SALESORDERITEMFEE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
--Merchandise
left join dbo.[SALESORDERITEMMERCHANDISE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join dbo.[MERCHANDISEDEPARTMENT]
on [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
--Order discounts
left join
dbo.[SALESORDERITEMORDERDISCOUNT] on [SALESORDERITEM].[ID] = [SALESORDERITEMORDERDISCOUNT].[ID]
left outer join
dbo.[SALESORDERITEMORDERDISCOUNTDETAIL] on [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMORDERDISCOUNTID] = [SALESORDERITEM].[ID]
left outer join
dbo.SALESORDERITEM DISCOUNTEDITEM on [DISCOUNTEDITEM].[ID] = [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID]
where
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
[SALESORDERITEM].[TYPECODE] in (0,1,3,5,14) and
FINANCIALTRANSACTION.CALCULATEDDATE between @FROM and @TO
and FINANCIALTRANSACTION.TYPECODE = 23 -- Refund
group by
[SALESORDERITEM].[TYPECODE],
[SALESORDERITEMTICKET].[PROGRAMNAME],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME],
[SALESORDERITEMFEE].[FEENAME],
[SALESORDERITEMTICKET].[PROGRAMCATEGORYNAME],
[SALESORDERITEMORDERDISCOUNT].[DISCOUNTNAME],
[MERCHANDISEDEPARTMENT].[NAME],
[SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT],
[SALESORDERITEMORDERDISCOUNTDETAIL].[ID],
[DISCOUNTEDITEM].[TYPECODE]
--Outside-order Refunds (Memberships)
union all
select
10 as [SALESORDERITEMTYPECODE],
[CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME],
-1 * count(1) as [QUANTITY],
0 as [GROSS],
0 as [DISCOUNT],
sum((FINANCIALTRANSACTIONLINEITEM.QUANTITY * FINANCIALTRANSACTIONLINEITEM.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS) as [REFUND],
0 as [ISORDERDISCOUNT],
null as CATEGORY
from
dbo.FINANCIALTRANSACTION
inner join
dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join
dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join
dbo.CREDITITEMMEMBERSHIP on CREDITITEMMEMBERSHIP.ID = FINANCIALTRANSACTIONLINEITEM.ID
where
@SALESMETHODTYPECODE is null
and FINANCIALTRANSACTION.CALCULATEDDATE between @FROM and @TO
and FINANCIALTRANSACTION.TYPECODE = 23 -- Refund
and CREDITITEM_EXT.SALESORDERITEMID is null
group by
[CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMNAME]
--Event Registrations and event registration refunds
union all
select
5 as [SALESORDERITEMTYPECODE],
[PROGRAMNAME],
sum(
case
when [GROSS] = 0 and [REFUND] > 0 then
-1
when [GROSS] > [REFUND] or ([GROSS] = 0 and [REFUND] = 0) then
1
else
0
end
) [QUANTITY],
sum([GROSS]) [GROSS],
0 as [DISCOUNT],
sum([REFUND]) [REFUND],
0 as [ISORDERDISCOUNT],
null as [CATEGORY]
from --Sum the gross and refund for each registrant so we can compare it for the quantity calculation
(select
[PROGRAMNAME],
sum([GROSS]) as [GROSS],
sum([REFUND]) as [REFUND],
[REGISTRANTID]
from --Get all the payment and refund pieces so that we can aggregate them for each registrant
(select --Event registration payments
[EVENT].[NAME] as [PROGRAMNAME],
coalesce(sum([SALESORDERITEM].[TOTAL]),0) as [GROSS],
0 as [REFUND],
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as REGISTRANTID
from
dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join
dbo.[SALESORDERITEM] on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
inner join dbo.[REGISTRANT]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
inner join dbo.[EVENT]
on [REGISTRANT].[EVENTID] = [EVENT].[ID]
inner join dbo.[SALESORDER]
on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
where [SALESORDER].[TRANSACTIONDATE] between @FROM and @TO and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
group by
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID],
[EVENT].[NAME]
union all
select
[EVENT].[NAME] as [PROGRAMNAME],
0 as [GROSS],
coalesce(sum((FINANCIALTRANSACTIONLINEITEM.QUANTITY * FINANCIALTRANSACTIONLINEITEM.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS),0) as [REFUND],
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as REGISTRANTID
from
dbo.FINANCIALTRANSACTION
inner join
dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join
dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = CREDITITEM_EXT.SALESORDERITEMID
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
inner join dbo.[REGISTRANT]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
inner join dbo.[EVENT]
on [REGISTRANT].[EVENTID] = [EVENT].[ID]
inner join dbo.[SALESORDER]
on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
where
FINANCIALTRANSACTION.CALCULATEDDATE between @FROM and @TO
and ([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
group by
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID],
[EVENT].[NAME]
--Event Registration refunds/exchanges purchased outside the context of an order
union all
select
[EVENT].[NAME] as [PROGRAMNAME],
0 as [GROSS],
coalesce(sum(EVENTREGISTRATIONTRANSACTION.BASEAMOUNT),0) as [REFUND],
[EVENTREGISTRANTPAYMENT].[REGISTRANTID] as REGISTRANTID
from
dbo.FINANCIALTRANSACTION as REFUNDTRANSACTION
inner join
dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = REFUNDTRANSACTION.ID
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as REFUNDEDLINEITEM on REFUNDEDLINEITEM.ID = CREDITPAYMENT.REVENUESPLITID
inner join
dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.PAYMENTID = REFUNDEDLINEITEM.ID
inner join
dbo.[REGISTRANT] on [REGISTRANT].[ID] = [EVENTREGISTRANTPAYMENT].[REGISTRANTID]
inner join
dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
inner join
dbo.FINANCIALTRANSACTION as EVENTREGISTRATIONTRANSACTION on EVENTREGISTRATIONTRANSACTION.ID = REFUNDEDLINEITEM.FINANCIALTRANSACTIONID
where
REFUNDTRANSACTION.CALCULATEDDATE between @FROM and @TO
and @SALESMETHODTYPECODE is null
group by
[EVENTREGISTRANTPAYMENT].[REGISTRANTID], [EVENT].[NAME]
) PAYMENTPIECES
group by
[PROGRAMNAME], [REGISTRANTID]
)REGISTRANTS
group by [PROGRAMNAME], [REGISTRANTID]) REPORTITEMS
order by
[SALESORDERITEMTYPECODE],
[ISORDERDISCOUNT],
[PROGRAMNAME]
return 0;