USP_BATCHSPONSORSHIP_ADDINDIVIDUALHOUSEHOLD

Creates the sponsorship batch household that is specified on the add/edit sponsorship batch individual forms.
Parameters

Parameter	Parameter Type	Mode
@BATCHCONSTITUENTID	uniqueidentifier	IN
@BATCHSPOUSEID	uniqueidentifier	IN
@HOUSEHOLDCOPYPRIMARYCONTACTINFO	bit	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
Definition

Copy


            create procedure dbo.USP_BATCHSPONSORSHIP_ADDINDIVIDUALHOUSEHOLD
            (
                @BATCHCONSTITUENTID uniqueidentifier,
                @BATCHSPOUSEID uniqueidentifier,
                @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit,
                @CURRENTAPPUSERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
                set nocount on;

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

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

                declare @HOUSEHOLDNAME nvarchar(100), @HOUSEHOLDID uniqueidentifier, @USEHOUSEHOLDID bit
                exec dbo.USP_BATCHSPONSORSHIP_GETHOUSEHOLDNAMEORID @BATCHCONSTITUENTID, @BATCHSPOUSEID, @HOUSEHOLDID output, @HOUSEHOLDNAME output, @USEHOUSEHOLDID output

                -- Insert the household

                declare @BATCHHOUSEHOLDID uniqueidentifier
                set @BATCHHOUSEHOLDID = newid()

                insert into dbo.BATCHSPONSORSHIPCONSTITUENT
                (
                    [ID],
                    [EXISTINGCONSTITUENTID],
                    [ISGROUP],
                    [GROUPTYPECODE],
                    [KEYNAME],
                    [ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED]
                )
                values
                (
                    @BATCHHOUSEHOLDID,
                    case when @USEHOUSEHOLDID = 1 then @HOUSEHOLDID else null end,
                    1,
                    0,
                    case when @USEHOUSEHOLDID = 0 then @HOUSEHOLDNAME else '' end,
                    @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
                )

                -- Create membership in the household for the new constituent

                insert into dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER
                (
                    ID, 
                    GROUPID, 
                    MEMBERID, 
                    ISPRIMARY, 
                    COPYMEMBERCONTACTINFOTOGROUP, 
                    [ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED])
                values 
                (
                    newid(), 
                    @BATCHHOUSEHOLDID, 
                    @BATCHCONSTITUENTID, 
                    0, 
                    coalesce(@HOUSEHOLDCOPYPRIMARYCONTACTINFO, 0), 
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                )

                -- If this is an existing household, then the spouse already is a member

                -- and so they shouldn't be added as a member again.  Otherwise, add them as

                -- a member.

                if @HOUSEHOLDID is null and @BATCHSPOUSEID is not null
                    insert into dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER
                    (
                        ID,
                        GROUPID,
                        MEMBERID,
                        ISPRIMARY,
                        [ADDEDBYID],[CHANGEDBYID],[DATEADDED],[DATECHANGED]
                    )
                    values 
                    (
                        newid(),
                        @BATCHHOUSEHOLDID,
                        @BATCHSPOUSEID,
                        0,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                    )