USP_DATAFORMTEMPLATE_EDIT_PHONE_2

The save procedure used by the edit dataform template "Phone Edit Form 2".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@PHONETYPECODEID	uniqueidentifier	IN	Type
@NUMBER	nvarchar(100)	IN	Number
@PRIMARY	bit	IN	Set as primary phone number
@DONOTCALL	bit	IN	Do not call this phone number
@UPDATEMATCHINGSPOUSEPHONE	bit	IN	Update matching phone information for household
@UPDATEMATCHINGHOUSEHOLDPHONE	bit	IN	Update matching phone numbers in household
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PHONE_2
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @PHONETYPECODEID uniqueidentifier,
                    @NUMBER nvarchar(100),
                    @PRIMARY bit,
                    @DONOTCALL bit,
                    @UPDATEMATCHINGSPOUSEPHONE bit,
                    @UPDATEMATCHINGHOUSEHOLDPHONE bit
                )
                as
                    set nocount on;

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

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

                    declare @CONSTITUENTID uniqueidentifier;
                    declare @OLDNUMBER nvarchar(100);
                    declare @OLDPHONETYPECODEID uniqueidentifier;

                    -- @UPDATEMATCHINGSPOUSEPHONE has been deprecated in favor of updating all of the members of a household

                    set @UPDATEMATCHINGHOUSEHOLDPHONE = case when @UPDATEMATCHINGSPOUSEPHONE = 1 then 1 else @UPDATEMATCHINGHOUSEHOLDPHONE end;

                    select
                        @CONSTITUENTID = PHONE.[CONSTITUENTID],
                        @OLDNUMBER = PHONE.[NUMBER],
                        @OLDPHONETYPECODEID = PHONE.[PHONETYPECODEID]
                    from
                        dbo.[PHONE]
                    where
                        PHONE.[ID] = @ID;

                    begin try
                        if @PRIMARY = 1
                            update
                                dbo.[PHONE] 
                            set
                                ISPRIMARY = 0,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where
                                CONSTITUENTID = @CONSTITUENTID and
                                ISPRIMARY = 1 and
                                ID <> @ID;

                        update
                            dbo.PHONE
                        set
                            PHONETYPECODEID = @PHONETYPECODEID,
                            NUMBER = @NUMBER,
                            ISPRIMARY = @PRIMARY,
                            DONOTCALL = @DONOTCALL,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where
                            ID = @ID;

                        if @UPDATEMATCHINGHOUSEHOLDPHONE = 1 begin
                            declare @MATCHINGCONSTITUENTS table(ID uniqueidentifier);
                            insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_PHONE_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @OLDNUMBER, @OLDPHONETYPECODEID);

                            -- remove primary indicator if we're going to update with a new one

                            if @PRIMARY = 1
                                update dbo.PHONE
                                set
                                    ISPRIMARY = 0,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where
                                    CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);

                            -- update the existing records

                            update dbo.PHONE
                            set
                                PHONETYPECODEID = @PHONETYPECODEID,
                                NUMBER = @NUMBER,
                                ISPRIMARY = @PRIMARY,
                                DONOTCALL = @DONOTCALL,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where
                                NUMBER = @OLDNUMBER
                                and ( (PHONETYPECODEID = @OLDPHONETYPECODEID) or (@OLDPHONETYPECODEID is null and PHONETYPECODEID is null) )
                                and CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);
                        end
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;