USP_BATCHSPONSORSHIP_ADDGROUPMEMBER

Adds a group member through batch sponsorship.
Parameters

Parameter	Parameter Type	Mode
@BATCHSPONSORSHIPMEMBERID	uniqueidentifier	INOUT
@BATCHSPONSORSHIPGROUPID	uniqueidentifier	IN
@EXISTINGCONSTITUENTID	uniqueidentifier	IN
@KEYNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@TITLECODEID	uniqueidentifier	IN
@SUFFIXCODEID	uniqueidentifier	IN
@COPYHOUSEHOLDCONTACT	bit	IN
@ISPRIMARYMEMBER	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
Definition

Copy


            create procedure dbo.USP_BATCHSPONSORSHIP_ADDGROUPMEMBER
            (
                @BATCHSPONSORSHIPMEMBERID uniqueidentifier = null output,
                @BATCHSPONSORSHIPGROUPID uniqueidentifier,
                @EXISTINGCONSTITUENTID uniqueidentifier,
                @KEYNAME nvarchar(100),
                @FIRSTNAME nvarchar(50),
                @MIDDLENAME nvarchar(50),
                @TITLECODEID uniqueidentifier,
                @SUFFIXCODEID uniqueidentifier,
                @COPYHOUSEHOLDCONTACT bit,
                @ISPRIMARYMEMBER bit,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
            begin
                set nocount on;

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

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

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                declare @COUNTRYID uniqueidentifier
                exec @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

                -- If an existing constituent is being used, clear the name

                declare @ISORGANIZATION bit, @ISGROUP bit, @GROUPTYPECODE tinyint
                if @EXISTINGCONSTITUENTID is not null
                begin
                    set @KEYNAME = ''

                    select
                        @ISORGANIZATION = C.ISORGANIZATION,
                        @ISGROUP = C.ISGROUP,
                        @GROUPTYPECODE = GD.GROUPTYPECODE
                    from dbo.CONSTITUENT C
                    left join dbo.GROUPDATA GD on C.ID = GD.ID
                    where
                        C.ID = @EXISTINGCONSTITUENTID
                end
                else
                begin
                    set @ISORGANIZATION = 0
                    set @ISGROUP = 0
                    set @GROUPTYPECODE = 0
                end

                insert into dbo.BATCHSPONSORSHIPCONSTITUENT
                (
                    ID,
                    EXISTINGCONSTITUENTID,
                    KEYNAME,
                    FIRSTNAME,
                    MIDDLENAME,
                    TITLECODEID,
                    SUFFIXCODEID,
                    ISORGANIZATION,
                    ISGROUP,
                    GROUPTYPECODE,
                    COUNTRYID,
                    CURRENTAPPUSERID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                values
                (
                    @BATCHSPONSORSHIPMEMBERID,
                    @EXISTINGCONSTITUENTID,
                    coalesce(@KEYNAME, ''),
                    @FIRSTNAME,
                    @MIDDLENAME,
                    @TITLECODEID,
                    @SUFFIXCODEID,
                    coalesce(@ISORGANIZATION, 0),
                    coalesce(@ISGROUP, 0),
                    coalesce(@GROUPTYPECODE, 0),
                    @COUNTRYID,
                    @CURRENTAPPUSERID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                );

                insert into dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER
                (
                    GROUPID,
                    MEMBERID,
                    ISPRIMARY,
                    COPYGROUPCONTACTINFOTOMEMBER,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                values
                (
                    @BATCHSPONSORSHIPGROUPID,
                    @BATCHSPONSORSHIPMEMBERID,
                    @ISPRIMARYMEMBER,
                    @COPYHOUSEHOLDCONTACT,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                )
            end