USP_DATAFORMTEMPLATE_ADD_TEAM_EXTENSION

The save procedure used by the add dataform template "Team Extension 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.
@TEAMFUNDRAISINGTEAMID	uniqueidentifier	IN	Team
@EVENTID	uniqueidentifier	IN	Event
@PARTICIPANTGOAL	int	IN	Total Participant Goal
@TEAMMEMBERGOAL	int	IN	Total Team Member Goal
@VOLUNTEERGOAL	int	IN	Total Volunteer Recruitment Goal
@COMMUNICATIONGOAL	int	IN	Communication Goal
@OTHERGOAL	int	IN	Custom Goal
@PCTTEAMMEMBERRETENSION	decimal(5, 2)	IN	Percentage of Team Members to retain
@TEAMRECRUITMENTGOAL	int	IN	Total number of Teams in a Company
@PCTTEAMSRETENSION	decimal(5, 2)	IN	Percentage of Teams to retain in a Company
@STATUSCODE	tinyint	IN	Status
@TYPECODE	tinyint	IN	Type
@HOUSEHOLDID	uniqueidentifier	IN	Household
@TARGETFUNDRAISINGGOAL	money	IN	TargetFundraisingGoal
@DONORRETENTIONGOAL	decimal(5, 2)	IN	DonorRetentionGoal
@PREVIOUSTEAMID	uniqueidentifier	IN	Previousteam

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_TEAM_EXTENSION
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @TEAMFUNDRAISINGTEAMID uniqueidentifier,
    @EVENTID uniqueidentifier,
    @PARTICIPANTGOAL int = 0,
      @TEAMMEMBERGOAL int = 0,
    @VOLUNTEERGOAL int = 0,
    @COMMUNICATIONGOAL int = 0,
      @OTHERGOAL int = 0,
      @PCTTEAMMEMBERRETENSION decimal(5,2) = 0,
      @TEAMRECRUITMENTGOAL int = 0,
      @PCTTEAMSRETENSION decimal(5,2) = 0,
    @STATUSCODE tinyint = 0,
      @TYPECODE tinyint = 1,
      @HOUSEHOLDID uniqueidentifier = null,
      @TARGETFUNDRAISINGGOAL money = 0,
      @DONORRETENTIONGOAL decimal(5,2) = 0,
    @PREVIOUSTEAMID uniqueidentifier = null
)
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()

declare @NAME nvarchar(200)
declare @TYPENAME varchar(50)
declare @GROUPTYPEID uniqueidentifier
declare @CONSTITUENTID uniqueidentifier
declare @IsInactive bit

  IF @PREVIOUSTEAMID IS NOT NULL
  BEGIN

    SELECT @CONSTITUENTID = TEAMCONSTITUENTID FROM dbo.TEAMEXTENSION (NOLOCK) WHERE TEAMFUNDRAISINGTEAMID = @PREVIOUSTEAMID

    IF @TYPECODE = 3 and @CONSTITUENTID IS NOT NULL
    SET @HOUSEHOLDID = @CONSTITUENTID

  END

  IF @CONSTITUENTID IS NULL
  BEGIN
      SET @PREVIOUSTEAMID = null
      SET @CONSTITUENTID = newid()
  END


begin try

  IF @TYPECODE IN (1,2,3)
    BEGIN

        SET @TYPENAME = 'FAF Fundraising Group'

        SELECT @NAME = NAME from dbo.TEAMFUNDRAISINGTEAM WHERE ID = @TEAMFUNDRAISINGTEAMID

        SELECT @GROUPTYPEID = ID FROM dbo.GROUPTYPE WHERE NAME = @TYPENAME

        IF @GROUPTYPEID IS NOT NULL
        BEGIN

      IF @PREVIOUSTEAMID IS NULL
              EXEC USP_FAF_GROUPDATA_ADD @ID=@CONSTITUENTID, @GROUPTYPEID=@GROUPTYPEID, @NAME=@NAME, @CHANGEAGENTID=@CHANGEAGENTID

            IF @STATUSCODE <> 0 AND @PREVIOUSTEAMID IS NULL
              UPDATE CONSTITUENT
              set ISINACTIVE =1
              WHERE ID = @CONSTITUENTID
        END

    END

    -- handle inserting the data
    insert into dbo.TEAMEXTENSION
        (ID, TEAMFUNDRAISINGTEAMID, EVENTID, PARTICIPANTGOAL, TEAMMEMBERGOAL, VOLUNTEERGOAL, COMMUNICATIONGOAL, OTHERGOAL, PCTTEAMMEMBERRETENSION, TEAMRECRUITMENTGOAL, PCTTEAMSRETENSION, STATUSCODE, TYPECODE, TARGETFUNDRAISINGGOAL, DONORRETENTIONGOAL, TEAMCONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values
        (@ID, @TEAMFUNDRAISINGTEAMID, @EVENTID, @PARTICIPANTGOAL, @TEAMMEMBERGOAL, @VOLUNTEERGOAL, @COMMUNICATIONGOAL, @OTHERGOAL, @PCTTEAMMEMBERRETENSION, @TEAMRECRUITMENTGOAL, @PCTTEAMSRETENSION, @STATUSCODE, @TYPECODE, @TARGETFUNDRAISINGGOAL, @DONORRETENTIONGOAL, @CONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)


     -- link site to constituent
  Exec dbo.USP_ADDSITETOCONSTITUENT @CONSTITUENTID, @EVENTID

  IF @TYPECODE = 3 AND @CONSTITUENTID IS NOT NULL
        UPDATE TEAMEXTENSION
        SET HOUSEHOLDID = @CONSTITUENTID
        WHERE ID = @ID

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0