USP_PHONE_EDITLOAD

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@PHONETYPECODEID	uniqueidentifier	INOUT
@NUMBER	nvarchar(100)	INOUT
@PRIMARY	bit	INOUT
@DONOTCALL	bit	INOUT
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSEHASMATCHINGPHONE	bit	INOUT
@UPDATEMATCHINGSPOUSEPHONE	bit	INOUT
@ISHOUSEHOLD	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@UPDATEMATCHINGHOUSEHOLDPHONE	bit	INOUT
@MATCHINGHOUSEHOLDMEMBERS	xml	INOUT
@TSLONG	bigint	INOUT
@STARTTIME	UDT_HOURMINUTE	INOUT
@ENDTIME	UDT_HOURMINUTE	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@COUNTRYID	uniqueidentifier	INOUT
@STARTDATE	date	INOUT
@ENDDATE	date	INOUT
@DONOTCALLREASONCODEID	uniqueidentifier	INOUT
@ISCONFIDENTIAL	bit	INOUT
@COUNTRYCODES	xml	INOUT
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	INOUT
@FORCEDPRIMARY	bit	INOUT
@CANEDITPRIMARY	bit	INOUT
@INVALIDFIELDS	nvarchar(256)	INOUT
@ORIGINCODE	tinyint	INOUT
@SEASONALSTARTDATE	UDT_MONTHDAY	INOUT
@SEASONALENDDATE	UDT_MONTHDAY	INOUT
@DONOTTEXT	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_PHONE_EDITLOAD
    (
        @ID uniqueidentifier,
        @DATALOADED bit = 0 output,
        @PHONETYPECODEID uniqueidentifier = null output,
        @NUMBER nvarchar(100) = null output,
        @PRIMARY bit = null output,
        @DONOTCALL bit = null output,
        @SPOUSENAME nvarchar(154) = null output,
        @SPOUSEHASMATCHINGPHONE bit = null output,
        @UPDATEMATCHINGSPOUSEPHONE bit = null output,
        @ISHOUSEHOLD bit = null output,
        @ISHOUSEHOLDMEMBER bit = null output,
        @UPDATEMATCHINGHOUSEHOLDPHONE bit = null output,
        @MATCHINGHOUSEHOLDMEMBERS xml = null output,
        @TSLONG bigint = 0 output,
        @STARTTIME dbo.UDT_HOURMINUTE = null output,
        @ENDTIME dbo.UDT_HOURMINUTE = null output,
        @INFOSOURCECODEID uniqueidentifier = null output,
        @INFOSOURCECOMMENTS nvarchar(256) = null output,
        @COUNTRYID uniqueidentifier = null output,
        @STARTDATE date = null output,
        @ENDDATE date = null output,
        @DONOTCALLREASONCODEID uniqueidentifier = null output,
        @ISCONFIDENTIAL bit = null output,
        @COUNTRYCODES xml = null output,
        @CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier = null output,    -- used by constituent data review

        @FORCEDPRIMARY bit = null output,            -- used by constituent data review

        @CANEDITPRIMARY bit = null output,            -- used by constituent data review

        @INVALIDFIELDS nvarchar(256) = null output,    -- used by constituent data review

        @ORIGINCODE tinyint = null output,
        @SEASONALSTARTDATE dbo.UDT_MONTHDAY = null output,
        @SEASONALENDDATE dbo.UDT_MONTHDAY = null output,
        @DONOTTEXT bit = null output
    )
    as
        set nocount on;

        exec dbo.USP_PHONE_UPDATE_LOAD @ID, @DATALOADED output, @TSLONG output, @PHONETYPECODEID output,
            @NUMBER output, @PRIMARY output, @DONOTCALL output, @SPOUSENAME output, @SPOUSEHASMATCHINGPHONE output,
            null, @MATCHINGHOUSEHOLDMEMBERS output, @STARTTIME output, @ENDTIME output,
            @INFOSOURCECODEID output, @INFOSOURCECOMMENTS output, @COUNTRYID output, @STARTDATE output, @ENDDATE output,
            @DONOTCALLREASONCODEID output, @ISCONFIDENTIAL output, @ORIGINCODE output,
            @SEASONALSTARTDATE output, @SEASONALENDDATE output, @DONOTTEXT output;

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

        declare @CONSTITUENTID uniqueidentifier;
        select @CONSTITUENTID = CONSTITUENTID from dbo.PHONE where ID = @ID;

        set @ISHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID);
        set @ISHOUSEHOLDMEMBER = case when exists (
            select 1 
            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 > @CURRENTDATE))
                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
        ) then 1 else 0 end;                                

        set @MATCHINGHOUSEHOLDMEMBERS = (
            select
                CONSTITUENTID,
                NAME,
                RELATIONSHIPTOPRIMARY
            from
                dbo.UFN_PHONE_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @NUMBER, @PHONETYPECODEID)
            for xml raw('ITEM'), type, elements, root('MATCHINGHOUSEHOLDMEMBERS'), binary base64
        );

        select @COUNTRYCODES = dbo.UFN_COUNTRY_GETCOUNTRYCODES_TOITEMLISTXML();

        return 0;