USP_REPORT_DEFAULTITEMIZEDRECEIPT
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ORDERID | uniqueidentifier | IN |
Definition
Copy
create procedure [dbo].[USP_REPORT_DEFAULTITEMIZEDRECEIPT]
(
@ORDERID uniqueidentifier
)
as
set nocount on;
set transaction isolation level read uncommitted;
-- 0 - Ticket
-- 1 - Combination
-- 2 - Event Registration
-- 3 - Membership
-- 4 - Merchandise
-- 5 - Flat Rate (Reservation)
-- 6 - Facility
-- 7 - Equipment/Supply Resources
-- 8 - Staff Resources
-- 9 - Donation
-- 10 - Discounts
-- 11 - Fee
-- 12 - Tax
declare @CURRENTDATE date;
set @CURRENTDATE = getdate();
--tickets
select
0 ORDERITEMTYPE,
0 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
case when [EVENT].ID is null then
PROGRAM.ID
else
[EVENT].ID
end as ITEMID,
case when [EVENT].ID is null then
PROGRAM.NAME
else
case when [EVENT].STARTTIME = '' then
[EVENT].NAME + ' ' + replace(convert(nvarchar, [EVENT].STARTDATE, 107), ',','')
else
[EVENT].NAME + ' ' + cast([EVENT].[STARTDATETIME] as nvarchar)
end
end as ORDERITEMDESCRIPTION,
cast(cast(SALESORDERITEM.QUANTITY as integer) as nvarchar) + ' ' + PRICETYPECODE.DESCRIPTION + ' @ ' + cast(SALESORDERITEM.PRICE as nvarchar) SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
case when [EVENT].ID is null then
@CURRENTDATE
else
[EVENT].[STARTDATETIME]
end as ORDERITEMSORTFIELD1,
case when [EVENT].ID is null then
PROGRAM.NAME
else
[EVENT].NAME
end
ORDERITEMSORTFIELD2,
PRICETYPECODE.DESCRIPTION SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET on
SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.PRICETYPECODE on
SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
left outer join dbo.SALESORDERITEMTICKETCOMBINATION on
SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
left outer join dbo.[EVENT] on
SALESORDERITEMTICKET.EVENTID = [EVENT].ID
left outer join dbo.PROGRAM on
SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
where SALESORDERITEM.SALESORDERID = @ORDERID
and SALESORDERITEMTICKETCOMBINATION.ID is null
and SALESORDERITEM.PRICINGSTRUCTURECODE <> 1
union all
--item discounts tickets
select
0 ORDERITEMTYPE,
10 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
DISCOUNT.ID ITEMID,
case when [EVENT].ID is null then
PROGRAM.NAME
else
case when [EVENT].STARTTIME = '' then
[EVENT].NAME + ' ' + replace(convert(nvarchar, [EVENT].STARTDATE, 107), ',','')
else
[EVENT].NAME + ' ' + cast([EVENT].[STARTDATETIME] as nvarchar)
end
end
ORDERITEMDESCRIPTION,
' ' + DISCOUNT.NAME SUBORDERITEMDESCRIPTION,
-1 * SALESORDERITEMITEMDISCOUNT.AMOUNT ORDERITEMTOTAL,
case when [EVENT].ID is null then
@CURRENTDATE
else
[EVENT].[STARTDATETIME]
end as ORDERITEMSORTFIELD1,
case when [EVENT].ID is null then
PROGRAM.NAME
else
[EVENT].NAME
end as ORDERITEMSORTFIELD2,
PRICETYPECODE.DESCRIPTION SUBORDERITEMSORTFIELD1,
DISCOUNT.NAME SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET on
SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.PRICETYPECODE on
SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.SALESORDERITEMITEMDISCOUNT on
SALESORDERITEM.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
inner join dbo.DISCOUNT on
SALESORDERITEMITEMDISCOUNT.DISCOUNTID = DISCOUNT.ID
left outer join dbo.[EVENT] on
SALESORDERITEMTICKET.EVENTID = [EVENT].ID
left outer join dbo.PROGRAM on
SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
where SALESORDERITEM.SALESORDERID = @ORDERID
union all
-- flat rate
select
5 ORDERITEMTYPE,
5 SUBORDERITEMTYPE,
@ORDERID ORDERITEMID,
@ORDERID ITEMID,
'Reservation' ORDERITEMDESCRIPTION,
RATESCALE.NAME SUBORDERITEMDESCRIPTION,
( -- the original rate scale price amount gets updated in RESERVATIONRATESCALEPRICE
-- if additional tickets are involved.
select RATESCALEPRICE.AMOUNT
from dbo.RATESCALEPRICE
where RATESCALE.ID = RATESCALEPRICE.RATESCALEID
and RATESCALEPRICE.GROUPMINIMUM = RESERVATIONRATESCALEPRICE.GROUPMINIMUM
and RATESCALEPRICE.GROUPMAXIMUM = RESERVATIONRATESCALEPRICE.GROUPMAXIMUM
)
ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.RESERVATION
inner join dbo.RESERVATIONRATESCALE on
RESERVATIONRATESCALE.ID = RESERVATION.ID
inner join dbo.RESERVATIONRATESCALEPRICE on
RESERVATIONRATESCALEPRICE.RESERVATIONRATESCALEID = RESERVATIONRATESCALE.ID
inner join dbo.RATESCALE on
RATESCALE.ID = RESERVATIONRATESCALE.RATESCALEID
where RESERVATION.ID = @ORDERID
and RESERVATION.PRICINGCODE = 1
and RESERVATIONRATESCALEPRICE.INUSE = 1
union all
-- extra flat rate items
select
5 ORDERITEMTYPE,
5 SUBORDERITEMTYPE,
@ORDERID ORDERITEMID,
ADDITIONALTICKETS.ID ITEMID,
'Reservation' ORDERITEMDESCRIPTION,
ADDITIONALTICKETS.NAME SUBORDERITEMDESCRIPTION,
ADDITIONALTICKETS.QUANTITY * ADDITIONALTICKETS.PRICE ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from
(
select
newid() as ID,
EFRP.QUANTITY as [QUANTITY],
cast(EFRP.QUANTITY as nvarchar) + ' ' + PRICETYPECODE.DESCRIPTION + ' @ ' + cast(SALESORDERITEM.PRICE as nvarchar) as [NAME],
SALESORDERITEM.PRICE as PRICE,
PRICETYPECODE.ID as PRICETYPEID
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
inner join
(
select
ITINERARYITEM.EVENTID,
ITINERARYITEM.PROGRAMID,
EFR.PRICETYPEID,
sum(EFR.QUANTITY) as QUANTITY
from dbo.UFN_RESERVATION_GETEXTRAFLATRATEPEOPLE(@ORDERID) as EFR
inner join ITINERARYITEM on
ITINERARYITEM.ITINERARYID = EFR.ITINERARYID
where (ITINERARYITEM.PROGRAMID is not null or ITINERARYITEM.EVENTID is not null)
group by ITINERARYITEM.EVENTID, ITINERARYITEM.PROGRAMID, EFR.PRICETYPEID
) as EFRP on PRICETYPECODE.ID = EFRP.PRICETYPEID and (EVENT.ID = EFRP.EVENTID or PROGRAM.ID = EFRP.PROGRAMID)
where [SALESORDERITEM].[PRICINGSTRUCTURECODE] = 1
and [SALESORDERITEM].[SALESORDERID] = @ORDERID
) as ADDITIONALTICKETS
union all
--item fees
select
0 ORDERITEMTYPE,
11 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
FEE.ID ITEMID,
case when [EVENT].ID is null then
PROGRAM.NAME
else
case when [EVENT].STARTTIME = '' then
[EVENT].NAME + ' ' + replace(convert(nvarchar, [EVENT].STARTDATE, 107), ',','')
else
[EVENT].NAME + ' ' + cast([EVENT].[STARTDATETIME] as nvarchar)
end
end as ORDERITEMDESCRIPTION,
' ' + FEE.NAME SUBORDERITEMDESCRIPTION,
FEEPARENT.TOTAL ORDERITEMTOTAL,
case when [EVENT].ID is null then
@CURRENTDATE
else
[EVENT].[STARTDATETIME]
end as ORDERITEMSORTFIELD1,
case when [EVENT].ID is null then
PROGRAM.NAME
else
[EVENT].NAME
end as ORDERITEMSORTFIELD2,
PRICETYPECODE.DESCRIPTION SUBORDERITEMSORTFIELD1,
FEE.NAME SUBORDERITEMSORTFIELD2
from dbo.SALESORDER
inner join dbo.SALESORDERITEM on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.SALESORDERITEMTICKET on
SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.PRICETYPECODE on
SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.SALESORDERITEMID
inner join dbo.FEE on
SALESORDERITEMFEE.FEEID = FEE.ID
inner join SALESORDERITEM FEEPARENT on
SALESORDERITEMFEE.ID = FEEPARENT.ID
left outer join dbo.[EVENT] on
SALESORDERITEMTICKET.EVENTID = [EVENT].ID
left outer join dbo.PROGRAM on
SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
-- exclude fees for combo items
left outer join dbo.SALESORDERITEMTICKETCOMBINATION on
SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEM.ID
where
SALESORDER.ID = @ORDERID
and SALESORDERITEMTICKETCOMBINATION.ID is null
and FEEPARENT.PRICINGSTRUCTURECODE <> 1 -- Flat rate
union all
--combinations
select
ORDERITEMTYPE,
SUBORDERITEMTYPE,
ORDERITEMID,
ITEMID,
ORDERITEMDESCRIPTION,
SUBORDERITEMDESCRIPTION,
ORDERITEMTOTAL,
ORDERITEMSORTFIELD1,
ORDERITEMSORTFIELD2,
SUBORDERITEMSORTFIELD1,
SUBORDERITEMSORTFIELD2
from dbo.UFN_SALESORDER_GETCOMBINATIONITEMSFORDEFAULTITEMIZEDRECEIPT(@ORDERID)
union all
--event registrations
select
2 ORDERITEMTYPE,
1 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
EVENTPRICE.ID ITEMID,
case when [EVENT].STARTTIME = '' then
[EVENT].NAME + ' ' + replace(convert(nvarchar, [EVENT].STARTDATE, 107), ',','')
else
[EVENT].NAME + ' ' + cast([EVENT].[STARTDATETIME] as nvarchar)
end
ORDERITEMDESCRIPTION,
cast(SALESORDERITEMEVENTREGISTRANTREGISTRATION.QUANTITY as nvarchar) + ' ' + EVENTPRICE.NAME + ' @ ' + cast(EVENTPRICE.AMOUNT as nvarchar) SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
[EVENT].[STARTDATETIME] ORDERITEMSORTFIELD1,
[EVENT].NAME ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
--EVENTPRICE.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMEVENTREGISTRATION on
SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
inner join dbo.SALESORDERITEMEVENTREGISTRANTREGISTRATION on
SALESORDERITEMEVENTREGISTRATION.ID = SALESORDERITEMEVENTREGISTRANTREGISTRATION.SALESORDERITEMEVENTREGISTRATIONID
inner join dbo.EVENTPRICE on
SALESORDERITEMEVENTREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
inner join dbo.REGISTRANT on
SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
inner join dbo.[EVENT] on
REGISTRANT.EVENTID = [EVENT].ID
inner join dbo.REGISTRANTREGISTRATION on
REGISTRANT.ID = REGISTRANTREGISTRATION.REGISTRANTID and
SALESORDERITEMEVENTREGISTRANTREGISTRATION.EVENTPRICEID = REGISTRANTREGISTRATION.EVENTPRICEID
where SALESORDERITEM.SALESORDERID = @ORDERID
union all
--event registration payment information
select distinct
2 ORDERITEMTYPE,
2 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
[EVENT].ID ITEMID,
case when [EVENT].STARTTIME = '' then
[EVENT].NAME + ' ' + replace(convert(nvarchar, [EVENT].STARTDATE, 107), ',','')
else
[EVENT].NAME + ' ' + cast([EVENT].[STARTDATETIME] as nvarchar)
end as ORDERITEMDESCRIPTION,
' ' +
case when RR.AMOUNT = 0 and EVENTPRICE.AMOUNT > 0 then
'Fee waived'
when EVENTBALANCE.BALANCE = 0 then
'Payment in full'
when EVENTBALANCE.BALANCE > 0 then
'Partial payment'
end as SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
[EVENT].[STARTDATETIME] ORDERITEMSORTFIELD1,
[EVENT].NAME ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
--EVENTPRICE.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMEVENTREGISTRATION on
SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
inner join dbo.SALESORDERITEMEVENTREGISTRANTREGISTRATION on
SALESORDERITEMEVENTREGISTRATION.ID = SALESORDERITEMEVENTREGISTRANTREGISTRATION.SALESORDERITEMEVENTREGISTRATIONID
inner join dbo.EVENTPRICE on
SALESORDERITEMEVENTREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
inner join dbo.REGISTRANT on
SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
inner join dbo.[EVENT] on
REGISTRANT.EVENTID = [EVENT].ID
inner join (select REGISTRANTID, sum(AMOUNT) AMOUNT from dbo.REGISTRANTREGISTRATION group by REGISTRANTID) RR on
REGISTRANT.ID = RR.REGISTRANTID
inner join dbo.UFN_GETUNPAIDPATRONEVENTREGISTRATIONS(null,@ORDERID, null, 0) EVENTBALANCE on
REGISTRANT.ID = EVENTBALANCE.REGISTRANTID
where SALESORDERID = @ORDERID
union all
--event registration balance due
select distinct
2 ORDERITEMTYPE,
3 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
REGISTRANT.ID ITEMID,
case when [EVENT].STARTTIME = '' then
[EVENT].NAME + ' ' + replace(convert(nvarchar, [EVENT].STARTDATE, 107), ',','')
else
[EVENT].NAME + ' ' + cast([EVENT].[STARTDATETIME] as nvarchar)
end as ORDERITEMDESCRIPTION,
' Balance due ' + CAST(EVENTBALANCE.BALANCE as nvarchar)
SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
[EVENT].[STARTDATETIME] ORDERITEMSORTFIELD1,
[EVENT].NAME ORDERITEMSORTFIELD2,
'' SUBORDERITEMSORTFIELD1,
--EVENTPRICE.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMEVENTREGISTRATION on
SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
inner join dbo.SALESORDERITEMEVENTREGISTRANTREGISTRATION on
SALESORDERITEMEVENTREGISTRATION.ID = SALESORDERITEMEVENTREGISTRANTREGISTRATION.SALESORDERITEMEVENTREGISTRATIONID
inner join dbo.EVENTPRICE on
SALESORDERITEMEVENTREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
inner join dbo.REGISTRANT on
SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
inner join dbo.[EVENT] on
REGISTRANT.EVENTID = [EVENT].ID
inner join dbo.UFN_GETUNPAIDPATRONEVENTREGISTRATIONS(null, @ORDERID, null, 0) EVENTBALANCE on
REGISTRANT.ID = EVENTBALANCE.REGISTRANTID
where SALESORDERID = @ORDERID and
EVENTBALANCE.BALANCE > 0
union all
--memberships
select
3 ORDERITEMTYPE,
3 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
MEMBERSHIPPROGRAM.ID ITEMID,
MEMBERSHIPPROGRAM.NAME ORDERITEMDESCRIPTION,
cast(cast(SALESORDERITEM.QUANTITY as integer) as nvarchar) + ' ' + MEMBERSHIPLEVEL.NAME + ' @ ' + cast(SALESORDERITEM.PRICE as nvarchar) SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
MEMBERSHIPPROGRAM.NAME ORDERITEMSORTFIELD2,
cast(SALESORDERITEM.ID as nvarchar(36)) SUBORDERITEMSORTFIELD1,
MEMBERSHIPLEVEL.NAME SUBORDERITEMSORTFIELD2
from dbo.SALESORDER
inner join SALESORDERITEM on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.SALESORDERITEMMEMBERSHIP on
SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
inner join dbo.MEMBERSHIPPROGRAM on
SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
inner join dbo.MEMBERSHIPLEVEL on
SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
where SALESORDER.ID = @ORDERID
union all
--membership add-ons
select
3 ORDERITEMTYPE,
5 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
MEMBERSHIPPROGRAM.ID ITEMID,
MEMBERSHIPPROGRAM.NAME ORDERITEMDESCRIPTION,
cast(cast(SALESORDERITEM.QUANTITY as integer) as nvarchar) + ' ' + ADDON.NAME + ' @ ' + cast(SALESORDERITEM.PRICE as nvarchar) SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
MEMBERSHIPPROGRAM.NAME ORDERITEMSORTFIELD2,
cast(SALESORDERITEMMEMBERSHIP.ID as nvarchar(36)) SUBORDERITEMSORTFIELD1,
ADDON.NAME SUBORDERITEMSORTFIELD2
from dbo.SALESORDER
inner join SALESORDERITEM on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.SALESORDERITEMMEMBERSHIPADDON on
SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
inner join dbo.SALESORDERITEMMEMBERSHIP on
SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID
inner join dbo.MEMBERSHIPPROGRAM on
SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
inner join dbo.ADDON on
SALESORDERITEMMEMBERSHIPADDON.ADDONID = ADDON.ID
where SALESORDER.ID = @ORDERID
union all
--applied tickets to membership
select
3 ORDERITEMTYPE,
10 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID ITEMID,
MEMBERSHIPPROGRAM.NAME ORDERITEMDESCRIPTION,
' ' + SALESORDERITEMMEMBERSHIPITEMPROMOTION.PROMOTIONNAME SUBORDERITEMDESCRIPTION,
-1 * SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
MEMBERSHIPPROGRAM.NAME ORDERITEMSORTFIELD2,
cast(SALESORDERITEM.ID as nvarchar(36)) SUBORDERITEMSORTFIELD1,
SALESORDERITEMMEMBERSHIPITEMPROMOTION.PROMOTIONNAME SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMMEMBERSHIP on
SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
inner join dbo.MEMBERSHIPPROGRAM on
SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on
SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
where SALESORDERID = @ORDERID
union all
--merchandise
select
4 ORDERITEMTYPE,
4 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
MERCHANDISEPRODUCTINSTANCE.ID ITEMID,
MERCHANDISEPRODUCTINSTANCE.BARCODE + ' ' + SALESORDERITEM.DESCRIPTION ORDERITEMDESCRIPTION,
cast(cast(SALESORDERITEM.QUANTITY as integer) as nvarchar) + ' @ ' + cast(SALESORDERITEM.PRICE as nvarchar) SUBORDERITEMDESCRIPTION,
--'Merchandise' ORDERITEMDESCRIPTION,
--SALESORDERITEM.DESCRIPTION + ' ' + MERCHANDISEPRODUCTINSTANCE.BARCODE SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEM.DESCRIPTION SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMMERCHANDISE on
SALESORDERITEM.ID = SALESORDERITEMMERCHANDISE.ID
inner join dbo.MERCHANDISEPRODUCTINSTANCE on
SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
inner join dbo.MERCHANDISEPRODUCT on
MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = MERCHANDISEPRODUCT.ID
where SALESORDERID = @ORDERID
union all
-- item discount merchandise
select
4 ORDERITEMTYPE,
10 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
DISCOUNT.ID ITEMID,
MERCHANDISEPRODUCTINSTANCE.BARCODE + ' ' + SALESORDERITEM.DESCRIPTION ORDERITEMDESCRIPTION,
' ' + DISCOUNT.NAME SUBORDERITEMDESCRIPTION,
-1 * SALESORDERITEMITEMDISCOUNT.AMOUNT ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEM.DESCRIPTION SUBORDERITEMSORTFIELD1,
DISCOUNT.NAME SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMMERCHANDISE on
SALESORDERITEM.ID = SALESORDERITEMMERCHANDISE.ID
inner join dbo.SALESORDERITEMITEMDISCOUNT on
SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.DISCOUNT on
SALESORDERITEMITEMDISCOUNT.DISCOUNTID = DISCOUNT.ID
inner join dbo.MERCHANDISEPRODUCTINSTANCE on
SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID = MERCHANDISEPRODUCTINSTANCE.ID
inner join dbo.MERCHANDISEPRODUCT on
MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID = MERCHANDISEPRODUCT.ID
where SALESORDERID = @ORDERID
union all
-- facilities
select
6 ORDERITEMTYPE,
6 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
EVENTLOCATION.ID ITEMID,
'Facilities' ORDERITEMDESCRIPTION,
EVENTLOCATION.NAME SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
EVENTLOCATION.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMFACILITY on
SALESORDERITEM.ID = SALESORDERITEMFACILITY.ID
inner join dbo.EVENTLOCATION on
SALESORDERITEMFACILITY.EVENTLOCATIONID = EVENTLOCATION.ID
where SALESORDERID = @ORDERID
union all
-- staff resources
select
7 ORDERITEMTYPE,
7 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
RESOURCES.ITID ITEMID,
'Staff' ORDERITEMDESCRIPTION,
case RESOURCES.QUANTITYNEEDED
when 0 then -- Per ticket resource
case
when RESOURCES.NUMBEROFHOURS > 0 then
-- If this is an hourly resource, divide to get quantity needed per hour
cast(cast(SALESORDERITEM.QUANTITY / RESOURCES.NUMBEROFHOURS as float) as nvarchar)
else
cast(cast(SALESORDERITEM.QUANTITY as float) as nvarchar)
end
else
cast (RESOURCES.QUANTITYNEEDED as nvarchar)
end + ' ' + RESOURCES.DESCRIPTION + ' ' +
case when RESOURCES.PRICINGSTRUCTURECODE <> 2 then ' @ ' + cast(SALESORDERITEM.PRICE as nvarchar)
else ''
end
SUBORDERITEMDESCRIPTION,
case when (RESOURCES.PRICINGSTRUCTURECODE = 2) then null else SALESORDERITEM.TOTAL end as ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEM.DESCRIPTION SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from (
select
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID as [RESOURCEID],
SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID,
dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID) as [DESCRIPTION],
null as [ITINERARYITEMID],
ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
case ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID), dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID)) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID as [ITID],
ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYSTAFFRESOURCE
inner join
dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
union all
select
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID as [RESOURCEID],
SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID,
dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID) as [DESCRIPTION],
ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID,
ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
case ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID as [ITID],
ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE
inner join
dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
inner join
dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
) as [RESOURCES]
inner join
dbo.SALESORDERITEM on RESOURCES.SALESORDERITEMID = SALESORDERITEM.ID
where
SALESORDERITEM.SALESORDERID = @ORDERID and
SALESORDERITEM.PRICINGSTRUCTURECODE <> 1
union all
-- Staff resources per hour
select
7 ORDERITEMTYPE,
8 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
SALESORDERITEM.ID ITEMID,
'Staff' ORDERITEMDESCRIPTION,
cast(cast (RESOURCES.NUMBEROFHOURS as float) as nvarchar) + ' hour' +
case when RESOURCES.NUMBEROFHOURS > 1
then 's'
else
''
end +
' @ ' + cast(SALESORDERITEM.PRICE as nvarchar) SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL as ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEM.DESCRIPTION SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from (
select
ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID as [RESOURCEID],
SALESORDERITEMITINERARYSTAFFRESOURCE.SALESORDERITEMID,
dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID) as [DESCRIPTION],
null as [ITINERARYITEMID],
ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
case ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID), dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYSTAFFRESOURCE.ITINERARYID)) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID as [ITID],
ITINERARYSTAFFRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYSTAFFRESOURCE
inner join
dbo.ITINERARYSTAFFRESOURCE on SALESORDERITEMITINERARYSTAFFRESOURCE.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
union all
select
ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID as [RESOURCEID],
SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID,
dbo.UFN_VOLUNTEERTYPE_GETDESCRIPTION(ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID) as [DESCRIPTION],
ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID,
ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
case ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID as [ITID],
ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE
inner join
dbo.ITINERARYITEMSTAFFRESOURCE on SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
inner join
dbo.ITINERARYITEM on ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
) as [RESOURCES]
inner join
dbo.SALESORDERITEM on RESOURCES.SALESORDERITEMID = SALESORDERITEM.ID
where
SALESORDERITEM.SALESORDERID = @ORDERID and
RESOURCES.PRICINGSTRUCTURECODE = 2 and
SALESORDERITEM.PRICINGSTRUCTURECODE <> 1
union all
-- Supply/equipment resources
select
8 ORDERITEMTYPE,
8 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
RESOURCES.ITID ITEMID,
'Resources' ORDERITEMDESCRIPTION,
case RESOURCES.QUANTITYNEEDED
when 0 then -- Per ticket resource
case
when RESOURCES.NUMBEROFHOURS > 0 then
-- If this is an hourly resource, divide to get quantity needed per hour
cast(cast(SALESORDERITEM.QUANTITY / RESOURCES.NUMBEROFHOURS as float) as nvarchar)
else
cast(cast(SALESORDERITEM.QUANTITY as float) as nvarchar)
end
else
cast (RESOURCES.QUANTITYNEEDED as nvarchar)
end + ' ' + RESOURCES.DESCRIPTION + ' ' +
case when RESOURCES.PRICINGSTRUCTURECODE <> 2 then ' @ ' + cast(SALESORDERITEM.PRICE as nvarchar)
else ''
end
SUBORDERITEMDESCRIPTION,
case when (RESOURCES.PRICINGSTRUCTURECODE = 2) then null else SALESORDERITEM.TOTAL end as ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEM.DESCRIPTION SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from (
select
ITINERARYRESOURCE.RESOURCEID,
SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID,
dbo.UFN_RESOURCE_GETNAME(ITINERARYRESOURCE.RESOURCEID) as [DESCRIPTION],
null as [ITINERARYITEMID],
ITINERARYRESOURCE.QUANTITYNEEDED,
case ITINERARYRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYRESOURCE.ITINERARYID), dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYRESOURCE.ITINERARYID)) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID as [ITID],
ITINERARYRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYRESOURCE
inner join
dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
union all
select
ITINERARYITEMRESOURCE.RESOURCEID,
SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID,
dbo.UFN_RESOURCE_GETNAME(ITINERARYITEMRESOURCE.RESOURCEID) as [DESCRIPTION],
ITINERARYITEMRESOURCE.ITINERARYITEMID,
ITINERARYITEMRESOURCE.QUANTITYNEEDED,
case ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID as [ITID],
ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYITEMRESOURCE
inner join
dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
inner join
dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
) as [RESOURCES]
inner join
dbo.SALESORDERITEM on RESOURCES.SALESORDERITEMID = SALESORDERITEM.ID
where
SALESORDERITEM.SALESORDERID = @ORDERID
and SALESORDERITEM.PRICINGSTRUCTURECODE <> 1
union all
-- Supply/equipment resources per hour
select
8 ORDERITEMTYPE,
9 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
SALESORDERITEM.ID ITEMID,
'Resources' ORDERITEMDESCRIPTION,
cast(cast (RESOURCES.NUMBEROFHOURS as float) as nvarchar) + ' hour' +
case when RESOURCES.NUMBEROFHOURS > 1
then 's'
else
''
end +
' @ ' + cast(SALESORDERITEM.PRICE as nvarchar) SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL as ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEM.DESCRIPTION SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from (
select
ITINERARYRESOURCE.RESOURCEID,
SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID,
dbo.UFN_RESOURCE_GETNAME(ITINERARYRESOURCE.RESOURCEID) as [DESCRIPTION],
null as [ITINERARYITEMID],
ITINERARYRESOURCE.QUANTITYNEEDED,
case ITINERARYRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYRESOURCE.ITINERARYID), dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYRESOURCE.ITINERARYID)) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID as [ITID],
ITINERARYRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYRESOURCE
inner join
dbo.ITINERARYRESOURCE on SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
union all
select
ITINERARYITEMRESOURCE.RESOURCEID,
SALESORDERITEMITINERARYITEMRESOURCE.SALESORDERITEMID,
dbo.UFN_RESOURCE_GETNAME(ITINERARYITEMRESOURCE.RESOURCEID) as [DESCRIPTION],
ITINERARYITEMRESOURCE.ITINERARYITEMID,
ITINERARYITEMRESOURCE.QUANTITYNEEDED,
case ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
when 2 then
datediff(minute, ITINERARYITEM.STARTDATETIME, ITINERARYITEM.ENDDATETIME) / 60.0
else
0
end as [NUMBEROFHOURS],
SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID as [ITID],
ITINERARYITEMRESOURCE.PRICINGSTRUCTURECODE
from
dbo.SALESORDERITEMITINERARYITEMRESOURCE
inner join
dbo.ITINERARYITEMRESOURCE on SALESORDERITEMITINERARYITEMRESOURCE.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
inner join
dbo.ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
) as [RESOURCES]
inner join
dbo.SALESORDERITEM on RESOURCES.SALESORDERITEMID = SALESORDERITEM.ID
where
SALESORDERITEM.SALESORDERID = @ORDERID
and RESOURCES.PRICINGSTRUCTURECODE = 2
and SALESORDERITEM.PRICINGSTRUCTURECODE <> 1
union all
--donations
select
9 ORDERITEMTYPE,
9 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
DESIGNATION.ID ITEMID,
'Donations' ORDERITEMDESCRIPTION,
DESIGNATIONLEVEL.NAME SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
DESIGNATIONLEVEL.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDER
inner join SALESORDERITEM on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.SALESORDERITEMDONATION on
SALESORDERITEM.ID = SALESORDERITEMDONATION.ID
inner join dbo.DESIGNATION on
SALESORDERITEMDONATION.DESIGNATIONID = DESIGNATION.ID
inner join dbo.DESIGNATIONLEVEL on
DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID
where SALESORDER.ID = @ORDERID
union all
--order discounts
select
10 ORDERITEMTYPE,
10 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
coalesce(DISCOUNT.ID, SALESORDERADJUSTABLEDISCOUNT.ID) ITEMID,
'Discounts' ORDERITEMDESCRIPTION,
SALESORDERITEMORDERDISCOUNT.DISCOUNTNAME SUBORDERITEMDESCRIPTION,
-1 * SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
SALESORDERITEMORDERDISCOUNT.DISCOUNTNAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDER
inner join SALESORDERITEM on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.SALESORDERITEMORDERDISCOUNT on
SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNT.ID
left outer join dbo.DISCOUNT on
SALESORDERITEMORDERDISCOUNT.ISADJUSTABLEDISCOUNT = 0 and
SALESORDERITEMORDERDISCOUNT.DISCOUNTID = DISCOUNT.ID
left outer join dbo.SALESORDERADJUSTABLEDISCOUNT on
SALESORDERITEMORDERDISCOUNT.ISADJUSTABLEDISCOUNT = 1 and
SALESORDERITEM.SALESORDERID = SALESORDERADJUSTABLEDISCOUNT.SALESORDERID
where SALESORDER.ID = @ORDERID
union all
--order fees
select
11 ORDERITEMTYPE,
11 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
FEE.ID ITEMID,
'Fees' ORDERITEMDESCRIPTION,
FEE.NAME SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
FEE.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDER
inner join SALESORDERITEM on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.ID
inner join dbo.FEE on
SALESORDERITEMFEE.FEEID = FEE.ID
where SALESORDER.ID = @ORDERID and
SALESORDERITEMFEE.SALESORDERITEMID is null and
SALESORDERITEM.PRICINGSTRUCTURECODE <> 1
union all
--taxes
select distinct
12 ORDERITEMTYPE,
12 SUBORDERITEMTYPE,
SALESORDERITEM.ID ORDERITEMID,
TAX.ID ITEMID,
'Taxes' ORDERITEMDESCRIPTION,
TAX.NAME SUBORDERITEMDESCRIPTION,
SALESORDERITEM.TOTAL ORDERITEMTOTAL,
'' ORDERITEMSORTFIELD1,
'' ORDERITEMSORTFIELD2,
TAX.NAME SUBORDERITEMSORTFIELD1,
'' SUBORDERITEMSORTFIELD2
from dbo.SALESORDER
inner join SALESORDERITEM on
SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join dbo.SALESORDERITEMTAX on
SALESORDERITEM.ID = SALESORDERITEMTAX.TAXITEMID
inner join dbo.TAX on
SALESORDERITEMTAX.TAXID = TAX.ID
where SALESORDER.ID = @ORDERID
order by ORDERITEMTYPE, ORDERITEMSORTFIELD1, ORDERITEMSORTFIELD2, SUBORDERITEMSORTFIELD1, SUBORDERITEMTYPE, SUBORDERITEMSORTFIELD2
return 0;