USP_WPRELATIONSHIP_NPA_IND_ADD

Adds WealthPoint Nonprofit Affiliation relationship individual information.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@LASTNAME	nvarchar(100)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLE	nvarchar(50)	IN
@SUFFIX	nvarchar(50)	IN
@TITLE	nvarchar(100)	IN
@CITY	nvarchar(50)	IN
@STATE	nvarchar(2)	IN
@ZIP	nvarchar(10)	IN
@LASTDATE	datetime	IN
@YROBSERVE	nvarchar(100)	IN
@EIN	nvarchar(30)	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@SPOUSEFLAG	bit	IN
@MATCHPROSPECT	bit	IN
@MATCHPRIMARYBUSINESS	bit	IN
@EXECCOUNT	int	IN
Definition

Copy


            CREATE procedure dbo.USP_WPRELATIONSHIP_NPA_IND_ADD
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @LASTNAME nvarchar(100) = '',
                @FIRSTNAME nvarchar(50) = '',
                @MIDDLE nvarchar(50) = '',
                @SUFFIX nvarchar(50) = '',
                @TITLE nvarchar(100) = '',
                @CITY nvarchar(50) = '',
                @STATE nvarchar(2) = '',
                @ZIP nvarchar(10) = '',
                @LASTDATE datetime = null,
                @YROBSERVE nvarchar(100) = '',
                @EIN nvarchar(30) = '',
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SPOUSEFLAG bit = 0,
                @MATCHPROSPECT bit = 0,
                @MATCHPRIMARYBUSINESS bit = 0,
                @EXECCOUNT int = 0
            )
            as
            begin

                set nocount on;

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

                begin try
                    declare @NEWRECORD bit;
                    declare @UPDATEDRECORD bit;
                    declare @CURRENTDATE datetime;

                    set @NEWRECORD = 0;
                    set @UPDATEDRECORD = 0;
                    set @CURRENTDATE = getdate();

                    declare @WPRELATIONSHIP_NPA_ID uniqueidentifier;

                    --Get common nonprofit association record ID

                    exec dbo.USP_WPRELATIONSHIP_NPA_ADD @ID = @WPRELATIONSHIP_NPA_ID output, @EIN = @EIN, @CHANGEAGENTID = @CHANGEAGENTID, @EXECCOUNT = @EXECCOUNT;

                    --Set constituentID to spouse ID if spouse flag set

                    if @SPOUSEFLAG = 1 and @CONSTITUENTID is not null begin
                        select
                            @CONSTITUENTID = RECIPROCALCONSTITUENTID
                        from
                            dbo.RELATIONSHIP
                        where
                            RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and ISSPOUSE = 1
                    end

                    --Get the primary business

                    declare @PRIMARYBUSINESSID uniqueidentifier;
                    select
                        @PRIMARYBUSINESSID = REL.RECIPROCALCONSTITUENTID
                    from
                        dbo.RELATIONSHIP REL
                    where
                        REL.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                        REL.ISPRIMARYBUSINESS = 1

                    update
                        dbo.WPRELATIONSHIP_NPA
                    set
                        CONSTITUENTID = @PRIMARYBUSINESSID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where
                        ID = @WPRELATIONSHIP_NPA_ID and
                        @MATCHPRIMARYBUSINESS = 1 and
                        CONSTITUENTID is null and
                        @PRIMARYBUSINESSID is not null;

                    --Get any existing individual ID

                    select 
                        @ID = RNPAI.ID 
                    from 
                        dbo.WPRELATIONSHIP_NPA RNPA
                    left join dbo.WPRELATIONSHIP_NPA_IND RNPAI
                        on RNPA.ID = RNPAI.WPRELATIONSHIP_NPA_ID
                    where 
                        RNPA.ID = @WPRELATIONSHIP_NPA_ID and
                        coalesce(RNPAI.FIRSTNAME, '') = coalesce(@FIRSTNAME, '') and
                        coalesce(RNPAI.LASTNAME, '') = coalesce(@LASTNAME, '') and
                        coalesce(RNPAI.MIDDLE, '') = coalesce(@MIDDLE, '') and
                        coalesce(RNPAI.SUFFIX, '') = coalesce(@SUFFIX, '');

                    -- Record exists

                    if @ID is not null begin

                        set @UPDATEDRECORD = 1;

                        update
                            dbo.WPRELATIONSHIP_NPA_IND
                        set
                            CONSTITUENTID = case when @MATCHPROSPECT = 1 then coalesce(CONSTITUENTID, @CONSTITUENTID) else CONSTITUENTID end,
                            LASTNAME = @LASTNAME,
                            FIRSTNAME = @FIRSTNAME,
                            MIDDLE = @MIDDLE,
                            SUFFIX = @SUFFIX,
                            TITLE = @TITLE,
                            CITY = @CITY,
                            STATE = @STATE,
                            ZIP = @ZIP,
                            LASTDATE = @LASTDATE,
                            YROBSERVE = @YROBSERVE,
                            SPOUSEFLAG = @SPOUSEFLAG,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where
                            ID = @ID and (
                                (@MATCHPROSPECT = 1 and
                                CONSTITUENTID is null and
                                @CONSTITUENTID is not null) or
                                (TITLE <> @TITLE and (TITLE is not null or @TITLE is not null)) or
                                (CITY <> @CITY and (CITY is not null or @CITY is not null)) or
                                (STATE <> @STATE and (STATE is not null or @STATE is not null)) or
                                (ZIP <> @ZIP and (ZIP is not null or @ZIP is not null)) or
                                (LASTDATE <> @LASTDATE and (LASTDATE is not null or @LASTDATE is not null)) or
                                (YROBSERVE <> @YROBSERVE and (YROBSERVE is not null or @YROBSERVE is not null)) or
                                (SPOUSEFLAG <> @SPOUSEFLAG and (SPOUSEFLAG is not null or @SPOUSEFLAG is not null))
                            );

                    end
                    else begin
                        --Record is new and needs to be Added

                        set @NEWRECORD = 1;

                        if @ID is null
                            set @ID = newid();

                        insert into dbo.WPRELATIONSHIP_NPA_IND
                        (
                            ID,
                            CONSTITUENTID,
                            LASTNAME,
                            FIRSTNAME,
                            MIDDLE,
                            SUFFIX,
                            TITLE,
                            CITY,
                            STATE,
                            ZIP,
                            LASTDATE,
                            YROBSERVE,
                            WPRELATIONSHIP_NPA_ID,
                            SPOUSEFLAG,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        ) values (
                            @ID,
                            case when @MATCHPROSPECT = 1 then @CONSTITUENTID else null end,
                            coalesce(@LASTNAME, ''),
                            coalesce(@FIRSTNAME, ''),
                            coalesce(@MIDDLE, ''),
                            coalesce(@SUFFIX, ''),
                            coalesce(@TITLE, ''),
                            coalesce(@CITY, ''),
                            coalesce(@STATE, ''),
                            coalesce(@ZIP, ''),
                            @LASTDATE,
                            coalesce(@YROBSERVE, ''),
                            @WPRELATIONSHIP_NPA_ID,
                            @SPOUSEFLAG,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        )
                    end

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                end catch

                return 0;

            end