USP_WPRELATIONSHIP_NPA_IND_ADD_BULK

        create procedure dbo.[USP_WPRELATIONSHIP_NPA_IND_ADD_BULK] (
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTAPPUSERID uniqueidentifier = null
        ) as
        begin
        set nocount on;

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

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

        begin try
            ;with DUPECTE as (
                select RN = row_number() over(partition by
                     [ID]
                    ,[CONSTITUENTID]
                    ,[EIN]
                    ,[FIRSTNAME]
                    ,[MIDDLE]
                    ,[LASTNAME]
                    ,[SUFFIX]
                    ,[TITLE]
                    ,[CITY]
                    ,[STATE]
                    ,[ZIP]
                    ,[LASTDATE]
                    ,[YROBSERVE]
                    ,[SPOUSEFLAG]
                    ,[MATCHPROSPECT]
                    ,[MATCHPRIMARYBUSINESS]
                    ,[EXECCOUNT] order by ID
                    )
              from dbo.[#BULK_WPRELATIONSHIP_NPA_IND]
            )
            delete from DUPECTE where RN > 1;

            update T set
                EXECCOUNT = EC.EXECCOUNT
            from
                dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
            inner join (
                select
                    TEIN.EIN,
                    TEIN.EXECCOUNT
                from
                    dbo.[#BULK_WPRELATIONSHIP_NPA_IND] TEIN
                where
                    TEIN.ROWNUM = (select max(TS.ROWNUM) from dbo.[#BULK_WPRELATIONSHIP_NPA_IND] TS where TS.EIN=TEIN.EIN)) EC
            on
                EC.EIN=T.EIN
            where
                T.EXECCOUNT <> EC.EXECCOUNT;

            if exists (select EIN,FIRSTNAME,LASTNAME,MIDDLE,SUFFIX from dbo.[#BULK_WPRELATIONSHIP_NPA_IND] group by EIN,FIRSTNAME,LASTNAME,MIDDLE,SUFFIX having count(*) > 1)
            begin
                declare @ID uniqueidentifier;
                declare @CONSTITUENTID uniqueidentifier;
                declare @EIN nvarchar(30);
                declare @FIRSTNAME nvarchar(50);
                declare @MIDDLE nvarchar(50);
                declare @LASTNAME nvarchar(100);
                declare @SUFFIX nvarchar(50);
                declare @TITLE nvarchar(100);
                declare @CITY nvarchar(50);
                declare @STATE nvarchar(2);
                declare @ZIP nvarchar(10);
                declare @LASTDATE datetime;
                declare @YROBSERVE nvarchar(100);
                declare @SPOUSEFLAG bit;
                declare @MATCHPROSPECT bit;
                declare @MATCHPRIMARYBUSINESS bit;
                declare @EXECCOUNT int;

                declare cur cursor local fast_forward for
                select T.[ID],T.[CONSTITUENTID],T.[EIN],T.[FIRSTNAME],T.[MIDDLE],T.[LASTNAME],T.[SUFFIX],T.[TITLE],T.[CITY],T.[STATE],T.[ZIP],T.[LASTDATE],T.[YROBSERVE],T.[SPOUSEFLAG],T.[MATCHPROSPECT],T.[MATCHPRIMARYBUSINESS],T.[EXECCOUNT]
                from
                    dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
                inner join
                    (select TS.EIN,TS.FIRSTNAME,TS.LASTNAME,TS.MIDDLE,TS.SUFFIX from dbo.[#BULK_WPRELATIONSHIP_NPA_IND] TS group by TS.EIN,TS.FIRSTNAME,TS.LASTNAME,TS.MIDDLE,TS.SUFFIX having count(*) > 1) D
                on
                    D.EIN = T.EIN and D.FIRSTNAME = T.FIRSTNAME and D.LASTNAME = T.LASTNAME and D.MIDDLE = T.MIDDLE and D.SUFFIX = T.SUFFIX
                order by T.ROWNUM;

                open cur;
                fetch next from cur into @ID,@CONSTITUENTID,@EIN,@FIRSTNAME,@MIDDLE,@LASTNAME,@SUFFIX,@TITLE,@CITY,@STATE,@ZIP,@LASTDATE,@YROBSERVE,@SPOUSEFLAG,@MATCHPROSPECT,@MATCHPRIMARYBUSINESS,@EXECCOUNT;

                while @@FETCH_STATUS = 0
                begin
                    exec dbo.USP_WPRELATIONSHIP_NPA_IND_ADD
                        @ID=@ID,
                        @CHANGEAGENTID=@CHANGEAGENTID,
                        @CONSTITUENTID=@CONSTITUENTID,
                        @LASTNAME=@LASTNAME,
                        @FIRSTNAME=@FIRSTNAME,
                        @MIDDLE=@MIDDLE,
                        @SUFFIX=@SUFFIX,
                        @TITLE=@TITLE,
                        @CITY=@CITY,
                        @STATE=@STATE,
                        @ZIP=@ZIP,
                        @LASTDATE=@LASTDATE,
                        @YROBSERVE=@YROBSERVE,
                        @EIN=@EIN,
                        @SPOUSEFLAG=@SPOUSEFLAG,
                        @MATCHPROSPECT=@MATCHPROSPECT,
                        @MATCHPRIMARYBUSINESS=@MATCHPRIMARYBUSINESS,
                        @EXECCOUNT=@EXECCOUNT;

                    fetch next from cur into @ID,@CONSTITUENTID,@EIN,@FIRSTNAME,@MIDDLE,@LASTNAME,@SUFFIX,@TITLE,@CITY,@STATE,@ZIP,@LASTDATE,@YROBSERVE,@SPOUSEFLAG,@MATCHPROSPECT,@MATCHPRIMARYBUSINESS,@EXECCOUNT;
                end

                close cur;
                deallocate cur;

                delete T
                from
                    dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
                inner join
                    (select TS.EIN,TS.FIRSTNAME,TS.LASTNAME,TS.MIDDLE,TS.SUFFIX from dbo.[#BULK_WPRELATIONSHIP_NPA_IND] TS group by TS.EIN,TS.FIRSTNAME,TS.LASTNAME,TS.MIDDLE,TS.SUFFIX having count(*) > 1) D
                on
                    D.EIN = T.EIN and D.FIRSTNAME = T.FIRSTNAME and D.LASTNAME = T.LASTNAME and D.MIDDLE = T.MIDDLE and D.SUFFIX = T.SUFFIX;
            end

            update RNPA set
                EXECCOUNT = T.EXECCOUNT,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
                dbo.WPRELATIONSHIP_NPA RNPA
            inner join
                (select distinct TS.EIN, TS.EXECCOUNT from dbo.[#BULK_WPRELATIONSHIP_NPA_IND] TS) T
            on
                RNPA.EIN = T.EIN and
                RNPA.EXECCOUNT <> T.EXECCOUNT;

            insert into dbo.WPRELATIONSHIP_NPA
                (
                EIN,
                EXECCOUNT,
                CHANGEDBYID,
                ADDEDBYID,
                DATEADDED,
                DATECHANGED
                )
            select distinct
                T.EIN,
                T.EXECCOUNT,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from
                dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
            where
                not exists (select * from dbo.WPRELATIONSHIP_NPA RNPA where RNPA.EIN = T.EIN);

            update T set
                SPOUSEID = R.RECIPROCALCONSTITUENTID
            from
                dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
            inner join
                dbo.RELATIONSHIP R
            on
                R.RELATIONSHIPCONSTITUENTID = T.CONSTITUENTID and
                R.ISSPOUSE = 1
            where
                T.SPOUSEFLAG = '1';

            update RNPA set
                CONSTITUENTID = R.RECIPROCALCONSTITUENTID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
                dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
            inner join
                dbo.WPRELATIONSHIP_NPA RNPA
            on
                RNPA.EIN = T.EIN and
                RNPA.CONSTITUENTID is null
            inner join
                dbo.RELATIONSHIP R
            on
                R.RELATIONSHIPCONSTITUENTID = coalesce(T.SPOUSEID, T.CONSTITUENTID) and
                R.ISPRIMARYBUSINESS = 1
            where
                T.MATCHPRIMARYBUSINESS = '1';

            update RNPAI set
                CONSTITUENTID = case when T.MATCHPROSPECT = '1' then coalesce(RNPAI.CONSTITUENTID, T.SPOUSEID, T.CONSTITUENTID) else RNPAI.CONSTITUENTID end,
                LASTNAME = T.LASTNAME,
                FIRSTNAME = T.FIRSTNAME,
                MIDDLE = T.MIDDLE,
                SUFFIX = T.SUFFIX,
                TITLE = left(T.TITLE, 100),
                CITY = T.CITY,
                [STATE] = T.[STATE],
                ZIP = T.ZIP,
                LASTDATE = T.LASTDATE,
                YROBSERVE = T.YROBSERVE,
                SPOUSEFLAG = T.SPOUSEFLAG,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
                dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
            inner join
                dbo.WPRELATIONSHIP_NPA RNPA
            on
                RNPA.EIN = T.EIN
            inner join
                dbo.WPRELATIONSHIP_NPA_IND RNPAI
            on
                RNPAI.WPRELATIONSHIP_NPA_ID = RNPA.ID and
                RNPAI.FIRSTNAME = T.FIRSTNAME and
                RNPAI.LASTNAME = T.LASTNAME and
                RNPAI.MIDDLE = T.MIDDLE and
                RNPAI.SUFFIX = T.SUFFIX
            where
                (T.MATCHPROSPECT = '1' and
                RNPAI.CONSTITUENTID is null and
                (T.CONSTITUENTID is not null or T.SPOUSEID is not null)) or
                (RNPAI.TITLE <> left(T.TITLE, 100)) or
                (RNPAI.CITY <> T.CITY) or
                (RNPAI.[STATE] <> T.[STATE]) or
                (RNPAI.ZIP <> T.ZIP) or
                (RNPAI.LASTDATE <> T.LASTDATE and (RNPAI.LASTDATE is not null or T.LASTDATE is not null)) or
                (RNPAI.YROBSERVE <> T.YROBSERVE) or
                (RNPAI.SPOUSEFLAG <> T.SPOUSEFLAG);

            insert into dbo.WPRELATIONSHIP_NPA_IND
            (
                CONSTITUENTID,
                LASTNAME,
                FIRSTNAME,
                MIDDLE,
                SUFFIX,
                TITLE,
                CITY,
                [STATE],
                ZIP,
                LASTDATE,
                YROBSERVE,
                WPRELATIONSHIP_NPA_ID,
                SPOUSEFLAG,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                case when T.MATCHPROSPECT = '1' then coalesce(T.SPOUSEID, T.CONSTITUENTID) else null end,
                T.LASTNAME,
                T.FIRSTNAME,
                T.MIDDLE,
                T.SUFFIX,
                left(T.TITLE, 100),
                T.CITY,
                T.[STATE],
                T.ZIP,
                T.LASTDATE,
                T.YROBSERVE,
                RNPA.ID,
                T.SPOUSEFLAG,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from
                dbo.[#BULK_WPRELATIONSHIP_NPA_IND] T
            inner join
                dbo.WPRELATIONSHIP_NPA RNPA
            on
                RNPA.EIN = T.EIN
            where not exists (
                                select * from
                                    dbo.WPRELATIONSHIP_NPA_IND RNPAI
                                where
                                    RNPAI.WPRELATIONSHIP_NPA_ID = RNPA.ID and
                                    RNPAI.FIRSTNAME = T.FIRSTNAME and
                                    RNPAI.LASTNAME = T.LASTNAME and
                                    RNPAI.MIDDLE = T.MIDDLE and
                                    RNPAI.SUFFIX = T.SUFFIX);
        end try
        begin catch
            exec dbo.USP_RAISE_ERROR;
        end catch

        return 0;
        end