USP_DATAFORMTEMPLATE_ADD_EVENTGOAL

Add an event goal record

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.
@EVENTID	uniqueidentifier	IN	Event
@REVENUETOTAL	money	IN
@REVENUEMONTHLY	money	IN
@REVENUEWEEKLY	money	IN
@REVENUEDAILY	money	IN
@REVENUESPONSOR	money	IN
@REVENUECOMPANY	money	IN
@REVENUETEAM	money	IN
@REVENUEINDIVIDUAL	money	IN
@RECRUITMENTTOTAL	int	IN
@RECRUITMENTMONTHLY	int	IN
@RECRUITMENTWEEKLY	int	IN
@RECRUITMENTDAILY	int	IN
@RECRUITMENTSPONSOR	int	IN
@RECRUITMENTCOMPANY	int	IN
@RECRUITMENTTEAM	int	IN
@RECRUITMENTINDIVIDUAL	int	IN
@COMMUNICATIONTOTAL	int	IN
@COMMUNICATIONMONTHLY	int	IN
@COMMUNICATIONWEEKLY	int	IN
@COMMUNICATIONDAILY	int	IN
@COMMUNICATIONSPONSOR	int	IN
@COMMUNICATIONCOMPANY	int	IN
@COMMUNICATIONTEAM	int	IN
@COMMUNICATIONINDIVIDUAL	int	IN
@ISACTIVE	bit	IN
@MINIMIMUMFUNDRAISINGGOAL	money	IN
@MINIMIMUMPARTICIPANTGOAL	int	IN
@MINIMUMFUNDRAISEGOALPERMEMBER	money	IN
@MINIMIMUMTEAMMEMBERGOAL	int	IN
@MINIMIMUMDONORGOAL	int	IN
@MINIMIMUMNEWMEMBERGOAL	int	IN
@MINIMIMUMNEWDONORGOAL	int	IN
@RECRUITMENTFUNDRAISINGCOACHES	int	IN
@RECRUITMENTFITNESSCOACHES	int	IN
@RECRUITMENTDONOR	int	IN
@REVENUEHOUSEHOLD	money	IN
@RECRUITMENTHOUSEHOLD	int	IN
@COMMUNICATIONHOUSEHOLD	int	IN
@REVENUERETENTION	decimal(6, 3)	IN
@REVENUESPONSORRETENTION	decimal(6, 3)	IN
@REVENUECOMPANYRETENTION	decimal(6, 3)	IN
@REVENUETEAMRETENTION	decimal(6, 3)	IN
@REVENUEINDIVIDUALRETENTION	decimal(6, 3)	IN
@RECRUITMENTFUNDRAISINGCOACHRETENTION	decimal(6, 3)	IN
@RECRUITMENTFITNESSCOACHRETENTION	decimal(6, 3)	IN
@RECRUITMENTSPONSORRETENTION	decimal(6, 3)	IN
@RECRUITMENTCOMPANYRETENTION	decimal(6, 3)	IN
@RECRUITMENTDONORRETENTION	decimal(6, 3)	IN
@RECRUITMENTTEAMRETENTION	decimal(6, 3)	IN
@RECRUITMENTINDIVIDUALRETENTION	decimal(6, 3)	IN
@REVENUEHOUSEHOLDRETENTION	decimal(6, 3)	IN
@RECRUITMENTHOUSEHOLDRETENTION	decimal(6, 3)	IN
@REVENUEREGISTRATION	money	IN
@REVENUEREGISTRATIONRETENTION	decimal(6, 3)	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_EVENTGOAL
(
  @ID uniqueidentifier= null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @EVENTID uniqueidentifier,
  --@TEAMFUNDRAISINGTEAMID uniqueidentifier = null,

  --@REGISTRANTID uniqueidentifier = null,

  --@FAFGOALTYPEID uniqueidentifier = null,

  @REVENUETOTAL money = 0,
  @REVENUEMONTHLY money = 0,
  @REVENUEWEEKLY money = 0,
  @REVENUEDAILY money = 0,
  @REVENUESPONSOR money = 0,
  @REVENUECOMPANY money = 0,
  @REVENUETEAM money = 0,
  @REVENUEINDIVIDUAL money = 0,
  @RECRUITMENTTOTAL int = 0,
  @RECRUITMENTMONTHLY int = 0,
  @RECRUITMENTWEEKLY int = 0,
  @RECRUITMENTDAILY int = 0,
  @RECRUITMENTSPONSOR int = 0,
  @RECRUITMENTCOMPANY int = 0,
  @RECRUITMENTTEAM int = 0,
  @RECRUITMENTINDIVIDUAL int = 0,
  @COMMUNICATIONTOTAL int = 0,
  @COMMUNICATIONMONTHLY int = 0,
  @COMMUNICATIONWEEKLY int = 0,
  @COMMUNICATIONDAILY int = 0,
  @COMMUNICATIONSPONSOR int = 0,
  @COMMUNICATIONCOMPANY int = 0,
  @COMMUNICATIONTEAM int = 0,
  @COMMUNICATIONINDIVIDUAL int = 0,  
  @ISACTIVE bit= 0,
  @MINIMIMUMFUNDRAISINGGOAL money = 0,
  @MINIMIMUMPARTICIPANTGOAL int = 0,
  @MINIMUMFUNDRAISEGOALPERMEMBER money = 0,
  @MINIMIMUMTEAMMEMBERGOAL int = 0,
  @MINIMIMUMDONORGOAL int = 0,
  @MINIMIMUMNEWMEMBERGOAL int = 0,
  @MINIMIMUMNEWDONORGOAL int = 0,
  @RECRUITMENTFUNDRAISINGCOACHES int = 0,
  @RECRUITMENTFITNESSCOACHES int = 0,
  @RECRUITMENTDONOR int = 0,
  @REVENUEHOUSEHOLD money = 0,
  @RECRUITMENTHOUSEHOLD int = 0,
  @COMMUNICATIONHOUSEHOLD int = 0,  
  @REVENUERETENTION decimal(6,3) = 0,
  @REVENUESPONSORRETENTION decimal(6,3) = 0,
  @REVENUECOMPANYRETENTION decimal(6,3) = 0,
  @REVENUETEAMRETENTION decimal(6,3) = 0,
  @REVENUEINDIVIDUALRETENTION decimal(6,3) = 0,
  @RECRUITMENTFUNDRAISINGCOACHRETENTION decimal(6,3) = 0,
  @RECRUITMENTFITNESSCOACHRETENTION decimal(6,3) = 0,
  @RECRUITMENTSPONSORRETENTION decimal(6,3) = 0,
  @RECRUITMENTCOMPANYRETENTION decimal(6,3) = 0,
  @RECRUITMENTDONORRETENTION decimal(6,3) = 0,
  @RECRUITMENTTEAMRETENTION decimal(6,3) = 0,
  @RECRUITMENTINDIVIDUALRETENTION decimal(6,3) = 0,
  @REVENUEHOUSEHOLDRETENTION decimal(6,3) = 0,
  @RECRUITMENTHOUSEHOLDRETENTION decimal(6,3) = 0,
  @REVENUEREGISTRATION money = 0,
  @REVENUEREGISTRATIONRETENTION decimal(6,3) = 0
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try
    DECLARE @PREVIOUSYEAREVENTID uniqueidentifier
    SELECT    @PREVIOUSYEAREVENTID= PRIORYEAREVENTID FROM EVENT E(NOLOCK) JOIN EVENTEXTENSION EX (NOLOCK) ON E.ID= EX.EVENTID AND E.ID= @EVENTID

    IF @PREVIOUSYEAREVENTID IS NOT NULL
    --------------------------------------------------

    INSERT INTO dbo.EVENTGOAL
        (    ID, EVENTID, REVENUETOTAL, REVENUEMONTHLY, REVENUEWEEKLY, REVENUEDAILY, REVENUESPONSOR, REVENUECOMPANY, REVENUETEAM, REVENUEINDIVIDUAL, 
            RECRUITMENTTOTAL, RECRUITMENTMONTHLY, RECRUITMENTWEEKLY, RECRUITMENTDAILY, RECRUITMENTSPONSOR, RECRUITMENTCOMPANY, RECRUITMENTTEAM, RECRUITMENTINDIVIDUAL, 
            COMMUNICATIONTOTAL, COMMUNICATIONMONTHLY, COMMUNICATIONWEEKLY, COMMUNICATIONDAILY, COMMUNICATIONSPONSOR, COMMUNICATIONCOMPANY, 
            COMMUNICATIONTEAM, COMMUNICATIONINDIVIDUAL, ISACTIVE, MINIMIMUMFUNDRAISINGGOAL, MINIMIMUMPARTICIPANTGOAL, MINIMUMFUNDRAISEGOALPERMEMBER, 
            MINIMIMUMTEAMMEMBERGOAL, MINIMIMUMDONORGOAL, MINIMIMUMNEWMEMBERGOAL, MINIMIMUMNEWDONORGOAL, RECRUITMENTFUNDRAISINGCOACHES, RECRUITMENTFITNESSCOACHES,RECRUITMENTDONOR,
            REVENUERETENTION, REVENUESPONSORRETENTION, REVENUECOMPANYRETENTION, REVENUETEAMRETENTION, REVENUEINDIVIDUALRETENTION, RECRUITMENTFUNDRAISINGCOACHRETENTION, RECRUITMENTFITNESSCOACHRETENTION, 
            RECRUITMENTSPONSORRETENTION, RECRUITMENTCOMPANYRETENTION, RECRUITMENTDONORRETENTION, RECRUITMENTTEAMRETENTION, RECRUITMENTINDIVIDUALRETENTION,REVENUEHOUSEHOLD ,RECRUITMENTHOUSEHOLD,COMMUNICATIONHOUSEHOLD,REVENUEHOUSEHOLDRETENTION, RECRUITMENTHOUSEHOLDRETENTION, 
            REVENUEREGISTRATION, REVENUEREGISTRATIONRETENTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    SELECT 
            @ID, @EVENTID, REVENUETOTAL, REVENUEMONTHLY, REVENUEWEEKLY, REVENUEDAILY, REVENUESPONSOR, REVENUECOMPANY, REVENUETEAM, REVENUEINDIVIDUAL, 
            RECRUITMENTTOTAL, RECRUITMENTMONTHLY, RECRUITMENTWEEKLY, RECRUITMENTDAILY, RECRUITMENTSPONSOR, RECRUITMENTCOMPANY, RECRUITMENTTEAM, RECRUITMENTINDIVIDUAL, 
            COMMUNICATIONTOTAL, COMMUNICATIONMONTHLY, COMMUNICATIONWEEKLY, COMMUNICATIONDAILY, COMMUNICATIONSPONSOR, COMMUNICATIONCOMPANY, 
            COMMUNICATIONTEAM, COMMUNICATIONINDIVIDUAL, EG.ISACTIVE, MINIMIMUMFUNDRAISINGGOAL, MINIMIMUMPARTICIPANTGOAL, MINIMUMFUNDRAISEGOALPERMEMBER, 
            MINIMIMUMTEAMMEMBERGOAL, MINIMIMUMDONORGOAL, MINIMIMUMNEWMEMBERGOAL, MINIMIMUMNEWDONORGOAL, RECRUITMENTFUNDRAISINGCOACHES, RECRUITMENTFITNESSCOACHES,RECRUITMENTDONOR,
            REVENUERETENTION, REVENUESPONSORRETENTION, REVENUECOMPANYRETENTION, REVENUETEAMRETENTION, REVENUEINDIVIDUALRETENTION, RECRUITMENTFUNDRAISINGCOACHRETENTION, RECRUITMENTFITNESSCOACHRETENTION, 
            RECRUITMENTSPONSORRETENTION, RECRUITMENTCOMPANYRETENTION, RECRUITMENTDONORRETENTION, RECRUITMENTTEAMRETENTION, RECRUITMENTINDIVIDUALRETENTION, REVENUEHOUSEHOLD ,RECRUITMENTHOUSEHOLD,COMMUNICATIONHOUSEHOLD,REVENUEHOUSEHOLDRETENTION, RECRUITMENTHOUSEHOLDRETENTION, 
            REVENUEREGISTRATION, REVENUEREGISTRATIONRETENTION, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    FROM    dbo.EVENTGOAL EG (NOLOCK)    
    JOIN    dbo.EVENT E (NOLOCK)ON EG.EVENTID= E.ID
    WHERE    E.ID= @PREVIOUSYEAREVENTID    

    ELSE
    --------------------------------------------------

    INSERT INTO dbo.EVENTGOAL
        (    ID, EVENTID, REVENUETOTAL, REVENUEMONTHLY, REVENUEWEEKLY, REVENUEDAILY, REVENUESPONSOR, REVENUECOMPANY, REVENUETEAM, REVENUEINDIVIDUAL, 
            RECRUITMENTTOTAL, RECRUITMENTMONTHLY, RECRUITMENTWEEKLY, RECRUITMENTDAILY, RECRUITMENTSPONSOR, RECRUITMENTCOMPANY, RECRUITMENTTEAM, RECRUITMENTINDIVIDUAL, 
            COMMUNICATIONTOTAL, COMMUNICATIONMONTHLY, COMMUNICATIONWEEKLY, COMMUNICATIONDAILY, COMMUNICATIONSPONSOR, COMMUNICATIONCOMPANY, 
            COMMUNICATIONTEAM, COMMUNICATIONINDIVIDUAL, ISACTIVE, MINIMIMUMFUNDRAISINGGOAL, MINIMIMUMPARTICIPANTGOAL, MINIMUMFUNDRAISEGOALPERMEMBER, 
            MINIMIMUMTEAMMEMBERGOAL, MINIMIMUMDONORGOAL, MINIMIMUMNEWMEMBERGOAL, MINIMIMUMNEWDONORGOAL, RECRUITMENTFUNDRAISINGCOACHES, RECRUITMENTFITNESSCOACHES,RECRUITMENTDONOR,
            REVENUERETENTION, REVENUESPONSORRETENTION, REVENUECOMPANYRETENTION, REVENUETEAMRETENTION, REVENUEINDIVIDUALRETENTION, RECRUITMENTFUNDRAISINGCOACHRETENTION, RECRUITMENTFITNESSCOACHRETENTION, 
            RECRUITMENTSPONSORRETENTION, RECRUITMENTCOMPANYRETENTION, RECRUITMENTDONORRETENTION, RECRUITMENTTEAMRETENTION, RECRUITMENTINDIVIDUALRETENTION,REVENUEHOUSEHOLD ,RECRUITMENTHOUSEHOLD,COMMUNICATIONHOUSEHOLD,REVENUEHOUSEHOLDRETENTION, RECRUITMENTHOUSEHOLDRETENTION, 
            REVENUEREGISTRATION, REVENUEREGISTRATIONRETENTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    VALUES
        (    @ID, @EVENTID, @REVENUETOTAL, @REVENUEMONTHLY, @REVENUEWEEKLY, @REVENUEDAILY, @REVENUESPONSOR, @REVENUECOMPANY, @REVENUETEAM, @REVENUEINDIVIDUAL, 
            @RECRUITMENTTOTAL, @RECRUITMENTMONTHLY, @RECRUITMENTWEEKLY, @RECRUITMENTDAILY, @RECRUITMENTSPONSOR, @RECRUITMENTCOMPANY, @RECRUITMENTTEAM, @RECRUITMENTINDIVIDUAL, 
            @COMMUNICATIONTOTAL, @COMMUNICATIONMONTHLY, @COMMUNICATIONWEEKLY, @COMMUNICATIONDAILY, @COMMUNICATIONSPONSOR, @COMMUNICATIONCOMPANY, 
            @COMMUNICATIONTEAM, @COMMUNICATIONINDIVIDUAL, @ISACTIVE, @MINIMIMUMFUNDRAISINGGOAL, @MINIMIMUMPARTICIPANTGOAL, @MINIMUMFUNDRAISEGOALPERMEMBER, 
            @MINIMIMUMTEAMMEMBERGOAL, @MINIMIMUMDONORGOAL, @MINIMIMUMNEWMEMBERGOAL, @MINIMIMUMNEWDONORGOAL, @RECRUITMENTFUNDRAISINGCOACHES, @RECRUITMENTFITNESSCOACHES, @RECRUITMENTDONOR,@REVENUEHOUSEHOLD ,@RECRUITMENTHOUSEHOLD,@COMMUNICATIONHOUSEHOLD,
            @REVENUERETENTION/100, @REVENUESPONSORRETENTION/100, @REVENUECOMPANYRETENTION/100, @REVENUETEAMRETENTION/100, @REVENUEINDIVIDUALRETENTION/100,
      @RECRUITMENTFUNDRAISINGCOACHRETENTION/100, @RECRUITMENTFITNESSCOACHRETENTION/100,@RECRUITMENTSPONSORRETENTION/100, @RECRUITMENTCOMPANYRETENTION/100,
      @RECRUITMENTDONORRETENTION/100, @RECRUITMENTTEAMRETENTION/100, @RECRUITMENTINDIVIDUALRETENTION/100,@REVENUEHOUSEHOLDRETENTION/100, @RECRUITMENTHOUSEHOLDRETENTION/100, 
        @REVENUEREGISTRATION, @REVENUEREGISTRATIONRETENTION/100,    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
    -----------------------------------------------------------------------------------------------------------


end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0