USP_SALESORDERITEM_DELETE
Executes the "Sales Order Item Delete" record operation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the ID of the record being deleted. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the delete. |
Definition
Copy
CREATE procedure dbo.USP_SALESORDERITEM_DELETE
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as begin
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @ORDERID uniqueidentifier
declare @TYPECODE int
declare @SALESORDERSTATUSCODE tinyint
select @ORDERID = SALESORDERID, @TYPECODE = TYPECODE from dbo.SALESORDERITEM where ID = @ID
select @SALESORDERSTATUSCODE = [STATUSCODE] from dbo.[SALESORDER] where ID = @ORDERID
begin try
exec dbo.USP_SALESORDER_ISCOMPLETE_RAISERROR @ORDERID, @EXCLUDEGROUPSALES = 1;
declare @e int;
declare @contextCache varbinary(128);
declare @REGISTRANTS table
(
ID uniqueidentifier,
GUESTOFREGISTRANTID uniqueidentifier
);
if @SALESORDERSTATUSCODE = 7
begin
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.[SALESORDERNOTE] where [SALESORDERITEMID] = @ID
end
if @TYPECODE in (0, 14)
begin
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL where SALESORDERITEMID = @ID
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
if @TYPECODE = 0
begin
exec dbo.USP_SALESORDERITEM_REMOVEFEES @ID, @CHANGEAGENTID;
insert into @REGISTRANTS
(
ID,
GUESTOFREGISTRANTID
)
select
REGISTRANT.ID,
REGISTRANT.GUESTOFREGISTRANTID
from
dbo.SALESORDERITEMTICKETREGISTRANT
inner join
dbo.REGISTRANT on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
where
SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = @ID;
insert into @REGISTRANTS
(
ID,
GUESTOFREGISTRANTID
)
select
GUESTOFREGISTRANTID,
null
from
@REGISTRANTS
where
GUESTOFREGISTRANTID not in (select ID from @REGISTRANTS);
--Combinations, delete tickets for this combination with the same price type
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
declare @TICKETCOMBINATIONID uniqueidentifier = (select [TICKETCOMBINATIONID] from dbo.[SALESORDERITEMTICKETCOMBINATION] where [ID] = @ID)
declare @PRICETYPECODEID uniqueidentifier = (select [PRICETYPECODEID] from dbo.[SALESORDERITEMTICKET] where [ID] = @ID)
declare @COMBOITEMS table (SALESORDERITEMID uniqueidentifier);
insert into @COMBOITEMS
select [SALESORDERITEM].[ID]
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMTICKET]
on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
inner join dbo.[SALESORDERITEMTICKETCOMBINATION]
on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @ORDERID and
[SALESORDERITEMTICKET].[PRICETYPECODEID] = @PRICETYPECODEID and
[SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] = @TICKETCOMBINATIONID
delete from dbo.[SALESORDERITEM]
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMFEE]
on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
where
[SALESORDERITEMFEE].[SALESORDERITEMID] in
(
select SALESORDERITEMID
from @COMBOITEMS
)
delete from dbo.[SALESORDERITEMORDERDISCOUNTDETAIL]
where [SALESORDERITEMID] in
(
select SALESORDERITEMID
from @COMBOITEMS
)
delete dbo.[SALESORDERITEM]
where ID in
(
select SALESORDERITEMID
from @COMBOITEMS
)
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
if @TYPECODE = 12
begin
declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier
select @SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITYID
from dbo.SALESORDERITEMSPONSORSHIP
where ID = @ID
if @SPONSORSHIPOPPORTUNITYID is not null
exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @SPONSORSHIPOPPORTUNITYID, 0
end
if @TYPECODE = 7
begin
declare @ITINERARYITEMLOCATIONID uniqueidentifier
select
@ITINERARYITEMLOCATIONID = ITINERARYITEMLOCATION.ID
from dbo.ITINERARYITEMLOCATION
where SALESORDERITEMID = @ID
if @ITINERARYITEMLOCATIONID is not null
exec dbo.USP_ITINERARYITEMLOCATION_DELETEBYID_WITHCHANGEAGENTID @ITINERARYITEMLOCATIONID, @CHANGEAGENTID;
end
if @TYPECODE = 1 -- delete membership addon first
begin
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.[SALESORDERITEM]
from dbo.[SALESORDERITEM]
inner join dbo.[SALESORDERITEMMEMBERSHIPADDON]
on [SALESORDERITEM].ID = [SALESORDERITEMMEMBERSHIPADDON].ID
where
[SALESORDERITEMMEMBERSHIPADDON].SALESORDERITEMMEMBERSHIPID = @ID
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
-- use the system generated delete routine to allow proper recording of the deleting agent
exec dbo.USP_SALESORDERITEM_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
if @TYPECODE = 0
begin
exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- Delete guests
delete from dbo.REGISTRANT
where ID in (select ID from @REGISTRANTS where GUESTOFREGISTRANTID is not null);
-- Delete hosts if they don't have anymore guests and don't have their own ticket
delete from dbo.REGISTRANT
where ID in (
select REGISTRANTSTODELETE.ID from @REGISTRANTS as REGISTRANTSTODELETE
left outer join dbo.REGISTRANT on REGISTRANT.GUESTOFREGISTRANTID = REGISTRANTSTODELETE.ID
left outer join dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANTSTODELETE.ID
where REGISTRANT.ID is null and SALESORDERITEMTICKETREGISTRANT.ID is null
);
-- Update hosts who still have guests and don't have a ticket and delete their preferences
-- TODO: Make this more efficient
delete from dbo.REGISTRANTPREFERENCE
where REGISTRANTID in (
select REGISTRANTSTODELETE.ID from @REGISTRANTS as REGISTRANTSTODELETE
left outer join dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANTSTODELETE.ID
where SALESORDERITEMTICKETREGISTRANT.ID is null
);
if not @contextCache is null
set CONTEXT_INFO @contextCache;
select @e=@@error;
if @e<>0 return -456; --always return non-zero sp result if an error occurs
update dbo.REGISTRANT set
WILLNOTATTEND = 1,
ATTENDED = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID in (
select REGISTRANTSTODELETE.ID from @REGISTRANTS as REGISTRANTSTODELETE
left outer join dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANTSTODELETE.ID
where SALESORDERITEMTICKETREGISTRANT.ID is null
)
and WILLNOTATTEND <> 1;
end
if @TYPECODE <> 2
begin
exec dbo.USP_COMBINATION_REMOVEINELIGIBLECOMBINATIONS @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;
end
if @TYPECODE in (1,13) -- Membership or Membership Promotion
exec dbo.USP_SALESORDER_UPDATEMEMBERSHIPPROMOS @ORDERID, @CHANGEAGENTID;
exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;
end