USP_FAFEVENT_GETEVENTGOAL_ADDFROMXML
Used to add a set of records defined by UFN_FAFEVENT_GETEVENTGOAL from the given xml string.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @EVENTID | uniqueidentifier | IN | |
| @XML | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/1/2011 9:06:15 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.9.1001.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_FAFEVENT_GETEVENTGOAL_ADDFROMXML
(
@EVENTID uniqueidentifier,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CHANGEDATE is null
set @CHANGEDATE = getdate()
-- build a temporary table containing the values from the XML
declare @TempTbl table (
[COMMUNICATIONCOMPANY] int,
[COMMUNICATIONDAILY] int,
[COMMUNICATIONHOUSEHOLD] int,
[COMMUNICATIONINDIVIDUAL] int,
[COMMUNICATIONMONTHLY] int,
[COMMUNICATIONSPONSOR] int,
[COMMUNICATIONTEAM] int,
[COMMUNICATIONTOTAL] int,
[COMMUNICATIONWEEKLY] int,
[COPYBATCHGUID] uniqueidentifier,
[COPYSOURCEID] uniqueidentifier,
[ID] varchar(36),
[ISACTIVE] bit,
[MINIMIMUMDONORGOAL] int,
[MINIMIMUMFUNDRAISINGGOAL] money,
[MINIMIMUMNEWDONORGOAL] int,
[MINIMIMUMNEWMEMBERGOAL] int,
[MINIMIMUMPARTICIPANTGOAL] int,
[MINIMIMUMTEAMMEMBERGOAL] int,
[MINIMUMFUNDRAISEGOALPERMEMBER] money,
[RECRUITMENTCOMPANY] int,
[RECRUITMENTCOMPANYRETENTION] decimal(6, 5),
[RECRUITMENTDAILY] int,
[RECRUITMENTDONOR] int,
[RECRUITMENTDONORRETENTION] decimal(6, 5),
[RECRUITMENTFITNESSCOACHES] int,
[RECRUITMENTFITNESSCOACHRETENTION] decimal(6, 5),
[RECRUITMENTFUNDRAISINGCOACHES] int,
[RECRUITMENTFUNDRAISINGCOACHRETENTION] decimal(6, 5),
[RECRUITMENTHOUSEHOLD] int,
[RECRUITMENTHOUSEHOLDRETENTION] decimal(6, 5),
[RECRUITMENTINDIVIDUAL] int,
[RECRUITMENTINDIVIDUALRETENTION] decimal(6, 5),
[RECRUITMENTMONTHLY] int,
[RECRUITMENTSPONSOR] int,
[RECRUITMENTSPONSORRETENTION] decimal(6, 5),
[RECRUITMENTTEAM] int,
[RECRUITMENTTEAMRETENTION] decimal(6, 5),
[RECRUITMENTTOTAL] int,
[RECRUITMENTWEEKLY] int,
[REVENUECOMPANY] money,
[REVENUECOMPANYRETENTION] decimal(6, 5),
[REVENUEDAILY] money,
[REVENUEHOUSEHOLD] money,
[REVENUEHOUSEHOLDRETENTION] decimal(6, 5),
[REVENUEINDIVIDUAL] money,
[REVENUEINDIVIDUALRETENTION] decimal(6, 5),
[REVENUEMONTHLY] money,
[REVENUEREGISTRATION] money,
[REVENUEREGISTRATIONRETENTION] decimal(6, 5),
[REVENUERETENTION] decimal(6, 5),
[REVENUESPONSOR] money,
[REVENUESPONSORRETENTION] decimal(6, 5),
[REVENUETEAM] money,
[REVENUETEAMRETENTION] decimal(6, 5),
[REVENUETOTAL] money,
[REVENUEWEEKLY] money)
insert into @TempTbl select
[COMMUNICATIONCOMPANY],
[COMMUNICATIONDAILY],
[COMMUNICATIONHOUSEHOLD],
[COMMUNICATIONINDIVIDUAL],
[COMMUNICATIONMONTHLY],
[COMMUNICATIONSPONSOR],
[COMMUNICATIONTEAM],
[COMMUNICATIONTOTAL],
[COMMUNICATIONWEEKLY],
[COPYBATCHGUID],
[COPYSOURCEID],
[ID],
[ISACTIVE],
[MINIMIMUMDONORGOAL],
[MINIMIMUMFUNDRAISINGGOAL],
[MINIMIMUMNEWDONORGOAL],
[MINIMIMUMNEWMEMBERGOAL],
[MINIMIMUMPARTICIPANTGOAL],
[MINIMIMUMTEAMMEMBERGOAL],
[MINIMUMFUNDRAISEGOALPERMEMBER],
[RECRUITMENTCOMPANY],
[RECRUITMENTCOMPANYRETENTION],
[RECRUITMENTDAILY],
[RECRUITMENTDONOR],
[RECRUITMENTDONORRETENTION],
[RECRUITMENTFITNESSCOACHES],
[RECRUITMENTFITNESSCOACHRETENTION],
[RECRUITMENTFUNDRAISINGCOACHES],
[RECRUITMENTFUNDRAISINGCOACHRETENTION],
[RECRUITMENTHOUSEHOLD],
[RECRUITMENTHOUSEHOLDRETENTION],
[RECRUITMENTINDIVIDUAL],
[RECRUITMENTINDIVIDUALRETENTION],
[RECRUITMENTMONTHLY],
[RECRUITMENTSPONSOR],
[RECRUITMENTSPONSORRETENTION],
[RECRUITMENTTEAM],
[RECRUITMENTTEAMRETENTION],
[RECRUITMENTTOTAL],
[RECRUITMENTWEEKLY],
[REVENUECOMPANY],
[REVENUECOMPANYRETENTION],
[REVENUEDAILY],
[REVENUEHOUSEHOLD],
[REVENUEHOUSEHOLDRETENTION],
[REVENUEINDIVIDUAL],
[REVENUEINDIVIDUALRETENTION],
[REVENUEMONTHLY],
[REVENUEREGISTRATION],
[REVENUEREGISTRATIONRETENTION],
[REVENUERETENTION],
[REVENUESPONSOR],
[REVENUESPONSORRETENTION],
[REVENUETEAM],
[REVENUETEAMRETENTION],
[REVENUETOTAL],
[REVENUEWEEKLY]
from dbo.UFN_FAFEVENT_GETEVENTGOAL_FROMITEMLISTXML(@XML)
update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
if @@Error <> 0
return 1;
-- insert new items
insert into [EVENTGOAL]
([EVENTID],
[COMMUNICATIONCOMPANY],
[COMMUNICATIONDAILY],
[COMMUNICATIONHOUSEHOLD],
[COMMUNICATIONINDIVIDUAL],
[COMMUNICATIONMONTHLY],
[COMMUNICATIONSPONSOR],
[COMMUNICATIONTEAM],
[COMMUNICATIONTOTAL],
[COMMUNICATIONWEEKLY],
[COPYBATCHGUID],
[COPYSOURCEID],
[ID],
[ISACTIVE],
[MINIMIMUMDONORGOAL],
[MINIMIMUMFUNDRAISINGGOAL],
[MINIMIMUMNEWDONORGOAL],
[MINIMIMUMNEWMEMBERGOAL],
[MINIMIMUMPARTICIPANTGOAL],
[MINIMIMUMTEAMMEMBERGOAL],
[MINIMUMFUNDRAISEGOALPERMEMBER],
[RECRUITMENTCOMPANY],
[RECRUITMENTCOMPANYRETENTION],
[RECRUITMENTDAILY],
[RECRUITMENTDONOR],
[RECRUITMENTDONORRETENTION],
[RECRUITMENTFITNESSCOACHES],
[RECRUITMENTFITNESSCOACHRETENTION],
[RECRUITMENTFUNDRAISINGCOACHES],
[RECRUITMENTFUNDRAISINGCOACHRETENTION],
[RECRUITMENTHOUSEHOLD],
[RECRUITMENTHOUSEHOLDRETENTION],
[RECRUITMENTINDIVIDUAL],
[RECRUITMENTINDIVIDUALRETENTION],
[RECRUITMENTMONTHLY],
[RECRUITMENTSPONSOR],
[RECRUITMENTSPONSORRETENTION],
[RECRUITMENTTEAM],
[RECRUITMENTTEAMRETENTION],
[RECRUITMENTTOTAL],
[RECRUITMENTWEEKLY],
[REVENUECOMPANY],
[REVENUECOMPANYRETENTION],
[REVENUEDAILY],
[REVENUEHOUSEHOLD],
[REVENUEHOUSEHOLDRETENTION],
[REVENUEINDIVIDUAL],
[REVENUEINDIVIDUALRETENTION],
[REVENUEMONTHLY],
[REVENUEREGISTRATION],
[REVENUEREGISTRATIONRETENTION],
[REVENUERETENTION],
[REVENUESPONSOR],
[REVENUESPONSORRETENTION],
[REVENUETEAM],
[REVENUETEAMRETENTION],
[REVENUETOTAL],
[REVENUEWEEKLY],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select @EVENTID,
[COMMUNICATIONCOMPANY],
[COMMUNICATIONDAILY],
[COMMUNICATIONHOUSEHOLD],
[COMMUNICATIONINDIVIDUAL],
[COMMUNICATIONMONTHLY],
[COMMUNICATIONSPONSOR],
[COMMUNICATIONTEAM],
[COMMUNICATIONTOTAL],
[COMMUNICATIONWEEKLY],
[COPYBATCHGUID],
[COPYSOURCEID],
[ID],
[ISACTIVE],
[MINIMIMUMDONORGOAL],
[MINIMIMUMFUNDRAISINGGOAL],
[MINIMIMUMNEWDONORGOAL],
[MINIMIMUMNEWMEMBERGOAL],
[MINIMIMUMPARTICIPANTGOAL],
[MINIMIMUMTEAMMEMBERGOAL],
[MINIMUMFUNDRAISEGOALPERMEMBER],
[RECRUITMENTCOMPANY],
[RECRUITMENTCOMPANYRETENTION],
[RECRUITMENTDAILY],
[RECRUITMENTDONOR],
[RECRUITMENTDONORRETENTION],
[RECRUITMENTFITNESSCOACHES],
[RECRUITMENTFITNESSCOACHRETENTION],
[RECRUITMENTFUNDRAISINGCOACHES],
[RECRUITMENTFUNDRAISINGCOACHRETENTION],
[RECRUITMENTHOUSEHOLD],
[RECRUITMENTHOUSEHOLDRETENTION],
[RECRUITMENTINDIVIDUAL],
[RECRUITMENTINDIVIDUALRETENTION],
[RECRUITMENTMONTHLY],
[RECRUITMENTSPONSOR],
[RECRUITMENTSPONSORRETENTION],
[RECRUITMENTTEAM],
[RECRUITMENTTEAMRETENTION],
[RECRUITMENTTOTAL],
[RECRUITMENTWEEKLY],
[REVENUECOMPANY],
[REVENUECOMPANYRETENTION],
[REVENUEDAILY],
[REVENUEHOUSEHOLD],
[REVENUEHOUSEHOLDRETENTION],
[REVENUEINDIVIDUAL],
[REVENUEINDIVIDUALRETENTION],
[REVENUEMONTHLY],
[REVENUEREGISTRATION],
[REVENUEREGISTRATIONRETENTION],
[REVENUERETENTION],
[REVENUESPONSOR],
[REVENUESPONSORRETENTION],
[REVENUETEAM],
[REVENUETEAMRETENTION],
[REVENUETOTAL],
[REVENUEWEEKLY],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from @TempTbl as [temp]
if @@Error <> 0
return 2;
return 0;