USP_RELATIONSHIP_SETUPHOUSEHOLD

            CREATE procedure dbo.USP_RELATIONSHIP_SETUPHOUSEHOLD
            (
                @CONSTITUENTID uniqueidentifier,
                @SECONDMEMBERID uniqueidentifier,
                @COPYCONTACTINFO bit,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @SKIP_ADDING_SECURITYGROUPS bit = 0,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SITESECURITYDATAFORMTEMPLATEID uniqueidentifier = null,
                @SKIP_ADDING_SITES bit = 0,
                @COPYEMAILADDRESS bit = 1,
                @COPYPHONENUMBER bit = 1,
                @COPYCONTACTINFOINDIVIDUAL bit = 1
            )
            as
                set nocount on;

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

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate()

                declare @CONSTITUENTHOUSEHOLDID uniqueidentifier;
                declare @SECONDMEMBERHOUSEHOLDID uniqueidentifier;     
              declare @EXISTINGID uniqueidentifier;
        declare @EXISTINGDATERANGEID uniqueidentifier;
        declare @SECONDMEMBEREXISTINGID uniqueidentifier;
        declare @SECONDMEMBEREXISTINGDATERANGEID uniqueidentifier;

                select 
                    @CONSTITUENTHOUSEHOLDID = GD.ID
                from
                    dbo.GROUPMEMBER GM
                left outer join
                    dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                left outer join
                    dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                where
                    GM.MEMBERID = @CONSTITUENTID
                and
                    GD.GROUPTYPECODE = 0
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CHANGEDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CHANGEDATE)) 
                    or (GMDR.DATEFROM <= @CHANGEDATE and GMDR.DATETO > @CHANGEDATE));    

                select
                    @SECONDMEMBERHOUSEHOLDID = GD.ID
                from
                    dbo.GROUPMEMBER GM
                left outer join
                    dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                left outer join
                    dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                where
                    GM.MEMBERID = @SECONDMEMBERID
                and
                    GD.GROUPTYPECODE = 0
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CHANGEDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CHANGEDATE)) 
                    or (GMDR.DATEFROM <= @CHANGEDATE and GMDR.DATETO > @CHANGEDATE));    

      --Find the existing groupmember record and groupmemberdaterange for the constituents if they exist


        select
          @EXISTINGID = ID
        from
          dbo.GROUPMEMBER
        where
          GROUPID = @SECONDMEMBERHOUSEHOLDID and
          MEMBERID = @CONSTITUENTID;

        select top 1
           @EXISTINGDATERANGEID = ID
        from
          dbo.GROUPMEMBERDATERANGE 
        where
          GROUPMEMBERID = @EXISTINGID
        order by 
          case
            when DATETO is null then 0
          else 1
         end, 
         DATETO desc

        select
          @SECONDMEMBEREXISTINGID = ID
        from
          dbo.GROUPMEMBER
        where
          GROUPID = @CONSTITUENTHOUSEHOLDID and
          MEMBERID = @SECONDMEMBERID;

        select top 1
           @SECONDMEMBEREXISTINGDATERANGEID = ID
        from
          dbo.GROUPMEMBERDATERANGE 
        where
          GROUPMEMBERID = @EXISTINGID
        order by 
          case
            when DATETO is null then 0
          else 1
         end, 
         DATETO desc

                declare @GROUPMEMBERID uniqueidentifier;
                if (@CONSTITUENTHOUSEHOLDID is not null) begin
                    if (@SECONDMEMBERHOUSEHOLDID is null)begin
            if (@SECONDMEMBEREXISTINGID is null and @SECONDMEMBEREXISTINGDATERANGEID is null)
                         exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @CONSTITUENTHOUSEHOLDID, @SECONDMEMBERID;    
            end
          end 
                else begin
                    if (@SECONDMEMBERHOUSEHOLDID is null) begin
                        declare @HOUSEHOLDID uniqueidentifier;
                        set @HOUSEHOLDID = newid();

                        declare @NAMEFORMATFUNCTIONID uniqueidentifier;
                        select top(1) @NAMEFORMATFUNCTIONID = NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED;

                        declare @HOUSEHOLDNAME nvarchar(100);
                        select @HOUSEHOLDNAME = left(dbo.UFN_NAMEFORMAT_FROMID(@NAMEFORMATFUNCTIONID, @CONSTITUENTID), 100);

                        insert into dbo.CONSTITUENT
                        (
                            ID, 
                            KEYNAME, 
                            ISORGANIZATION, 
                            ISGROUP,
                            ISCONSTITUENT,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @HOUSEHOLDID, 
                            @HOUSEHOLDNAME, 
                            0, -- ISORGANIZATION

                            1, -- ISGROUP

                            1, -- ISCONSTITUENT

                            @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                        );

                        /*Start security groups*/
                        if COALESCE(@SKIP_ADDING_SECURITYGROUPS,0) = 0
                            begin
                                exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
                                        @APPUSERID =@CURRENTAPPUSERID,
                                        @DATAFORMTEMPLATEID='9d3aff90-42d0-4db0-a4c1-703d25fd1902',
                                        @CONSTITUENTID  =@HOUSEHOLDID,
                                        @DATEADDEDTOUSE =@CHANGEDATE,
                                        @CHANGEAGENTID = @CHANGEAGENTID;
                            end
                        /*end security groups*/    

                        if coalesce(@SKIP_ADDING_SITES,0) = 0
                            exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
                                @APPUSERID = @CURRENTAPPUSERID,
                                @DATAFORMTEMPLATEID = @SITESECURITYDATAFORMTEMPLATEID,
                                @CONSTITUENTID = @HOUSEHOLDID,
                                @DATEADDEDTOUSE = @CHANGEDATE,
                                @CHANGEAGENTID = @CHANGEAGENTID;

                        insert into dbo.GROUPDATA
                        (
                            ID,
                            GROUPTYPECODE,
                            STARTDATE,
                            GROUPTYPEID,
                            NAMEFORMATFUNCTIONID,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @HOUSEHOLDID,
                            0,
                            @CHANGEDATE,
                            null,
                            @NAMEFORMATFUNCTIONID,
                            @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                        );

                        if @COPYCONTACTINFO = 1 
                            exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @CONSTITUENTID, @HOUSEHOLDID;
                        if @COPYCONTACTINFOINDIVIDUAL = 1
                            exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @CONSTITUENTID, @SECONDMEMBERID, @COPYEMAILADDRESS, @COPYPHONENUMBER;
                        exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @CONSTITUENTID;

                        update dbo.GROUPMEMBER 
                        set 
                            ISPRIMARY = 1,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where 
                            GROUPMEMBER.ID = @GROUPMEMBERID;

                        set @GROUPMEMBERID = null;
                        exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @SECONDMEMBERID;
                    end
                    else begin
             if (@EXISTINGID is null and @EXISTINGDATERANGEID is null)
                           exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @SECONDMEMBERHOUSEHOLDID, @CONSTITUENTID;
             end
                    end