USP_FAFEVENT_GETEVENTGOAL_UPDATEFROMXML
Used to update 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_UPDATEFROMXML
(
@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;
declare @contextCache varbinary(128);
declare @e int;
-- cache current context information
set @contextCache = CONTEXT_INFO();
-- set CONTEXT_INFO to @CHANGEAGENTID
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- delete any items that no longer exist in the XML table
delete from dbo.[EVENTGOAL] where [EVENTGOAL].ID in
(select ID from dbo.UFN_FAFEVENT_GETEVENTGOAL
(
@EVENTID
)
EXCEPT select ID from @TempTbl)
select @e=@@error;
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
if @e <> 0
return 2;
-- update the items that exist in the XML table and the db
update [EVENTGOAL]
set [EVENTGOAL].[COMMUNICATIONCOMPANY]=temp.[COMMUNICATIONCOMPANY],
[EVENTGOAL].[COMMUNICATIONDAILY]=temp.[COMMUNICATIONDAILY],
[EVENTGOAL].[COMMUNICATIONHOUSEHOLD]=temp.[COMMUNICATIONHOUSEHOLD],
[EVENTGOAL].[COMMUNICATIONINDIVIDUAL]=temp.[COMMUNICATIONINDIVIDUAL],
[EVENTGOAL].[COMMUNICATIONMONTHLY]=temp.[COMMUNICATIONMONTHLY],
[EVENTGOAL].[COMMUNICATIONSPONSOR]=temp.[COMMUNICATIONSPONSOR],
[EVENTGOAL].[COMMUNICATIONTEAM]=temp.[COMMUNICATIONTEAM],
[EVENTGOAL].[COMMUNICATIONTOTAL]=temp.[COMMUNICATIONTOTAL],
[EVENTGOAL].[COMMUNICATIONWEEKLY]=temp.[COMMUNICATIONWEEKLY],
[EVENTGOAL].[COPYBATCHGUID]=temp.[COPYBATCHGUID],
[EVENTGOAL].[COPYSOURCEID]=temp.[COPYSOURCEID],
[EVENTGOAL].[ID]=temp.[ID],
[EVENTGOAL].[ISACTIVE]=temp.[ISACTIVE],
[EVENTGOAL].[MINIMIMUMDONORGOAL]=temp.[MINIMIMUMDONORGOAL],
[EVENTGOAL].[MINIMIMUMFUNDRAISINGGOAL]=temp.[MINIMIMUMFUNDRAISINGGOAL],
[EVENTGOAL].[MINIMIMUMNEWDONORGOAL]=temp.[MINIMIMUMNEWDONORGOAL],
[EVENTGOAL].[MINIMIMUMNEWMEMBERGOAL]=temp.[MINIMIMUMNEWMEMBERGOAL],
[EVENTGOAL].[MINIMIMUMPARTICIPANTGOAL]=temp.[MINIMIMUMPARTICIPANTGOAL],
[EVENTGOAL].[MINIMIMUMTEAMMEMBERGOAL]=temp.[MINIMIMUMTEAMMEMBERGOAL],
[EVENTGOAL].[MINIMUMFUNDRAISEGOALPERMEMBER]=temp.[MINIMUMFUNDRAISEGOALPERMEMBER],
[EVENTGOAL].[RECRUITMENTCOMPANY]=temp.[RECRUITMENTCOMPANY],
[EVENTGOAL].[RECRUITMENTCOMPANYRETENTION]=temp.[RECRUITMENTCOMPANYRETENTION],
[EVENTGOAL].[RECRUITMENTDAILY]=temp.[RECRUITMENTDAILY],
[EVENTGOAL].[RECRUITMENTDONOR]=temp.[RECRUITMENTDONOR],
[EVENTGOAL].[RECRUITMENTDONORRETENTION]=temp.[RECRUITMENTDONORRETENTION],
[EVENTGOAL].[RECRUITMENTFITNESSCOACHES]=temp.[RECRUITMENTFITNESSCOACHES],
[EVENTGOAL].[RECRUITMENTFITNESSCOACHRETENTION]=temp.[RECRUITMENTFITNESSCOACHRETENTION],
[EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHES]=temp.[RECRUITMENTFUNDRAISINGCOACHES],
[EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHRETENTION]=temp.[RECRUITMENTFUNDRAISINGCOACHRETENTION],
[EVENTGOAL].[RECRUITMENTHOUSEHOLD]=temp.[RECRUITMENTHOUSEHOLD],
[EVENTGOAL].[RECRUITMENTHOUSEHOLDRETENTION]=temp.[RECRUITMENTHOUSEHOLDRETENTION],
[EVENTGOAL].[RECRUITMENTINDIVIDUAL]=temp.[RECRUITMENTINDIVIDUAL],
[EVENTGOAL].[RECRUITMENTINDIVIDUALRETENTION]=temp.[RECRUITMENTINDIVIDUALRETENTION],
[EVENTGOAL].[RECRUITMENTMONTHLY]=temp.[RECRUITMENTMONTHLY],
[EVENTGOAL].[RECRUITMENTSPONSOR]=temp.[RECRUITMENTSPONSOR],
[EVENTGOAL].[RECRUITMENTSPONSORRETENTION]=temp.[RECRUITMENTSPONSORRETENTION],
[EVENTGOAL].[RECRUITMENTTEAM]=temp.[RECRUITMENTTEAM],
[EVENTGOAL].[RECRUITMENTTEAMRETENTION]=temp.[RECRUITMENTTEAMRETENTION],
[EVENTGOAL].[RECRUITMENTTOTAL]=temp.[RECRUITMENTTOTAL],
[EVENTGOAL].[RECRUITMENTWEEKLY]=temp.[RECRUITMENTWEEKLY],
[EVENTGOAL].[REVENUECOMPANY]=temp.[REVENUECOMPANY],
[EVENTGOAL].[REVENUECOMPANYRETENTION]=temp.[REVENUECOMPANYRETENTION],
[EVENTGOAL].[REVENUEDAILY]=temp.[REVENUEDAILY],
[EVENTGOAL].[REVENUEHOUSEHOLD]=temp.[REVENUEHOUSEHOLD],
[EVENTGOAL].[REVENUEHOUSEHOLDRETENTION]=temp.[REVENUEHOUSEHOLDRETENTION],
[EVENTGOAL].[REVENUEINDIVIDUAL]=temp.[REVENUEINDIVIDUAL],
[EVENTGOAL].[REVENUEINDIVIDUALRETENTION]=temp.[REVENUEINDIVIDUALRETENTION],
[EVENTGOAL].[REVENUEMONTHLY]=temp.[REVENUEMONTHLY],
[EVENTGOAL].[REVENUEREGISTRATION]=temp.[REVENUEREGISTRATION],
[EVENTGOAL].[REVENUEREGISTRATIONRETENTION]=temp.[REVENUEREGISTRATIONRETENTION],
[EVENTGOAL].[REVENUERETENTION]=temp.[REVENUERETENTION],
[EVENTGOAL].[REVENUESPONSOR]=temp.[REVENUESPONSOR],
[EVENTGOAL].[REVENUESPONSORRETENTION]=temp.[REVENUESPONSORRETENTION],
[EVENTGOAL].[REVENUETEAM]=temp.[REVENUETEAM],
[EVENTGOAL].[REVENUETEAMRETENTION]=temp.[REVENUETEAMRETENTION],
[EVENTGOAL].[REVENUETOTAL]=temp.[REVENUETOTAL],
[EVENTGOAL].[REVENUEWEEKLY]=temp.[REVENUEWEEKLY],
[EVENTGOAL].CHANGEDBYID = @CHANGEAGENTID,
[EVENTGOAL].DATECHANGED = @CHANGEDATE
from dbo.[EVENTGOAL] inner join @TempTbl as [temp] on [EVENTGOAL].ID = [temp].ID
where ([EVENTGOAL].[COMMUNICATIONCOMPANY]<>temp.[COMMUNICATIONCOMPANY]) or
([EVENTGOAL].[COMMUNICATIONCOMPANY] is null and temp.[COMMUNICATIONCOMPANY] is not null) or
([EVENTGOAL].[COMMUNICATIONCOMPANY] is not null and temp.[COMMUNICATIONCOMPANY] is null) or
([EVENTGOAL].[COMMUNICATIONDAILY]<>temp.[COMMUNICATIONDAILY]) or
([EVENTGOAL].[COMMUNICATIONDAILY] is null and temp.[COMMUNICATIONDAILY] is not null) or
([EVENTGOAL].[COMMUNICATIONDAILY] is not null and temp.[COMMUNICATIONDAILY] is null) or
([EVENTGOAL].[COMMUNICATIONHOUSEHOLD]<>temp.[COMMUNICATIONHOUSEHOLD]) or
([EVENTGOAL].[COMMUNICATIONHOUSEHOLD] is null and temp.[COMMUNICATIONHOUSEHOLD] is not null) or
([EVENTGOAL].[COMMUNICATIONHOUSEHOLD] is not null and temp.[COMMUNICATIONHOUSEHOLD] is null) or
([EVENTGOAL].[COMMUNICATIONINDIVIDUAL]<>temp.[COMMUNICATIONINDIVIDUAL]) or
([EVENTGOAL].[COMMUNICATIONINDIVIDUAL] is null and temp.[COMMUNICATIONINDIVIDUAL] is not null) or
([EVENTGOAL].[COMMUNICATIONINDIVIDUAL] is not null and temp.[COMMUNICATIONINDIVIDUAL] is null) or
([EVENTGOAL].[COMMUNICATIONMONTHLY]<>temp.[COMMUNICATIONMONTHLY]) or
([EVENTGOAL].[COMMUNICATIONMONTHLY] is null and temp.[COMMUNICATIONMONTHLY] is not null) or
([EVENTGOAL].[COMMUNICATIONMONTHLY] is not null and temp.[COMMUNICATIONMONTHLY] is null) or
([EVENTGOAL].[COMMUNICATIONSPONSOR]<>temp.[COMMUNICATIONSPONSOR]) or
([EVENTGOAL].[COMMUNICATIONSPONSOR] is null and temp.[COMMUNICATIONSPONSOR] is not null) or
([EVENTGOAL].[COMMUNICATIONSPONSOR] is not null and temp.[COMMUNICATIONSPONSOR] is null) or
([EVENTGOAL].[COMMUNICATIONTEAM]<>temp.[COMMUNICATIONTEAM]) or
([EVENTGOAL].[COMMUNICATIONTEAM] is null and temp.[COMMUNICATIONTEAM] is not null) or
([EVENTGOAL].[COMMUNICATIONTEAM] is not null and temp.[COMMUNICATIONTEAM] is null) or
([EVENTGOAL].[COMMUNICATIONTOTAL]<>temp.[COMMUNICATIONTOTAL]) or
([EVENTGOAL].[COMMUNICATIONTOTAL] is null and temp.[COMMUNICATIONTOTAL] is not null) or
([EVENTGOAL].[COMMUNICATIONTOTAL] is not null and temp.[COMMUNICATIONTOTAL] is null) or
([EVENTGOAL].[COMMUNICATIONWEEKLY]<>temp.[COMMUNICATIONWEEKLY]) or
([EVENTGOAL].[COMMUNICATIONWEEKLY] is null and temp.[COMMUNICATIONWEEKLY] is not null) or
([EVENTGOAL].[COMMUNICATIONWEEKLY] is not null and temp.[COMMUNICATIONWEEKLY] is null) or
([EVENTGOAL].[COPYBATCHGUID]<>temp.[COPYBATCHGUID]) or
([EVENTGOAL].[COPYBATCHGUID] is null and temp.[COPYBATCHGUID] is not null) or
([EVENTGOAL].[COPYBATCHGUID] is not null and temp.[COPYBATCHGUID] is null) or
([EVENTGOAL].[COPYSOURCEID]<>temp.[COPYSOURCEID]) or
([EVENTGOAL].[COPYSOURCEID] is null and temp.[COPYSOURCEID] is not null) or
([EVENTGOAL].[COPYSOURCEID] is not null and temp.[COPYSOURCEID] is null) or
([EVENTGOAL].[ID]<>temp.[ID]) or
([EVENTGOAL].[ID] is null and temp.[ID] is not null) or
([EVENTGOAL].[ID] is not null and temp.[ID] is null) or
([EVENTGOAL].[ISACTIVE]<>temp.[ISACTIVE]) or
([EVENTGOAL].[ISACTIVE] is null and temp.[ISACTIVE] is not null) or
([EVENTGOAL].[ISACTIVE] is not null and temp.[ISACTIVE] is null) or
([EVENTGOAL].[MINIMIMUMDONORGOAL]<>temp.[MINIMIMUMDONORGOAL]) or
([EVENTGOAL].[MINIMIMUMDONORGOAL] is null and temp.[MINIMIMUMDONORGOAL] is not null) or
([EVENTGOAL].[MINIMIMUMDONORGOAL] is not null and temp.[MINIMIMUMDONORGOAL] is null) or
([EVENTGOAL].[MINIMIMUMFUNDRAISINGGOAL]<>temp.[MINIMIMUMFUNDRAISINGGOAL]) or
([EVENTGOAL].[MINIMIMUMFUNDRAISINGGOAL] is null and temp.[MINIMIMUMFUNDRAISINGGOAL] is not null) or
([EVENTGOAL].[MINIMIMUMFUNDRAISINGGOAL] is not null and temp.[MINIMIMUMFUNDRAISINGGOAL] is null) or
([EVENTGOAL].[MINIMIMUMNEWDONORGOAL]<>temp.[MINIMIMUMNEWDONORGOAL]) or
([EVENTGOAL].[MINIMIMUMNEWDONORGOAL] is null and temp.[MINIMIMUMNEWDONORGOAL] is not null) or
([EVENTGOAL].[MINIMIMUMNEWDONORGOAL] is not null and temp.[MINIMIMUMNEWDONORGOAL] is null) or
([EVENTGOAL].[MINIMIMUMNEWMEMBERGOAL]<>temp.[MINIMIMUMNEWMEMBERGOAL]) or
([EVENTGOAL].[MINIMIMUMNEWMEMBERGOAL] is null and temp.[MINIMIMUMNEWMEMBERGOAL] is not null) or
([EVENTGOAL].[MINIMIMUMNEWMEMBERGOAL] is not null and temp.[MINIMIMUMNEWMEMBERGOAL] is null) or
([EVENTGOAL].[MINIMIMUMPARTICIPANTGOAL]<>temp.[MINIMIMUMPARTICIPANTGOAL]) or
([EVENTGOAL].[MINIMIMUMPARTICIPANTGOAL] is null and temp.[MINIMIMUMPARTICIPANTGOAL] is not null) or
([EVENTGOAL].[MINIMIMUMPARTICIPANTGOAL] is not null and temp.[MINIMIMUMPARTICIPANTGOAL] is null) or
([EVENTGOAL].[MINIMIMUMTEAMMEMBERGOAL]<>temp.[MINIMIMUMTEAMMEMBERGOAL]) or
([EVENTGOAL].[MINIMIMUMTEAMMEMBERGOAL] is null and temp.[MINIMIMUMTEAMMEMBERGOAL] is not null) or
([EVENTGOAL].[MINIMIMUMTEAMMEMBERGOAL] is not null and temp.[MINIMIMUMTEAMMEMBERGOAL] is null) or
([EVENTGOAL].[MINIMUMFUNDRAISEGOALPERMEMBER]<>temp.[MINIMUMFUNDRAISEGOALPERMEMBER]) or
([EVENTGOAL].[MINIMUMFUNDRAISEGOALPERMEMBER] is null and temp.[MINIMUMFUNDRAISEGOALPERMEMBER] is not null) or
([EVENTGOAL].[MINIMUMFUNDRAISEGOALPERMEMBER] is not null and temp.[MINIMUMFUNDRAISEGOALPERMEMBER] is null) or
([EVENTGOAL].[RECRUITMENTCOMPANY]<>temp.[RECRUITMENTCOMPANY]) or
([EVENTGOAL].[RECRUITMENTCOMPANY] is null and temp.[RECRUITMENTCOMPANY] is not null) or
([EVENTGOAL].[RECRUITMENTCOMPANY] is not null and temp.[RECRUITMENTCOMPANY] is null) or
([EVENTGOAL].[RECRUITMENTCOMPANYRETENTION]<>temp.[RECRUITMENTCOMPANYRETENTION]) or
([EVENTGOAL].[RECRUITMENTCOMPANYRETENTION] is null and temp.[RECRUITMENTCOMPANYRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTCOMPANYRETENTION] is not null and temp.[RECRUITMENTCOMPANYRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTDAILY]<>temp.[RECRUITMENTDAILY]) or
([EVENTGOAL].[RECRUITMENTDAILY] is null and temp.[RECRUITMENTDAILY] is not null) or
([EVENTGOAL].[RECRUITMENTDAILY] is not null and temp.[RECRUITMENTDAILY] is null) or
([EVENTGOAL].[RECRUITMENTDONOR]<>temp.[RECRUITMENTDONOR]) or
([EVENTGOAL].[RECRUITMENTDONOR] is null and temp.[RECRUITMENTDONOR] is not null) or
([EVENTGOAL].[RECRUITMENTDONOR] is not null and temp.[RECRUITMENTDONOR] is null) or
([EVENTGOAL].[RECRUITMENTDONORRETENTION]<>temp.[RECRUITMENTDONORRETENTION]) or
([EVENTGOAL].[RECRUITMENTDONORRETENTION] is null and temp.[RECRUITMENTDONORRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTDONORRETENTION] is not null and temp.[RECRUITMENTDONORRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTFITNESSCOACHES]<>temp.[RECRUITMENTFITNESSCOACHES]) or
([EVENTGOAL].[RECRUITMENTFITNESSCOACHES] is null and temp.[RECRUITMENTFITNESSCOACHES] is not null) or
([EVENTGOAL].[RECRUITMENTFITNESSCOACHES] is not null and temp.[RECRUITMENTFITNESSCOACHES] is null) or
([EVENTGOAL].[RECRUITMENTFITNESSCOACHRETENTION]<>temp.[RECRUITMENTFITNESSCOACHRETENTION]) or
([EVENTGOAL].[RECRUITMENTFITNESSCOACHRETENTION] is null and temp.[RECRUITMENTFITNESSCOACHRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTFITNESSCOACHRETENTION] is not null and temp.[RECRUITMENTFITNESSCOACHRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHES]<>temp.[RECRUITMENTFUNDRAISINGCOACHES]) or
([EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHES] is null and temp.[RECRUITMENTFUNDRAISINGCOACHES] is not null) or
([EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHES] is not null and temp.[RECRUITMENTFUNDRAISINGCOACHES] is null) or
([EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHRETENTION]<>temp.[RECRUITMENTFUNDRAISINGCOACHRETENTION]) or
([EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHRETENTION] is null and temp.[RECRUITMENTFUNDRAISINGCOACHRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTFUNDRAISINGCOACHRETENTION] is not null and temp.[RECRUITMENTFUNDRAISINGCOACHRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTHOUSEHOLD]<>temp.[RECRUITMENTHOUSEHOLD]) or
([EVENTGOAL].[RECRUITMENTHOUSEHOLD] is null and temp.[RECRUITMENTHOUSEHOLD] is not null) or
([EVENTGOAL].[RECRUITMENTHOUSEHOLD] is not null and temp.[RECRUITMENTHOUSEHOLD] is null) or
([EVENTGOAL].[RECRUITMENTHOUSEHOLDRETENTION]<>temp.[RECRUITMENTHOUSEHOLDRETENTION]) or
([EVENTGOAL].[RECRUITMENTHOUSEHOLDRETENTION] is null and temp.[RECRUITMENTHOUSEHOLDRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTHOUSEHOLDRETENTION] is not null and temp.[RECRUITMENTHOUSEHOLDRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTINDIVIDUAL]<>temp.[RECRUITMENTINDIVIDUAL]) or
([EVENTGOAL].[RECRUITMENTINDIVIDUAL] is null and temp.[RECRUITMENTINDIVIDUAL] is not null) or
([EVENTGOAL].[RECRUITMENTINDIVIDUAL] is not null and temp.[RECRUITMENTINDIVIDUAL] is null) or
([EVENTGOAL].[RECRUITMENTINDIVIDUALRETENTION]<>temp.[RECRUITMENTINDIVIDUALRETENTION]) or
([EVENTGOAL].[RECRUITMENTINDIVIDUALRETENTION] is null and temp.[RECRUITMENTINDIVIDUALRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTINDIVIDUALRETENTION] is not null and temp.[RECRUITMENTINDIVIDUALRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTMONTHLY]<>temp.[RECRUITMENTMONTHLY]) or
([EVENTGOAL].[RECRUITMENTMONTHLY] is null and temp.[RECRUITMENTMONTHLY] is not null) or
([EVENTGOAL].[RECRUITMENTMONTHLY] is not null and temp.[RECRUITMENTMONTHLY] is null) or
([EVENTGOAL].[RECRUITMENTSPONSOR]<>temp.[RECRUITMENTSPONSOR]) or
([EVENTGOAL].[RECRUITMENTSPONSOR] is null and temp.[RECRUITMENTSPONSOR] is not null) or
([EVENTGOAL].[RECRUITMENTSPONSOR] is not null and temp.[RECRUITMENTSPONSOR] is null) or
([EVENTGOAL].[RECRUITMENTSPONSORRETENTION]<>temp.[RECRUITMENTSPONSORRETENTION]) or
([EVENTGOAL].[RECRUITMENTSPONSORRETENTION] is null and temp.[RECRUITMENTSPONSORRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTSPONSORRETENTION] is not null and temp.[RECRUITMENTSPONSORRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTTEAM]<>temp.[RECRUITMENTTEAM]) or
([EVENTGOAL].[RECRUITMENTTEAM] is null and temp.[RECRUITMENTTEAM] is not null) or
([EVENTGOAL].[RECRUITMENTTEAM] is not null and temp.[RECRUITMENTTEAM] is null) or
([EVENTGOAL].[RECRUITMENTTEAMRETENTION]<>temp.[RECRUITMENTTEAMRETENTION]) or
([EVENTGOAL].[RECRUITMENTTEAMRETENTION] is null and temp.[RECRUITMENTTEAMRETENTION] is not null) or
([EVENTGOAL].[RECRUITMENTTEAMRETENTION] is not null and temp.[RECRUITMENTTEAMRETENTION] is null) or
([EVENTGOAL].[RECRUITMENTTOTAL]<>temp.[RECRUITMENTTOTAL]) or
([EVENTGOAL].[RECRUITMENTTOTAL] is null and temp.[RECRUITMENTTOTAL] is not null) or
([EVENTGOAL].[RECRUITMENTTOTAL] is not null and temp.[RECRUITMENTTOTAL] is null) or
([EVENTGOAL].[RECRUITMENTWEEKLY]<>temp.[RECRUITMENTWEEKLY]) or
([EVENTGOAL].[RECRUITMENTWEEKLY] is null and temp.[RECRUITMENTWEEKLY] is not null) or
([EVENTGOAL].[RECRUITMENTWEEKLY] is not null and temp.[RECRUITMENTWEEKLY] is null) or
([EVENTGOAL].[REVENUECOMPANY]<>temp.[REVENUECOMPANY]) or
([EVENTGOAL].[REVENUECOMPANY] is null and temp.[REVENUECOMPANY] is not null) or
([EVENTGOAL].[REVENUECOMPANY] is not null and temp.[REVENUECOMPANY] is null) or
([EVENTGOAL].[REVENUECOMPANYRETENTION]<>temp.[REVENUECOMPANYRETENTION]) or
([EVENTGOAL].[REVENUECOMPANYRETENTION] is null and temp.[REVENUECOMPANYRETENTION] is not null) or
([EVENTGOAL].[REVENUECOMPANYRETENTION] is not null and temp.[REVENUECOMPANYRETENTION] is null) or
([EVENTGOAL].[REVENUEDAILY]<>temp.[REVENUEDAILY]) or
([EVENTGOAL].[REVENUEDAILY] is null and temp.[REVENUEDAILY] is not null) or
([EVENTGOAL].[REVENUEDAILY] is not null and temp.[REVENUEDAILY] is null) or
([EVENTGOAL].[REVENUEHOUSEHOLD]<>temp.[REVENUEHOUSEHOLD]) or
([EVENTGOAL].[REVENUEHOUSEHOLD] is null and temp.[REVENUEHOUSEHOLD] is not null) or
([EVENTGOAL].[REVENUEHOUSEHOLD] is not null and temp.[REVENUEHOUSEHOLD] is null) or
([EVENTGOAL].[REVENUEHOUSEHOLDRETENTION]<>temp.[REVENUEHOUSEHOLDRETENTION]) or
([EVENTGOAL].[REVENUEHOUSEHOLDRETENTION] is null and temp.[REVENUEHOUSEHOLDRETENTION] is not null) or
([EVENTGOAL].[REVENUEHOUSEHOLDRETENTION] is not null and temp.[REVENUEHOUSEHOLDRETENTION] is null) or
([EVENTGOAL].[REVENUEINDIVIDUAL]<>temp.[REVENUEINDIVIDUAL]) or
([EVENTGOAL].[REVENUEINDIVIDUAL] is null and temp.[REVENUEINDIVIDUAL] is not null) or
([EVENTGOAL].[REVENUEINDIVIDUAL] is not null and temp.[REVENUEINDIVIDUAL] is null) or
([EVENTGOAL].[REVENUEINDIVIDUALRETENTION]<>temp.[REVENUEINDIVIDUALRETENTION]) or
([EVENTGOAL].[REVENUEINDIVIDUALRETENTION] is null and temp.[REVENUEINDIVIDUALRETENTION] is not null) or
([EVENTGOAL].[REVENUEINDIVIDUALRETENTION] is not null and temp.[REVENUEINDIVIDUALRETENTION] is null) or
([EVENTGOAL].[REVENUEMONTHLY]<>temp.[REVENUEMONTHLY]) or
([EVENTGOAL].[REVENUEMONTHLY] is null and temp.[REVENUEMONTHLY] is not null) or
([EVENTGOAL].[REVENUEMONTHLY] is not null and temp.[REVENUEMONTHLY] is null) or
([EVENTGOAL].[REVENUEREGISTRATION]<>temp.[REVENUEREGISTRATION]) or
([EVENTGOAL].[REVENUEREGISTRATION] is null and temp.[REVENUEREGISTRATION] is not null) or
([EVENTGOAL].[REVENUEREGISTRATION] is not null and temp.[REVENUEREGISTRATION] is null) or
([EVENTGOAL].[REVENUEREGISTRATIONRETENTION]<>temp.[REVENUEREGISTRATIONRETENTION]) or
([EVENTGOAL].[REVENUEREGISTRATIONRETENTION] is null and temp.[REVENUEREGISTRATIONRETENTION] is not null) or
([EVENTGOAL].[REVENUEREGISTRATIONRETENTION] is not null and temp.[REVENUEREGISTRATIONRETENTION] is null) or
([EVENTGOAL].[REVENUERETENTION]<>temp.[REVENUERETENTION]) or
([EVENTGOAL].[REVENUERETENTION] is null and temp.[REVENUERETENTION] is not null) or
([EVENTGOAL].[REVENUERETENTION] is not null and temp.[REVENUERETENTION] is null) or
([EVENTGOAL].[REVENUESPONSOR]<>temp.[REVENUESPONSOR]) or
([EVENTGOAL].[REVENUESPONSOR] is null and temp.[REVENUESPONSOR] is not null) or
([EVENTGOAL].[REVENUESPONSOR] is not null and temp.[REVENUESPONSOR] is null) or
([EVENTGOAL].[REVENUESPONSORRETENTION]<>temp.[REVENUESPONSORRETENTION]) or
([EVENTGOAL].[REVENUESPONSORRETENTION] is null and temp.[REVENUESPONSORRETENTION] is not null) or
([EVENTGOAL].[REVENUESPONSORRETENTION] is not null and temp.[REVENUESPONSORRETENTION] is null) or
([EVENTGOAL].[REVENUETEAM]<>temp.[REVENUETEAM]) or
([EVENTGOAL].[REVENUETEAM] is null and temp.[REVENUETEAM] is not null) or
([EVENTGOAL].[REVENUETEAM] is not null and temp.[REVENUETEAM] is null) or
([EVENTGOAL].[REVENUETEAMRETENTION]<>temp.[REVENUETEAMRETENTION]) or
([EVENTGOAL].[REVENUETEAMRETENTION] is null and temp.[REVENUETEAMRETENTION] is not null) or
([EVENTGOAL].[REVENUETEAMRETENTION] is not null and temp.[REVENUETEAMRETENTION] is null) or
([EVENTGOAL].[REVENUETOTAL]<>temp.[REVENUETOTAL]) or
([EVENTGOAL].[REVENUETOTAL] is null and temp.[REVENUETOTAL] is not null) or
([EVENTGOAL].[REVENUETOTAL] is not null and temp.[REVENUETOTAL] is null) or
([EVENTGOAL].[REVENUEWEEKLY]<>temp.[REVENUEWEEKLY]) or
([EVENTGOAL].[REVENUEWEEKLY] is null and temp.[REVENUEWEEKLY] is not null) or
([EVENTGOAL].[REVENUEWEEKLY] is not null and temp.[REVENUEWEEKLY] is null)
if @@Error <> 0
return 3;
-- 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]
where not exists (select ID from dbo.[EVENTGOAL] as data where data.ID = [temp].ID)
if @@Error <> 0
return 4;
return 0;