USP_DATAFORMTEMPLATE_ADD_BATCHEVENTBATCHCOMMIT
The save procedure used by the add dataform template "Event Batch Row Commit Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @VALIDATEONLY | bit | IN | Validate only |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @STARTDATE | date | IN | Start date |
| @STARTTIME | UDT_HOURMINUTE | IN | Start time |
| @ENDDATE | date | IN | End date |
| @ENDTIME | UDT_HOURMINUTE | IN | End time |
| @CAPACITY | int | IN | Capacity |
| @EVENTLOCATIONID | uniqueidentifier | IN | Location |
| @EVENTLOCATIONCONTACTID | uniqueidentifier | IN | Contact |
| @APPEALID | uniqueidentifier | IN | Appeal |
| @EVENTCATEGORYCODEID | uniqueidentifier | IN | Category |
| @PRICES | xml | IN | Registration options |
| @TASKS | xml | IN | Tasks |
| @EXPENSES | xml | IN | Expenses |
| @COORDINATORS | xml | IN | Coordinators |
| @SITES | xml | IN | Sites |
| @EVENTLOCATIONROOMID | uniqueidentifier | IN | Room/Unit |
| @BATCHROWID | uniqueidentifier | IN | |
| @CUSTOMIDENTIFIER | nvarchar(100) | IN | Lookup ID |
| @HIDEFROMCALENDAR | bit | IN | Do not show event on calendar |
| @BASECURRENCYID | uniqueidentifier | IN | Base currency |
| @DESIGNATIONSONFEES | bit | IN | Event allows designations on fees |
| @DESIGNATIONS | xml | IN | Designations |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHEVENTBATCHCOMMIT
(
@ID uniqueidentifier = null output,
@VALIDATEONLY bit = 0,
@CHANGEAGENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@NAME nvarchar(100) = '',
@DESCRIPTION nvarchar(255) = '',
@STARTDATE date = null,
@STARTTIME dbo.UDT_HOURMINUTE = null,
@ENDDATE date = null,
@ENDTIME dbo.UDT_HOURMINUTE = null,
@CAPACITY int = 0,
@EVENTLOCATIONID uniqueidentifier = null,
@EVENTLOCATIONCONTACTID uniqueidentifier = null,
@APPEALID uniqueidentifier = null,
@EVENTCATEGORYCODEID uniqueidentifier = null,
@PRICES xml = null,
@TASKS xml = null,
@EXPENSES xml = null,
@COORDINATORS xml = null,
@SITES xml = null,
@EVENTLOCATIONROOMID uniqueidentifier = null,
@BATCHROWID uniqueidentifier = null,
@CUSTOMIDENTIFIER nvarchar(100) = '',
@HIDEFROMCALENDAR bit = 0,
@BASECURRENCYID uniqueidentifier = null,
@DESIGNATIONSONFEES bit = 0,
@DESIGNATIONS xml = null
) as
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
if @BASECURRENCYID is null
select @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
declare @CURRENCYEXCHANGERATE uniqueidentifier = null
if @BASECURRENCYID <> dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
begin
select @CURRENCYEXCHANGERATE = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), getdate(), 1, null)
end
begin try
if @SITES is null
begin
if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1
begin
raiserror('ERR_EVENTSITE_SITEID', 13, 1);
return;
end
end
exec dbo.USP_EVENTSITE_VALIDATESITES @SITES;
if @VALIDATEONLY = 1
begin
-- Main event checks
if (@STARTDATE is not null) and (@ENDDATE is not null)
if @STARTDATE > @ENDDATE
raiserror('CK_EVENT_VALIDDATERANGE', 13, 1);
if (@STARTDATE is not null) and (@ENDDATE is not null) and (@STARTTIME is not null) and (@ENDTIME is not null)
if dbo.UFN_EVENT_VALIDDATESANDTIMES(@STARTDATE, @ENDDATE, @STARTTIME, @ENDTIME) <> 1
raiserror('CK_EVENT_VALIDTIMERANGE', 13, 1);
declare @INVALIDCOUNT int;
-- Event price checks
select
@INVALIDCOUNT = count(*)
from
@PRICES.nodes('/PRICES/ITEM') T(c)
where
T.c.value('(AMOUNT)[1]','money') < 0;
if @INVALIDCOUNT > 0
raiserror('CK_EVENTPRICE_VALIDAMOUNT', 13, 1);
select
@INVALIDCOUNT = count(*)
from
@PRICES.nodes('/PRICES/ITEM') T(c)
where
T.c.value('(COST)[1]','money') < 0;
if @INVALIDCOUNT > 0
raiserror('CK_EVENTPRICE_VALIDCOST', 13, 1);
select
@INVALIDCOUNT = count(*)
from
@PRICES.nodes('/PRICES/ITEM') T(c)
where
T.c.value('(REGISTRATIONCOUNT)[1]','int') < 0;
if @INVALIDCOUNT > 0
raiserror('CK_EVENTPRICE_VALIDREGISTRATIONCOUNT', 13, 1);
-- Expense checks
select
@INVALIDCOUNT = count(*)
from
@EXPENSES.nodes('/EXPENSES/ITEM') T(c)
where
T.c.value('(BUDGETEDAMOUNT)[1]','money') < 0;
if @INVALIDCOUNT > 0
raiserror('CK_EVENTEXPENSE_VALIDBUDGETEDAMOUNT', 13, 1);
select
@INVALIDCOUNT = count(*)
from
@EXPENSES.nodes('/EXPENSES/ITEM') T(c)
where
T.c.value('(ACTUALAMOUNT)[1]','money') < 0;
if @INVALIDCOUNT > 0
raiserror('CK_EVENTEXPENSE_VALIDACTUALAMOUNT', 13, 1);
select
@INVALIDCOUNT = count(*)
from
@EXPENSES.nodes('/EXPENSES/ITEM') T(c)
where
T.c.value('(AMOUNTPAID)[1]','money') < 0;
if @INVALIDCOUNT > 0
raiserror('CK_EVENTEXPENSE_VALIDAMOUNTPAID', 13, 1);
declare @BATCHID uniqueidentifier
select @BATCHID = BATCHID
from dbo.BATCHEVENT
where ID = @BATCHROWID
select @BATCHID, @BATCHROWID, @CUSTOMIDENTIFIER
if exists
(select 1
from dbo.EVENT
where @CUSTOMIDENTIFIER <> '' and EVENT.CUSTOMIDENTIFIER = @CUSTOMIDENTIFIER
) begin
raiserror('UC_EVENT_LOOKUPID : Lookup ID must be unique.', 13, 1);
return 1;
end
else if
(select count(BATCHEVENT.ID)
from dbo.BATCHEVENT
where
BATCHID = @BATCHID and
coalesce(@CUSTOMIDENTIFIER, '') <> '' and
BATCHEVENT.CUSTOMID = @CUSTOMIDENTIFIER
) > 1 begin
raiserror('BBERR_EVENT_LOOKUPID_MULTIPLE : The Lookup ID has multiple occurrences in this batch.', 13, 1);
return 1;
end
-- Designation validation
if @DESIGNATIONSONFEES = 1
begin
select
@INVALIDCOUNT = count(*)
from
@DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') T(c)
where
T.c.value('(DEFAULT)[1]','bit') = 1;
if @INVALIDCOUNT = 0
raiserror('EVENTDESIGNATION_DEFAULTREQUIRED', 13, 1);
if @INVALIDCOUNT > 1
raiserror('EVENTDESIGNATIONS_ONLYONEDEFAULT', 13, 1);
end
end
else
begin
set @STARTTIME = coalesce(@STARTTIME, '');
set @ENDTIME = coalesce(@ENDTIME, '');
--Bug 72688 - AdamBu 1/5/10 - Use the event add SP to create any sites, to avoid issues when they are required.
-- Also, don't call the SP defined on the Event Add dataform. I've created a new SP to call instead.
--exec dbo.USP_DATAFORMTEMPLATE_ADD_EVENT @ID = @ID, @CURRENTAPPUSERID = @CURRENTAPPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @NAME = @NAME, @DESCRIPTION = @DESCRIPTION, @STARTDATE = @STARTDATE, @STARTTIME = @STARTTIME, @ENDDATE = @ENDDATE, @ENDTIME = @ENDTIME, @CAPACITY = @CAPACITY, @EVENTLOCATIONID = @EVENTLOCATIONID, @EVENTLOCATIONCONTACTID = @EVENTLOCATIONCONTACTID, @APPEALID = @APPEALID, @EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID;
--exec dbo.USP_EVENT_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_EVENT_ADD @ID = @ID,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@CHANGEAGENTID = @CHANGEAGENTID,
@NAME = @NAME,
@DESCRIPTION = @DESCRIPTION,
@STARTDATE = @STARTDATE,
@STARTTIME = @STARTTIME,
@ENDDATE = @ENDDATE,
@ENDTIME = @ENDTIME,
@CAPACITY = @CAPACITY,
@EVENTLOCATIONID = @EVENTLOCATIONID,
@EVENTLOCATIONCONTACTID = @EVENTLOCATIONCONTACTID,
@MAINEVENTID = null,
@COPYFROMEVENTID = null,
@COPYTASKS = null,
@COPYEXPENSES = null,
@COPYPRICES = null,
@COPYINVITATIONS = null,
@COPYINVITEES = null,
@COPYATTRIBUTES = null,
@ATTRIBUTEDEFINED = null,
@SITES = @SITES,
@COPYPREFERENCES = null,
@EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID,
@COPYTEAMSTRUCTURE = null,
@COPYTEAMFUNDRAISERS = null,
@APPEALID = @APPEALID,
@ADDQUICKCOMPARE = null,
@COPYLODGINGOPTIONS = null,
@EVENTLOCATIONROOMID = @EVENTLOCATIONROOMID,
@COPYJOBOCCURRENCES = 0,
@ISAUCTION = 0,
@HIDEFROMCALENDAR = @HIDEFROMCALENDAR,
@BASECURRENCYID = @BASECURRENCYID,
@DESIGNATIONSONFEES=@DESIGNATIONSONFEES,
@COPYDESIGNATIONS = 0;
insert into dbo.EVENTPRICE (
ID,
EVENTID,
EVENTREGISTRATIONTYPEID,
NAME,
REGISTRATIONCOUNT,
AMOUNT,
COST,
RECEIPTAMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ORGANIZATIONAMOUNT,
ORGANIZATIONCOST,
ORGANIZATIONEXCHANGERATEID
)
select
newid(),
@ID,
T.c.value('(EVENTREGISTRATIONTYPEID)[1]','uniqueidentifier'),
T.c.value('(NAME)[1]','nvarchar(100)'),
T.c.value('(REGISTRATIONCOUNT)[1]','int'),
T.c.value('(AMOUNT)[1]','money'),
T.c.value('(COST)[1]','money'),
T.c.value('(AMOUNT)[1]','money') - T.c.value('(COST)[1]','money'),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
case @CURRENCYEXCHANGERATE when null then T.c.value('(AMOUNT)[1]','money') else dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNT)[1]','money'), @CURRENCYEXCHANGERATE) end,
case @CURRENCYEXCHANGERATE when null then T.c.value('(COST)[1]','money') else dbo.UFN_CURRENCY_CONVERT(T.c.value('(COST)[1]','money'), @CURRENCYEXCHANGERATE) end,
@CURRENCYEXCHANGERATE
from
@PRICES.nodes('/PRICES/ITEM') T(c)
insert into dbo.EVENTTASK (
ID,
EVENTID,
NAME,
OWNERID,
COMPLETEBYDATE,
COMMENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
T.c.value('(NAME)[1]','nvarchar(100)'),
T.c.value('(OWNERID)[1]','uniqueidentifier'),
T.c.value('(COMPLETEBYDATE)[1]','datetime'),
T.c.value('(COMMENT)[1]','nvarchar(250)'),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@TASKS.nodes('/TASKS/ITEM') T(c)
insert into dbo.EVENTEXPENSE (
ID,
EVENTID,
EVENTEXPENSETYPECODEID,
VENDORID,
BUDGETEDAMOUNT,
ACTUALAMOUNT,
DATEDUE,
AMOUNTPAID,
DATEPAID,
COMMENT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONBUDGETEDAMOUNT,
ORGANIZATIONACTUALAMOUNT,
ORGANIZATIONAMOUNTPAID
)
select
newid(),
@ID,
T.c.value('(EVENTEXPENSETYPECODEID)[1]','uniqueidentifier'),
T.c.value('(VENDORID)[1]','uniqueidentifier'),
T.c.value('(BUDGETEDAMOUNT)[1]','money'),
T.c.value('(ACTUALAMOUNT)[1]','money'),
T.c.value('(DATEDUE)[1]','datetime'),
T.c.value('(AMOUNTPAID)[1]','money'),
T.c.value('(DATEPAID)[1]','datetime'),
T.c.value('(COMMENT)[1]','nvarchar(250)'),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@BASECURRENCYID,
@CURRENCYEXCHANGERATE,
case @CURRENCYEXCHANGERATE when null then T.c.value('(BUDGETEDAMOUNT)[1]','money') else dbo.UFN_CURRENCY_CONVERT(T.c.value('(BUDGETEDAMOUNT)[1]','money'), @CURRENCYEXCHANGERATE) end,
case @CURRENCYEXCHANGERATE when null then T.c.value('(ACTUALAMOUNT)[1]','money') else dbo.UFN_CURRENCY_CONVERT(T.c.value('(ACTUALAMOUNT)[1]','money'), @CURRENCYEXCHANGERATE) end,
case @CURRENCYEXCHANGERATE when null then T.c.value('(AMOUNTPAID)[1]','money') else dbo.UFN_CURRENCY_CONVERT(T.c.value('(AMOUNTPAID)[1]','money'), @CURRENCYEXCHANGERATE) end
from
@EXPENSES.nodes('/EXPENSES/ITEM') T(c)
insert into dbo.EVENTCOORDINATOR (
ID,
EVENTID,
CONSTITUENTID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier'),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@COORDINATORS.nodes('/COORDINATORS/ITEM') T(c)
if @DESIGNATIONSONFEES = 1
begin
declare @DUPLICATECOUNT int;
select
@DUPLICATECOUNT = count(T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'))
from @DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') T(c)
where T.c.value('(DEFAULT)[1]','bit') = 1
if @DUPLICATECOUNT = 0
raiserror('EVENTDESIGNATION_DEFAULTREQUIRED : At least one default designation is required.',13,1);
if @DUPLICATECOUNT > 1
raiserror('EVENTDESIGNATIONS_ONLYONEDEFAULT : Events can have only one default designation.',13,1);
insert into dbo.EVENTDESIGNATION (
ID,
EVENTID,
DESIGNATIONID,
[DEFAULT],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
@ID,
T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
T.c.value('(DEFAULT)[1]','bit'),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') T(c)
end
if @CUSTOMIDENTIFIER <> ''
begin
update dbo.EVENT
set CUSTOMIDENTIFIER = @CUSTOMIDENTIFIER
where ID = @ID;
end
end
end try
begin catch
exec.dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;