USP_DATALIST_SALESCOMPARISONREPORT
Returns list of sales revenue from two periods of time.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @THISFROMDATE | datetime | IN | This period start date |
| @THISTODATE | datetime | IN | This period end date |
| @LASTFROMDATE | datetime | IN | Last period start date |
| @LASTTODATE | datetime | IN | Last period end date |
| @SALESMETHODID | uniqueidentifier | IN | Sales method |
| @ITEMTYPECODE | tinyint | IN | Item type |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_SALESCOMPARISONREPORT
(
@THISFROMDATE datetime = null,
@THISTODATE datetime = null,
@LASTFROMDATE datetime = null,
@LASTTODATE datetime = null,
@SALESMETHODID uniqueidentifier = null,
@ITEMTYPECODE tinyint = null
)
as
set nocount on;
set @THISFROMDATE = cast(@THISFROMDATE as date)
set @THISTODATE = dbo.UFN_DATE_GETLATESTTIME(@THISTODATE)
set @LASTFROMDATE = cast(@LASTFROMDATE as date)
set @LASTTODATE = dbo.UFN_DATE_GETLATESTTIME(@LASTTODATE)
declare @SALESMETHODTYPECODE tinyint = null;
select @SALESMETHODTYPECODE = [TYPECODE]
from dbo.[SALESMETHOD]
where [ID] = @SALESMETHODID;
--This Period Sales Item Totals
declare @THISPERIODSALESORDERTOTALS table (
TOTAL money,
QUANTITY decimal(20,0),
PROGRAMID uniqueidentifier,
MEMBERSHIPPROGRAMID uniqueidentifier,
DESIGNATIONID uniqueidentifier,
REGISTRANTEVENTID uniqueidentifier,
RESOURCEID uniqueidentifier,
VOLUNTEERTYPEID uniqueidentifier,
ITEMDISCOUNTS money,
LOCATIONID uniqueidentifier,
MERCHANDISEDEPARTMENTID uniqueidentifier,
ADDONID uniqueidentifier
)
insert into @THISPERIODSALESORDERTOTALS
select
sum([SALESORDERITEM].[TOTAL]) - case [SALESORDERITEM].[TYPECODE]
when 1 then
sum(SALESORDERITEMMEMBERSHIPITEMPROMOTIONS.AMOUNT)
else
sum(SALESORDERITEMITEMDISCOUNTS.AMOUNT)
end,
case [SALESORDERITEM].[TYPECODE]
when 6 then count(distinct [REGISTRANT].[ID])
else sum([SALESORDERITEM].[QUANTITY])
end,
coalesce([SALESORDERITEMTICKET].[PROGRAMID], [EVENT].[PROGRAMID]) as [PROGRAMID],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID],
[SALESORDERITEMDONATION].[DESIGNATIONID],
[REGISTRANT].[EVENTID] as [REGISTRANTEVENTID],
coalesce(ITINERARYRESOURCE.RESOURCEID, ITINERARYITEMRESOURCE.RESOURCEID) as [RESOURCEID],
coalesce(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID, ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID) as [VOLUNTEERTYPEID],
case [SALESORDERITEM].[TYPECODE]
when 1 then
sum(SALESORDERITEMMEMBERSHIPITEMPROMOTIONS.AMOUNT)
else
sum(SALESORDERITEMITEMDISCOUNTS.AMOUNT)
end as [ITEMDISCOUNTS],
[SALESORDERITEMFACILITY].[EVENTLOCATIONID] as [LOCATIONID],
[SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] as [MERCHANDISEDEPARTMENTID],
SALESORDERITEMMEMBERSHIPADDON.ADDONID as ADDONID
from
dbo.UFN_SALESORDER_BETWEENDATES(@THISFROMDATE, @THISTODATE) as FILTEREDSALESORDERS
inner join
dbo.SALESORDER on SALESORDER.ID = FILTEREDSALESORDERS.ID
inner join dbo.[SALESORDERITEM]
on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
left join dbo.[SALESORDERITEMTICKET]
on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
left join dbo.[EVENT]
on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
left join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left join dbo.[SALESORDERITEMDONATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
left join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
left join dbo.[SALESORDERITEMITINERARYRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYRESOURCE]
on [SALESORDERITEMITINERARYRESOURCE].[ITINERARYRESOURCEID] = [ITINERARYRESOURCE].[ID]
left join dbo.[SALESORDERITEMITINERARYITEMRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYITEMRESOURCE]
on [SALESORDERITEMITINERARYITEMRESOURCE].[ITINERARYITEMRESOURCEID] = [ITINERARYITEMRESOURCE].[ID]
left join dbo.[SALESORDERITEMITINERARYSTAFFRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYSTAFFRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYSTAFFRESOURCE]
on [SALESORDERITEMITINERARYSTAFFRESOURCE].[ITINERARYSTAFFRESOURCEID] = [ITINERARYSTAFFRESOURCE].[ID]
left join dbo.[SALESORDERITEMITINERARYITEMSTAFFRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYITEMSTAFFRESOURCE]
on [SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[ITINERARYITEMSTAFFRESOURCEID] = [ITINERARYITEMSTAFFRESOURCE].[ID]
left join dbo.[REGISTRANT]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
left join dbo.[SALESORDERITEMFACILITY]
on [SALESORDERITEM].[ID] = [SALESORDERITEMFACILITY].[ID]
left join dbo.[SALESORDERITEMMERCHANDISE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join dbo.SALESORDERITEMMEMBERSHIPADDON
on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
outer apply (
select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMITEMDISCOUNT
where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
) as SALESORDERITEMITEMDISCOUNTS
outer apply (
select isnull(sum(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
where SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
) as SALESORDERITEMMEMBERSHIPITEMPROMOTIONS
where
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
[SALESORDER].[STATUSCODE] = 1 and
(
(
[SALESORDERITEM].[TYPECODE] = @ITEMTYPECODE or
([SALESORDERITEM].[TYPECODE] = 9 and @ITEMTYPECODE = 8) or
([SALESORDERITEM].[TYPECODE] = 11 and @ITEMTYPECODE = 10) or
@ITEMTYPECODE is null
) and
[SALESORDERITEM].[TYPECODE] in (0,1,2,6,7,8,9,10,11,14,16)
)
group by
[SALESORDERITEM].[TYPECODE],
coalesce([SALESORDERITEMTICKET].[PROGRAMID], [EVENT].[PROGRAMID]),
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID],
[SALESORDERITEMDONATION].[DESIGNATIONID],
coalesce(ITINERARYRESOURCE.RESOURCEID, ITINERARYITEMRESOURCE.RESOURCEID),
coalesce(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID, ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID),
[REGISTRANT].[EVENTID],
[REGISTRANT].[ID],
[SALESORDERITEMFACILITY].[EVENTLOCATIONID],
[SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID],
SALESORDERITEMMEMBERSHIPADDON.ADDONID
option (recompile);
--This Period Sales Order Discounts
declare @THISPERIODORDERDISCOUNTS table (
TOTAL money,
PROGRAMID uniqueidentifier,
MERCHANDISEDEPARTMENTID uniqueidentifier,
TYPECODE tinyint
)
--This Period Sales Order Discount Refunds
declare @THISPERIODORDERDISCOUNTREFUNDS table (
TOTAL money,
PROGRAMID uniqueidentifier,
MERCHANDISEDEPARTMENTID uniqueidentifier,
TYPECODE tinyint
)
if @ITEMTYPECODE in(0,14) or @ITEMTYPECODE is null begin
insert into @THISPERIODORDERDISCOUNTS
select
sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT),
SALESORDERITEMTICKET.PROGRAMID,
SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID,
case
when SALESORDERITEMTICKET.PROGRAMID is not null then
0
else
1
end
from
dbo.SALESORDERITEMORDERDISCOUNTDETAIL
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
inner join
dbo.UFN_SALESORDER_BETWEENDATES(@THISFROMDATE, @THISTODATE) as FILTEREDSALESORDERS on FILTEREDSALESORDERS.ID = SALESORDERITEM.SALESORDERID
inner join
dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
left outer join
dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
left outer join
dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
where
(SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
and SALESORDER.STATUSCODE = 1
and (@ITEMTYPECODE is null or SALESORDERITEM.TYPECODE = @ITEMTYPECODE)
group by
SALESORDERITEMTICKET.PROGRAMID, SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID
option (recompile);
insert into @THISPERIODORDERDISCOUNTREFUNDS
select
-sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT),
SALESORDERITEMTICKET.PROGRAMID,
SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID,
case
when SALESORDERITEMTICKET.PROGRAMID is not null then
0
else
1
end
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = EXT.SALESORDERITEMID
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
inner join
dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
left outer join
dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
left outer join
dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = EXT.SALESORDERITEMID
where
(SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
and FT.CALCULATEDDATE between @THISFROMDATE and @THISTODATE
and FT.TYPECODE = 23 -- Refund
and SALESORDER.STATUSCODE = 1
and (@ITEMTYPECODE is null or SALESORDERITEM.TYPECODE = @ITEMTYPECODE)
group by
SALESORDERITEMTICKET.PROGRAMID, SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID
option (recompile);
end
--Last Period Sales Program Totals
declare @LASTPERIODSALESORDERTOTALS table (
TOTAL money,
QUANTITY decimal(20,0),
PROGRAMID uniqueidentifier,
MEMBERSHIPPROGRAMID uniqueidentifier,
DESIGNATIONID uniqueidentifier,
REGISTRANTEVENTID uniqueidentifier,
RESOURCEID uniqueidentifier,
VOLUNTEERTYPEID uniqueidentifier,
ITEMDISCOUNTS money,
LOCATIONID uniqueidentifier,
MERCHANDISEDEPARTMENTID uniqueidentifier,
ADDONID uniqueidentifier
)
insert into @LASTPERIODSALESORDERTOTALS
select
sum([SALESORDERITEM].[TOTAL]) - case [SALESORDERITEM].[TYPECODE]
when 1 then
sum(SALESORDERITEMMEMBERSHIPITEMPROMOTIONS.AMOUNT)
else
sum(SALESORDERITEMITEMDISCOUNTS.AMOUNT)
end,
case [SALESORDERITEM].[TYPECODE]
when 6 then count(distinct [REGISTRANT].[ID])
else sum([SALESORDERITEM].[QUANTITY])
end,
coalesce([SALESORDERITEMTICKET].[PROGRAMID], [EVENT].[PROGRAMID]) as [PROGRAMID],
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID],
[SALESORDERITEMDONATION].[DESIGNATIONID],
[REGISTRANT].[EVENTID] as [REGISTRANTEVENTID],
coalesce(ITINERARYRESOURCE.RESOURCEID, ITINERARYITEMRESOURCE.RESOURCEID) as [RESOURCEID],
coalesce(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID, ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID) as [VOLUNTEERTYPEID],
case [SALESORDERITEM].[TYPECODE]
when 1 then
sum(SALESORDERITEMMEMBERSHIPITEMPROMOTIONS.AMOUNT)
else
sum(SALESORDERITEMITEMDISCOUNTS.AMOUNT)
end as [ITEMDISCOUNTS],
[SALESORDERITEMFACILITY].[EVENTLOCATIONID] as [LOCATIONID],
[SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] as [MERCHANDISEDEPARTMENTID],
SALESORDERITEMMEMBERSHIPADDON.ADDONID as ADDONID
from
dbo.UFN_SALESORDER_BETWEENDATES(@LASTFROMDATE, @LASTTODATE) as FILTEREDSALESORDERS
inner join
dbo.SALESORDER on SALESORDER.ID = FILTEREDSALESORDERS.ID
inner join dbo.[SALESORDERITEM]
on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
left join dbo.[SALESORDERITEMTICKET]
on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
left join dbo.[EVENT]
on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
left join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left join dbo.[SALESORDERITEMDONATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
left join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
left join dbo.[SALESORDERITEMITINERARYRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYRESOURCE]
on [SALESORDERITEMITINERARYRESOURCE].[ITINERARYRESOURCEID] = [ITINERARYRESOURCE].[ID]
left join dbo.[SALESORDERITEMITINERARYITEMRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYITEMRESOURCE]
on [SALESORDERITEMITINERARYITEMRESOURCE].[ITINERARYITEMRESOURCEID] = [ITINERARYITEMRESOURCE].[ID]
left join dbo.[SALESORDERITEMITINERARYSTAFFRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYSTAFFRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYSTAFFRESOURCE]
on [SALESORDERITEMITINERARYSTAFFRESOURCE].[ITINERARYSTAFFRESOURCEID] = [ITINERARYSTAFFRESOURCE].[ID]
left join dbo.[SALESORDERITEMITINERARYITEMSTAFFRESOURCE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[SALESORDERITEMID]
left join dbo.[ITINERARYITEMSTAFFRESOURCE]
on [SALESORDERITEMITINERARYITEMSTAFFRESOURCE].[ITINERARYITEMSTAFFRESOURCEID] = [ITINERARYITEMSTAFFRESOURCE].[ID]
left join dbo.[REGISTRANT]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
left join dbo.[SALESORDERITEMFACILITY]
on [SALESORDERITEM].[ID] = [SALESORDERITEMFACILITY].[ID]
left join dbo.[SALESORDERITEMMERCHANDISE]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join dbo.SALESORDERITEMMEMBERSHIPADDON
on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
outer apply (
select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMITEMDISCOUNT
where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
) as SALESORDERITEMITEMDISCOUNTS
outer apply (
select isnull(sum(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT), 0) as AMOUNT
from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
where SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
) as SALESORDERITEMMEMBERSHIPITEMPROMOTIONS
where
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
[SALESORDER].[STATUSCODE] = 1 and
(
(
[SALESORDERITEM].[TYPECODE] = @ITEMTYPECODE or
([SALESORDERITEM].[TYPECODE] = 9 and @ITEMTYPECODE = 8) or
([SALESORDERITEM].[TYPECODE] = 11 and @ITEMTYPECODE = 10) or
@ITEMTYPECODE is null
) and
[SALESORDERITEM].[TYPECODE] in (0,1,2,6,7,8,9,10,11,14,16)
)
group by
[SALESORDERITEM].[TYPECODE],
coalesce([SALESORDERITEMTICKET].[PROGRAMID], [EVENT].[PROGRAMID]),
[SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID],
[SALESORDERITEMDONATION].[DESIGNATIONID],
coalesce(ITINERARYRESOURCE.RESOURCEID, ITINERARYITEMRESOURCE.RESOURCEID),
coalesce(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID, ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID),
[REGISTRANT].[EVENTID],
[REGISTRANT].[ID],
[SALESORDERITEMFACILITY].[EVENTLOCATIONID],
[SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID],
SALESORDERITEMMEMBERSHIPADDON.ADDONID
option (recompile);
--Last Period Sales Order Discounts
declare @LASTPERIODORDERDISCOUNTS table (
TOTAL money,
PROGRAMID uniqueidentifier,
MERCHANDISEDEPARTMENTID uniqueidentifier,
TYPECODE tinyint
)
--Last Period Sales Order Discount Refunds
declare @LASTPERIODORDERDISCOUNTREFUNDS table (
TOTAL money,
PROGRAMID uniqueidentifier,
MERCHANDISEDEPARTMENTID uniqueidentifier,
TYPECODE tinyint
)
if @ITEMTYPECODE in(0,14) or @ITEMTYPECODE is null begin
insert into @LASTPERIODORDERDISCOUNTS
select
sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT),
SALESORDERITEMTICKET.PROGRAMID,
SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID,
case
when SALESORDERITEMTICKET.PROGRAMID is not null then
0
else
1
end
from
dbo.SALESORDERITEMORDERDISCOUNTDETAIL
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
inner join
dbo.UFN_SALESORDER_BETWEENDATES(@LASTFROMDATE, @LASTTODATE) as FILTEREDSALESORDERS on FILTEREDSALESORDERS.ID = SALESORDERITEM.SALESORDERID
inner join
dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
left outer join
dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
left outer join
dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
where
(SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
and SALESORDER.STATUSCODE = 1
and (@ITEMTYPECODE is null or SALESORDERITEM.TYPECODE = @ITEMTYPECODE)
group by
SALESORDERITEMTICKET.PROGRAMID, SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID
option (recompile);
insert into @LASTPERIODORDERDISCOUNTREFUNDS
select
-sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT),
SALESORDERITEMTICKET.PROGRAMID,
SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID,
case
when SALESORDERITEMTICKET.PROGRAMID is not null then
0
else
1
end
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.SALESORDERITEMORDERDISCOUNTDETAIL on SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = EXT.SALESORDERITEMID
inner join
dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
inner join
dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
left outer join
dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = EXT.SALESORDERITEMID
left outer join
dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = EXT.SALESORDERITEMID
where
(SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)
and FT.CALCULATEDDATE between @LASTFROMDATE and @LASTTODATE
and FT.TYPECODE = 23 -- Refund
and SALESORDER.STATUSCODE = 1
and (@ITEMTYPECODE is null or SALESORDERITEM.TYPECODE = @ITEMTYPECODE)
group by
SALESORDERITEMTICKET.PROGRAMID, SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID
option (recompile);
end
--This Period Tickets
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.[PROGRAM]
on [THISPERIODSOT].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
--This Period Ticket Order Discounts
union all
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
-[THISPERIODOD].[TOTAL] as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODORDERDISCOUNTS as [THISPERIODOD]
inner join dbo.[PROGRAM]
on [THISPERIODOD].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where THISPERIODOD.TYPECODE = 0
--This ticket refunds/exchanges
union all
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [THISPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
inner join
dbo.[SALESORDERITEMTICKET] on [SALESORDERITEMTICKET].[ID] = EXT.SALESORDERITEMID
inner join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join
dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where
(@ITEMTYPECODE = 0 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @THISFROMDATE and @THISTODATE and
FT.TYPECODE = 23 -- Refund
group by
[PROGRAM].[NAME],
[PROGRAMCATEGORYCODE].[DESCRIPTION]
--This Period Ticket Order Discount Refunds
union all
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
-[THISPERIODODR].[TOTAL] as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODORDERDISCOUNTREFUNDS as [THISPERIODODR]
inner join dbo.[PROGRAM]
on [THISPERIODODR].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where THISPERIODODR.TYPECODE = 0
--Last Period Tickets
union all
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.[PROGRAM]
on [LASTPERIODSOT].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
--Last Period Ticket Order Discounts
union all
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-[LASTPERIODOD].[TOTAL] as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODORDERDISCOUNTS as [LASTPERIODOD]
inner join dbo.[PROGRAM]
on [LASTPERIODOD].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where LASTPERIODOD.TYPECODE = 0
--Last Period Ticket Order Discount Refunds
union all
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-[LASTPERIODODR].[TOTAL] as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODORDERDISCOUNTREFUNDS as [LASTPERIODODR]
inner join dbo.[PROGRAM]
on [LASTPERIODODR].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where LASTPERIODODR.TYPECODE = 0
--Last ticket refunds/exchanges
union all
select
0 as [SALESORDERITEMTYPE],
[PROGRAM].[NAME] as [PROGRAMNAME],
coalesce([PROGRAMCATEGORYCODE].[DESCRIPTION],'') as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [LASTPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
inner join
dbo.[SALESORDERITEMTICKET] on [SALESORDERITEMTICKET].[ID] = EXT.SALESORDERITEMID
inner join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join dbo.[PROGRAM]
on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
left join dbo.[PROGRAMCATEGORYCODE]
on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
where
(@ITEMTYPECODE = 0 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @LASTFROMDATE and @LASTTODATE and
FT.TYPECODE = 23 -- Refunds
group by
[PROGRAM].[NAME],
[PROGRAMCATEGORYCODE].[DESCRIPTION]
--This Period Event Registrations
union all
select
1 as [SALESORDERITEMTYPE],
[EVENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.[EVENT]
on [THISPERIODSOT].[REGISTRANTEVENTID] = [EVENT].[ID]
--This Period Event Registration refunds/exchanges
union all
select
1 as [SALESORDERITEMTYPE],
[REFUNDS].[PROGRAMNAME],
'' as [PROGRAMCATEGORY],
-sum([REFUNDS].[THISPERIODTOTAL]) [THISPERIODTOTAL],
-sum([REFUNDS].[REFUNDED]) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from (
select
[REGISTRANTREFUNDS].[PROGRAMNAME],
sum([REGISTRANTREFUNDS].[THISPERIODTOTAL]) as [THISPERIODTOTAL],
--When the amount in sales refunds for this registration (summing refunds prior to the last refund
--by the currently selected sales method) is greater than or equal to the amount in sales payments (summing payments
--prior to the end date selected in the filter), then the registration is considered refunded.
case when (
select
coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0)
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[CREDITITEMEVENTREGISTRATION] on [CREDITITEMEVENTREGISTRATION].[ID] = LI.ID
where
FT.TYPECODE = 23 -- Refund
and FT.CALCULATEDDATE <= [REGISTRANTREFUNDS].[LASTREFUNDDATE]
and [CREDITITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREFUNDS].[REGISTRANTID]
) >= (
select
coalesce(sum([SALESORDERITEM].[TOTAL]),0)
from
dbo.UFN_SALESORDER_BETWEENDATES(null, @THISTODATE) as FILTEREDSALESORDERS
inner join
dbo.SALESORDER on SALESORDER.ID = FILTEREDSALESORDERS.ID
inner join
dbo.[SALESORDERITEM] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
inner join
dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where
[SALESORDER].[STATUSCODE] = 1 and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREFUNDS].[REGISTRANTID]
) then 1
else 0
end as [REFUNDED]
from (
select
[EVENT].[NAME] as [PROGRAMNAME],
coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [THISPERIODTOTAL],
[REGISTRANT].[ID] as [REGISTRANTID],
cast(max(FT.DATE) as datetime) as [LASTREFUNDDATE]
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[CREDITITEMEVENTREGISTRATION] on [CREDITITEMEVENTREGISTRATION].[ID] = LI.ID
inner join
dbo.[REGISTRANT] on [CREDITITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
inner join
dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join dbo.[SALESORDER]
on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
where
(@ITEMTYPECODE = 6 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @THISFROMDATE and @THISTODATE and
FT.TYPECODE = 23 -- Refund
group by
[EVENT].[NAME],
[REGISTRANT].[ID]
) as [REGISTRANTREFUNDS]
group by
[REGISTRANTREFUNDS].[REGISTRANTID],
[REGISTRANTREFUNDS].[PROGRAMNAME],
[REGISTRANTREFUNDS].[LASTREFUNDDATE]
) as [REFUNDS]
group by [REFUNDS].[PROGRAMNAME]
--Last Period Event Registrations
union all
select
1 as [SALESORDERITEMTYPE],
[EVENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.[EVENT]
on [LASTPERIODSOT].[REGISTRANTEVENTID] = [EVENT].[ID]
--Last Period Event Registration refunds/exchanges
union all
select
1 as [SALESORDERITEMTYPE],
[REFUNDS].[PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-sum([REFUNDS].[THISPERIODTOTAL]) as [LASTPERIODTOTAL],
-sum([REFUNDS].[REFUNDED]) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from (
select
[REGISTRANTREFUNDS].[PROGRAMNAME],
sum([REGISTRANTREFUNDS].[THISPERIODTOTAL]) as [THISPERIODTOTAL],
--When the amount in sales refunds for this registration (summing refunds prior to the last refund
--by the currently selected sales method) is greater than or equal to the amount in sales payments (summing payments
--prior to the end date selected in the filter), then the registration is considered refunded.
case when (
select
coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0)
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
inner join
dbo.[CREDITITEMEVENTREGISTRATION] on [CREDITITEMEVENTREGISTRATION].[ID] = LI.ID
where
FT.TYPECODE = 23 -- Refund
and FT.CALCULATEDDATE <= [REGISTRANTREFUNDS].[LASTREFUNDDATE]
and [CREDITITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREFUNDS].[REGISTRANTID]
) >= (
select
coalesce(sum([SALESORDERITEM].[TOTAL]),0)
from
dbo.UFN_SALESORDER_BETWEENDATES(null, @LASTTODATE) as FILTEREDSALESORDERS
inner join
dbo.[SALESORDER] on SALESORDER.ID = FILTEREDSALESORDERS.ID
inner join
dbo.[SALESORDERITEM] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
inner join
dbo.[SALESORDERITEMEVENTREGISTRATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where
[SALESORDER].[STATUSCODE] = 1 and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREFUNDS].[REGISTRANTID]
) then 1
else 0
end as [REFUNDED]
from (
select
[EVENT].[NAME] as [PROGRAMNAME],
coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [THISPERIODTOTAL],
[REGISTRANT].[ID] as [REGISTRANTID],
cast(max(FT.DATE) as datetime) as [LASTREFUNDDATE]
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[CREDITITEMEVENTREGISTRATION] on [CREDITITEMEVENTREGISTRATION].[ID] = LI.ID
inner join
dbo.[REGISTRANT] on [CREDITITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
inner join
dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
where
(@ITEMTYPECODE = 6 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @LASTFROMDATE and @LASTTODATE and
FT.TYPECODE = 23 -- REfund
group by
[EVENT].[NAME],
[REGISTRANT].[ID]
) as [REGISTRANTREFUNDS]
group by
[REGISTRANTREFUNDS].[REGISTRANTID],
[REGISTRANTREFUNDS].[PROGRAMNAME],
[REGISTRANTREFUNDS].[LASTREFUNDDATE]
) as [REFUNDS]
group by [REFUNDS].[PROGRAMNAME]
--This Period Memberships
union all
select
2 as [SALESORDERITEMTYPE],
[MEMBERSHIPPROGRAM].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.[MEMBERSHIPPROGRAM]
on [THISPERIODSOT].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
--This period membership add-ons
union all
select
16 as SALESORDERITEMTYPE,
ADDON.NAME as PROGRAMNAME,
'' as PROGRAMCATEGORY,
coalesce(THISPERIODSOT.TOTAL,0) as THISPERIODTOTAL,
coalesce(THISPERIODSOT.QUANTITY,0) as THISPERIODQUANTITY,
0 as LASTPERIODTOTAL,
0 as LASTPERIODQUANTITY,
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as THISPERIODSOT
inner join dbo.ADDON
on ADDON.ID = THISPERIODSOT.ADDONID
--This Period Membership refunds/exchanges
union all
select
2 as [SALESORDERITEMTYPE],
[MEMBERSHIPPROGRAM].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [THISPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[CREDITITEMMEMBERSHIP] on [CREDITITEMMEMBERSHIP].[ID] = LI.ID
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join
dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join
dbo.[MEMBERSHIPPROGRAM] on [CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
where
(@ITEMTYPECODE = 1 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @THISFROMDATE and @THISTODATE and
FT.TYPECODE = 23 -- Refund
group by
[MEMBERSHIPPROGRAM].[NAME]
--This Period Membership add-ons refunds/exchanges
union all
select
16 as [SALESORDERITEMTYPE],
[ADDON].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [THISPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = LI.SOURCELINEITEMID
inner join
dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
where
(@ITEMTYPECODE = 16 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @THISFROMDATE and @THISTODATE and
FT.TYPECODE = 23 -- Refund
group by
[ADDON].[NAME]
--Last Period Memberships
union all
select
2 as [SALESORDERITEMTYPE],
[MEMBERSHIPPROGRAM].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.[MEMBERSHIPPROGRAM]
on [LASTPERIODSOT].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
--Last period membership add-ons
union all
select
16 as SALESORDERITEMTYPE,
ADDON.NAME as PROGRAMNAME,
'' as PROGRAMCATEGORY,
0 as THISPERIODTOTAL,
0 as THISPERIODQUANTITY,
coalesce(LASTPERIODSOT.TOTAL,0) as LASTPERIODTOTAL,
coalesce(LASTPERIODSOT.QUANTITY,0) as LASTPERIODQUANTITY,
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as LASTPERIODSOT
inner join dbo.ADDON
on ADDON.ID = LASTPERIODSOT.ADDONID
--Last Period Membership refunds/exchanges
union all
select
2 as [SALESORDERITEMTYPE],
[MEMBERSHIPPROGRAM].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [LASTPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[CREDITITEMMEMBERSHIP] on [CREDITITEMMEMBERSHIP].[ID] = LI.ID
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join
dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join
dbo.[MEMBERSHIPPROGRAM] on [CREDITITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
where
(@ITEMTYPECODE = 1 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @LASTFROMDATE and @LASTTODATE and
FT.TYPECODE = 23 -- Refund
group by
[MEMBERSHIPPROGRAM].[NAME]
--Last Period Membership add-ons refunds/exchanges
union all
select
16 as [SALESORDERITEMTYPE],
[ADDON].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [LASTPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = LI.SOURCELINEITEMID
inner join
dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
where
(@ITEMTYPECODE = 16 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @LASTFROMDATE and @LASTTODATE and
FT.TYPECODE = 23 -- Refund
group by
[ADDON].[NAME]
--This Period Resources (for the purposes of this report, combine itinerary and itinerary item resources)
union all
select
8 as [SALESORDERITEMTYPE],
RESOURCE.NAME as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
RESOURCECATEGORYCODE.DESCRIPTION as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.RESOURCE on [THISPERIODSOT].RESOURCEID = RESOURCE.ID
left join RESOURCECATEGORYCODE on RESOURCE.RESOURCECATEGORYCODEID = RESOURCECATEGORYCODE.ID
--Last Period Resources (for the purposes of this report, combine itinerary and itinerary item resources)
union all
select
8 as [SALESORDERITEMTYPE],
RESOURCE.NAME as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
RESOURCECATEGORYCODE.DESCRIPTION as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.RESOURCE on [LASTPERIODSOT].RESOURCEID = RESOURCE.ID
left join RESOURCECATEGORYCODE on RESOURCE.RESOURCECATEGORYCODEID = RESOURCECATEGORYCODE.ID
--This Period Staffing Resources (for the purposes of this report, combine itinerary and itinerary item resources)
union all
select
10 as [SALESORDERITEMTYPE],
VOLUNTEERTYPE.NAME as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.VOLUNTEERTYPE on [THISPERIODSOT].VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
--Last Period Staffing Resources (for the purposes of this report, combine itinerary and itinerary item resources)
union all
select
10 as [SALESORDERITEMTYPE],
VOLUNTEERTYPE.NAME as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.VOLUNTEERTYPE on [LASTPERIODSOT].VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
--This Period Donations
union all
select
3 as [SALESORDERITEMTYPE],
[DESIGNATION].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.[DESIGNATION]
on [THISPERIODSOT].[DESIGNATIONID] = [DESIGNATION].[ID]
--This Period Donation Refunds
union all
select
3 as [SALESORDERITEMTYPE],
[DESIGNATION].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [THISPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
inner join
dbo.[SALESORDERITEMDONATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join
dbo.[DESIGNATION] on [SALESORDERITEMDONATION].DESIGNATIONID = [DESIGNATION].ID
where
(@ITEMTYPECODE = 2 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @THISFROMDATE and @THISTODATE and
FT.TYPECODE = 23 -- Refund
group by
[DESIGNATION].[NAME]
--Last Period Donations
union all
select
3 as [SALESORDERITEMTYPE],
[DESIGNATION].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.[DESIGNATION]
on [LASTPERIODSOT].[DESIGNATIONID] = [DESIGNATION].[ID]
--Last Period Donation Refunds
union all
select
3 as [SALESORDERITEMTYPE],
[DESIGNATION].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [LASTPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
inner join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
inner join
dbo.[SALESORDERITEMDONATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join
dbo.[DESIGNATION] on [SALESORDERITEMDONATION].DESIGNATIONID = [DESIGNATION].ID
where
(@ITEMTYPECODE = 2 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @LASTFROMDATE and @LASTTODATE and
FT.TYPECODE = 23 -- Refund
group by
[DESIGNATION].[NAME]
--This Period Facility Rentals
union all
select
7 as [SALESORDERITEMTYPE],
[EVENTLOCATION].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.[EVENTLOCATION]
on [THISPERIODSOT].[LOCATIONID] = [EVENTLOCATION].[ID]
--Last Period Facility Rentals
union all
select
7 as [SALESORDERITEMTYPE],
[EVENTLOCATION].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.[EVENTLOCATION]
on [LASTPERIODSOT].[LOCATIONID] = [EVENTLOCATION].[ID]
union all
-- This period merchandise sales
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
coalesce([THISPERIODSOT].[TOTAL],0) as [THISPERIODTOTAL],
coalesce([THISPERIODSOT].[QUANTITY],0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODSALESORDERTOTALS as [THISPERIODSOT]
inner join dbo.[MERCHANDISEDEPARTMENT]
on [MERCHANDISEDEPARTMENT].[ID] = [THISPERIODSOT].[MERCHANDISEDEPARTMENTID]
--This Period Merchandise Order Discounts
union all
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
-[THISPERIODOD].[TOTAL] as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODORDERDISCOUNTS as [THISPERIODOD]
inner join dbo.[MERCHANDISEDEPARTMENT]
on [THISPERIODOD].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
where THISPERIODOD.TYPECODE = 1
--This Period Merchandise Order Discount Refunds
union all
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
-[THISPERIODODR].[TOTAL] as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @THISPERIODORDERDISCOUNTREFUNDS as [THISPERIODODR]
inner join dbo.[MERCHANDISEDEPARTMENT]
on [THISPERIODODR].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
where THISPERIODODR.TYPECODE = 1
union all
-- This period merchandise refunds
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [THISPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [THISPERIODQUANTITY],
0 as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join
dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join
dbo.[MERCHANDISEDEPARTMENT] on [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
where
(@ITEMTYPECODE = 14 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @THISFROMDATE and @THISTODATE and
FT.TYPECODE = 23 -- Refund
group by
[MERCHANDISEDEPARTMENT].[NAME]
union all
-- Last period merchandise sales
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
coalesce([LASTPERIODSOT].[TOTAL],0) as [LASTPERIODTOTAL],
coalesce([LASTPERIODSOT].[QUANTITY],0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODSALESORDERTOTALS as [LASTPERIODSOT]
inner join dbo.[MERCHANDISEDEPARTMENT]
on [MERCHANDISEDEPARTMENT].[ID] = [LASTPERIODSOT].[MERCHANDISEDEPARTMENTID]
--Last Period Merchandise Order Discounts
union all
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-[LASTPERIODOD].[TOTAL] as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODORDERDISCOUNTS as [LASTPERIODOD]
inner join dbo.[MERCHANDISEDEPARTMENT]
on [LASTPERIODOD].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
where LASTPERIODOD.TYPECODE = 1
--Last Period Merchandise Order Discount Refunds
union all
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-[LASTPERIODODR].[TOTAL] as [LASTPERIODTOTAL],
0 as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from @LASTPERIODORDERDISCOUNTREFUNDS as [LASTPERIODODR]
inner join dbo.[MERCHANDISEDEPARTMENT]
on [LASTPERIODODR].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
where LASTPERIODODR.TYPECODE = 1
union all
-- Last period merchandise refunds
select
14 as [SALESORDERITEMTYPE],
[MERCHANDISEDEPARTMENT].[NAME] as [PROGRAMNAME],
'' as [PROGRAMCATEGORY],
0 as [THISPERIODTOTAL],
0 as [THISPERIODQUANTITY],
-coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0) as [LASTPERIODTOTAL],
-coalesce(sum(LI.QUANTITY),0) as [LASTPERIODQUANTITY],
null as RESOURCECATEGORY
from
dbo.FINANCIALTRANSACTION as FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join
dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
left join
dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = EXT.SALESORDERITEMID
left join
dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join
dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
inner join
dbo.[MERCHANDISEDEPARTMENT] on [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID] = [MERCHANDISEDEPARTMENT].[ID]
where
(@ITEMTYPECODE = 14 or @ITEMTYPECODE is null) and
([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
FT.CALCULATEDDATE between @LASTFROMDATE and @LASTTODATE and
FT.TYPECODE = 23 -- Refund
group by
[MERCHANDISEDEPARTMENT].[NAME]
order by
[SALESORDERITEMTYPE] ASC,
[PROGRAMCATEGORY] ASC,
[RESOURCECATEGORY] ASC,
[PROGRAMNAME] ASC
option (recompile);
return 0;