UFN_COAUPDATE_GETMATCHINGHOUSEHOLDS

            CREATE function dbo.UFN_COAUPDATE_GETMATCHINGHOUSEHOLDS
            (
                @INCLUDECONSTITUENTDNM bit,
                @ISPRIMARY bit,
                @ADDRESSTYPECODEID uniqueidentifier,
                @INCLUDEADDRESSESDNM bit,
                @SELECTIONID uniqueidentifier,
                @OWNERID uniqueidentifier
            ) returns @MATCHING table(ADDRESSID uniqueidentifier,
                                        CONSTITUENTID uniqueidentifier,
                                        GROUPID uniqueidentifier,
                                        ISPRIMARY bit,
                                        ADDRESSBLOCK nvarchar(150),
                                        CITY nvarchar(100),
                                        POSTCODE nvarchar(12),
                                        STATEID uniqueidentifier,
                                        ADDRESSTYPECODEID uniqueidentifier)as
            begin
                declare @CURRENTDATE datetime;
                set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                declare @SELECTION table (ADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, groupid uniqueidentifier);

                declare @BYPASSSECURITY bit;
                declare @BPID uniqueidentifier;
                declare @BYPASSSITESECURITY bit;
                set @BPID = '113412a7-daa5-4ef6-9c0d-8aaa9024e06a';
                set @BYPASSSECURITY = 0;
                set @BYPASSSITESECURITY = 0;

                declare @ISADMIN bit;
                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);
                declare @APPUSER_IN_NONSITEROLE bit;
                set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@OWNERID);
                declare @APPUSER_IN_NOSITEROLE bit;                
                set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@OWNERID);

                if @OWNERID is not null
                begin
                    select @BYPASSSECURITY = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);

                    if @BYPASSSECURITY = 0 
                        begin
                            select @BYPASSSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE(@OWNERID, @BPID);
                            select @BYPASSSITESECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONSITEROLE(@OWNERID, @BPID);                        
                        end
                    else
                        set @BYPASSSITESECURITY = 1;
                end
                else
                    begin
                        set @BYPASSSECURITY = 1;
                        set @BYPASSSITESECURITY = 1;
                    end

                -- create selection of addresses

                if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
                begin
                    if @SELECTIONID is null or @SELECTIONID = '00000000-0000-0000-0000-000000000000'
                        insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
                        (select ADDRESS.ID,
                            CONSTITUENT.ID,
                            GROUPMEMBER.GROUPID
                        from dbo.CONSTITUENT
                        left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
                        left join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                        inner join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                        inner join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID and COUNTRY.USEFORCOAUPDATE = 1
                        left join dbo.STATE on ADDRESS.STATEID = STATE.ID
                        where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0)
                            and (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                            and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null)
                            and (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0)
                            and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT))
                            and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                            and (@BYPASSSITESECURITY = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@OWNERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                        );
                    else
                        insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
                        (select ADDRESS.ID,
                            CONSTITUENT.ID,
                            GROUPMEMBER.GROUPID
                        from dbo.CONSTITUENT
                        inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on CONSTITUENT.ID = SELECTION.ID
                        left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
                        left join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                        inner join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                        inner join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID and COUNTRY.USEFORCOAUPDATE = 1
                        left join dbo.STATE on ADDRESS.STATEID = STATE.ID
                        where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0)
                            and (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                            and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null)
                            and (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0)
                            and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT))
                            and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
                            and (@BYPASSSITESECURITY = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@OWNERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)

                        );                            
                end
                else
                begin
                    if @SELECTIONID is null or @SELECTIONID = '00000000-0000-0000-0000-000000000000'
                        insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
                        (select ADDRESS.ID,
                            CONSTITUENT.ID,
                            GROUPMEMBER.GROUPID
                        from dbo.CONSTITUENT
                        left join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                        inner join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                        inner join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID and COUNTRY.USEFORCOAUPDATE = 1
                        left join dbo.STATE on ADDRESS.STATEID = STATE.ID
                        where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0)
                            and (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                            and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null)
                            and (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0)
                            and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT)));
                    else
                        insert into @SELECTION(ADDRESSID, CONSTITUENTID, GROUPID)
                        (select ADDRESS.ID,
                            CONSTITUENT.ID,
                            GROUPMEMBER.GROUPID
                        from dbo.CONSTITUENT
                        inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on CONSTITUENT.ID = SELECTION.ID
                        left join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                        inner join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID
                        inner join dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID and COUNTRY.USEFORCOAUPDATE = 1
                        left join dbo.STATE on ADDRESS.STATEID = STATE.ID
                        where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0)
                            and (ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0)
                            and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null)
                            and (ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0)
                            and (CONSTITUENT.ID not in(select ID from dbo.DECEASEDCONSTITUENT)));
                end

                -- AaronCr 04/24/15 - WI#490340: The code below that used to return all household member addresses now only includes

                -- shared household addresses. These are only used to filter out addresses that will be submitted as a single address.


                -- find all household records in address selection

                declare @SELECTIONHOUSEHOLDS table (CONSTITUENTID uniqueidentifier, ADDRESSBLOCK nvarchar(150), CITY nvarchar(100), POSTCODE nvarchar(12), STATEID uniqueidentifier, ADDRESSTYPECODEID uniqueidentifier);
                insert into @SELECTIONHOUSEHOLDS
                (CONSTITUENTID, ADDRESSBLOCK, CITY, POSTCODE, STATEID, ADDRESSTYPECODEID)
                (select
                    ADDRESS.CONSTITUENTID,
                    ADDRESS.ADDRESSBLOCK,
                    ADDRESS.CITY,
                    ADDRESS.POSTCODE,
                    ADDRESS.STATEID,
                    ADDRESS.ADDRESSTYPECODEID
                from dbo.ADDRESS
                inner join @SELECTION SELECTION on ADDRESS.ID = SELECTION.ADDRESSID
                inner join dbo.CONSTITUENT on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
                where CONSTITUENT.ISGROUP = 1)

                -- Now add all shared addresses with the household records

                insert into @MATCHING
                (ADDRESSID, CONSTITUENTID, GROUPID, ISPRIMARY, ADDRESSBLOCK, CITY, POSTCODE, STATEID, ADDRESSTYPECODEID)
                (select distinct
                    ADDRESS.ID,
                    ADDRESS.CONSTITUENTID,
                    coalesce(SELECTION.GROUPID, ADDRESS.CONSTITUENTID),
                    ADDRESS.ISPRIMARY,
                    ADDRESS.ADDRESSBLOCK,
                    ADDRESS.CITY,
                    ADDRESS.POSTCODE,
                    ADDRESS.STATEID,
                    ADDRESS.ADDRESSTYPECODEID
                from @SELECTIONHOUSEHOLDS HOUSEHOLD
                inner join @SELECTION SELECTION on (SELECTION.GROUPID = HOUSEHOLD.CONSTITUENTID or SELECTION.CONSTITUENTID = HOUSEHOLD.CONSTITUENTID)
                inner join dbo.ADDRESS on SELECTION.ADDRESSID = ADDRESS.ID
                where ( (ADDRESS.ADDRESSBLOCK = HOUSEHOLD.ADDRESSBLOCK)
                    and (ADDRESS.CITY = HOUSEHOLD.CITY)
                    and ( (ADDRESS.STATEID = HOUSEHOLD.STATEID) or (ADDRESS.STATEID is null and HOUSEHOLD.STATEID is null) )
                    and (ADDRESS.POSTCODE = HOUSEHOLD.POSTCODE)
                    and ( (ADDRESS.ADDRESSTYPECODEID = HOUSEHOLD.ADDRESSTYPECODEID) or (ADDRESS.ADDRESSTYPECODEID is null and HOUSEHOLD.ADDRESSTYPECODEID is null) ))
                );

                return;
            end