USP_EXCHANGECONTACTSYNC_CONSTITUENTLIST

            CREATE procedure dbo.USP_EXCHANGECONTACTSYNC_CONSTITUENTLIST
            (
                @PARAMETERSETID uniqueidentifier
            )
            as
                set nocount on;

                declare @APPUSERIDSETREGISTERID uniqueidentifier
                declare @CONSTITUENTIDSETREGISTERID uniqueidentifier
                declare @LASTRUNON datetime
                declare @LIMITTOASSIGNEDCONSTITUENTS bit
                declare @SPECIFICAPPUSERID uniqueidentifier
                declare @APPLYTOCODE tinyint
                declare @CONSTITUENTPAIRINGS table (CONSTITUENTID uniqueidentifier, APPUSERID uniqueidentifier);

                /* Get the current settings for the business process.*/
                select
                    @APPUSERIDSETREGISTERID = EXCHANGECONTACTSYNC.APPUSERIDSETREGISTERID,
                    @CONSTITUENTIDSETREGISTERID = EXCHANGECONTACTSYNC.CONSTITUENTIDSETREGISTERID,
                    @LASTRUNON = EXCHANGECONTACTSYNC.LASTRUNON,
                    @LIMITTOASSIGNEDCONSTITUENTS = EXCHANGECONTACTSYNC.LIMITTOASSIGNEDCONSTITUENTS,
                    @SPECIFICAPPUSERID = EXCHANGECONTACTSYNC.SPECIFICAPPUSERID,
                    @APPLYTOCODE = EXCHANGECONTACTSYNC.APPLYTOCODE
                from
                    dbo.EXCHANGECONTACTSYNC
                where
                    EXCHANGECONTACTSYNC.ID = @PARAMETERSETID

                declare @CONSTITUENTS TABLE (ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);
                INSERT INTO @CONSTITUENTS (ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTIDSETREGISTERID);

                if @APPLYTOCODE = 1 begin
                    declare @APPUSERS TABLE (ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);
                    INSERT INTO @APPUSERS (ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@APPUSERIDSETREGISTERID);
                end;

                /* First build a list of proposed constituent ID and app user ID pairings.*/
                if @LIMITTOASSIGNEDCONSTITUENTS = 1 
                    /* App user must be an owner of a planned step for the constituent */
                    begin
                        if @APPLYTOCODE = 0 -- All users

                            begin
                                insert into @CONSTITUENTPAIRINGS(CONSTITUENTID, APPUSERID) 
                                    select 
                                        CONSTITUENT.ID, 
                                        APPUSER.ID
                                    from
                                        dbo.CONSTITUENT
                                        inner join @CONSTITUENTS as CONSTITUENTSELECTION on CONSTITUENTSELECTION.ID = CONSTITUENT.ID
                                        cross join dbo.APPUSER
                                    where
                                        APPUSER.ID in(select 
                                                                    APPUSER.ID 
                                                                from 
                                                                    dbo.APPUSER 
                                                                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = APPUSER.CONSTITUENTID
                                                                    inner join dbo.INTERACTION on INTERACTION.FUNDRAISERID = CONSTITUENT.ID
                                                                where
                                                                    INTERACTION.CONSTITUENTID = CONSTITUENTSELECTION.ID) and 
                                        CONSTITUENT.ISORGANIZATION = 0
                                    order by 
                                        CONSTITUENT.ID;
                            end                            
                        else if @APPLYTOCODE = 1 -- Selected users

                            begin
                                insert into @CONSTITUENTPAIRINGS(CONSTITUENTID, APPUSERID) 
                                    select 
                                        CONSTITUENT.ID, 
                                        APPUSER.ID
                                    from
                                        dbo.CONSTITUENT
                                        inner join @CONSTITUENTS as CONSTITUENTSELECTION on CONSTITUENTSELECTION.ID = CONSTITUENT.ID
                                        cross join dbo.APPUSER 
                                        inner join @APPUSERS as APPUSERSELECTION on APPUSERSELECTION.ID = APPUSER.ID
                                    where
                                        APPUSER.ID in(select 
                                                                    APPUSER.ID 
                                                                from 
                                                                    dbo.APPUSER 
                                                                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = APPUSER.CONSTITUENTID
                                                                    inner join dbo.INTERACTION on INTERACTION.FUNDRAISERID = CONSTITUENT.ID
                                                                where
                                                                    INTERACTION.CONSTITUENTID = CONSTITUENTSELECTION.ID) and 
                                        CONSTITUENT.ISORGANIZATION = 0
                                    order by 
                                        CONSTITUENT.ID;
                            end
                        else if @APPLYTOCODE = 2 -- Specific user

                            begin
                                insert into @CONSTITUENTPAIRINGS(CONSTITUENTID, APPUSERID) 
                                    select 
                                        CONSTITUENT.ID, 
                                        @SPECIFICAPPUSERID
                                    from
                                        dbo.CONSTITUENT
                                        inner join @CONSTITUENTS as CONSTITUENTSELECTION on CONSTITUENTSELECTION.ID = CONSTITUENT.ID
                                    where
                                        @SPECIFICAPPUSERID in(select 
                                                                    APPUSER.ID 
                                                                from 
                                                                    dbo.APPUSER 
                                                                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = APPUSER.CONSTITUENTID
                                                                    inner join dbo.INTERACTION on INTERACTION.FUNDRAISERID = CONSTITUENT.ID
                                                                where
                                                                    INTERACTION.CONSTITUENTID = CONSTITUENTSELECTION.ID) and 
                                        CONSTITUENT.ISORGANIZATION = 0
                                    order by 
                                        CONSTITUENT.ID;                                    
                            end    
                    end
                else
                    begin
                        if @APPLYTOCODE = 0 -- All users

                            begin
                                insert into @CONSTITUENTPAIRINGS(CONSTITUENTID, APPUSERID) 
                                    select 
                                        CONSTITUENT.ID, 
                                        APPUSER.ID
                                    from
                                        dbo.CONSTITUENT
                                        inner join @CONSTITUENTS as CONSTITUENTSELECTION on CONSTITUENTSELECTION.ID = CONSTITUENT.ID
                                        cross join dbo.APPUSER 
                                    where
                                        CONSTITUENT.ISORGANIZATION = 0
                                    order by 
                                        CONSTITUENT.ID;                                    
                            end
                        else if @APPLYTOCODE = 1 --Selected users

                            begin
                                insert into @CONSTITUENTPAIRINGS(CONSTITUENTID, APPUSERID) 
                                    select 
                                        CONSTITUENT.ID, 
                                        APPUSER.ID
                                    from
                                        dbo.CONSTITUENT
                                        inner join @CONSTITUENTS as CONSTITUENTSELECTION on CONSTITUENTSELECTION.ID = CONSTITUENT.ID
                                        cross join dbo.APPUSER 
                                        inner join @APPUSERS as APPUSERSELECTION on APPUSERSELECTION.ID = APPUSER.ID
                                    where
                                        CONSTITUENT.ISORGANIZATION = 0
                                    order by 
                                        CONSTITUENT.ID;
                            end
                        else if @APPLYTOCODE = 2 -- Specific user

                            begin
                                insert into @CONSTITUENTPAIRINGS(CONSTITUENTID, APPUSERID) 
                                    select 
                                        CONSTITUENT.ID, 
                                        @SPECIFICAPPUSERID
                                    from
                                        dbo.CONSTITUENT
                                        inner join @CONSTITUENTS as CONSTITUENTSELECTION on CONSTITUENTSELECTION.ID = CONSTITUENT.ID
                                    where
                                        CONSTITUENT.ISORGANIZATION = 0
                                    order by 
                                        CONSTITUENT.ID;
                            end                            
                    end                

                /* Return pairings that have either not been created in exchange or where the constituent information has been updated since the last run */
                select 
                    PAIRINGS.CONSTITUENTID, 
                    PAIRINGS.APPUSERID, 
                    APPUSER.USERSID, 
                    coalesce(EXCHANGEUSER.WATERMARK,'') as WATERMARK, 
                    EXCHANGESERVERAPPUSER.EMAILADDRESS,
                    coalesce(EXCHANGESERVERAPPUSER.IDENTIFIERTYPECODE,0) as IDENTIFIERTYPECODE,
                    coalesce(EXCHANGESERVERAPPUSER.EXCHANGESERVERID, (select EXCHANGESERVER.ID from dbo.EXCHANGESERVER where ISDEFAULTSERVER = 1)) as EXCHANGESERVERID
                from
                    @CONSTITUENTPAIRINGS AS PAIRINGS
                    inner join dbo.APPUSER on APPUSER.ID = PAIRINGS.APPUSERID
                    left join dbo.EXCHANGEUSER on EXCHANGEUSER.APPUSERID = PAIRINGS.APPUSERID
                    left join dbo.EXCHANGESERVERAPPUSER on APPUSER.ID = EXCHANGESERVERAPPUSER.APPUSERID
                where
                    @LASTRUNON is null or 
                    not exists(select ID from dbo.EXCHANGECONTACT where EXCHANGECONTACT.CONSTITUENTID = PAIRINGS.CONSTITUENTID AND EXCHANGECONTACT.APPUSERID = PAIRINGS.APPUSERID) or
                    (
                        PAIRINGS.CONSTITUENTID in (select ID from dbo.CONSTITUENT where DATECHANGED > @LASTRUNON) or 
                        PAIRINGS.CONSTITUENTID in (select CONSTITUENTID from dbo.ADDRESS inner join dbo.EXCHANGEADDRESSMAPPING on EXCHANGEADDRESSMAPPING.ADDRESSTYPECODEID = ADDRESS.ADDRESSTYPECODEID where ADDRESS.DATECHANGED > @LASTRUNON) or
                        PAIRINGS.CONSTITUENTID in (select CONSTITUENTID from dbo.PHONE inner join dbo.EXCHANGEPHONEMAPPING on EXCHANGEPHONEMAPPING.PHONETYPECODEID = PHONE.PHONETYPECODEID where PHONE.DATECHANGED > @LASTRUNON) or
                        PAIRINGS.CONSTITUENTID in (select CONSTITUENTID from dbo.EMAILADDRESS inner join dbo.EXCHANGEEMAILADDRESSMAPPING on EXCHANGEEMAILADDRESSMAPPING.EMAILADDRESSTYPECODEID = EMAILADDRESS.EMAILADDRESSTYPECODEID where EMAILADDRESS.DATECHANGED > @LASTRUNON) or
                        PAIRINGS.CONSTITUENTID in (select RELATIONSHIPCONSTITUENTID from dbo.RELATIONSHIP inner join dbo.CONSTITUENT on CONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID where RELATIONSHIP.ISSPOUSE = 1 and CONSTITUENT.DATECHANGED > @LASTRUNON)
                    );