USP_DATAFORMTEMPLATE_ADD_REGISTRANTMAINEVENTFROMINVITEE
The save procedure used by the add dataform template "Registrant Main Event from Invitee Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @INVITEEID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CONSTITUENTID | uniqueidentifier | IN | Registrant |
| @DATEPURCHASED | datetime | IN | Date |
| @PACKAGEREGISTRATIONS | xml | IN | Packages |
| @PACKAGESPRICES | xml | IN | |
| @SINGLEEVENTREGISTRATIONS | xml | IN | Single events |
| @WAIVEBENEFITS | bit | IN | Waive benefits |
| @REGISTRANTMAPPINGS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_REGISTRANTMAINEVENTFROMINVITEE
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@INVITEEID uniqueidentifier,
@CONSTITUENTID uniqueidentifier = null,
@DATEPURCHASED datetime = null,
@PACKAGEREGISTRATIONS xml = null,
@PACKAGESPRICES xml = null,
@SINGLEEVENTREGISTRATIONS xml = null,
@WAIVEBENEFITS bit = null,
@REGISTRANTMAPPINGS xml = null
)
as
set nocount on;
declare @CURRENTDATE datetime;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
begin try
declare @EVENTID uniqueidentifier;
select @EVENTID = EVENTID from dbo.INVITEE where ID = @INVITEEID;
--List the registrations that will be created
declare @REGISTRATIONTOADDTABLE table
(
EVENTID uniqueidentifier,
EVENTPRICEID uniqueidentifier,
AMOUNT money,
RECEIPTAMOUNT money,
QUANTITY int,
DATEPURCHASED datetime,
REGISTRATIONPACKAGEID uniqueidentifier,
REGISTRANTREGISTRATIONID uniqueidentifier default newid()
);
declare @PACKAGECOUNT int;
insert into @REGISTRATIONTOADDTABLE
(
EVENTID,
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY,
DATEPURCHASED,
REGISTRATIONPACKAGEID
)
select
EVENTPRICE.EVENTID,
PACKAGEPRICE.EVENTPRICEID,
(EVENTPRICE.AMOUNT * SELECTEDPACKAGE.QUANTITY),
(EVENTPRICE.RECEIPTAMOUNT * SELECTEDPACKAGE.QUANTITY),
SELECTEDPACKAGE.QUANTITY,
@DATEPURCHASED,
SELECTEDPACKAGE.PACKAGEREGISTRATIONPACKAGEID
from
(
--TODO: Change to FROMITEMLISTXML function
SELECT
T.c.value('(PACKAGEREGISTRATIONPACKAGEID)[1]','uniqueidentifier') AS 'PACKAGEREGISTRATIONPACKAGEID',
T.c.value('(QUANTITY)[1]','int') AS 'QUANTITY'
FROM @PACKAGEREGISTRATIONS.nodes('/PACKAGEREGISTRATIONS/ITEM') T(c)
) SELECTEDPACKAGE
inner join dbo.UFN_REGISTRATIONPACKAGE_GETPACKAGESPRICES_FROMITEMLISTXML(@PACKAGESPRICES) PACKAGEPRICE on SELECTEDPACKAGE.PACKAGEREGISTRATIONPACKAGEID = PACKAGEPRICE.ID
inner join dbo.EVENTPRICE on PACKAGEPRICE.EVENTPRICEID = EVENTPRICE.ID;
set @PACKAGECOUNT = @@ROWCOUNT;
--TODO: Fix this hack with a new FROMITEMLISTXML function or by changing the field names in the spec
set @SINGLEEVENTREGISTRATIONS = replace(cast(@SINGLEEVENTREGISTRATIONS as nvarchar(max)), N'SINGLEEVENTREGISTRATIONS>', N'REGISTRATIONS>');
set @SINGLEEVENTREGISTRATIONS = replace(cast(@SINGLEEVENTREGISTRATIONS as nvarchar(max)), N'SINGLEEVENTREGISTRATIONEVENTPRICEID>', N'EVENTPRICEID>');
declare @REGISTRATIONCOUNT int;
insert into @REGISTRATIONTOADDTABLE
(
EVENTID,
EVENTPRICEID,
AMOUNT,
RECEIPTAMOUNT,
QUANTITY,
DATEPURCHASED,
REGISTRATIONPACKAGEID
)
select
EVENTPRICE.EVENTID,
SELECTEDREGISTRATION.EVENTPRICEID,
SELECTEDREGISTRATION.AMOUNT,
SELECTEDREGISTRATION.RECEIPTAMOUNT,
SELECTEDREGISTRATION.QUANTITY,
@DATEPURCHASED,
null
from
dbo.UFN_REGISTRANT_GETREGISTRATIONS_FROMITEMLISTXML(@SINGLEEVENTREGISTRATIONS) SELECTEDREGISTRATION
inner join dbo.EVENTPRICE on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICE.ID;
set @REGISTRATIONCOUNT = @@ROWCOUNT;
--When there are no packages or registrations options the registrant is just added to the main event
--Otherwise the rest of the procedure is executed
if @PACKAGECOUNT + @REGISTRATIONCOUNT = 0
insert into dbo.REGISTRANT
(
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EVENTID,
@CONSTITUENTID,
0, --Not attended by default
0, --Will attend
null,
@WAIVEBENEFITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
else
begin
-- There are packages or registrations to add
/*
--List the registration mappings
declare @REGISTRANTMAPPINGTABLE table
(
REGISTRATIONPACKAGEID uniqueidentifier,
EVENTID uniqueidentifier,
EVENTPRICEID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
PREFERENCES xml,
MAPPINGID uniqueidentifier default newid() --Used to connect preferences to unknown guest registrant IDs
);
insert into @REGISTRANTMAPPINGTABLE
(
REGISTRATIONPACKAGEID,
EVENTID,
EVENTPRICEID,
CONSTITUENTID,
PREFERENCES
)
--TODO: Change to FROMITEMLISTXML function
SELECT
T.c.value('(REGISTRATIONPACKAGEID)[1]','uniqueidentifier') AS 'REGISTRATIONPACKAGEID',
T.c.value('(EVENTID)[1]','uniqueidentifier') AS 'EVENTID',
T.c.value('(EVENTPRICEID)[1]','uniqueidentifier') AS 'EVENTPRICEID',
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID',
T.c.value('(PREFERENCES)[1]','nvarchar(max)') AS 'PREFERENCES'
FROM @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(c);
*/
--List the registration mappings
declare @REGISTRANTMAPPINGTABLE table
(
REGISTRATIONPACKAGEID uniqueidentifier,
EVENTID uniqueidentifier,
EVENTPRICEID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
MAPPINGID uniqueidentifier default newid() --Used to connect preferences to unknown guest registrant IDs
);
insert into @REGISTRANTMAPPINGTABLE
(
REGISTRATIONPACKAGEID,
EVENTID,
EVENTPRICEID,
CONSTITUENTID
)
--TODO: Change to FROMITEMLISTXML function
SELECT
T.c.value('(REGISTRATIONPACKAGEID)[1]','uniqueidentifier') AS 'REGISTRATIONPACKAGEID',
T.c.value('(EVENTID)[1]','uniqueidentifier') AS 'EVENTID',
T.c.value('(EVENTPRICEID)[1]','uniqueidentifier') AS 'EVENTPRICEID',
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID'
FROM @REGISTRANTMAPPINGS.nodes('/REGISTRANTMAPPINGS/ITEM') T(c);
--Replace the well-known GUIDs for registrant mapping with the actual IDs
update @REGISTRANTMAPPINGTABLE
set
CONSTITUENTID = @CONSTITUENTID
where
CONSTITUENTID = '0F87025C-8FCA-4392-9780-45E4569DC331'; --Well-known GUID for registrant
update @REGISTRANTMAPPINGTABLE
set
CONSTITUENTID = null
where
CONSTITUENTID = 'C776DAB5-65B2-4258-ADAE-AE396D28E251'; --Well-known GUID for unknown guest
--List the event-to-constituent mappings by expanding the package mappings
declare @REGISTRANTEVENTMAPPINGTABLE table
(
REGISTRANTID uniqueidentifier default newid(), --Used to keep track of unknown guests
MAPPINGID uniqueidentifier, --Used to map the registrant ID for unknown guests back to preferences in @REGISTRANTMAPPINGTABLE
EVENTID uniqueidentifier,
EVENTPRICEID uniqueidentifier,
CONSTITUENTID uniqueidentifier
);
insert into @REGISTRANTEVENTMAPPINGTABLE
(
MAPPINGID,
EVENTID,
EVENTPRICEID,
CONSTITUENTID
)
select
REGISTRANTMAPPING.MAPPINGID,
coalesce(EVENTPRICE.EVENTID, REGISTRANTMAPPING.EVENTID),
coalesce(EVENTPRICE.ID, REGISTRANTMAPPING.EVENTPRICEID),
REGISTRANTMAPPING.CONSTITUENTID
from
@REGISTRANTMAPPINGTABLE REGISTRANTMAPPING
left join dbo.UFN_REGISTRATIONPACKAGE_GETPACKAGESPRICES_FROMITEMLISTXML(@PACKAGESPRICES) PACKAGEPRICE on REGISTRANTMAPPING.REGISTRATIONPACKAGEID = PACKAGEPRICE.ID
left join dbo.EVENTPRICE on PACKAGEPRICE.EVENTPRICEID = EVENTPRICE.ID;
--MAPPINGID and REGISTRANTID are only used for unknown guests so clear out the meaningless IDs
update @REGISTRANTEVENTMAPPINGTABLE
set
MAPPINGID = null,
REGISTRANTID = null
from
@REGISTRANTEVENTMAPPINGTABLE
where
CONSTITUENTID is not null;
--Add a registrant row for events with registrations
--Filter for existing registrant records, which would cause an error; adding a registrant to an event where they are already registered implies adding registrations
--Existing registrants may already be marked with WILLNOTATTEND as false and that value will not be changed, those with WILLNOTATTEND set to true will be updated in the next statement
insert into dbo.REGISTRANT
(
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select distinct
MAPPING.EVENTID,
@CONSTITUENTID,
0, --Not attended
case
when exists
(
select
MAPPINGFORCONSTITUENT.CONSTITUENTID
from
@REGISTRANTEVENTMAPPINGTABLE MAPPINGFORCONSTITUENT
where
MAPPINGFORCONSTITUENT.EVENTID = MAPPING.EVENTID
and MAPPINGFORCONSTITUENT.CONSTITUENTID = @CONSTITUENTID
)
then
0 --Will attend (the registrant is mapped to a registration for this event)
else
1 --Will not attend
end, --CR261256-120706 by determining the correct WILLNOTATTEND value in this insert we avoid an unneeded row in the audit table
null,
@WAIVEBENEFITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
left join dbo.REGISTRANT on
MAPPING.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID
where
REGISTRANT.ID is null;
--Update the WILLNOTATTEND bit to 0 on events where the registrant is mapped to a
--registration and that registrant is currently marked as will not attend.
update dbo.REGISTRANT
set
WILLNOTATTEND = 0, --Will attend
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.REGISTRANT on
MAPPING.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID
where
MAPPING.CONSTITUENTID = @CONSTITUENTID
and WILLNOTATTEND = 1;
--Add a registrant row for the guests mapped to events (excluding unknown guests)
--Filter for existing registrant records where the guest is already a guest of the same constituent
--Allow errors where the guest is a guest of some other constituent for the same event, a constituent may only be a guest once per event
insert into dbo.REGISTRANT
(
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select distinct
MAPPING.EVENTID,
MAPPING.CONSTITUENTID,
0, --Not attended
0, --Will attend
HOSTREGISTRANT.ID,
@WAIVEBENEFITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.REGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
left join dbo.REGISTRANT EXISTINGGUEST on
MAPPING.EVENTID = EXISTINGGUEST.EVENTID
and MAPPING.CONSTITUENTID = EXISTINGGUEST.CONSTITUENTID
and HOSTREGISTRANT.ID = EXISTINGGUEST.GUESTOFREGISTRANTID
where
MAPPING.CONSTITUENTID is not null
and MAPPING.CONSTITUENTID <> @CONSTITUENTID
and EXISTINGGUEST.ID is null;
--Add a registrant row for the unknown guests mapped to events
--Always adds unknown guests even if the registrant already has existing unknown guests
insert into dbo.REGISTRANT
(
[ID],
[EVENTID],
[CONSTITUENTID],
[ATTENDED],
[WILLNOTATTEND],
[GUESTOFREGISTRANTID],
[BENEFITSWAIVED],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
MAPPING.REGISTRANTID,
MAPPING.EVENTID,
null,
0, --Not attended
0, --Will attend
HOSTREGISTRANT.ID,
@WAIVEBENEFITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE MAPPING
inner join dbo.REGISTRANT HOSTREGISTRANT on
MAPPING.EVENTID = HOSTREGISTRANT.EVENTID
and @CONSTITUENTID = HOSTREGISTRANT.CONSTITUENTID
where
MAPPING.CONSTITUENTID is null;
--Add the packages to the registrant record
insert into dbo.REGISTRANTPACKAGE
(
[CONSTITUENTID],
[REGISTRATIONPACKAGEID],
[GUESTOFCONSTITUENTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
REGISTRATIONMAPPING.CONSTITUENTID,
REGISTRATIONMAPPING.REGISTRATIONPACKAGEID,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRATIONMAPPING
where
REGISTRATIONMAPPING.REGISTRATIONPACKAGEID <> '00000000-0000-0000-0000-000000000000'
and REGISTRATIONMAPPING.CONSTITUENTID = @CONSTITUENTID;
--Add the packages to the guest and unknown guest records
insert into dbo.REGISTRANTPACKAGE
(
[CONSTITUENTID],
[REGISTRATIONPACKAGEID],
[GUESTOFCONSTITUENTID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
REGISTRATIONMAPPING.CONSTITUENTID,
REGISTRATIONMAPPING.REGISTRATIONPACKAGEID,
@CONSTITUENTID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTMAPPINGTABLE REGISTRATIONMAPPING
where
REGISTRATIONMAPPING.REGISTRATIONPACKAGEID <> '00000000-0000-0000-0000-000000000000'
and
(
REGISTRATIONMAPPING.CONSTITUENTID is null
or
REGISTRATIONMAPPING.CONSTITUENTID <> @CONSTITUENTID
);
/*
--Iterate over preferences on the registrant and guests
declare PREFERENCECURSOR cursor local fast_forward for
select
MAPPING.CONSTITUENTID,
MAPPING.MAPPINGID,
MAPPING.PREFERENCES
from
@REGISTRANTMAPPINGTABLE MAPPING;
declare @PREFERENCESCONSTITUENTID uniqueidentifier;
declare @PREFERENCESMAPPINGID uniqueidentifier;
declare @PREFERENCES xml;
open PREFERENCECURSOR;
fetch next from PREFERENCECURSOR into @PREFERENCESCONSTITUENTID, @PREFERENCESMAPPINGID, @PREFERENCES;
while @@FETCH_STATUS = 0
begin
--Save the current preferences
insert into dbo.REGISTRANTPREFERENCE
(
REGISTRANTID,
EVENTPREFERENCEID,
QUANTITY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
coalesce(REGISTRANT.ID, UNKNOWNGUESTMAPPING.REGISTRANTID),
SELECTEDPREFERENCE.EVENTPREFERENCEID,
SELECTEDPREFERENCE.QUANTITY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.UFN_REGISTRANT_GETPREFERENCES_FROMITEMLISTXML(@PREFERENCES) SELECTEDPREFERENCE
inner join dbo.EVENTPREFERENCE on SELECTEDPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
inner join dbo.EVENTPREFERENCEGROUP on EVENTPREFERENCE.EVENTPREFERENCEGROUPID = EVENTPREFERENCEGROUP.ID
left join dbo.REGISTRANT on
EVENTPREFERENCEGROUP.EVENTID = REGISTRANT.EVENTID
and @PREFERENCESCONSTITUENTID = REGISTRANT.CONSTITUENTID
left join @REGISTRANTEVENTMAPPINGTABLE UNKNOWNGUESTMAPPING on
EVENTPREFERENCEGROUP.EVENTID = UNKNOWNGUESTMAPPING.EVENTID
and @PREFERENCESMAPPINGID = UNKNOWNGUESTMAPPING.MAPPINGID;
fetch next from PREFERENCECURSOR into @PREFERENCESCONSTITUENTID, @PREFERENCESMAPPINGID, @PREFERENCES;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close PREFERENCECURSOR;
deallocate PREFERENCECURSOR;
set @PREFERENCESCONSTITUENTID = null;
set @PREFERENCESMAPPINGID = null;
set @PREFERENCES = null;
*/
--Add the registrations
insert into dbo.REGISTRANTREGISTRATION
(
ID,
REGISTRANTID,
EVENTPRICEID,
QUANTITY,
AMOUNT,
RECEIPTAMOUNT,
DATEPURCHASED,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
SELECTEDREGISTRATION.REGISTRANTREGISTRATIONID,
REGISTRANT.ID,
SELECTEDREGISTRATION.EVENTPRICEID,
SELECTEDREGISTRATION.QUANTITY,
SELECTEDREGISTRATION.AMOUNT,
SELECTEDREGISTRATION.RECEIPTAMOUNT,
SELECTEDREGISTRATION.DATEPURCHASED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRATIONTOADDTABLE SELECTEDREGISTRATION
inner join dbo.REGISTRANT on
SELECTEDREGISTRATION.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID;
--Add the default benefits for the added registrations, give benefits to
--the registrant/guests when they are mapped and when the registration count is 1.
insert into dbo.REGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
coalesce(REGISTRANTEVENTMAPPING.REGISTRANTID, REGISTRANT.ID),
EVENTPRICEBENEFIT.BENEFITID,
EVENTPRICEBENEFIT.UNITVALUE,
EVENTPRICEBENEFIT.QUANTITY,
(EVENTPRICEBENEFIT.UNITVALUE * EVENTPRICEBENEFIT.QUANTITY),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRANTEVENTMAPPINGTABLE REGISTRANTEVENTMAPPING
inner join dbo.EVENTPRICEBENEFIT on REGISTRANTEVENTMAPPING.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.EVENTPRICE on REGISTRANTEVENTMAPPING.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
left join dbo.REGISTRANT on
REGISTRANTEVENTMAPPING.EVENTID = REGISTRANT.EVENTID
and REGISTRANTEVENTMAPPING.CONSTITUENTID = REGISTRANT.CONSTITUENTID
where
EVENTPRICE.REGISTRATIONCOUNT = 1;
--Benefits from registration options with high registration counts always
--go to the registrant so that they don't have to be divided among guests
insert into dbo.REGISTRANTBENEFIT
(
REGISTRANTID,
BENEFITID,
UNITVALUE,
QUANTITY,
TOTALVALUE,
DETAILS,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
REGISTRANT.ID,
EVENTPRICEBENEFIT.BENEFITID,
(SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE),
(SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY),
((SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.UNITVALUE) * (SELECTEDREGISTRATION.QUANTITY * EVENTPRICEBENEFIT.QUANTITY)),
EVENTPRICEBENEFIT.DETAILS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@REGISTRATIONTOADDTABLE SELECTEDREGISTRATION
inner join dbo.EVENTPRICEBENEFIT on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICEBENEFIT.EVENTPRICEID
left join dbo.EVENTPRICE on SELECTEDREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
left join dbo.REGISTRANT on
SELECTEDREGISTRATION.EVENTID = REGISTRANT.EVENTID
and @CONSTITUENTID = REGISTRANT.CONSTITUENTID
where
EVENTPRICE.REGISTRATIONCOUNT > 1;
end
--Multiple registrant rows can be created so always select and return the registrant ID for the current event
--If registrant did not register for main event return the registrant ID for a sub event.
select
@ID = ID
from
dbo.REGISTRANT
where
CONSTITUENTID = @CONSTITUENTID and
EVENTID = @EVENTID;
if @ID is null
select top(1)
@ID = REGISTRANT.ID
from
dbo.REGISTRANT
inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
where
REGISTRANT.CONSTITUENTID = @CONSTITUENTID and
EVENT.MAINEVENTID = @EVENTID;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;