USP_DATALIST_SHOPPINGCARTSALESORDER
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONTEXTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_SHOPPINGCARTSALESORDER
(
@CONTEXTID uniqueidentifier
)
as
set nocount on;
declare @SALESTYPECODE tinyint;
declare @ORDERSTATUSCODE tinyint;
declare @SALESMETHODID uniqueidentifier;
select
@SALESTYPECODE = SALESORDER.SALESMETHODTYPECODE,
@ORDERSTATUSCODE = SALESORDER.STATUSCODE
from dbo.SALESORDER
where SALESORDER.ID = @CONTEXTID;
declare @ISBASICCMS bit = 0;
if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('e5e0494b-ba0f-4e23-b8fb-a59112dbf3c8') = 1 -- Check for BasicCMS
and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('f238e8fe-06ae-4fdc-beaf-fdf6637e1982') = 0 -- Check for not CMS
set @ISBASICCMS = 1;
select @SALESMETHODID = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(@SALESTYPECODE);
declare @SALESMETHODFLATFEESPERITEM money;
select
@SALESMETHODFLATFEESPERITEM = SUM(FEE.AMOUNT)
from dbo.SALESMETHODFEE
inner join dbo.FEE
on SALESMETHODFEE.FEEID = FEE.ID
inner join dbo.SALESMETHOD
on SALESMETHOD.ID = SALESMETHODFEE.SALESMETHODID
where FEE.APPLIESTOCODE = 1 --item
and FEE.TYPECODE = 0
and FEE.ISACTIVE = 1
and SALESMETHOD.TYPECODE = @SALESTYPECODE;
declare @HASUNRESTRICTEDDELIVERYMETHOD bit = 0;
declare @EARLIESTVALIDEVENTDATETIMEWITHOFFSET datetimeoffset = null;
declare @DELIVERYMETHODEXISTS bit = 0;
exec dbo.USP_DELIVERYMETHOD_INFO_BYSALESMETHODID
@SALESMETHODID,
@DELIVERYMETHODEXISTS output,
@HASUNRESTRICTEDDELIVERYMETHOD output,
@EARLIESTVALIDEVENTDATETIMEWITHOFFSET output;
declare @MAXINT int = 2147483647;
declare @MAXDATETIMEOFFSETFORDIFFFROMEARLIESTVALIDEVENT datetimeoffset = dateadd(second, @MAXINT, @EARLIESTVALIDEVENTDATETIMEWITHOFFSET);
declare @CURRENTDATE datetime = getdate();
declare @MAXDATETIMEFORDIFFFROMCURRENT datetime = dateadd(second, @MAXINT, @CURRENTDATE);
declare @CURRENTDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1);
declare @MAXDATETIMEOFFSETFORDIFFFROMCURRENT datetimeoffset = dateadd(second, @MAXINT, @CURRENTDATETIMEOFFSET);
declare @COMBINATIONS table (
TICKETCOMBINATIONID uniqueidentifier,
PRICETYPECODEID uniqueidentifier
);
insert into @COMBINATIONS
select distinct
SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID,
SALESORDERITEMTICKET.PRICETYPECODEID
from dbo.SALESORDERITEM
inner join dbo.SALESORDERITEMTICKET on
SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.PRICETYPECODE
on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION
on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where
SALESORDERITEM.SALESORDERID = @CONTEXTID;
select
ID,
TICKETCOMBINATIONID,
DESCRIPTION,
PRICE,
QUANTITY,
TOTAL,
TYPECODE,
DATA,
OPTIONS,
CALLBACKURL,
EXPIREDCALLBACKURL,
SYSTEMTYPENAME,
ASSEMBLYNAME,
ATTRIBUTES,
CATEGORYNAME,
ACKNOWLEDGEMENT,
DATEADDED,
EXPIRATIONDATE,
ISEXPIRED,
HASNOVALIDDELIVERYMETHOD,
EXPIRATIONDELTA,
ORDERITEMTYPE,
SUBORDERITEMTYPE,
ORDERSORTFIELD1,
ORDERSORTFIELD2
from (
select
SALESORDERITEM.ID,
null as TICKETCOMBINATIONID,
SALESORDERITEM.DESCRIPTION,
SALESORDERITEM.PRICE,
SALESORDERITEM.QUANTITY,
SALESORDERITEM.TOTAL,
SALESORDERITEM.TYPECODE,
SALESORDERITEM.DATA,
SALESORDERITEM.OPTIONS,
SALESORDERITEM.CALLBACKURL,
SALESORDERITEM.EXPIREDCALLBACKURL,
SALESORDERITEM.SYSTEMTYPENAME,
SALESORDERITEM.ASSEMBLYNAME,
SALESORDERITEM.ATTRIBUTES,
SALESORDERITEM.CATEGORYNAME,
SALESORDERITEM.ACKNOWLEDGEMENT,
SALESORDERRESERVEDITEM.DATEADDED,
SALESORDERRESERVEDITEM.EXPIRATIONDATE,
[EXPIRATIONSUMMARY].[RESERVATIONHASEXPIRED] as ISEXPIRED,
[EXPIRATIONSUMMARY].[DELIVERYHASEXPIRED] as [HASNOVALIDDELIVERYMETHOD],
[EXPIRATIONSUMMARY].[EXPIRATIONDELTA],
case
when [SALESORDERITEM].[TYPECODE] = 0 then 0 -- ticket
when [SALESORDERITEM].[TYPECODE] = 2 then 50 -- donation
when [SALESORDERITEM].[TYPECODE] = 6 then 20 -- event registration
when [SALESORDERITEM].[TYPECODE] in (1, 16) then 30 -- membership
when SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID is not null then 30 -- membership promotion
when [SALESORDERITEMMERCHANDISE].[ID] is not null then 40 -- merchandise
when [SALESORDERITEMITEMDISCOUNT].[ID] is not null then 60 -- item discount
when [SALESORDERITEMORDERDISCOUNT].[ID] is not null then 60 -- order discount
else 0 --should not occur
end as [ORDERITEMTYPE],
case
when [SALESORDERITEM].[TYPECODE] in (1, 16) then SALESORDERITEM.TYPECODE
when SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID is not null then 60
else 0
end as SUBORDERITEMTYPE,
case
when [SALESORDERITEM].[TYPECODE] = 0 then [SALESORDERITEM].[DESCRIPTION] + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
when [SALESORDERITEM].[TYPECODE] = 1 then cast([SALESORDERITEM].[ID] as nvarchar(36))
when [SALESORDERITEM].[TYPECODE] = 6 then REGISTRANTNAMEFORMAT.NAME + ' - ' + [SALESORDERITEM].[DESCRIPTION]
when [SALESORDERITEM].[TYPECODE] = 16 then cast([SALESORDERITEMMEMBERSHIPADDON].[SALESORDERITEMMEMBERSHIPID] as nvarchar(36))
else [SALESORDERITEM].[DESCRIPTION]
end as [ORDERSORTFIELD1],
cast([SALESORDERITEM].[ID] as nvarchar(36))as [ORDERSORTFIELD2]
from dbo.SALESORDERITEM
left join dbo.SALESORDERITEMTICKET on
SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
left join dbo.SALESORDERITEMTICKETCOMBINATION
on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
left join dbo.EVENT on
SALESORDERITEMTICKET.EVENTID = EVENT.ID
left join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.ID
left join dbo.SALESORDERRESERVEDITEM on
SALESORDERITEM.ID = SALESORDERRESERVEDITEM.ID
left join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
left join dbo.[REGISTRANT]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
outer apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) as REGISTRANTNAMEFORMAT
left join dbo.[SALESORDERITEMMEMBERSHIP]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]
left join dbo.[SALESORDERITEMMEMBERSHIPADDON]
on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIPADDON].[ID]
left join dbo.SALESORDERITEMMERCHANDISE
on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
left join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
left join dbo.SALESORDERITEMITEMDISCOUNT
on SALESORDERITEM.ID = SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID
left join dbo.[SALESORDERITEMORDERDISCOUNT]
on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNT.ID
outer apply (
select
case
when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] is not null then
case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE]
when 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
end
else [EVENT].[STARTDATETIMEWITHOFFSET]
end as [DATETIMEWITHOFFSET]
from dbo.[PROGRAMSALESMETHOD]
where
[PROGRAMSALESMETHOD].[PROGRAMID] = [EVENT].[PROGRAMID] and
[PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
) [EVENTOFFSALE]
outer apply (
select
case when [SALESORDERRESERVEDITEM].[ID] is null then 0 else 1 end [RESERVATION],
case when (SALESORDERITEM.TYPECODE <> 0) or (EVENT.ID is null and @DELIVERYMETHODEXISTS = 1) then 0 else 1 end [DELIVERY],
case when SALESORDERITEM.TYPECODE <> 0 or EVENT.ID is null then 0 else 1 end as [ONSALE]
) [CANEXPIRE]
outer apply (
select
case when [CANEXPIRE].[RESERVATION] = 1 then
case
when @CURRENTDATE > SALESORDERRESERVEDITEM.EXPIRATIONDATE then -1 --Already expired
when @MAXDATETIMEFORDIFFFROMCURRENT <= SALESORDERRESERVEDITEM.EXPIRATIONDATE then @MAXINT --Beyond comparison(tm)
else datediff(second,@CURRENTDATE,SALESORDERRESERVEDITEM.EXPIRATIONDATE)
end
else null
end as [RESERVATION],
case
when [CANEXPIRE].[DELIVERY] = 1 and @DELIVERYMETHODEXISTS = 0 then -1
when [CANEXPIRE].[DELIVERY] = 1 then
case
when @EARLIESTVALIDEVENTDATETIMEWITHOFFSET > EVENT.STARTDATETIMEWITHOFFSET then -1 --Already expired
when @MAXDATETIMEOFFSETFORDIFFFROMEARLIESTVALIDEVENT <= EVENT.STARTDATETIMEWITHOFFSET then @MAXINT --Beyond datediff comparison
else datediff(second,@EARLIESTVALIDEVENTDATETIMEWITHOFFSET, EVENT.STARTDATETIMEWITHOFFSET)
end
else null
end as [DELIVERY],
case when [CANEXPIRE].[ONSALE] = 1 then
case
when @CURRENTDATETIMEOFFSET > [EVENTOFFSALE].[DATETIMEWITHOFFSET] then -1 --Already expired
when @MAXDATETIMEOFFSETFORDIFFFROMCURRENT <= [EVENTOFFSALE].[DATETIMEWITHOFFSET] then @MAXINT --Beyond datediff comparison
else datediff(second,@CURRENTDATETIMEOFFSET, [EVENTOFFSALE].[DATETIMEWITHOFFSET])
end
else null
end as [ONSALE]
) as [EXPIRATIONDELTA]
outer apply (
select
case when [EXPIRATIONDELTA].[RESERVATION] <= 0 then 1 else 0 end as [RESERVATIONHASEXPIRED],
case when [EXPIRATIONDELTA].[DELIVERY] <= 0 or [EXPIRATIONDELTA].[ONSALE] <= 0 then 1 else 0 end [DELIVERYHASEXPIRED],
coalesce((
select min(EXPIRATION)
from (
select [EXPIRATIONDELTA].[DELIVERY] as [EXPIRATION]
union all
select [EXPIRATIONDELTA].[ONSALE] as [EXPIRATION]
union all
select [EXPIRATIONDELTA].[RESERVATION] as [EXPIRATION]
) [DELTAS]
),0) as [EXPIRATIONDELTA]
) as [EXPIRATIONSUMMARY]
where
SALESORDERITEM.SALESORDERID = @CONTEXTID
and SALESORDERITEM.TYPECODE <> 3
and SALESORDERITEM.TYPECODE <> 4
and not (
SALESORDERITEM.TYPECODE = 16 and
@SALESTYPECODE = 2 and
@ISBASICCMS = 1 and
@ORDERSTATUSCODE = 0
)
and SALESORDERITEMTICKETCOMBINATION.ID is null -- Not part of a Combination
union all
-- Combinations
select
SALESORDERITEMINFO.ID,
COMBINATIONS.TICKETCOMBINATIONID,
SALESORDERITEMINFO.DESCRIPTION,
SALESORDERITEMINFO.PRICE,
SALESORDERITEMINFO.QUANTITY,
SALESORDERITEMINFO.TOTAL,
SALESORDERITEMINFO.TYPECODE,
SALESORDERITEMINFO.DATA,
SALESORDERITEMINFO.OPTIONS,
SALESORDERITEMINFO.CALLBACKURL,
SALESORDERITEMINFO.EXPIREDCALLBACKURL,
SALESORDERITEMINFO.SYSTEMTYPENAME,
SALESORDERITEMINFO.ASSEMBLYNAME,
SALESORDERITEMINFO.ATTRIBUTES,
SALESORDERITEMINFO.CATEGORYNAME,
SALESORDERITEMINFO.ACKNOWLEDGEMENT,
SALESORDERITEMINFO.DATEADDED,
SALESORDERITEMINFO.EXPIRATIONDATE,
SALESORDERITEMINFO.ISEXPIRED,
SALESORDERITEMINFO.[HASNOVALIDDELIVERYMETHOD],
SALESORDERITEMINFO.[EXPIRATIONDELTA],
10 as ORDERITEMTYPE,
SALESORDERITEMINFO.SEQUENCE as SUBORDERITEMTYPE,
SALESORDERITEMINFO.ORDERSORTFIELD1,
SALESORDERITEMINFO.ORDERSORTFIELD2
from @COMBINATIONS COMBINATIONS
outer apply (
select top 1
SALESORDERITEM.ID,
COMBINATION.NAME + ' - ' + PRICETYPECODE.DESCRIPTION as DESCRIPTION,
dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]) as PRICE,
SALESORDERITEM.QUANTITY,
SALESORDERITEM.QUANTITY * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES([SALESORDERITEM].[ID]) as TOTAL,
SALESORDERITEM.TYPECODE,
SALESORDERITEM.DATA,
SALESORDERITEM.OPTIONS,
SALESORDERITEM.CALLBACKURL,
SALESORDERITEM.EXPIREDCALLBACKURL,
SALESORDERITEM.SYSTEMTYPENAME,
SALESORDERITEM.ASSEMBLYNAME,
SALESORDERITEM.ATTRIBUTES,
SALESORDERITEM.CATEGORYNAME,
SALESORDERITEM.ACKNOWLEDGEMENT,
SALESORDERRESERVEDITEM.DATEADDED,
SALESORDERRESERVEDITEM.EXPIRATIONDATE,
[EXPIRATIONSUMMARY].[RESERVATIONHASEXPIRED] as ISEXPIRED,
[EXPIRATIONSUMMARY].[DELIVERYHASEXPIRED] as [HASNOVALIDDELIVERYMETHOD],
[EXPIRATIONSUMMARY].[EXPIRATIONDELTA],
PRICETYPECODE.SEQUENCE,
cast(SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID as nvarchar(36)) as ORDERSORTFIELD1,
case
when [PROGRAM].[ISDAILYADMISSION] = 1 then [PROGRAM].[NAME]
else [EVENT].[NAME] + ' - ' + coalesce(' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')','')
end as ORDERSORTFIELD2
from dbo.SALESORDERITEMTICKET
inner join dbo.SALESORDERITEMTICKETCOMBINATION
on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = COMBINATIONS.TICKETCOMBINATIONID
inner join dbo.COMBINATION
on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID
inner join dbo.PRICETYPECODE
on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
inner join dbo.SALESORDERITEM
on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
left outer join dbo.PROGRAM
on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
left join dbo.EVENT on
SALESORDERITEMTICKET.EVENTID = EVENT.ID
left join dbo.SALESORDERRESERVEDITEM on
SALESORDERITEM.ID = SALESORDERRESERVEDITEM.ID
outer apply (
select
case
when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] is not null then
case [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE]
when 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
when 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
when 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
when 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
end
else [EVENT].[STARTDATETIMEWITHOFFSET]
end as [DATETIMEWITHOFFSET]
from dbo.[PROGRAMSALESMETHOD]
where
[PROGRAMSALESMETHOD].[PROGRAMID] = [EVENT].[PROGRAMID] and
[PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
) [EVENTOFFSALE]
outer apply (
select
case when [SALESORDERRESERVEDITEM].[ID] is null then 0 else 1 end [RESERVATION],
case when (SALESORDERITEM.TYPECODE <> 0) or (EVENT.ID is null and @DELIVERYMETHODEXISTS = 1) then 0 else 1 end [DELIVERY],
case when SALESORDERITEM.TYPECODE <> 0 or EVENT.ID is null then 0 else 1 end as [ONSALE]
) [CANEXPIRE]
outer apply (
select
case when [CANEXPIRE].[RESERVATION] = 1 then
case
when @CURRENTDATE > SALESORDERRESERVEDITEM.EXPIRATIONDATE then -1 --Already expired
when @MAXDATETIMEFORDIFFFROMCURRENT <= SALESORDERRESERVEDITEM.EXPIRATIONDATE then @MAXINT --Beyond comparison(tm)
else datediff(second,@CURRENTDATE,SALESORDERRESERVEDITEM.EXPIRATIONDATE)
end
else null
end as [RESERVATION],
case
when [CANEXPIRE].[DELIVERY] = 1 and @DELIVERYMETHODEXISTS = 0 then -1
when [CANEXPIRE].[DELIVERY] = 1 then
case
when @EARLIESTVALIDEVENTDATETIMEWITHOFFSET > EVENT.STARTDATETIMEWITHOFFSET then -1 --Already expired
when @MAXDATETIMEOFFSETFORDIFFFROMEARLIESTVALIDEVENT <= EVENT.STARTDATETIMEWITHOFFSET then @MAXINT --Beyond datediff comparison
else datediff(second,@EARLIESTVALIDEVENTDATETIMEWITHOFFSET, EVENT.STARTDATETIMEWITHOFFSET)
end
else null
end as [DELIVERY],
case when [CANEXPIRE].[ONSALE] = 1 then
case
when @CURRENTDATETIMEOFFSET > [EVENTOFFSALE].[DATETIMEWITHOFFSET] then -1 --Already expired
when @MAXDATETIMEOFFSETFORDIFFFROMCURRENT <= [EVENTOFFSALE].[DATETIMEWITHOFFSET] then @MAXINT --Beyond datediff comparison
else datediff(second,@CURRENTDATETIMEOFFSET, [EVENTOFFSALE].[DATETIMEWITHOFFSET])
end
else null
end as [ONSALE]
) as [EXPIRATIONDELTA]
outer apply (
select
case when [EXPIRATIONDELTA].[RESERVATION] <= 0 then 1 else 0 end as [RESERVATIONHASEXPIRED],
case when [EXPIRATIONDELTA].[DELIVERY] <= 0 or [EXPIRATIONDELTA].[ONSALE] <= 0 then 1 else 0 end [DELIVERYHASEXPIRED],
coalesce((
select min(EXPIRATION)
from (
select [EXPIRATIONDELTA].[DELIVERY] as [EXPIRATION]
union all
select [EXPIRATIONDELTA].[ONSALE] as [EXPIRATION]
union all
select [EXPIRATIONDELTA].[RESERVATION] as [EXPIRATION]
) [DELTAS]
),0) as [EXPIRATIONDELTA]
) as [EXPIRATIONSUMMARY]
where SALESORDERITEMTICKET.PRICETYPECODEID = COMBINATIONS.PRICETYPECODEID
) SALESORDERITEMINFO
union all
select
SALESORDERITEM.ID,
null as TICKETCOMBINATIONID,
SALESORDERITEM.DESCRIPTION,
SALESORDERITEM.PRICE,
SALESORDERITEM.QUANTITY,
SALESORDERITEM.TOTAL,
SALESORDERITEM.TYPECODE,
SALESORDERITEM.DATA,
SALESORDERITEM.OPTIONS,
SALESORDERITEM.CALLBACKURL,
SALESORDERITEM.EXPIREDCALLBACKURL,
SALESORDERITEM.SYSTEMTYPENAME,
SALESORDERITEM.ASSEMBLYNAME,
SALESORDERITEM.ATTRIBUTES,
SALESORDERITEM.CATEGORYNAME,
SALESORDERITEM.ACKNOWLEDGEMENT,
null DATEADDED,
null EXPIRATIONDATE,
0 ISEXPIRED,
0 as [HASNOVALIDDELIVERYMETHOD],
0 as [EXPIRATIONDELTA],
70 as ORDERITEMTYPE,
0 as SUBORDERITEMTYPE,
null as ORDERSORTFIELD1,
null as ORDERSORTFIELD2
from dbo.SALESORDERITEM
left join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.ID
where
SALESORDERITEM.SALESORDERID = @CONTEXTID and
SALESORDERITEM.TYPECODE = 3 and
SALESORDERITEMFEE.APPLIESTOCODE = 0
union all
select
SALESORDERITEM.ID,
null as TICKETCOMBINATIONID,
SALESORDERITEM.DESCRIPTION,
SALESORDERITEM.PRICE,
SALESORDERITEM.QUANTITY,
SALESORDERITEM.TOTAL,
SALESORDERITEM.TYPECODE,
SALESORDERITEM.DATA,
SALESORDERITEM.OPTIONS,
SALESORDERITEM.CALLBACKURL,
SALESORDERITEM.EXPIREDCALLBACKURL,
SALESORDERITEM.SYSTEMTYPENAME,
SALESORDERITEM.ASSEMBLYNAME,
SALESORDERITEM.ATTRIBUTES,
SALESORDERITEM.CATEGORYNAME,
SALESORDERITEM.ACKNOWLEDGEMENT,
null DATEADDED,
null EXPIRATIONDATE,
0 ISEXPIRED,
0 as [HASNOVALIDDELIVERYMETHOD],
0 as [EXPIRATIONDELTA],
70 as ORDERITEMTYPE,
0 as SUBORDERITEMTYPE,
null as ORDERSORTFIELD1,
null as ORDERSORTFIELD2
from dbo.SALESORDERITEM
left join dbo.SALESORDERITEMFEE on
SALESORDERITEM.ID = SALESORDERITEMFEE.ID
where
SALESORDERITEM.SALESORDERID = @CONTEXTID and
SALESORDERITEM.TYPECODE = 4
) INFO;