USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTDUPLICATESEARCHRESOLUTION
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @NAME | nvarchar(154) | INOUT | |
| @LASTNAME | nvarchar(100) | INOUT | |
| @FIRSTNAME | nvarchar(50) | INOUT | |
| @MIDDLENAME | nvarchar(50) | INOUT | |
| @SUFFIXCODEID | uniqueidentifier | INOUT | |
| @BIRTHDATE | UDT_FUZZYDATE | INOUT | |
| @ADDRESS_COUNTRYID | uniqueidentifier | INOUT | |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | INOUT | |
| @ADDRESS_CITY | nvarchar(50) | INOUT | |
| @ADDRESS_STATEID | uniqueidentifier | INOUT | |
| @ADDRESS_POSTCODE | nvarchar(12) | INOUT | |
| @PHONENUMBER | nvarchar(100) | INOUT | |
| @EMAILADDRESS | UDT_EMAILADDRESS | INOUT | |
| @BATCHNUMBER | nvarchar(100) | INOUT | |
| @CREATEDON | date | INOUT | |
| @BATCHID | uniqueidentifier | INOUT | |
| @BATCHTYPE | int | INOUT | |
| @DATALOADED | bit | INOUT | |
| @ADDRESS_TYPECODEID | uniqueidentifier | INOUT | |
| @TITLECODEID | uniqueidentifier | INOUT | |
| @PHONETYPECODEID | uniqueidentifier | INOUT | |
| @EMAILADDRESSTYPECODEID | uniqueidentifier | INOUT | |
| @ADDRESSTYPECODEID | uniqueidentifier | INOUT | |
| @PRIMARYRECORDID | uniqueidentifier | INOUT | |
| @BATCHROWID | uniqueidentifier | INOUT | |
| @NAMECODE | tinyint | INOUT | |
| @SIMILARADDRESSCODE | tinyint | INOUT | |
| @UNSIMILARADDRESSCODE | tinyint | INOUT | |
| @NEWADDRESSPRIMARYCODE | tinyint | INOUT | |
| @DIFFERENTPHONECODE | tinyint | INOUT | |
| @NEWPHONEPRIMARYCODE | tinyint | INOUT | |
| @DIFFERENTEMAILCODE | tinyint | INOUT | |
| @NEWEMAILPRIMARYCODE | tinyint | INOUT | |
| @BIRTHDATERULECODE | tinyint | INOUT | |
| @INCOMINGADDRESSID | uniqueidentifier | INOUT | |
| @INCOMINGEMAILID | uniqueidentifier | INOUT | |
| @INCOMINGPHONEID | uniqueidentifier | INOUT | |
| @MAIDENNAME | nvarchar(100) | INOUT | |
| @NICKNAME | nvarchar(50) | INOUT | |
| @GENDERCODE | 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 | |
| @NAMEFORMATS | xml | INOUT | |
| @ADDRESSISPRIMARY | bit | INOUT | |
| @PHONEISPRIMARY | bit | INOUT | |
| @EMAILISPRIMARY | bit | INOUT | |
| @CONSTITUENCIES | xml | INOUT | |
| @ORIGINAL_KEYNAME | nvarchar(100) | INOUT | |
| @ORIGINAL_FIRSTNAME | nvarchar(50) | INOUT | |
| @GENDERCODEID | uniqueidentifier | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTDUPLICATESEARCHRESOLUTION
(
@ID uniqueidentifier,
@NAME nvarchar(154) = null output,
@LASTNAME nvarchar(100) = null output,
@FIRSTNAME nvarchar(50) = null output,
@MIDDLENAME nvarchar(50) = null output,
@SUFFIXCODEID uniqueidentifier = null output,
@BIRTHDATE dbo.UDT_FUZZYDATE = null output,
@ADDRESS_COUNTRYID 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,
@PHONENUMBER nvarchar(100) = null output,
@EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
@BATCHNUMBER nvarchar(100) = null output,
@CREATEDON date = null output,
@BATCHID uniqueidentifier = null output,
@BATCHTYPE int = null output,
@DATALOADED bit = 0 output,
@ADDRESS_TYPECODEID uniqueidentifier = null output,
@TITLECODEID uniqueidentifier = null output,
@PHONETYPECODEID uniqueidentifier = null output,
@EMAILADDRESSTYPECODEID uniqueidentifier = null output,
@ADDRESSTYPECODEID uniqueidentifier = null output,
@PRIMARYRECORDID uniqueidentifier = null output,
@BATCHROWID uniqueidentifier = null output,
@NAMECODE tinyint = 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,
@BIRTHDATERULECODE tinyint = null output,
@INCOMINGADDRESSID uniqueidentifier = null output,
@INCOMINGEMAILID uniqueidentifier = null output,
@INCOMINGPHONEID uniqueidentifier = null output,
@MAIDENNAME nvarchar(100) = null output,
@NICKNAME nvarchar(50) = null output,
@GENDERCODE 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,
@NAMEFORMATS xml = null output,
@ADDRESSISPRIMARY bit = null output,
@PHONEISPRIMARY bit = null output,
@EMAILISPRIMARY bit = null output,
@CONSTITUENCIES xml = null output,
@ORIGINAL_KEYNAME nvarchar(100) = null output,
@ORIGINAL_FIRSTNAME nvarchar(50) = null output,
@GENDERCODEID uniqueidentifier = null output
)
as
set nocount on
select
@NAME = BRC.NAME,
@LASTNAME = BRC.KEYNAME,
@FIRSTNAME = BRC.FIRSTNAME,
@MIDDLENAME = BRC.MIDDLENAME,
@SUFFIXCODEID = BRC.SUFFIXCODEID,
@BIRTHDATE = BRC.BIRTHDATE,
@ADDRESS_COUNTRYID = BRC.COUNTRYID,
@ADDRESS_ADDRESSBLOCK = BRC.ADDRESSBLOCK,
@ADDRESS_CITY = BRC.CITY,
@ADDRESS_STATEID = BRC.STATEID,
@ADDRESS_POSTCODE = BRC.POSTCODE,
@PHONENUMBER = BRC.NUMBER,
@EMAILADDRESS = BRC.EMAILADDRESS,
@BATCHNUMBER = B.BATCHNUMBER,
@CREATEDON = B.DATEADDED,
@BATCHID = B.ID,
@BATCHTYPE = 0, -- ERB
@DATALOADED = 1,
@ADDRESS_TYPECODEID = BRC.ADDRESSTYPECODEID,
@TITLECODEID = BRC.TITLECODEID,
@PHONETYPECODEID = BRC.PHONETYPECODEID,
@EMAILADDRESSTYPECODEID = BRC.EMAILADDRESSTYPECODEID,
@ADDRESSTYPECODEID = BRC.ADDRESSTYPECODEID,
@PRIMARYRECORDID = BRC.EXISTINGCONSTITUENTID,
@BATCHROWID = BR.ID,
@NAMECODE = NAMECODE,
@SIMILARADDRESSCODE = SIMILARADDRESSCODE,
@UNSIMILARADDRESSCODE = UNSIMILARADDRESSCODE,
@NEWADDRESSPRIMARYCODE = NEWADDRESSPRIMARYCODE,
@DIFFERENTPHONECODE = DIFFERENTPHONECODE,
@NEWPHONEPRIMARYCODE = NEWPHONEPRIMARYCODE,
@DIFFERENTEMAILCODE = DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE = NEWEMAILPRIMARYCODE,
@BIRTHDATERULECODE = BIRTHDATERULECODE,
@MAIDENNAME = MAIDENNAME,
@NICKNAME = NICKNAME,
@GIVESANONYMOUSLY = GIVESANONYMOUSLY,
@MARITALSTATUSCODEID = MARITALSTATUSCODEID,
@WEBADDRESS = WEBADDRESS,
@GENDERCODE = GENDERCODE,
@ADDRESSDONOTMAIL = DONOTMAIL,
@ADDRESSDONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
@ADDRESSDPC = DPC,
@ADDRESSCART = CART,
@ADDRESSLOT = LOT,
@ADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
@ADDRESSCOUNTYCODEID = COUNTYCODEID,
@ADDRESSCONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
@ADDRESSCERTIFICATIONDATA = CERTIFICATIONDATA,
@ADDRESSLASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
@ADDRESSOMITFROMVALIDATION = OMITFROMVALIDATION,
@ADDRESSVALIDATIONMESSAGE = VALIDATIONMESSAGE,
@PHONEDONOTCALL = DONOTCALL,
@PHONEINFOSOURCECODEID = INFOSOURCECODEID,
@EMAILADDRESSDONOTEMAIL = DONOTEMAIL,
@EMAILADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
@NAMEFORMATS = BRC.NAMEFORMATS,
@ORIGINAL_KEYNAME = BRC.ORIGINAL_LASTNAME,
@ORIGINAL_FIRSTNAME = BRC.ORIGINAL_FIRSTNAME,
@GENDERCODEID = BRC.GENDERCODEID
from dbo.BATCHREVENUECONSTITUENT BRC
inner join dbo.BATCHREVENUE BR on BRC.ID = BR.CONSTITUENTID
inner join dbo.BATCH B on B.ID = BR.BATCHID
where BR.ID = @ID
set @CONSTITUENCIES = (
select
CONSTITUENCYCODEID as '@CONSTITUENCYCODEID',
ORIGINALCONSTITUENCYID as '@ORIGINALCONSTITUENCYID',
DATEFROM as '@DATEFROM',
DATETO as '@DATETO'
from (
select
CONSTITUENCYCODEID,
ORIGINALCONSTITUENCYID,
DATEFROM,
DATETO
from
dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
inner join dbo.BATCHCONSTITUENTUPDATE
on BATCHCONSTITUENTUPDATE.ID = BATCHCONSTITUENTUPDATECONSTITUENCIES.BATCHCONSTITUENTUPDATEID
where
BATCHCONSTITUENTUPDATE.ID = @ID
union all
select
CONSTITUENCYCODEID,
ORIGINALCONSTITUENCYID,
DATEFROM,
DATETO
from
dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
inner join dbo.BATCHCONSTITUENTUPDATE
on BATCHCONSTITUENTUPDATE.ID = BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.BATCHCONSTITUENTUPDATEID
where
BATCHCONSTITUENTUPDATE.ID = @ID
) CONSTITUENCIESTEMP
for xml path('ITEM'), type, elements, root('CONSTITUENCIES'), BINARY BASE64
)
if @NAME is null
begin
select
@NAME = BRC.NAME,
@LASTNAME = BRC.KEYNAME,
@FIRSTNAME = BRC.FIRSTNAME,
@MIDDLENAME = BRC.MIDDLENAME,
@SUFFIXCODEID = BRC.SUFFIXCODEID,
@BIRTHDATE = BRC.BIRTHDATE,
@ADDRESS_COUNTRYID = BRC.COUNTRYID,
@ADDRESS_ADDRESSBLOCK = BRC.ADDRESSBLOCK,
@ADDRESS_CITY = BRC.CITY,
@ADDRESS_STATEID = BRC.STATEID,
@ADDRESS_POSTCODE = BRC.POSTCODE,
@PHONENUMBER = BRC.NUMBER,
@EMAILADDRESS = BRC.EMAILADDRESS,
@BATCHNUMBER = B.BATCHNUMBER,
@CREATEDON = B.DATEADDED,
@BATCHID = B.ID,
@BATCHTYPE = 3, -- RUB
@DATALOADED = 1,
@ADDRESS_TYPECODEID = BRC.ADDRESSTYPECODEID,
@TITLECODEID = BRC.TITLECODEID,
@PHONETYPECODEID = BRC.PHONETYPECODEID,
@EMAILADDRESSTYPECODEID = BRC.EMAILADDRESSTYPECODEID,
@ADDRESSTYPECODEID = BRC.ADDRESSTYPECODEID,
@PRIMARYRECORDID = BRC.EXISTINGCONSTITUENTID,
@BATCHROWID = BR.ID,
@NAMECODE = NAMECODE,
@SIMILARADDRESSCODE = SIMILARADDRESSCODE,
@UNSIMILARADDRESSCODE = UNSIMILARADDRESSCODE,
@NEWADDRESSPRIMARYCODE = NEWADDRESSPRIMARYCODE,
@DIFFERENTPHONECODE = DIFFERENTPHONECODE,
@NEWPHONEPRIMARYCODE = NEWPHONEPRIMARYCODE,
@DIFFERENTEMAILCODE = DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE = NEWEMAILPRIMARYCODE,
@BIRTHDATERULECODE = BIRTHDATERULECODE,
@MAIDENNAME = MAIDENNAME,
@NICKNAME = NICKNAME,
@GIVESANONYMOUSLY = GIVESANONYMOUSLY,
@MARITALSTATUSCODEID = MARITALSTATUSCODEID,
@WEBADDRESS = WEBADDRESS,
@GENDERCODE = GENDERCODE,
@ADDRESSDONOTMAIL = DONOTMAIL,
@ADDRESSDONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
@ADDRESSDPC = DPC,
@ADDRESSCART = CART,
@ADDRESSLOT = LOT,
@ADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
@ADDRESSCOUNTYCODEID = COUNTYCODEID,
@ADDRESSCONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
@ADDRESSCERTIFICATIONDATA = CERTIFICATIONDATA,
@ADDRESSLASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
@ADDRESSOMITFROMVALIDATION = OMITFROMVALIDATION,
@ADDRESSVALIDATIONMESSAGE = VALIDATIONMESSAGE,
@PHONEDONOTCALL = DONOTCALL,
@PHONEINFOSOURCECODEID = INFOSOURCECODEID,
@EMAILADDRESSDONOTEMAIL = DONOTEMAIL,
@EMAILADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
@NAMEFORMATS = BRC.NAMEFORMATS,
@ORIGINAL_KEYNAME = BRC.ORIGINAL_LASTNAME,
@ORIGINAL_FIRSTNAME = BRC.ORIGINAL_FIRSTNAME,
@GENDERCODEID = BRC.GENDERCODEID
from dbo.BATCHREVENUECONSTITUENT BRC
inner join dbo.BATCHREVENUE BR on BRC.ID = BR.CONSTITUENTID
inner join dbo.BATCH B on B.ID = BR.BATCHID
where BRC.ID = @ID
end
if @NAME is null
begin
with INCOMING_ADDRESS as(
select top 1 BATCHCONSTITUENTUPDATEADDRESSES.ID, BATCHCONSTITUENTUPDATEID,COUNTRYID,ADDRESSBLOCK,CITY,STATEID,POSTCODE,ADDRESSTYPECODEID,ISPRIMARY,
HISTORICALSTARTDATE, HISTORICALENDDATE, DONOTMAIL,DONOTMAILREASONCODEID, STARTDATE, ENDDATE, DPC, CART, LOT, INFOSOURCECODEID, INFOSOURCECOMMENTS,
COUNTYCODEID, REGIONCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID,
CERTIFICATIONDATA, LASTVALIDATIONATTEMPTDATE, OMITFROMVALIDATION, VALIDATIONMESSAGE
from dbo.BATCHCONSTITUENTUPDATEADDRESSES where
BATCHCONSTITUENTUPDATEID = @ID
order by ISMANUALEXCEPTION desc, ISPRIMARY desc, SEQUENCE
),
INCOMING_PHONE as(
select top 1 ID, BATCHCONSTITUENTUPDATEID,NUMBER,PHONETYPECODEID,DONOTCALL,COUNTRYID,SEASONALSTARTDATE,SEASONALENDDATE,STARTDATE,ENDDATE,STARTTIME,ENDTIME,INFOSOURCECODEID,ISPRIMARY
from dbo.BATCHCONSTITUENTUPDATEPHONES where BATCHCONSTITUENTUPDATEID = @ID
order by ISMANUALEXCEPTION desc, ISPRIMARY desc, SEQUENCE
),
INCOMING_EMAIL as (
select top 1 ID, BATCHCONSTITUENTUPDATEID,EMAILADDRESS,EMAILADDRESSTYPECODEID, DONOTEMAIL, STARTDATE, ENDDATE, INFOSOURCECODEID,ISPRIMARY
from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES where BATCHCONSTITUENTUPDATEID = @ID
order by ISMANUALEXCEPTION desc, ISPRIMARY desc, SEQUENCE
)
select
@NAME = (CASE FIRSTNAME WHEN '' THEN '' ELSE FIRSTNAME + ' ' END
+
CASE MIDDLENAME WHEN '' THEN '' ELSE LEFT(MIDDLENAME,1) + '. ' END
+
KEYNAME),
@LASTNAME = BCU.KEYNAME,
@FIRSTNAME = BCU.FIRSTNAME,
@MIDDLENAME = BCU.MIDDLENAME,
@SUFFIXCODEID = BCU.SUFFIXCODEID,
@BIRTHDATE = BCU.BIRTHDATE,
@ADDRESS_COUNTRYID = BCUA.COUNTRYID,
@ADDRESS_ADDRESSBLOCK = BCUA.ADDRESSBLOCK,
@ADDRESS_CITY = BCUA.CITY,
@ADDRESS_STATEID = BCUA.STATEID,
@ADDRESS_POSTCODE = BCUA.POSTCODE,
@PHONENUMBER = BCUP.NUMBER,
@PHONETYPECODEID = BCUP.PHONETYPECODEID,
@EMAILADDRESS = BCUE.EMAILADDRESS,
@EMAILADDRESSTYPECODEID = BCUE.EMAILADDRESSTYPECODEID,
@BATCHNUMBER = B.BATCHNUMBER,
@CREATEDON = B.DATEADDED,
@BATCHID = B.ID,
@BATCHTYPE = case when BR.ID is not null then 0 else 1 end, -- CUB or ERB
@DATALOADED = 1,
@ADDRESS_TYPECODEID = BCUA.ADDRESSTYPECODEID,
@TITLECODEID = BCU.TITLECODEID,
@PRIMARYRECORDID = BCU.PRIMARYRECORDID,
@NAMECODE = coalesce(BR.NAMECODE,BCU.NAMECODE),
@SIMILARADDRESSCODE = coalesce(BR.SIMILARADDRESSCODE,BCU.SIMILARADDRESSCODE),
@UNSIMILARADDRESSCODE = coalesce(BR.UNSIMILARADDRESSCODE,BCU.UNSIMILARADDRESSCODE),
@NEWADDRESSPRIMARYCODE = coalesce(BR.NEWADDRESSPRIMARYCODE,BCU.NEWADDRESSPRIMARYCODE),
@DIFFERENTPHONECODE = coalesce(BR.DIFFERENTPHONECODE,BCU.DIFFERENTPHONECODE),
@NEWPHONEPRIMARYCODE = coalesce(BR.NEWPHONEPRIMARYCODE,BCU.NEWPHONEPRIMARYCODE),
@DIFFERENTEMAILCODE = coalesce(BR.DIFFERENTEMAILCODE,BCU.DIFFERENTEMAILCODE),
@NEWEMAILPRIMARYCODE = coalesce(BR.NEWEMAILPRIMARYCODE,BCU.NEWEMAILPRIMARYCODE),
@BIRTHDATERULECODE = coalesce(BR.BIRTHDATERULECODE, BCU.BIRTHDATERULECODE),
@INCOMINGADDRESSID = BCUA.ID,
@INCOMINGEMAILID = BCUE.ID,
@INCOMINGPHONEID = BCUP.ID,
@MAIDENNAME = BCU.MAIDENNAME,
@NICKNAME = BCU.NICKNAME,
@GIVESANONYMOUSLY = BCU.GIVESANONYMOUSLY,
@MARITALSTATUSCODEID = BCU.MARITALSTATUSCODEID,
@DECEASED = BCU.DECEASED,
@DECEASEDDATE = BCU.DECEASEDDATE,
@WEBADDRESS = BCU.WEBADDRESS,
@GENDERCODE = BCU.GENDERCODE,
@ADDRESSHISTORICALSTARTDATE = BCUA.HISTORICALSTARTDATE,
@ADDRESSHISTORICALENDDATE = BCUA.HISTORICALENDDATE,
@ADDRESSDONOTMAIL = BCUA.DONOTMAIL,
@ADDRESSDONOTMAILREASONCODEID = BCUA.DONOTMAILREASONCODEID,
@ADDRESSSTARTDATE = BCUA.STARTDATE,
@ADDRESSENDDATE = BCUA.ENDDATE,
@ADDRESSDPC = BCUA.DPC,
@ADDRESSCART = BCUA.CART,
@ADDRESSLOT = BCUA.LOT,
@ADDRESSINFOSOURCECODEID = BCUA.INFOSOURCECODEID,
@ADDRESSINFOSOURCECOMMENTS = BCUA.INFOSOURCECOMMENTS,
@ADDRESSCOUNTYCODEID = BCUA.COUNTYCODEID,
@ADDRESSREGIONCODEID = BCUA.REGIONCODEID,
@ADDRESSCONGRESSIONALDISTRICTCODEID = BCUA.CONGRESSIONALDISTRICTCODEID,
@ADDRESSSTATEHOUSEDISTRICTCODEID = BCUA.STATEHOUSEDISTRICTCODEID,
@ADDRESSSTATESENATEDISTRICTCODEID = BCUA.STATESENATEDISTRICTCODEID,
@ADDRESSLOCALPRECINCTCODEID = BCUA.LOCALPRECINCTCODEID,
@ADDRESSCERTIFICATIONDATA = BCUA.CERTIFICATIONDATA,
@ADDRESSLASTVALIDATIONATTEMPTDATE = BCUA.LASTVALIDATIONATTEMPTDATE,
@ADDRESSOMITFROMVALIDATION = BCUA.OMITFROMVALIDATION,
@ADDRESSVALIDATIONMESSAGE = BCUA.VALIDATIONMESSAGE,
@PHONEDONOTCALL = BCUP.DONOTCALL,
@PHONECOUNTRYID = BCUP.COUNTRYID,
@PHONESEASONALSTARTDATE = BCUP.SEASONALSTARTDATE,
@PHONESEASONALENDDATE = BCUP.SEASONALENDDATE,
@PHONESTARTTIME = BCUP.STARTTIME,
@PHONEENDTIME = BCUP.ENDTIME,
@PHONESTARTDATE = BCUP.STARTDATE,
@PHONEENDDATE = BCUP.ENDDATE,
@PHONEINFOSOURCECODEID = BCUP.INFOSOURCECODEID,
@EMAILADDRESSDONOTEMAIL = BCUE.DONOTEMAIL,
@EMAILADDRESSSTARTDATE = BCUE.STARTDATE,
@EMAILADDRESSENDDATE = BCUE.ENDDATE,
@EMAILADDRESSINFOSOURCECODEID = BCUE.INFOSOURCECODEID,
@NAMEFORMATS = dbo.UFN_BATCHCONSTITUENTUPDATE_GETNAMEFORMATS_TOITEMLISTXML(BCU.ID),
@ADDRESSISPRIMARY = BCUA.ISPRIMARY,
@PHONEISPRIMARY = BCUP.ISPRIMARY,
@EMAILISPRIMARY = BCUE.ISPRIMARY,
@ORIGINAL_KEYNAME = BCU.ORIGINAL_KEYNAME,
@ORIGINAL_FIRSTNAME = BCU.ORIGINAL_FIRSTNAME,
@GENDERCODEID = BCU.GENDERCODEID
from dbo.BATCHCONSTITUENTUPDATE BCU
outer apply INCOMING_ADDRESS BCUA
outer apply INCOMING_PHONE BCUP
outer apply INCOMING_EMAIL BCUE
inner join dbo.BATCH B on B.ID = BCU.BATCHID
left join dbo.BATCHREVENUE BR on BR.ID = BCU.ID
where BCU.ID = @ID
end
if @NAME is null
begin
select
@NAME = BRC.NAME,
@LASTNAME = BRC.KEYNAME,
@FIRSTNAME = BRC.FIRSTNAME,
@MIDDLENAME = BRC.MIDDLENAME,
@SUFFIXCODEID = BRC.SUFFIXCODEID,
@BIRTHDATE = BRC.BIRTHDATE,
@ADDRESS_COUNTRYID = BRC.COUNTRYID,
@ADDRESS_ADDRESSBLOCK = BRC.ADDRESSBLOCK,
@ADDRESS_CITY = BRC.CITY,
@ADDRESS_STATEID = BRC.STATEID,
@ADDRESS_POSTCODE = BRC.POSTCODE,
@PHONENUMBER = BRC.NUMBER,
@EMAILADDRESS = BRC.EMAILADDRESS,
@BATCHNUMBER = B.BATCHNUMBER,
@CREATEDON = B.DATEADDED,
@BATCHID = B.ID,
@BATCHTYPE = 2, -- MDB
@DATALOADED = 1,
@ADDRESS_TYPECODEID = BRC.ADDRESSTYPECODEID,
@TITLECODEID = BRC.TITLECODEID,
@PHONETYPECODEID = BRC.PHONETYPECODEID,
@EMAILADDRESSTYPECODEID = BRC.EMAILADDRESSTYPECODEID,
@ADDRESSTYPECODEID = BRC.ADDRESSTYPECODEID,
@NAMECODE = NAMECODE,
@SIMILARADDRESSCODE = SIMILARADDRESSCODE,
@UNSIMILARADDRESSCODE = UNSIMILARADDRESSCODE,
@NEWADDRESSPRIMARYCODE = NEWADDRESSPRIMARYCODE,
@DIFFERENTPHONECODE = DIFFERENTPHONECODE,
@NEWPHONEPRIMARYCODE = NEWPHONEPRIMARYCODE,
@DIFFERENTEMAILCODE = DIFFERENTEMAILCODE,
@NEWEMAILPRIMARYCODE = NEWEMAILPRIMARYCODE,
@BIRTHDATERULECODE = BIRTHDATERULECODE,
@MAIDENNAME = BRC.MAIDENNAME,
@NICKNAME = BRC.NICKNAME,
@GIVESANONYMOUSLY = BRC.GIVESANONYMOUSLY,
@MARITALSTATUSCODEID = BRC.MARITALSTATUSCODEID,
@WEBADDRESS = BRC.WEBADDRESS,
@GENDERCODE = BRC.GENDERCODE,
@ADDRESSDONOTMAIL = DONOTMAIL,
@ADDRESSDONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
@ADDRESSDPC = DPC,
@ADDRESSCART = CART,
@ADDRESSLOT = LOT,
@ADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
@ADDRESSCOUNTYCODEID = COUNTYCODEID,
@ADDRESSCONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
@ADDRESSCERTIFICATIONDATA = CERTIFICATIONDATA,
@ADDRESSLASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
@ADDRESSOMITFROMVALIDATION = OMITFROMVALIDATION,
@ADDRESSVALIDATIONMESSAGE = VALIDATIONMESSAGE,
@PHONEDONOTCALL = DONOTCALL,
@PHONEINFOSOURCECODEID = INFOSOURCECODEID,
@EMAILADDRESSDONOTEMAIL = DONOTEMAIL,
@EMAILADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
@NAMEFORMATS = BRC.NAMEFORMATS,
@ORIGINAL_KEYNAME = BRC.ORIGINAL_LASTNAME,
@ORIGINAL_FIRSTNAME = BRC.ORIGINAL_FIRSTNAME,
@GENDERCODEID = BRC.GENDERCODEID
from dbo.BATCHREVENUECONSTITUENT BRC
inner join dbo.BATCHMEMBERSHIPDUES BMD on BRC.ID = BMD.BILLTOCONSTITUENTID
inner join dbo.BATCH B on B.ID = BMD.BATCHID
where BMD.ID = @ID
end