USP_DATAFORMTEMPLATE_EDIT_SALESORDERITEMEVENTREGISTRATION
The save procedure used by the edit dataform template "Sales Order Item Event Registration Edit Data Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @REGISTRANTID | uniqueidentifier | IN | Event registration |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESORDERITEMEVENTREGISTRATION
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@REGISTRANTID uniqueidentifier
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @CONSTITUENTID uniqueidentifier
declare @EVENTID uniqueidentifier
select
@EVENTID = [EVENTID],
@CONSTITUENTID = [CONSTITUENTID]
from dbo.[REGISTRANT]
where [ID] = @REGISTRANTID
--The user could have selected multiple events in which to register
--(if event selected was within a main event)
--Determine those other events
declare @REGISTRANTEVENTS table ([EVENTID] uniqueidentifier)
insert @REGISTRANTEVENTS ([EVENTID])
values(@EVENTID)
insert @REGISTRANTEVENTS
select [EVENT].[ID]
from (select [ID], [MAINEVENTID] from dbo.[EVENT] where [ID] = @EVENTID) as [REGISTRANTEVENT]
inner join dbo.[EVENT]
on [REGISTRANTEVENT].[MAINEVENTID] = [EVENT].[MAINEVENTID] or
[REGISTRANTEVENT].[MAINEVENTID] = [EVENT].[ID] or
[REGISTRANTEVENT].[ID] = [EVENT].[MAINEVENTID]
--Determine which other registrations could have been created/altered
declare @REGISTRATIONS table (
[ID] uniqueidentifier,
[REGISTRANTID] uniqueidentifier,
[EVENTID] uniqueidentifier,
[EVENTNAME] nvarchar(100),
[AMOUNT] money -- Balance
)
insert @REGISTRATIONS
select
coalesce(( --Get sales order item id for registrations that already exist in order
select [SALESORDERITEMEVENTREGISTRATION].[ID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @ID and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANT].[ID]
),newid()),
[REGISTRANT].[ID],
[EVENT].[ID],
[EVENT].[NAME],
dbo.UFN_EVENTREGISTRANT_GETBALANCE([REGISTRANT].[ID]) as [AMOUNT]
from dbo.[REGISTRANT]
inner join dbo.[EVENT]
on [REGISTRANT].[EVENTID] = [EVENT].[ID]
left join dbo.[REGISTRANTREGISTRATION]
on [REGISTRANT].[ID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
where
(
[REGISTRANT].[EVENTID] in (select [EVENTID] from @REGISTRANTEVENTS) and
[REGISTRANT].[CONSTITUENTID] = @CONSTITUENTID
)
group by
[REGISTRANT].[ID],
[EVENT].[ID],
[EVENT].[NAME]
--Add/Update Sales order items
begin try
exec dbo.USP_SALESORDER_ISCOMPLETE_RAISERROR @ID, @EXCLUDEGROUPSALES = 1;
--Update registrations that may have changed through registration form
update dbo.[SALESORDERITEM]
set
[PRICE] = (
select [AMOUNT]
from @REGISTRATIONS as [REGISTRATIONS]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
where [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where exists (
select [REGISTRATIONS].[REGISTRANTID]
from @REGISTRATIONS as [REGISTRATIONS]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
inner join dbo.[SALESORDERITEM] as [SOI]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SOI].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @ID and
[SOI].[ID] = [SALESORDERITEM].[ID] and
[REGISTRATIONS].[AMOUNT] >= 0
)
update dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
set
[QUANTITY] = (
select coalesce(sum([REGISTRANTREGISTRATION].[QUANTITY]),0)
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[REGISTRANTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
where
[REGISTRANTREGISTRATION].[EVENTPRICEID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] and
[SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID]
),
[AMOUNT] = (
select coalesce(sum([REGISTRANTREGISTRATION].[AMOUNT]),0)
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[REGISTRANTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
where
[REGISTRANTREGISTRATION].[EVENTPRICEID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] and
[SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID]
),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where exists (
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from @REGISTRATIONS as [REGISTRATIONS]
inner join dbo.[REGISTRANTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where
[SALESORDERITEM].[SALESORDERID] = @ID and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRATIONS].[REGISTRANTID] and
[SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] = [REGISTRANTREGISTRATION].[EVENTPRICEID] and
[REGISTRATIONS].[AMOUNT] >= 0
)
insert into dbo.[SALESORDERITEM]
(
[ID],
[SALESORDERID],
[TYPECODE],
[DESCRIPTION],
[QUANTITY],
[PRICE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[ID],
@ID,
6,
[EVENTNAME],
1,
[AMOUNT],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRATIONS as [REGISTRATIONS]
where
--Registration is not already in order
[REGISTRANTID] not in (
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where [SALESORDERITEM].[SALESORDERID] = @ID
) and
--Is not negative
[REGISTRATIONS].[AMOUNT] >= 0
insert into dbo.[SALESORDERITEMEVENTREGISTRATION]
(
[ID],
[REGISTRANTID],
[EVENTNAME],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
[ID],
[REGISTRANTID],
coalesce((select [EVENT].[NAME] from dbo.[EVENT] where [EVENT].[ID] = [REGISTRATIONS].[EVENTID]),''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRATIONS as [REGISTRATIONS]
where
--Registration not already in order
[REGISTRANTID] not in (
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
where [SALESORDERITEM].[SALESORDERID] = @ID
) and
--Is not negative
[REGISTRATIONS].[AMOUNT] >= 0
insert into dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
(
[ID],
[SALESORDERITEMEVENTREGISTRATIONID],
[EVENTPRICEID],
[QUANTITY],
[AMOUNT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
newid(),
[REGISTRATIONS].[ID],
[REGISTRANTREGISTRATION].[EVENTPRICEID],
coalesce(sum([REGISTRANTREGISTRATION].[QUANTITY]),0),
coalesce(sum([REGISTRANTREGISTRATION].[AMOUNT]),0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from @REGISTRATIONS as [REGISTRATIONS]
inner join dbo.[REGISTRANTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [REGISTRANTREGISTRATION].[REGISTRANTID]
where not exists ( --Allows for new pricing information on new and altered registrations
select [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
from dbo.[SALESORDERITEMEVENTREGISTRATION]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
inner join dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID]
where
[SALESORDERITEM].[SALESORDERID] = @ID and
[SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = [REGISTRATIONS].[REGISTRANTID] and
[SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] = [REGISTRANTREGISTRATION].[EVENTPRICEID]
) and
--Is not negative
[REGISTRATIONS].[AMOUNT] >= 0
group by
[REGISTRANTREGISTRATION].[EVENTPRICEID],
[REGISTRATIONS].[ID]
--Delete sales order items that have been edited to negative balance
declare @ORDERITEM uniqueidentifier = null
declare deletezeroitems_cursor cursor local fast_forward for
select [ID]
from dbo.[SALESORDERITEM]
where exists (
select [REGISTRATIONS].[REGISTRANTID]
from @REGISTRATIONS as [REGISTRATIONS]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [REGISTRATIONS].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
inner join dbo.[SALESORDERITEM] as [SOI]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SOI].[ID]
where
[REGISTRATIONS].[AMOUNT] < 0 and
[SALESORDERITEM].[SALESORDERID] = @ID and
[SOI].[ID] = [SALESORDERITEM].[ID]
)
OPEN deletezeroitems_cursor
FETCH NEXT FROM deletezeroitems_cursor
INTO @ORDERITEM
while @@FETCH_STATUS = 0
begin
exec dbo.USP_SALESORDERITEM_DELETE @ORDERITEM, @CHANGEAGENTID
FETCH NEXT FROM deletezeroitems_cursor
INTO @ORDERITEM
end
close deletezeroitems_cursor
deallocate deletezeroitems_cursor
--Delete pricing data that has been deleted
declare deleteprice_cursor cursor local fast_forward for
select [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[ID]
from dbo.[SALESORDERITEMEVENTREGISTRANTREGISTRATION]
inner join dbo.[SALESORDERITEMEVENTREGISTRATION]
on [SALESORDERITEMEVENTREGISTRANTREGISTRATION].[SALESORDERITEMEVENTREGISTRATIONID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
inner join dbo.[SALESORDERITEM]
on [SALESORDERITEMEVENTREGISTRATION].[ID] = [SALESORDERITEM].[ID]
where
[SALESORDERITEMEVENTREGISTRANTREGISTRATION].[EVENTPRICEID] not in (
select [EVENTPRICEID]
from dbo.[REGISTRANTREGISTRATION]
where [REGISTRANTREGISTRATION].[REGISTRANTID] = [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
) and
[SALESORDERITEM].[SALESORDERID] = @ID
OPEN deleteprice_cursor
FETCH NEXT FROM deleteprice_cursor
INTO @ORDERITEM
while @@FETCH_STATUS = 0
begin
exec dbo.USP_SALESORDERITEMEVENTREGISTRANTREGISTRATION_DELETEBYID_WITHCHANGEAGENTID @ORDERITEM, @CHANGEAGENTID
FETCH NEXT FROM deleteprice_cursor
INTO @ORDERITEM
end
close deleteprice_cursor
deallocate deleteprice_cursor
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0