USP_DATALIST_SALESORDER_TICKETS
Lists all tickets belonging to a sales order.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONTEXTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_SALESORDER_TICKETS
(
@CONTEXTID uniqueidentifier
)
as
declare @FEESDISCOUNTSANDREFUNDS table (
SALESORDERITEMID uniqueidentifier,
FEES money,
DISCOUNTS money,
QUANTITYREFUNDED int,
AMOUNTREFUNDED money
)
insert into @FEESDISCOUNTSANDREFUNDS
select
[SALESORDERITEM].[ID],
(
(select coalesce(sum([FEESALESORDERITEM].[TOTAL]),0.0)
from dbo.[SALESORDERITEM] as [FEESALESORDERITEM]
inner join dbo.[SALESORDERITEMFEE] on [FEESALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
where [SALESORDERITEMFEE].[SALESORDERITEMID] = [SALESORDERITEM].[ID])
-
(select isnull(sum((LI.QUANTITY * LI.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS),0)
from dbo.SALESORDERITEMFEE
inner join dbo.CREDITITEM_EXT on SALESORDERITEMFEE.ID = CREDITITEM_EXT.SALESORDERITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
where SALESORDERITEMFEE.SALESORDERITEMID = SALESORDERITEM.ID)
) as [FEES],
(
select coalesce(sum([AMOUNT]),0.0)
from dbo.[SALESORDERITEMITEMDISCOUNT]
where [SALESORDERITEMID] = [SALESORDERITEM].[ID]
) as [DISCOUNTS], --Item Discounts
(
select coalesce(count([ID]), 0)
from dbo.[TICKET]
where [TICKET].SALESORDERITEMTICKETID = [SALESORDERITEM].[ID] and
[TICKET].[STATUSCODE] = 2
) as [REFUNDEDQUANTITY],
(
select
isnull(sum((LI.QUANTITY * LI.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS), 0)
from
dbo.CREDITITEM_EXT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
inner join
dbo.FINANCIALTRANSACTION FT on FT.ID = CREDITITEM_EXT.CREDITID
where
CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
and FT.TYPECODE = 23 -- Refund
) as [AMOUNTREFUNDED]
from dbo.[SALESORDERITEM]
where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID
declare @TICKETCOMBINATION table (
SALESORDERID uniqueidentifier,
TICKETCOMBINATIONID uniqueidentifier,
PRICETYPECODEID uniqueidentifier,
SALESORDERTICKETCOMBINATIONID uniqueidentifier
)
insert into @TICKETCOMBINATION (SALESORDERID, TICKETCOMBINATIONID, PRICETYPECODEID)
select distinct SALESORDERITEM.SALESORDERID, SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID, SALESORDERITEMTICKET.PRICETYPECODEID
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
where SALESORDERITEM.SALESORDERID = @CONTEXTID
update @TICKETCOMBINATION set SALESORDERTICKETCOMBINATIONID = newid()
declare @CREDITTICKETCOMBINATION table (
CREDITID uniqueidentifier,
TICKETCOMBINATIONID uniqueidentifier,
PRICETYPECODEID uniqueidentifier,
CREDITTICKETCOMBINATIONID uniqueidentifier
)
insert into @CREDITTICKETCOMBINATION (CREDITID, TICKETCOMBINATIONID, PRICETYPECODEID)
select distinct FT.ID, SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID, SALESORDERITEMTICKET.PRICETYPECODEID
from dbo.FINANCIALTRANSACTION FT
inner join dbo.CREDIT_EXT on FT.ID = CREDIT_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
inner join dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = LI.ID
inner join dbo.SALESORDERITEM on CREDITITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
inner join dbo.SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
where CREDIT_EXT.SALESORDERID = @CONTEXTID and FT.TYPECODE = 23;
update @CREDITTICKETCOMBINATION set CREDITTICKETCOMBINATIONID = newid()
select
[TICKETCOMBINATIONID],
[ID],
[DESCRIPTION],
[STARTDATE],
[STARTTIME],
[LOCATION],
[QUANTITY],
[PRICE],
[DISCOUNTS],
[FEES],
[TOTAL],
[EVENTID],
[PROGRAMID],
[PRICETYPECODEID],
[ISREFUND],
case PRICINGSTRUCTURECODE
when 2 then 1 -- when structure is override rate
else 0
end as [ISPRICEOVERRIDDEN],
[ORDERDISCOUNT],
[NAME],
[COMBINATIONID]
from(
--Tickets
select
TICKETCOMBINATION.TICKETCOMBINATIONID,
[SALESORDERITEM].[ID],
[SALESORDERITEM].[DESCRIPTION],
[EVENT].[STARTDATE],
[EVENT].[STARTTIME],
dbo.UFN_EVENT_GETLOCATIONNAME([EVENT].[ID]) as [LOCATION],
[SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] as [QUANTITY],
[SALESORDERITEM].[PRICE],
[FEESDISCOUNTSANDREFUNDS].[DISCOUNTS],
[FEESDISCOUNTSANDREFUNDS].[FEES],
SALESORDERITEM.TOTAL + FEESDISCOUNTSANDREFUNDS.FEES - FEESDISCOUNTSANDREFUNDS.DISCOUNTS - FEESDISCOUNTSANDREFUNDS.AMOUNTREFUNDED as [TOTAL],
[EVENT].[ID] as [EVENTID],
[PROGRAM].[ID] as [PROGRAMID],
[SALESORDERITEMTICKET].[PRICETYPECODEID],
0 as [ISREFUND],
[SALESORDERITEM].[DATEADDED],
SALESORDERITEM.PRICINGSTRUCTURECODE,
(
select coalesce(sum([AMOUNT]),0)
from dbo.[SALESORDERITEMORDERDISCOUNTDETAIL]
where [SALESORDERITEMID] = SALESORDERITEM.ID
) as [ORDERDISCOUNT], --Only calculated here for performance reasons (and not on the other unioned pieces) since this value is only one used online
isnull([EVENT].[NAME], PROGRAM.NAME) as NAME,
[SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID]
from dbo.[SALESORDERITEM]
inner join @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
left join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
left join @TICKETCOMBINATION as TICKETCOMBINATION on [SALESORDERITEM].[SALESORDERID] = TICKETCOMBINATION.[SALESORDERID]
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = TICKETCOMBINATION.TICKETCOMBINATIONID
and SALESORDERITEMTICKET.PRICETYPECODEID = TICKETCOMBINATION.PRICETYPECODEID
left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
where ([FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] = 0) or ([SALESORDERITEM].[QUANTITY] > [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED])
union all
-- combination tickets
select distinct
null,
TICKETCOMBINATION.SALESORDERTICKETCOMBINATIONID,
[COMBINATION].[NAME] + ' - ' + [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
null as [STARTDATE],
null as [STARTTIME],
null as [LOCATION],
[SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] as [QUANTITY],
dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]),
0,
dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES([SALESORDERITEM].[ID]),
([SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED]) * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES([SALESORDERITEM].[ID]),
null,
null,
[SALESORDERITEMTICKET].[PRICETYPECODEID],
0 as [ISREFUND],
[SALESORDERITEM].[DATEADDED],
SALESORDERITEM.PRICINGSTRUCTURECODE,
0 as [ORDERDISCOUNT],
COMBINATION.NAME,
COMBINATION.ID as COMBINATIONID
from dbo.[SALESORDERITEM]
inner join @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
inner join @TICKETCOMBINATION as TICKETCOMBINATION on [SALESORDERITEM].[SALESORDERID] = TICKETCOMBINATION.[SALESORDERID]
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = TICKETCOMBINATION.TICKETCOMBINATIONID
and SALESORDERITEMTICKET.PRICETYPECODEID = TICKETCOMBINATION.PRICETYPECODEID
inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
inner join dbo.[COMBINATIONPRICETYPE] on [COMBINATIONPRICETYPE].PRICETYPECODEID = [SALESORDERITEMTICKET].[PRICETYPECODEID]
and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID]
inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
[SALESORDERITEM].[QUANTITY] - [FEESDISCOUNTSANDREFUNDS].[QUANTITYREFUNDED] > 0
union all
--Refunded tickets
select
CREDITTICKETCOMBINATION.CREDITTICKETCOMBINATIONID,
CREDITITEM_EXT.[ID] as [ID],
'Refund ' + convert(nvarchar(20),cast(FT.DATE as datetime), 1) + ', ' + [SALESORDERITEM].[DESCRIPTION] as [DESCRIPTION],
[EVENT].[STARTDATE],
[EVENT].[STARTTIME],
dbo.UFN_EVENT_GETLOCATIONNAME([EVENT].[ID]) as [LOCATION],
LI.[QUANTITY],
LI.UNITVALUE as [PRICE],
CREDITITEM_EXT.[DISCOUNTS],
CREDITITEM_EXT.[FEES],
(LI.QUANTITY * LI.UNITVALUE) - CREDITITEM_EXT.DISCOUNTS + CREDITITEM_EXT.FEES,
[EVENT].[ID] as [EVENTID],
[PROGRAM].[ID] as [PROGRAMID],
[SALESORDERITEMTICKET].[PRICETYPECODEID],
1 as [ISREFUND],
LI.[DATEADDED],
SALESORDERITEM.PRICINGSTRUCTURECODE,
0 as [ORDERDISCOUNT],
isnull([EVENT].[NAME], PROGRAM.NAME) as NAME,
[SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID]
from dbo.CREDITITEM_EXT
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.[ID] = LI.FINANCIALTRANSACTIONID
inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
inner join dbo.[SALESORDERITEM] on CREDITITEM_EXT.[SALESORDERITEMID] = [SALESORDERITEM].[ID]
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
left join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
left join @CREDITTICKETCOMBINATION as CREDITTICKETCOMBINATION on CREDIT_EXT.[ID] = CREDITTICKETCOMBINATION.[CREDITID]
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = CREDITTICKETCOMBINATION.TICKETCOMBINATIONID
and SALESORDERITEMTICKET.PRICETYPECODEID = CREDITTICKETCOMBINATION.PRICETYPECODEID
left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
where CREDIT_EXT.[SALESORDERID] = @CONTEXTID and FT.[TYPECODE] = 23
union all
-- Refunded combination tickets
select distinct
null,
CREDITTICKETCOMBINATION.CREDITTICKETCOMBINATIONID as [ID],
'Refund ' + [COMBINATION].[NAME] + ' - ' + [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
null,
null,
null,
LI.[QUANTITY],
dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]),
CREDITITEM_EXT.[DISCOUNTS],
dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED(LI.ID) as FEES,
LI.[QUANTITY] * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED(LI.ID),
null,
null,
[SALESORDERITEMTICKET].[PRICETYPECODEID],
1 as [ISREFUND],
LI.[DATEADDED],
SALESORDERITEM.PRICINGSTRUCTURECODE,
0 as [ORDERDISCOUNT],
COMBINATION.NAME,
[SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID]
from dbo.CREDITITEM_EXT
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITITEM_EXT.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.[ID] = LI.FINANCIALTRANSACTIONID
inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = FT.ID
inner join dbo.[SALESORDERITEM] on CREDITITEM_EXT.[SALESORDERITEMID] = [SALESORDERITEM].[ID]
inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
inner join @CREDITTICKETCOMBINATION as CREDITTICKETCOMBINATION on CREDIT_EXT.[ID] = CREDITTICKETCOMBINATION.[CREDITID]
and SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = CREDITTICKETCOMBINATION.TICKETCOMBINATIONID
and SALESORDERITEMTICKET.PRICETYPECODEID = CREDITTICKETCOMBINATION.PRICETYPECODEID
inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
inner join dbo.[COMBINATIONPRICETYPE] on [COMBINATIONPRICETYPE].PRICETYPECODEID = [SALESORDERITEMTICKET].[PRICETYPECODEID]
and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID]
inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
where CREDIT_EXT.[SALESORDERID] = @CONTEXTID and FT.[TYPECODE] = 23
) as [TICKETS]
order by [DATEADDED], [TICKETS].[ID], [TICKETS].[ISREFUND]