USP_BATCHREVENUE_UPDATEHOUSEHOLDMEMBER

Updates the primary or secondary member of a household when editing the household through revenue batch and the household hasn't been created in the constituent table yet.
Parameters

Parameter	Parameter Type	Mode
@CREATEDBATCHREVENUECONSTITUENTID	uniqueidentifier	INOUT
@HOUSEHOLDID	uniqueidentifier	IN
@ISPRIMARYMEMBER	bit	IN
@CONSTITUENTID	uniqueidentifier	IN
@KEYNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@TITLECODEID	uniqueidentifier	IN
@SUFFIXCODEID	uniqueidentifier	IN
@COPYHOUSEHOLDCONTACT	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_BATCHREVENUE_UPDATEHOUSEHOLDMEMBER
            (
                @CREATEDBATCHREVENUECONSTITUENTID uniqueidentifier = null output,
                @HOUSEHOLDID uniqueidentifier,
                @ISPRIMARYMEMBER bit,
                @CONSTITUENTID uniqueidentifier,
                @KEYNAME nvarchar(100),
                @FIRSTNAME nvarchar(50),
                @MIDDLENAME nvarchar(50),
                @TITLECODEID uniqueidentifier,
                @SUFFIXCODEID uniqueidentifier,
                @COPYHOUSEHOLDCONTACT bit,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
            begin
                set nocount on;

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

                declare @contextCache varbinary(128);

                --cache current context information

                set @contextCache = CONTEXT_INFO();

                --set CONTEXT_INFO to @CHANGEAGENTID

                set CONTEXT_INFO @CHANGEAGENTID;

                declare @CURRENTMEMBERID uniqueidentifier
                select @CURRENTMEMBERID = MEMBERID 
                from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                where GROUPID = @HOUSEHOLDID and ISPRIMARY = @ISPRIMARYMEMBER

                set @KEYNAME = coalesce(@KEYNAME, '')

                if @CURRENTMEMBERID is not null
                begin
                    -- Remove old member

                    if @CONSTITUENTID is null and @KEYNAME = ''
                    begin
                        delete from dbo.BATCHREVENUECONSTITUENTGROUPMEMBER
                        where MEMBERID = @CURRENTMEMBERID and GROUPID = @HOUSEHOLDID

                        -- Clear any relationships the constituent was involved in.  The relationship will be recreated

                        -- in RevenueBatchConstituent.Edit.

                        delete from dbo.BATCHREVENUECONSTITUENTRELATION
                        where CONSTITUENTID = @CURRENTMEMBERID or RELATIONID = @CURRENTMEMBERID

                        delete from dbo.BATCHREVENUECONSTITUENT
                        where ID = @CURRENTMEMBERID
                    end
                    else
                    begin
                        -- Update old member

                        set @CREATEDBATCHREVENUECONSTITUENTID = @CURRENTMEMBERID

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        declare @ISORGANIZATION bit, @ISGROUP bit, @GROUPTYPECODE tinyint
                        if @CONSTITUENTID is not null
                        begin
                            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 = @CONSTITUENTID
                        end
                        else
                        begin
                            set @ISORGANIZATION = 0
                            set @ISGROUP = 0
                            set @GROUPTYPECODE = 0
                        end                        

                        update dbo.BATCHREVENUECONSTITUENT set
                            EXISTINGCONSTITUENTID = @CONSTITUENTID,
                            KEYNAME = @KEYNAME,
                            FIRSTNAME = @FIRSTNAME,
                            MIDDLENAME = @MIDDLENAME,
                            TITLECODEID = @TITLECODEID,
                            SUFFIXCODEID = @SUFFIXCODEID,
                            ISORGANIZATION = coalesce(@ISORGANIZATION, 0),
                            ISGROUP = coalesce(@ISGROUP, 0),
                            GROUPTYPECODE = coalesce(@GROUPTYPECODE, 0),
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @CURRENTMEMBERID

                        update dbo.BATCHREVENUECONSTITUENTGROUPMEMBER set
                            -- Set MEMBERID even though the value hasn't changed so that the check constraints are run

                            -- in case the constituent type changed

                            MEMBERID = @CURRENTMEMBERID, 
                            COPYGROUPCONTACTINFOTOMEMBER = @COPYHOUSEHOLDCONTACT,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where GROUPID = @HOUSEHOLDID and ISPRIMARY = @ISPRIMARYMEMBER
                    end
                end
                else
                begin
                    -- Add new member

                    if @KEYNAME <> '' or @CONSTITUENTID is not null
                        exec dbo.USP_BATCHREVENUE_ADDGROUPMEMBER @CREATEDBATCHREVENUECONSTITUENTID output, @HOUSEHOLDID,
                            @CONSTITUENTID, @KEYNAME, @FIRSTNAME, @MIDDLENAME, @TITLECODEID, @SUFFIXCODEID,
                            @COPYHOUSEHOLDCONTACT, @ISPRIMARYMEMBER, @CHANGEAGENTID;
                end

                --reset CONTEXT_INFO to previous value

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;    
            end