USP_PHONE_CREATE

            CREATE procedure dbo.USP_PHONE_CREATE
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,    
                @CURRENTDATE datetime = null,
                @CONSTITUENTID uniqueidentifier,
                @PHONETYPECODEID uniqueidentifier = null,
                @NUMBER nvarchar(100),
                @PRIMARY bit = 0,
                @STARTTIME dbo.UDT_HOURMINUTE = '',
                @ENDTIME dbo.UDT_HOURMINUTE = '',
                @STARTDATE date = null,
                @DONOTCALL bit = 0,
                @DONOTCALLREASONCODEID uniqueidentifier = null,
                @INFOSOURCECODEID uniqueidentifier = null,
                @INFOSOURCECOMMENTS nvarchar(256) = '',
                @COUNTRYID uniqueidentifier = null,
                @ISCONFIDENTIAL bit = 0,
                @UPDATEMATCHINGHOUSEHOLDPHONE bit = 0,
                @RELATIONSHIPID uniqueidentifier = null,
                @ORIGINCODE tinyint = 0,
                @SEASONALSTARTDATE dbo.UDT_MONTHDAY = '0000',
                @SEASONALENDDATE dbo.UDT_MONTHDAY = '0000',
                @DONOTTEXT bit = 0
            )
            as
                set nocount on;

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

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

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                if @DONOTCALL = 1
                    begin
                        set @STARTTIME = '';
                        set @ENDTIME = '';
                    end
                else
                    begin
                        set @DONOTCALLREASONCODEID = null;
                    end

                declare @SEQUENCE int;

                select
                    @SEQUENCE=coalesce(max(SEQUENCE),0)+1
                from
                    dbo.PHONE
                where
                    CONSTITUENTID=@CONSTITUENTID;

                if @ORIGINCODE is not null and @ORIGINCODE <> 0
                    set @INFOSOURCECODEID = null
                else
                    set @ORIGINCODE = 0

                set @ISCONFIDENTIAL = coalesce(@ISCONFIDENTIAL, 0);

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

                    -- JNA <10/21/2009> Check for/remove country code

                    set @NUMBER = dbo.UFN_PHONE_REMOVECOUNTRYCODE(@NUMBER, @COUNTRYID);

                    insert into dbo.[PHONE]
                    (
                        [ID],
                        [CONSTITUENTID],
                        [PHONETYPECODEID],
                        [NUMBER],
                        [ISPRIMARY],
                        [DONOTCALL],
                        [STARTTIME],
                        [ENDTIME],
                        [INFOSOURCECODEID],
                        [INFOSOURCECOMMENTS],
                        [COUNTRYID],
                        [SEQUENCE],
                        [STARTDATE],
                        [DONOTCALLREASONCODEID],
                        [ISCONFIDENTIAL],
                        [RELATIONSHIPID],
                        [ORIGINCODE],
                        [SEASONALSTARTDATE],
                        [SEASONALENDDATE],
                        [ADDEDBYID],
                        [CHANGEDBYID],
                        [DATEADDED],
                        [DATECHANGED],
                        [DONOTTEXT]
                    )
                    values
                    (
                        @ID,
                        @CONSTITUENTID,
                        @PHONETYPECODEID,
                        @NUMBER,
                        @PRIMARY,
                        @DONOTCALL,
                        @STARTTIME,
                        @ENDTIME,
                        @INFOSOURCECODEID,
                        @INFOSOURCECOMMENTS,
                        @COUNTRYID,
                        @SEQUENCE,
                        @STARTDATE,
                        @DONOTCALLREASONCODEID,
                        @ISCONFIDENTIAL,
                        @RELATIONSHIPID,
                        @ORIGINCODE,
                        @SEASONALSTARTDATE,
                        @SEASONALENDDATE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE,
                        @DONOTTEXT
                    );

                    if @UPDATEMATCHINGHOUSEHOLDPHONE = 1 begin
                        declare @EARLIESTTIMECURRENTDATE date;
                        set @EARLIESTTIMECURRENTDATE = getdate();

                        -- if the constituent is an individual, householdid will be the household they are a member of

                        -- if the constituent is a household, householdid will be that household's id

                        declare @HOUSEHOLDID uniqueidentifier;
                        if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
                            set @HOUSEHOLDID = @CONSTITUENTID;
                        else
                            select
                                @HOUSEHOLDID = GM.GROUPID
                            from
                                dbo.GROUPMEMBER GM
                            left outer join
                                dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                            left outer join
                                dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                            where
                                GM.MEMBERID = @CONSTITUENTID
                            and
                                GD.GROUPTYPECODE = 0
                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE)) 
                                or (GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE and GMDR.DATETO > @EARLIESTTIMECURRENTDATE));

                        -- create a table of all of the members of the household previously identified

                        declare @IDSTOUPDATE table(ID uniqueidentifier, HASPRIMARY bit)
                        insert into @IDSTOUPDATE
                            select
                                GM.MEMBERID,
                                0
                            from
                                dbo.GROUPMEMBER GM
                            left outer join
                                dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                            where
                                GM.GROUPID = @HOUSEHOLDID
                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE)) 
                                or (GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE and GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
                            union all
                            select
                                @HOUSEHOLDID, 0; -- include the household itself (for the case where the constituent is an individual)

                        delete from @IDSTOUPDATE where ID = @CONSTITUENTID;    -- (if the constituent was an individual, they'll show as a member - if they were a household it got union'd in)


                        -- Do not overwrite another constituent's primary phone

                        update @IDSTOUPDATE
                        set
                            HASPRIMARY = 1
                        from 
                            @IDSTOUPDATE I
                            inner join dbo.PHONE on I.ID = PHONE.CONSTITUENTID
                        where
                            PHONE.ISPRIMARY = 1;

                        -- create a table of all of the matching phone numbers

                        declare @MATCHING table (ID uniqueidentifier);
                        insert into @MATCHING
                        select
                            ID
                        from
                            dbo.PHONE
                        where PHONE.NUMBER = @NUMBER
                            and PHONE.CONSTITUENTID in (select ID from @IDSTOUPDATE);

                        -- insert where there are no records with the new number

                        insert into dbo.PHONE
                            (CONSTITUENTID,PHONETYPECODEID,NUMBER,ISPRIMARY,DONOTCALL,STARTTIME,ENDTIME,INFOSOURCECODEID,INFOSOURCECOMMENTS,COUNTRYID, STARTDATE, DONOTCALLREASONCODEID, ISCONFIDENTIAL, ORIGINCODE, SEASONALSTARTDATE, SEASONALENDDATE, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,DONOTTEXT)
                        select
                            IDSTOUPDATE.ID,@PHONETYPECODEID,@NUMBER,case when IDSTOUPDATE.HASPRIMARY = 1 then 0 else 1 end,@DONOTCALL,@STARTTIME,@ENDTIME,@INFOSOURCECODEID,@INFOSOURCECOMMENTS,@COUNTRYID, @STARTDATE, @DONOTCALLREASONCODEID, @ISCONFIDENTIAL, @ORIGINCODE, @SEASONALSTARTDATE, @SEASONALENDDATE, @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@DONOTTEXT
                        from
                            @IDSTOUPDATE IDSTOUPDATE
                        where
                            not exists (
                                select 'x' 
                                from dbo.PHONE
                                inner join @MATCHING as M on M.ID = PHONE.ID
                                where PHONE.CONSTITUENTID = IDSTOUPDATE.ID
                            );                    

                    end -- update matching household phone

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;