USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTUPDATEBATCHDUPLICATEMATCH

Parameters

Parameter Parameter Type Mode Description
@ID uniqueidentifier IN
@DATALOADED bit INOUT
@TSLONG bigint INOUT
@LOOKUPID nvarchar(50) INOUT
@FIRSTNAME nvarchar(50) INOUT
@MIDDLENAME nvarchar(50) INOUT
@LASTNAME nvarchar(100) INOUT
@SUFFIXCODEID uniqueidentifier INOUT
@TITLECODEID uniqueidentifier INOUT
@MAIDENNAME nvarchar(100) INOUT
@NICKNAME nvarchar(50) INOUT
@GENDERCODE tinyint INOUT
@BIRTHDATE UDT_FUZZYDATE INOUT
@ADDRESSID uniqueidentifier INOUT
@ADDRESSTYPECODEID uniqueidentifier INOUT
@ADDRESS_ADDRESSBLOCK nvarchar(150) INOUT
@ADDRESS_CITY nvarchar(50) INOUT
@ADDRESS_STATEID uniqueidentifier INOUT
@ADDRESS_POSTCODE nvarchar(12) INOUT
@ADDRESS_COUNTRYID uniqueidentifier INOUT
@PHONEID uniqueidentifier INOUT
@PHONETYPECODEID uniqueidentifier INOUT
@PHONENUMBER nvarchar(100) INOUT
@EMAILID uniqueidentifier INOUT
@EMAILADDRESSTYPECODEID uniqueidentifier INOUT
@EMAILADDRESS UDT_EMAILADDRESS INOUT
@CREATEDON datetime INOUT
@DATECHANGED datetime INOUT
@ADDRESSES xml INOUT
@PHONES xml INOUT
@EMAILADDRESSES xml INOUT
@CONSTITUENTACTION tinyint INOUT
@ADDRESSACTION tinyint INOUT
@EMAILACTION tinyint INOUT
@PHONEACTION tinyint INOUT
@PRIMARYRECORDID uniqueidentifier INOUT
@EMAILISPRIMARY bit INOUT
@PHONEISPRIMARY bit INOUT
@ADDRESSISPRIMARY bit INOUT
@INCOMINGADDRESSID uniqueidentifier INOUT
@INCOMINGEMAILID uniqueidentifier INOUT
@INCOMINGPHONEID uniqueidentifier INOUT
@SIMILARADDRESSCODE tinyint INOUT
@UNSIMILARADDRESSCODE tinyint INOUT
@NEWADDRESSPRIMARYCODE tinyint INOUT
@DIFFERENTPHONECODE tinyint INOUT
@NEWPHONEPRIMARYCODE tinyint INOUT
@DIFFERENTEMAILCODE tinyint INOUT
@NEWEMAILPRIMARYCODE tinyint INOUT
@DECEASED bit INOUT
@DECEASEDDATE UDT_FUZZYDATE INOUT
@GIVESANONYMOUSLY bit INOUT
@MARITALSTATUSCODEID uniqueidentifier INOUT
@WEBADDRESS UDT_WEBADDRESS INOUT
@ADDRESSHISTORICALSTARTDATE date INOUT
@ADDRESSHISTORICALENDDATE date INOUT
@ADDRESSDONOTMAIL bit INOUT
@ADDRESSDONOTMAILREASONCODEID uniqueidentifier INOUT
@ADDRESSSTARTDATE UDT_MONTHDAY INOUT
@ADDRESSENDDATE UDT_MONTHDAY INOUT
@ADDRESSDPC nvarchar(max) INOUT
@ADDRESSCART nvarchar(max) INOUT
@ADDRESSLOT nvarchar(5) INOUT
@ADDRESSINFOSOURCECODEID uniqueidentifier INOUT
@ADDRESSINFOSOURCECOMMENTS nvarchar(256) INOUT
@ADDRESSCOUNTYCODEID uniqueidentifier INOUT
@ADDRESSREGIONCODEID uniqueidentifier INOUT
@ADDRESSCONGRESSIONALDISTRICTCODEID uniqueidentifier INOUT
@ADDRESSSTATEHOUSEDISTRICTCODEID uniqueidentifier INOUT
@ADDRESSSTATESENATEDISTRICTCODEID uniqueidentifier INOUT
@ADDRESSLOCALPRECINCTCODEID uniqueidentifier INOUT
@ADDRESSCERTIFICATIONDATA int INOUT
@ADDRESSLASTVALIDATIONATTEMPTDATE date INOUT
@ADDRESSOMITFROMVALIDATION bit INOUT
@ADDRESSVALIDATIONMESSAGE nvarchar(200) INOUT
@PHONEDONOTCALL bit INOUT
@PHONESTARTTIME UDT_HOURMINUTE INOUT
@PHONEENDTIME UDT_HOURMINUTE INOUT
@PHONEINFOSOURCECODEID uniqueidentifier INOUT
@PHONECOUNTRYID uniqueidentifier INOUT
@PHONESTARTDATE date INOUT
@PHONEENDDATE date INOUT
@PHONESEASONALSTARTDATE UDT_MONTHDAY INOUT
@PHONESEASONALENDDATE UDT_MONTHDAY INOUT
@EMAILADDRESSDONOTEMAIL bit INOUT
@EMAILADDRESSINFOSOURCECODEID uniqueidentifier INOUT
@EMAILADDRESSSTARTDATE date INOUT
@EMAILADDRESSENDDATE date INOUT
@CONSTITUENCIES xml INOUT
@ORIGINAL_KEYNAME nvarchar(100) INOUT
@ORIGINAL_FIRSTNAME nvarchar(50) INOUT
@SOLICITCODES xml INOUT
@GENDERCODEID uniqueidentifier INOUT

Definition

Copy 

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTUPDATEBATCHDUPLICATEMATCH (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @LOOKUPID nvarchar(50) = null output,
    @FIRSTNAME nvarchar(50) = null output,
    @MIDDLENAME nvarchar(50) = null output,
    @LASTNAME nvarchar(100) = null output, -- also used as ORGANIZATIONNAME
    @SUFFIXCODEID uniqueidentifier = null output,
    @TITLECODEID uniqueidentifier = null output,
    @MAIDENNAME nvarchar(100) = null output,
    @NICKNAME nvarchar(50) = null output,
    @GENDERCODE tinyint = null output,
    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @ADDRESSID uniqueidentifier = null output,
    @ADDRESSTYPECODEID uniqueidentifier = null output,
    @ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
    @ADDRESS_CITY nvarchar(50) = null output,
    @ADDRESS_STATEID uniqueidentifier = null output,
    @ADDRESS_POSTCODE nvarchar(12) = null output,
    @ADDRESS_COUNTRYID uniqueidentifier = null output,
    @PHONEID uniqueidentifier = null output,
    @PHONETYPECODEID uniqueidentifier = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @EMAILID uniqueidentifier = null output,
    @EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @CREATEDON datetime = null output,
    @DATECHANGED datetime = null output,
    @ADDRESSES xml = null output,
    @PHONES xml = null output,
    @EMAILADDRESSES xml = null output,
    @CONSTITUENTACTION tinyint = null output,
    @ADDRESSACTION tinyint = null output,
    @EMAILACTION tinyint = null output,
    @PHONEACTION tinyint = null output,
    @PRIMARYRECORDID uniqueidentifier = null output,
    @EMAILISPRIMARY bit = null output,
    @PHONEISPRIMARY bit = null output,
    @ADDRESSISPRIMARY bit = null output,
    @INCOMINGADDRESSID uniqueidentifier = null output,
    @INCOMINGEMAILID uniqueidentifier = null output,
    @INCOMINGPHONEID uniqueidentifier = null output,
    @SIMILARADDRESSCODE tinyint = null output,
    @UNSIMILARADDRESSCODE tinyint = null output,
    @NEWADDRESSPRIMARYCODE tinyint = null output,
    @DIFFERENTPHONECODE tinyint = null output,
    @NEWPHONEPRIMARYCODE tinyint = null output,
    @DIFFERENTEMAILCODE tinyint = null output,
    @NEWEMAILPRIMARYCODE tinyint = null output,
    @DECEASED bit = null output,
    @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
    @GIVESANONYMOUSLY bit = null output,
    @MARITALSTATUSCODEID uniqueidentifier = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @ADDRESSHISTORICALSTARTDATE date = null output,
    @ADDRESSHISTORICALENDDATE date = null output,
    @ADDRESSDONOTMAIL bit = null output,
    @ADDRESSDONOTMAILREASONCODEID uniqueidentifier = null output,
    @ADDRESSSTARTDATE dbo.UDT_MONTHDAY = null output,
    @ADDRESSENDDATE dbo.UDT_MONTHDAY = null output,
    @ADDRESSDPC nvarchar(max) = null output,
    @ADDRESSCART nvarchar(max) = null output,
    @ADDRESSLOT nvarchar(5) = null output,
    @ADDRESSINFOSOURCECODEID uniqueidentifier = null output,
    @ADDRESSINFOSOURCECOMMENTS nvarchar(256) = null output,
    @ADDRESSCOUNTYCODEID uniqueidentifier = null output,
    @ADDRESSREGIONCODEID uniqueidentifier = null output,
    @ADDRESSCONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @ADDRESSSTATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
    @ADDRESSSTATESENATEDISTRICTCODEID uniqueidentifier = null output,
    @ADDRESSLOCALPRECINCTCODEID uniqueidentifier = null output,
    @ADDRESSCERTIFICATIONDATA int = null output,
    @ADDRESSLASTVALIDATIONATTEMPTDATE date = null output,
    @ADDRESSOMITFROMVALIDATION bit = null output,
    @ADDRESSVALIDATIONMESSAGE nvarchar(200) = null output,
    @PHONEDONOTCALL bit = null output,
    @PHONESTARTTIME dbo.UDT_HOURMINUTE = null output,
    @PHONEENDTIME dbo.UDT_HOURMINUTE = null output,
    @PHONEINFOSOURCECODEID uniqueidentifier = null output,
    @PHONECOUNTRYID uniqueidentifier = null output,
    @PHONESTARTDATE date = null output,
    @PHONEENDDATE date = null output,
    @PHONESEASONALSTARTDATE dbo.UDT_MONTHDAY = null output,
    @PHONESEASONALENDDATE dbo.UDT_MONTHDAY = null output,
    @EMAILADDRESSDONOTEMAIL bit = null output,
    @EMAILADDRESSINFOSOURCECODEID uniqueidentifier = null output,
    @EMAILADDRESSSTARTDATE date = null output,
    @EMAILADDRESSENDDATE date = null output,
    @CONSTITUENCIES xml = null output,
    @ORIGINAL_KEYNAME nvarchar(100) = null output,
    @ORIGINAL_FIRSTNAME nvarchar(50) = null output,
    @SOLICITCODES xml = null output,
    @GENDERCODEID uniqueidentifier = null output
    )
as
set nocount on;
-- be sure to set these, in case the select returns no rows
set @DATALOADED = 0
set @TSLONG = 0

select @DATALOADED = 1,
    @TSLONG = BATCHCONSTITUENTUPDATE.TSLONG,
    @BIRTHDATE = [BIRTHDATE],
    @FIRSTNAME = [FIRSTNAME],
    @LASTNAME = [KEYNAME],
    @MAIDENNAME = [MAIDENNAME],
    @MIDDLENAME = [MIDDLENAME],
    @NICKNAME = [NICKNAME],
    @SUFFIXCODEID = [SUFFIXCODEID],
    @TITLECODEID = [TITLECODEID],
    @ADDRESSES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_TOITEMLISTXML(BATCHCONSTITUENTUPDATE.ID),
    @PHONES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETPHONES_WITHDATES_TOITEMLISTXML(BATCHCONSTITUENTUPDATE.ID),
    @EMAILADDRESSES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETEMAILADDRESSES_WITHDATES_2_TOITEMLISTXML(BATCHCONSTITUENTUPDATE.ID),
    @MARITALSTATUSCODEID = [MARITALSTATUSCODEID],
    @GIVESANONYMOUSLY = [GIVESANONYMOUSLY],
    @DECEASED = [DECEASED],
    @DECEASEDDATE = [DECEASEDDATE],
    @WEBADDRESS = [WEBADDRESS],
    @GENDERCODE = [GENDERCODE],
    @ADDRESSHISTORICALSTARTDATE = HISTORICALSTARTDATE,
    @ADDRESSHISTORICALENDDATE = HISTORICALENDDATE,
    @ADDRESSDONOTMAIL = DONOTMAIL,
    @ADDRESSDONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
    @ADDRESSSTARTDATE = BATCHCONSTITUENTUPDATEADDRESSES.STARTDATE,
    @ADDRESSENDDATE = BATCHCONSTITUENTUPDATEADDRESSES.ENDDATE,
    @ADDRESSDPC = DPC,
    @ADDRESSCART = CART,
    @ADDRESSLOT = LOT,
    @ADDRESSINFOSOURCECODEID = BATCHCONSTITUENTUPDATEADDRESSES.INFOSOURCECODEID,
    @ADDRESSINFOSOURCECOMMENTS = BATCHCONSTITUENTUPDATEADDRESSES.INFOSOURCECOMMENTS,
    @ADDRESSCOUNTYCODEID = BATCHCONSTITUENTUPDATEADDRESSES.COUNTYCODEID,
    @ADDRESSREGIONCODEID = BATCHCONSTITUENTUPDATEADDRESSES.REGIONCODEID,
    @ADDRESSCONGRESSIONALDISTRICTCODEID = BATCHCONSTITUENTUPDATEADDRESSES.CONGRESSIONALDISTRICTCODEID,
    @ADDRESSSTATEHOUSEDISTRICTCODEID = BATCHCONSTITUENTUPDATEADDRESSES.STATEHOUSEDISTRICTCODEID,
    @ADDRESSSTATESENATEDISTRICTCODEID = BATCHCONSTITUENTUPDATEADDRESSES.STATESENATEDISTRICTCODEID,
    @ADDRESSLOCALPRECINCTCODEID = BATCHCONSTITUENTUPDATEADDRESSES.LOCALPRECINCTCODEID,
    @ADDRESSCERTIFICATIONDATA = BATCHCONSTITUENTUPDATEADDRESSES.CERTIFICATIONDATA,
    @ADDRESSLASTVALIDATIONATTEMPTDATE = BATCHCONSTITUENTUPDATEADDRESSES.LASTVALIDATIONATTEMPTDATE,
    @ADDRESSOMITFROMVALIDATION = BATCHCONSTITUENTUPDATEADDRESSES.OMITFROMVALIDATION,
    @ADDRESSVALIDATIONMESSAGE = BATCHCONSTITUENTUPDATEADDRESSES.VALIDATIONMESSAGE,
    @PHONEDONOTCALL = BATCHCONSTITUENTUPDATEPHONES.DONOTCALL,
    @PHONECOUNTRYID = BATCHCONSTITUENTUPDATEPHONES.COUNTRYID,
    @PHONESEASONALSTARTDATE = BATCHCONSTITUENTUPDATEPHONES.SEASONALSTARTDATE,
    @PHONESEASONALENDDATE = BATCHCONSTITUENTUPDATEPHONES.SEASONALENDDATE,
    @PHONESTARTTIME = BATCHCONSTITUENTUPDATEPHONES.STARTTIME,
    @PHONEENDTIME = BATCHCONSTITUENTUPDATEPHONES.ENDTIME,
    @PHONESTARTDATE = BATCHCONSTITUENTUPDATEPHONES.STARTDATE,
    @PHONEENDDATE = BATCHCONSTITUENTUPDATEPHONES.ENDDATE,
    @PHONEINFOSOURCECODEID = BATCHCONSTITUENTUPDATEPHONES.INFOSOURCECODEID,
    @EMAILADDRESSDONOTEMAIL = BATCHCONSTITUENTUPDATEEMAILADDRESSES.DONOTEMAIL,
    @EMAILADDRESSSTARTDATE = BATCHCONSTITUENTUPDATEEMAILADDRESSES.STARTDATE,
    @EMAILADDRESSENDDATE = BATCHCONSTITUENTUPDATEEMAILADDRESSES.ENDDATE,
    @EMAILADDRESSINFOSOURCECODEID = BATCHCONSTITUENTUPDATEEMAILADDRESSES.INFOSOURCECODEID,
    @CONSTITUENCIES = CONSTITUENCIESXML.CONSTITUENCIES,
    @ORIGINAL_KEYNAME = BATCHCONSTITUENTUPDATE.ORIGINAL_KEYNAME,
    @ORIGINAL_FIRSTNAME = BATCHCONSTITUENTUPDATE.ORIGINAL_FIRSTNAME,
    @SOLICITCODES = dbo.UFN_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_TOITEMLISTXML(BATCHCONSTITUENTUPDATE.ID),    
    @GENDERCODEID = BATCHCONSTITUENTUPDATE.GENDERCODEID
from dbo.BATCHCONSTITUENTUPDATE
    left join dbo.BATCHCONSTITUENTUPDATEADDRESSES
        on (BATCHCONSTITUENTUPDATEADDRESSES.BATCHCONSTITUENTUPDATEID = BATCHCONSTITUENTUPDATE.ID
        and BATCHCONSTITUENTUPDATEADDRESSES.ISPRIMARY = 1)
    left join dbo.BATCHCONSTITUENTUPDATEPHONES
        on (BATCHCONSTITUENTUPDATEPHONES.BATCHCONSTITUENTUPDATEID = BATCHCONSTITUENTUPDATE.ID
        and BATCHCONSTITUENTUPDATEPHONES.ISPRIMARY = 1)
    left join dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES
        on (BATCHCONSTITUENTUPDATEEMAILADDRESSES.BATCHCONSTITUENTUPDATEID = BATCHCONSTITUENTUPDATE.ID
        and BATCHCONSTITUENTUPDATEEMAILADDRESSES.ISPRIMARY = 1)
    cross apply (
        select (
            select
                CONSTITUENCYCODEID as '@CONSTITUENCYCODEID',
                ORIGINALCONSTITUENCYID as '@ORIGINALCONSTITUENCYID',
                DATEFROM as '@DATEFROM',
                DATETO as '@DATETO'
            from (
                select
                    CONSTITUENCYCODEID,
                    ORIGINALCONSTITUENCYID,
                    DATEFROM,
                    DATETO
                from
                    dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
                where
                    BATCHCONSTITUENTUPDATECONSTITUENCIES.BATCHCONSTITUENTUPDATEID = @ID
                union all
                select
                    CONSTITUENCYCODEID,
                    ORIGINALCONSTITUENCYID,
                    DATEFROM,
                    DATETO
                from
                    dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
                where
                    BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.BATCHCONSTITUENTUPDATEID = @ID
            ) CONSTITUENCIES
            for xml path('ITEM'), type, elements, root('CONSTITUENCIES'), BINARY BASE64
        ) CONSTITUENCIES
    ) CONSTITUENCIESXML
where BATCHCONSTITUENTUPDATE.ID = @ID;

return 0;