USP_CONSTITUENTDATAREVIEW_PHONE_LOADAUDITDATA

Loads a phone number's data from an audit record.

Parameters

Parameter	Parameter Type	Mode
@AUDITID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@PHONEID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@PHONETYPECODEID	uniqueidentifier	INOUT
@NUMBER	nvarchar(100)	INOUT
@PRIMARY	bit	INOUT
@DONOTCALL	bit	INOUT
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSEHASMATCHINGPHONE	bit	INOUT
@UPDATEMATCHINGSPOUSEPHONE	bit	INOUT
@MATCHINGHOUSEHOLDMEMBERS	xml	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
@ORIGINCODE	tinyint	INOUT
@SEASONALSTARTDATE	UDT_MONTHDAY	INOUT
@SEASONALENDDATE	UDT_MONTHDAY	INOUT
@DONOTTEXT	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_CONSTITUENTDATAREVIEW_PHONE_LOADAUDITDATA (
    @AUDITID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PHONEID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null 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,
    @MATCHINGHOUSEHOLDMEMBERS xml = null 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,
    @ORIGINCODE tinyint = null output,
    @SEASONALSTARTDATE dbo.UDT_MONTHDAY = null output,
    @SEASONALENDDATE dbo.UDT_MONTHDAY = null output,
    @DONOTTEXT bit = null output
)
as
begin
    set nocount on;

    declare @SPOUSEID uniqueidentifier;

    set @DATALOADED = 0;
    set @SPOUSEHASMATCHINGPHONE = 0;
    set @UPDATEMATCHINGSPOUSEPHONE = 0;

    if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP') begin
        select
            @DATALOADED = 1,
            @PHONEID = PHONEAUDIT.AUDITRECORDID,
            @CONSTITUENTID = PHONEAUDIT.CONSTITUENTID,
            @PHONETYPECODEID = PHONEAUDIT.PHONETYPECODEID,
            @NUMBER = PHONEAUDIT.NUMBER,
            @PRIMARY = PHONEAUDIT.ISPRIMARY,
            @DONOTCALL = PHONEAUDIT.DONOTCALL,
            @SPOUSEID = SPOUSE.ID,
            @SPOUSENAME = SPOUSE.NAME,
            @STARTTIME = PHONEAUDIT.STARTTIME,
            @ENDTIME = PHONEAUDIT.ENDTIME,
            @INFOSOURCECODEID = PHONEAUDIT.INFOSOURCECODEID,
            @INFOSOURCECOMMENTS = PHONEAUDIT.INFOSOURCECOMMENTS,
            @COUNTRYID = PHONEAUDIT.COUNTRYID,
            @STARTDATE = PHONEAUDIT.STARTDATE,
            @ENDDATE = PHONEAUDIT.ENDDATE,
            @DONOTCALLREASONCODEID = PHONEAUDIT.DONOTCALLREASONCODEID,
            @ISCONFIDENTIAL = PHONEAUDIT.ISCONFIDENTIAL,
            @ORIGINCODE = PHONEAUDIT.ORIGINCODE,
            @SEASONALSTARTDATE = PHONEAUDIT.SEASONALSTARTDATE,
            @SEASONALENDDATE = PHONEAUDIT.SEASONALENDDATE,
            @DONOTTEXT = PHONEAUDIT.DONOTTEXT
        from 
            dbo.PHONEAUDIT
            left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PHONEAUDIT.CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
            left join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        where 
            PHONEAUDIT.AUDITID = @AUDITID;

        if exists (
            select PHONE.ID
            from dbo.PHONE
            where
                PHONE.CONSTITUENTID = @SPOUSEID and
                PHONE.NUMBER = @NUMBER
            ) begin
                set @SPOUSEHASMATCHINGPHONE = 1;    
                set @UPDATEMATCHINGSPOUSEPHONE = 1;
        end
    end
    else
    begin
        select
            @DATALOADED = 1,
            @PHONEID = PHONEAUDIT.AUDITRECORDID,
            @CONSTITUENTID = PHONEAUDIT.CONSTITUENTID,
            @PHONETYPECODEID = PHONEAUDIT.PHONETYPECODEID,
            @NUMBER = PHONEAUDIT.NUMBER,
            @PRIMARY = PHONEAUDIT.ISPRIMARY,
            @DONOTCALL = PHONEAUDIT.DONOTCALL,
            @STARTTIME = PHONEAUDIT.STARTTIME,
            @ENDTIME = PHONEAUDIT.ENDTIME,
            @INFOSOURCECODEID = PHONEAUDIT.INFOSOURCECODEID,
            @INFOSOURCECOMMENTS = PHONEAUDIT.INFOSOURCECOMMENTS,
            @COUNTRYID = PHONEAUDIT.COUNTRYID,
            @STARTDATE = PHONEAUDIT.STARTDATE,
            @ENDDATE = PHONEAUDIT.ENDDATE,
            @DONOTCALLREASONCODEID = PHONEAUDIT.DONOTCALLREASONCODEID,
            @ISCONFIDENTIAL = PHONEAUDIT.ISCONFIDENTIAL,
            @ORIGINCODE = PHONEAUDIT.ORIGINCODE,
            @SEASONALSTARTDATE = PHONEAUDIT.SEASONALSTARTDATE,
            @SEASONALENDDATE = PHONEAUDIT.SEASONALENDDATE,
            @DONOTTEXT = PHONEAUDIT.DONOTTEXT
        from 
            dbo.PHONEAUDIT
        where 
            PHONEAUDIT.AUDITID = @AUDITID;
    end

    return 0;
end