USP_DATAFORMTEMPLATE_EDIT_PHONE_8

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@PHONETYPECODEID	uniqueidentifier	IN
@NUMBER	nvarchar(100)	IN
@PRIMARY	bit	IN
@DONOTCALL	bit	IN
@UPDATEMATCHINGSPOUSEPHONE	bit	IN
@UPDATEMATCHINGHOUSEHOLDPHONE	bit	IN
@STARTTIME	UDT_HOURMINUTE	IN
@ENDTIME	UDT_HOURMINUTE	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@INFOSOURCECOMMENTS	nvarchar(256)	IN
@COUNTRYID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@DONOTCALLREASONCODEID	uniqueidentifier	IN
@ISCONFIDENTIAL	bit	IN
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	IN
@SEASONALSTARTDATE	UDT_MONTHDAY	IN
@SEASONALENDDATE	UDT_MONTHDAY	IN
@DONOTTEXT	bit	IN

Definition

Copy


                create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PHONE_8
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @PHONETYPECODEID uniqueidentifier,
                    @NUMBER nvarchar(100),
                    @PRIMARY bit,
                    @DONOTCALL bit,
                    @UPDATEMATCHINGSPOUSEPHONE bit,
                    @UPDATEMATCHINGHOUSEHOLDPHONE bit,
                    @STARTTIME dbo.UDT_HOURMINUTE,
                    @ENDTIME dbo.UDT_HOURMINUTE,
                    @INFOSOURCECODEID uniqueidentifier,
                    @INFOSOURCECOMMENTS nvarchar(256),
                    @COUNTRYID uniqueidentifier,
                    @STARTDATE date,
                    @ENDDATE date,
                    @DONOTCALLREASONCODEID uniqueidentifier,
                    @ISCONFIDENTIAL bit,
                    @CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier,  -- used by constituent data review
                    @SEASONALSTARTDATE dbo.UDT_MONTHDAY,
                    @SEASONALENDDATE dbo.UDT_MONTHDAY,
                    @DONOTTEXT bit
                )
                as
                    set nocount on;

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

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

                    -- #253763 Check to see if there are duplicate phone numbers.  Same phone type and same phone number.
                    -- If nothing has changed on the edit form we don't want to check for duplicates.  This way if dups already exist and we
                    -- change nothing, we can still save the form without getting an error message.
                    if @ENDDATE is null
                    begin
                        declare @NULLVALUE uniqueidentifier = newid();
                        declare @NULLDATE date = '9999-01-01'
                        declare @NUMBERNOFORMAT nvarchar(100) = coalesce(dbo.UFN_PHONE_REMOVEFORMATTING(@NUMBER),'') 
                        set @NUMBERNOFORMAT = dbo.UFN_PHONE_REMOVECOUNTRYCODE(@NUMBERNOFORMAT, @COUNTRYID);

                        if exists (select 'X' from dbo.PHONE a
                                    inner join dbo.PHONE a2 on a2.CONSTITUENTID = a.CONSTITUENTID and a2.ID <> @ID
                                    where (coalesce(a.NUMBERNOFORMAT,'') <> @NUMBERNOFORMAT
                                            or coalesce(a.PHONETYPECODEID,@NULLVALUE) <> coalesce(@PHONETYPECODEID,@NULLVALUE) 
                                            or coalesce(a.COUNTRYID,@NULLVALUE) <> coalesce(@COUNTRYID,@NULLVALUE)
                                            or coalesce(a.ENDDATE,@NULLDATE) <> coalesce(@ENDDATE,@NULLDATE))
                                    and a.ID = @ID
                                    and a2.ENDDATE is null
                                    and coalesce(a2.NUMBERNOFORMAT,'') = @NUMBERNOFORMAT
                                    and coalesce(a2.PHONETYPECODEID,@NULLVALUE) = coalesce(@PHONETYPECODEID,@NULLVALUE)
                                    and coalesce(a2.COUNTRYID,@NULLVALUE) = coalesce(@COUNTRYID,@NULLVALUE)) 
                                        raiserror('BBERR_PHONENUMBER_DUPLICATENOTALLOWED', 13, 1);
                    end

                    -- @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;

                    exec dbo.USP_PHONE_UPDATE @ID, @CHANGEAGENTID, @CURRENTDATE, @PHONETYPECODEID, @NUMBER,
                        @PRIMARY, @DONOTCALL, @UPDATEMATCHINGHOUSEHOLDPHONE, @STARTTIME, @ENDTIME, @INFOSOURCECODEID,
                        @INFOSOURCECOMMENTS, @COUNTRYID, @STARTDATE, @ENDDATE, 
                        @DONOTCALLREASONCODEID, @ISCONFIDENTIAL, @SEASONALSTARTDATE, @SEASONALENDDATE, @DONOTTEXT;

                    return 0;