USP_WPREALESTATE_ADD
Adds WealthPoint Real Estate information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @LXID | int | IN | |
| @REVISION | int | IN | |
| @ORIGINALID | int | IN | |
| @NEWROW | bit | IN | |
| @CDATE | datetime | IN | |
| @CVALUE | money | IN | |
| @CNOTES | nvarchar(1024) | IN | |
| @FULLHASH | nvarchar(32) | IN | |
| @PARTIALHASH | nvarchar(32) | IN | |
| @MC | nvarchar(3) | IN | |
| @DEEDTYPE | nvarchar(100) | IN | |
| @DOCUMENTNUMBER | nvarchar(200) | IN | |
| @SALEPRICE | money | IN | |
| @RECORDEDDATE | nvarchar(200) | IN | |
| @SALEDATE | datetime | IN | |
| @BUYERMAILINGADDRESS | nvarchar(200) | IN | |
| @SELLERMAILINGADDRESS | nvarchar(200) | IN | |
| @SELLER | nvarchar(300) | IN | |
| @BUYER | nvarchar(300) | IN | |
| @LEGALDESCRIPTION | nvarchar(2000) | IN | |
| @PROPERTYUSE | nvarchar(100) | IN | |
| @LOTSIZE | nvarchar(50) | IN | |
| @ROOF | nvarchar(30) | IN | |
| @FOUNDATION | nvarchar(30) | IN | |
| @EXTERIORWALLS | nvarchar(30) | IN | |
| @BASEMENT | nvarchar(30) | IN | |
| @CONSTRUCTION | nvarchar(30) | IN | |
| @HEATING | nvarchar(30) | IN | |
| @AIRCONDITIONING | nvarchar(30) | IN | |
| @STYLE | nvarchar(30) | IN | |
| @ELEVATOR | nvarchar(5) | IN | |
| @NOOFBUILDINGS | nvarchar(5) | IN | |
| @BUILDINGAREA | nvarchar(10) | IN | |
| @GARAGETYPE | nvarchar(30) | IN | |
| @FIREPLACE | nvarchar(5) | IN | |
| @TOTALROOMS | nvarchar(5) | IN | |
| @PARTIALBATHS | nvarchar(5) | IN | |
| @BATHS | nvarchar(5) | IN | |
| @BEDROOMS | nvarchar(5) | IN | |
| @UNITS | nvarchar(10) | IN | |
| @STORIES | nvarchar(50) | IN | |
| @POOLSPA | nvarchar(30) | IN | |
| @YEARBUILT | UDT_YEAR | IN | |
| @OWNER | nvarchar(300) | IN | |
| @BB_COUNTY | nvarchar(255) | IN | |
| @MAILINGADDRESS | nvarchar(200) | IN | |
| @TITLECOMPANY | nvarchar(50) | IN | |
| @TERM | nvarchar(20) | IN | |
| @LOANAMOUNT | money | IN | |
| @TYPEOFMORTGAGE | nvarchar(100) | IN | |
| @LENDER | nvarchar(100) | IN | |
| @LENDERTYPE | nvarchar(50) | IN | |
| @LOANTYPE | nvarchar(50) | IN | |
| @DUEDATE | datetime | IN | |
| @ASSESSORSPARCELNUMBER | nvarchar(100) | IN | |
| @LANDUSE | nvarchar(100) | IN | |
| @TAXRATECODE | nvarchar(50) | IN | |
| @MARKETIMPROVEMENTVALUE | money | IN | |
| @TOTALMARKETVALUE | money | IN | |
| @MARKETLANDVALUE | money | IN | |
| @MARKETVALUEYEAR | UDT_YEAR | IN | |
| @ASSESSEDMULTIPLIER | nvarchar(5) | IN | |
| @ASSESSMENTYEAR | UDT_YEAR | IN | |
| @ASSESSEDIMPROVEMENTVALUE | money | IN | |
| @ASSESSEDTOTALVALUE | money | IN | |
| @ASSESSEDLANDVALUE | money | IN | |
| @PROPADDRESS | nvarchar(150) | IN | |
| @PROPCITY | nvarchar(50) | IN | |
| @PROPSTATE | nvarchar(100) | IN | |
| @PROPZIP | nvarchar(10) | IN | |
| @BB_VALUE | money | IN | |
| @INPUT_BBAID | nvarchar(100) | IN | |
| @PROPERTYRECORDFOR | nvarchar(50) | IN | |
| @PROPERTYTRANSFERRECORDFOR | nvarchar(50) | IN | |
| @BORROWERS | nvarchar(200) | IN | |
| @MORTGAGERECORDFOR | nvarchar(50) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @REPORTTYPE | nvarchar(20) | IN | |
| @TAPECUTDATE | UDT_FUZZYDATE | IN | |
| @OWNERCAREOFNAME | nvarchar(40) | IN | |
| @ASSESSEENAME | nvarchar(80) | IN | |
| @ASSESSEENAME2 | nvarchar(60) | IN | |
| @ASSESSEECAREOFNAME | nvarchar(60) | IN | |
| @ASSESSEEMAILINGADDRESS | nvarchar(200) | IN | |
| @ASSESSMENTRECORDINGDATE | datetime | IN | |
| @DOCUMENTTYPE | nvarchar(25) | IN | |
| @ASSESSMENTSALEPRICE | money | IN | |
| @ASSESSMENTSALEPRICECODE | nvarchar(130) | IN | |
| @LEGALLOTNUMBER | nvarchar(7) | IN | |
| @LEGALBLOCK | nvarchar(7) | IN | |
| @LEGALUNIT | nvarchar(6) | IN | |
| @LEGALCITY | nvarchar(30) | IN | |
| @LEGALSUBDIVISION | nvarchar(40) | IN | |
| @LANDUSECODE | nvarchar(4) | IN | |
| @PARKINGNUMBEROFCARS | nvarchar(5) | IN | |
| @BUILDINGCLASS | nvarchar(250) | IN | |
| @BUYER2 | nvarchar(300) | IN | |
| @BUYERVESTING | nvarchar(50) | IN | |
| @BUYERCAREOFNAME | nvarchar(40) | IN | |
| @SELLER2 | nvarchar(300) | IN | |
| @PROPUSECODE | nvarchar(45) | IN | |
| @CONCURRENTLOANAMOUNT | money | IN | |
| @CONCURRENTLOANTYPE | nvarchar(35) | IN | |
| @CONCURRENTTYPEFINANCING | nvarchar(30) | IN | |
| @CONCURRENTDUEDATE | datetime | IN | |
| @CONCURRENTLENDERNAME | nvarchar(40) | IN | |
| @DEEDTITLECOMPANY | nvarchar(28) | IN | |
| @DEEDLEGALLOTCODE | nvarchar(2) | IN | |
| @DEEDLEGALLOTNUMBER | nvarchar(10) | IN | |
| @DEEDLEGALBLOCK | nvarchar(7) | IN | |
| @DEEDLEGALUNIT | nvarchar(6) | IN | |
| @DEEDLEGALCITY | nvarchar(30) | IN | |
| @DEEDLEGALSUBDIVISION | nvarchar(50) | IN | |
| @DEEDLEGALBRIEFDESCRIPTION | nvarchar(100) | IN | |
| @DEEDRECORDINGDATE | datetime | IN | |
| @MORTRECORDINGDATE | datetime | IN | |
| @BORROWERS2 | nvarchar(200) | IN | |
| @VESTINGCODE | nvarchar(50) | IN | |
| @BORROWERMAILINGADDRESS | nvarchar(200) | IN | |
| @ORIGINALCONTRACTDATE | datetime | IN | |
| @MORT2_RECORDINGDATE | datetime | IN | |
| @MORT2_BORROWERS | nvarchar(200) | IN | |
| @MORT2_BORROWERS2 | nvarchar(200) | IN | |
| @MORT2_VESTINGCODE | nvarchar(50) | IN | |
| @MORT2_BORROWERMAILINGADDRESS | nvarchar(200) | IN | |
| @MORT2_ORIGINALCONTRACTDATE | datetime | IN | |
| @MORT2_LOANAMOUNT | money | IN | |
| @MORT2_LOANTYPE | nvarchar(50) | IN | |
| @MORT2_TYPEOFMORTGAGE | nvarchar(100) | IN | |
| @MORT2_DUEDATE | datetime | IN | |
| @MORT2_LENDER | nvarchar(100) | IN | |
| @ESTIMATEDVALUE | money | IN | |
| @ESTIMATEDVALUEDATE | datetime | IN | |
| @CONFIDENCESCORE | int | IN | |
| @CONFIDENCESCOREDATE | datetime | IN | |
| @SUMMARYSALESVALUE | money | IN | |
| @SUMMARYSALESRECORDINGDATE | datetime | IN | |
| @PROPERTYVALUATION | money | IN | |
| @FIPSCODE | nvarchar(5) | IN | |
| @SETASCURRENT | bit | IN | |
| @WPSEARCHHISTORYID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_WPREALESTATE_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@LXID int = 0,
@REVISION int = 0,
@ORIGINALID int = 0,
@NEWROW bit = 0,
@CDATE datetime = null,
@CVALUE money = 0,
@CNOTES nvarchar(1024) = '',
@FULLHASH nvarchar(32) = '',
@PARTIALHASH nvarchar(32) = '',
@MC nvarchar(3) = '',
@DEEDTYPE nvarchar(100) = '',
@DOCUMENTNUMBER nvarchar(200) = '',
@SALEPRICE money = 0,
@RECORDEDDATE nvarchar(200) = '',
@SALEDATE datetime = null,
@BUYERMAILINGADDRESS nvarchar(200) = '',
@SELLERMAILINGADDRESS nvarchar(200) = '',
@SELLER nvarchar(300) = '',
@BUYER nvarchar(300) = '',
@LEGALDESCRIPTION nvarchar(2000) = '',
@PROPERTYUSE nvarchar(100) = '',
@LOTSIZE nvarchar(50) = '',
@ROOF nvarchar(30) = '',
@FOUNDATION nvarchar(30) = '',
@EXTERIORWALLS nvarchar(30) = '',
@BASEMENT nvarchar(30) = '',
@CONSTRUCTION nvarchar(30) = '',
@HEATING nvarchar(30) = '',
@AIRCONDITIONING nvarchar(30) = '',
@STYLE nvarchar(30) = '',
@ELEVATOR nvarchar(5) = '',
@NOOFBUILDINGS nvarchar(5) = '',
@BUILDINGAREA nvarchar(10) = '',
@GARAGETYPE nvarchar(30) = '',
@FIREPLACE nvarchar(5) = '',
@TOTALROOMS nvarchar(5) = '',
@PARTIALBATHS nvarchar(5) = '',
@BATHS nvarchar(5) = '',
@BEDROOMS nvarchar(5) = '',
@UNITS nvarchar(10) = '',
@STORIES nvarchar(50) = '',
@POOLSPA nvarchar(30) = '',
@YEARBUILT dbo.UDT_YEAR = 0,
@OWNER nvarchar(300) = '',
@BB_COUNTY nvarchar(255) = '',
@MAILINGADDRESS nvarchar(200) = '',
@TITLECOMPANY nvarchar(50) = '',
@TERM nvarchar(20) = '',
@LOANAMOUNT money = 0,
@TYPEOFMORTGAGE nvarchar(100) = '',
@LENDER nvarchar(100) = '',
@LENDERTYPE nvarchar(50) = '',
@LOANTYPE nvarchar(50) = '',
@DUEDATE datetime = null,
@ASSESSORSPARCELNUMBER nvarchar(100) = '',
@LANDUSE nvarchar(100) = '',
@TAXRATECODE nvarchar(50) = '',
@MARKETIMPROVEMENTVALUE money = 0,
@TOTALMARKETVALUE money = 0,
@MARKETLANDVALUE money = 0,
@MARKETVALUEYEAR dbo.UDT_YEAR = 0,
@ASSESSEDMULTIPLIER nvarchar(5) = '',
@ASSESSMENTYEAR dbo.UDT_YEAR = 0,
@ASSESSEDIMPROVEMENTVALUE money = 0,
@ASSESSEDTOTALVALUE money = 0,
@ASSESSEDLANDVALUE money = 0,
@PROPADDRESS nvarchar(150) = '',
@PROPCITY nvarchar(50) = '',
@PROPSTATE nvarchar(100) = '',
@PROPZIP nvarchar(10) = '',
@BB_VALUE money = null,
@INPUT_BBAID nvarchar(100) = '',
@PROPERTYRECORDFOR nvarchar(50) = '',
@PROPERTYTRANSFERRECORDFOR nvarchar(50) = '',
@BORROWERS nvarchar(200) = '',
@MORTGAGERECORDFOR nvarchar(50) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@RECURSIVEMATCH bit = 0,
@REPORTTYPE nvarchar(20) = '',
@TAPECUTDATE dbo.UDT_FUZZYDATE = '00000000',
@OWNERCAREOFNAME nvarchar(40) = '',
@ASSESSEENAME nvarchar(80) = '',
@ASSESSEENAME2 nvarchar(60) = '',
@ASSESSEECAREOFNAME nvarchar(60) = '',
@ASSESSEEMAILINGADDRESS nvarchar(200) = '',
@ASSESSMENTRECORDINGDATE datetime = null,
@DOCUMENTTYPE nvarchar(25) = '',
@ASSESSMENTSALEPRICE money = 0,
@ASSESSMENTSALEPRICECODE nvarchar(130) = '',
@LEGALLOTNUMBER nvarchar(7) = '',
@LEGALBLOCK nvarchar(7) = '',
@LEGALUNIT nvarchar(6) = '',
@LEGALCITY nvarchar(30) = '',
@LEGALSUBDIVISION nvarchar(40) = '',
@LANDUSECODE nvarchar(4) = '',
@PARKINGNUMBEROFCARS nvarchar(5) = '',
@BUILDINGCLASS nvarchar(250) = '',
@BUYER2 nvarchar(300) = '',
@BUYERVESTING nvarchar(50) = '',
@BUYERCAREOFNAME nvarchar(40) = '',
@SELLER2 nvarchar(300) = '',
@PROPUSECODE nvarchar(45) = '',
@CONCURRENTLOANAMOUNT money = 0,
@CONCURRENTLOANTYPE nvarchar(35) = '',
@CONCURRENTTYPEFINANCING nvarchar(30) = '',
@CONCURRENTDUEDATE datetime = null,
@CONCURRENTLENDERNAME nvarchar(40) = '',
@DEEDTITLECOMPANY nvarchar(28) = '',
@DEEDLEGALLOTCODE nvarchar(2) = '',
@DEEDLEGALLOTNUMBER nvarchar(10) = '',
@DEEDLEGALBLOCK nvarchar(7) = '',
@DEEDLEGALUNIT nvarchar(6) = '',
@DEEDLEGALCITY nvarchar(30) = '',
@DEEDLEGALSUBDIVISION nvarchar(50) = '',
@DEEDLEGALBRIEFDESCRIPTION nvarchar(100) = '',
@DEEDRECORDINGDATE datetime = null,
@MORTRECORDINGDATE datetime = null,
@BORROWERS2 nvarchar(200) = '',
@VESTINGCODE nvarchar(50) = '',
@BORROWERMAILINGADDRESS nvarchar(200) = '',
@ORIGINALCONTRACTDATE datetime = null,
@MORT2_RECORDINGDATE datetime = null,
@MORT2_BORROWERS nvarchar(200) = '',
@MORT2_BORROWERS2 nvarchar(200) = '',
@MORT2_VESTINGCODE nvarchar(50) = '',
@MORT2_BORROWERMAILINGADDRESS nvarchar(200) = '',
@MORT2_ORIGINALCONTRACTDATE datetime = null,
@MORT2_LOANAMOUNT money = 0,
@MORT2_LOANTYPE nvarchar(50) = '',
@MORT2_TYPEOFMORTGAGE nvarchar(100) = '',
@MORT2_DUEDATE datetime = null,
@MORT2_LENDER nvarchar(100) = '',
@ESTIMATEDVALUE money = 0,
@ESTIMATEDVALUEDATE datetime = null,
@CONFIDENCESCORE int = 0,
@CONFIDENCESCOREDATE datetime = null,
@SUMMARYSALESVALUE money = 0,
@SUMMARYSALESRECORDINGDATE datetime = null,
@PROPERTYVALUATION money = 0,
@FIPSCODE nvarchar(5) = '',
@SETASCURRENT bit = 0,
@WPSEARCHHISTORYID uniqueidentifier = null
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
begin try
declare @NEWRECORD bit;
declare @UPDATEDRECORD bit;
declare @CURRENTDATE datetime;
set @NEWRECORD = 0;
set @UPDATEDRECORD = 0;
set @CURRENTDATE = getdate();
if @YEARBUILT<(1753) OR @YEARBUILT>(9999)
set @YEARBUILT = 0
--Get any existing ID
select
@ID = ID
from
dbo.WPREALESTATE
where
WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH;
declare @CONFIRMED bit;
declare @CONFIRMEDBYAPPUSERID uniqueidentifier;
declare @DATECONFIRMED datetime;
declare @REJECTED bit;
declare @REJECTEDBYAPPUSERID uniqueidentifier;
declare @DATEREJECTED datetime;
set @CONFIRMED = 0;
set @REJECTED = 0;
if @CURRENTAPPUSERID is not null
begin
declare @CONFIDENCE int
select
@CONFIDENCE = coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE)
from
dbo.MATCHCODE MC
left outer join
dbo.WEALTHSOURCE WS on @SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and CR.WEALTHSOURCEID = WS.ID
where
MC.MATCHCODE = @MC
select
@REJECTED = case @CONFIDENCE
when 5 then 0
when 0 then 1
end,
@DATEREJECTED = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTDATE
end,
@REJECTEDBYAPPUSERID = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTAPPUSERID
end,
@CONFIRMED = case @CONFIDENCE
when 5 then 1
when 0 then 0
end,
@DATECONFIRMED = case @CONFIDENCE
when 5 then @CURRENTDATE
when 0 then NULL
end,
@CONFIRMEDBYAPPUSERID = case @CONFIDENCE
when 5 then @CURRENTAPPUSERID
when 0 then NULL
end
where
@CONFIDENCE in (0,5);
end
-- Record exists
if @ID is not null
begin
if not exists(select 1 from dbo.WPREALESTATE where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
--Record exists but needs to be updated
set @UPDATEDRECORD = 1
update
dbo.WPREALESTATE
set
FULLHASH = @FULLHASH,
MC = coalesce(nullif(@MC, 'EPB'), MC),
SOURCE = @SOURCE,
RECURSIVEMATCH = @RECURSIVEMATCH,
DEEDTYPE = coalesce(nullif(@DEEDTYPE, ''), DEEDTYPE),
SALEPRICE = coalesce(nullif(@SALEPRICE, 0), SALEPRICE),
SALEDATE = coalesce(@SALEDATE, SALEDATE),
BUYERMAILINGADDRESS = coalesce(nullif(@BUYERMAILINGADDRESS, ''), BUYERMAILINGADDRESS),
SELLERMAILINGADDRESS = coalesce(nullif(@SELLERMAILINGADDRESS, ''), SELLERMAILINGADDRESS),
SELLER = coalesce(nullif(@SELLER, ''), SELLER),
BUYER = coalesce(nullif(@BUYER, ''), BUYER),
LEGALDESCRIPTION = coalesce(nullif(@LEGALDESCRIPTION, ''), LEGALDESCRIPTION),
PROPERTYUSE = coalesce(nullif(@PROPERTYUSE, ''), PROPERTYUSE),
LOTSIZE = coalesce(nullif(@LOTSIZE, ''), LOTSIZE),
ROOF = coalesce(nullif(@ROOF, ''), ROOF),
FOUNDATION = coalesce(nullif(@FOUNDATION, ''), FOUNDATION),
EXTERIORWALLS = coalesce(nullif(@EXTERIORWALLS, ''), EXTERIORWALLS),
BASEMENT = coalesce(nullif(@BASEMENT, ''), BASEMENT),
CONSTRUCTION = coalesce(nullif(@CONSTRUCTION, ''), CONSTRUCTION),
HEATING = coalesce(nullif(@HEATING, ''), HEATING),
AIRCONDITIONING = coalesce(nullif(@AIRCONDITIONING, ''), AIRCONDITIONING),
STYLE = coalesce(nullif(@STYLE, ''), STYLE),
ELEVATOR = coalesce(nullif(@ELEVATOR, ''), ELEVATOR),
NOOFBUILDINGS = coalesce(nullif(@NOOFBUILDINGS, ''), NOOFBUILDINGS),
BUILDINGAREA = coalesce(nullif(@BUILDINGAREA, ''), BUILDINGAREA),
GARAGETYPE = coalesce(nullif(@GARAGETYPE, ''), GARAGETYPE),
FIREPLACE = coalesce(nullif(@FIREPLACE, ''), FIREPLACE),
TOTALROOMS = coalesce(nullif(@TOTALROOMS, ''), TOTALROOMS),
PARTIALBATHS = coalesce(nullif(@PARTIALBATHS, ''), PARTIALBATHS),
BATHS = coalesce(nullif(@BATHS, ''), BATHS),
BEDROOMS = coalesce(nullif(@BEDROOMS, ''), BEDROOMS),
UNITS = coalesce(nullif(@UNITS, ''), UNITS),
STORIES = coalesce(nullif(@STORIES, ''), STORIES),
POOLSPA = coalesce(nullif(@POOLSPA, ''), POOLSPA),
YEARBUILT = coalesce(nullif(@YEARBUILT, 0), YEARBUILT),
OWNER = coalesce(nullif(@OWNER, ''), OWNER),
BB_COUNTY = coalesce(nullif(@BB_COUNTY, ''), BB_COUNTY),
MAILINGADDRESS = coalesce(nullif(@MAILINGADDRESS, ''), MAILINGADDRESS),
LOANAMOUNT = coalesce(nullif(@LOANAMOUNT, 0), LOANAMOUNT),
TYPEOFMORTGAGE = coalesce(nullif(@TYPEOFMORTGAGE, ''), TYPEOFMORTGAGE),
LENDER = coalesce(nullif(@LENDER, ''), LENDER),
LOANTYPE = coalesce(nullif(@LOANTYPE, ''), LOANTYPE),
DUEDATE = coalesce(@DUEDATE, DUEDATE),
ASSESSORSPARCELNUMBER = coalesce(nullif(@ASSESSORSPARCELNUMBER, ''), ASSESSORSPARCELNUMBER),
LANDUSE = coalesce(nullif(@LANDUSE, ''), LANDUSE),
MARKETIMPROVEMENTVALUE = coalesce(nullif(@MARKETIMPROVEMENTVALUE, 0), MARKETIMPROVEMENTVALUE),
TOTALMARKETVALUE = coalesce(nullif(@TOTALMARKETVALUE, 0), TOTALMARKETVALUE),
MARKETLANDVALUE = coalesce(nullif(@MARKETLANDVALUE, 0), MARKETLANDVALUE),
MARKETVALUEYEAR = coalesce(nullif(@MARKETVALUEYEAR, 0), MARKETVALUEYEAR),
ASSESSMENTYEAR = coalesce(nullif(@ASSESSMENTYEAR, 0), ASSESSMENTYEAR),
ASSESSEDIMPROVEMENTVALUE = coalesce(nullif(@ASSESSEDIMPROVEMENTVALUE, 0), ASSESSEDIMPROVEMENTVALUE),
ASSESSEDTOTALVALUE = coalesce(nullif(@ASSESSEDTOTALVALUE, 0), ASSESSEDTOTALVALUE),
ASSESSEDLANDVALUE = coalesce(nullif(@ASSESSEDLANDVALUE, 0), ASSESSEDLANDVALUE),
PROPADDRESS = coalesce(nullif(@PROPADDRESS, ''), PROPADDRESS),
PROPCITY = coalesce(nullif(@PROPCITY, ''), PROPCITY),
PROPSTATE = coalesce(nullif(@PROPSTATE, ''), PROPSTATE),
PROPZIP = coalesce(nullif(@PROPZIP, ''), PROPZIP),
BORROWERS = coalesce(nullif(@BORROWERS, ''), BORROWERS),
TAPECUTDATE = coalesce(nullif(@TAPECUTDATE, ''), TAPECUTDATE),
OWNERCAREOFNAME = coalesce(nullif(@OWNERCAREOFNAME, ''), OWNERCAREOFNAME),
ASSESSEENAME = coalesce(nullif(@ASSESSEENAME, ''), ASSESSEENAME),
ASSESSEENAME2 = coalesce(nullif(@ASSESSEENAME2, ''), ASSESSEENAME2),
ASSESSEECAREOFNAME = coalesce(nullif(@ASSESSEECAREOFNAME, ''), ASSESSEECAREOFNAME),
ASSESSEEMAILINGADDRESS = coalesce(nullif(@ASSESSEEMAILINGADDRESS, ''), ASSESSEEMAILINGADDRESS),
ASSESSMENTRECORDINGDATE = coalesce(@ASSESSMENTRECORDINGDATE, ASSESSMENTRECORDINGDATE),
DOCUMENTTYPE = coalesce(nullif(@DOCUMENTTYPE, ''), DOCUMENTTYPE),
ASSESSMENTSALEPRICE = coalesce(nullif(@ASSESSMENTSALEPRICE, 0), ASSESSMENTSALEPRICE),
ASSESSMENTSALEPRICECODE = coalesce(nullif(@ASSESSMENTSALEPRICECODE, ''), ASSESSMENTSALEPRICECODE),
LEGALLOTNUMBER = coalesce(nullif(@LEGALLOTNUMBER, ''), LEGALLOTNUMBER),
LEGALBLOCK = coalesce(nullif(@LEGALBLOCK, ''), LEGALBLOCK),
LEGALUNIT = coalesce(nullif(@LEGALUNIT, ''), LEGALUNIT),
LEGALCITY = coalesce(nullif(@LEGALCITY, ''), LEGALCITY),
LEGALSUBDIVISION = coalesce(nullif(@LEGALSUBDIVISION, ''), LEGALSUBDIVISION),
LANDUSECODE = coalesce(nullif(@LANDUSECODE, ''), LANDUSECODE),
PARKINGNUMBEROFCARS = coalesce(nullif(@PARKINGNUMBEROFCARS, ''), PARKINGNUMBEROFCARS),
BUILDINGCLASS = coalesce(nullif(@BUILDINGCLASS, ''), BUILDINGCLASS),
BUYER2 = coalesce(nullif(@BUYER2, ''), BUYER2),
BUYERVESTING = coalesce(nullif(@BUYERVESTING, ''), BUYERVESTING),
BUYERCAREOFNAME = coalesce(nullif(@BUYERCAREOFNAME, ''), BUYERCAREOFNAME),
SELLER2 = coalesce(nullif(@SELLER2, ''), SELLER2),
PROPUSECODE = coalesce(nullif(@PROPUSECODE, ''), PROPUSECODE),
CONCURRENTLOANAMOUNT = coalesce(nullif(@CONCURRENTLOANAMOUNT, 0), CONCURRENTLOANAMOUNT),
CONCURRENTLOANTYPE = coalesce(nullif(@CONCURRENTLOANTYPE, ''), CONCURRENTLOANTYPE),
CONCURRENTTYPEFINANCING = coalesce(nullif(@CONCURRENTTYPEFINANCING, ''), CONCURRENTTYPEFINANCING),
CONCURRENTDUEDATE = coalesce(@CONCURRENTDUEDATE, CONCURRENTDUEDATE),
CONCURRENTLENDERNAME = coalesce(nullif(@CONCURRENTLENDERNAME, ''), CONCURRENTLENDERNAME),
DEEDTITLECOMPANY = coalesce(nullif(@DEEDTITLECOMPANY, ''), DEEDTITLECOMPANY),
DEEDLEGALLOTCODE = coalesce(nullif(@DEEDLEGALLOTCODE, ''), DEEDLEGALLOTCODE),
DEEDLEGALLOTNUMBER = coalesce(nullif(@DEEDLEGALLOTNUMBER, ''), DEEDLEGALLOTNUMBER),
DEEDLEGALBLOCK = coalesce(nullif(@DEEDLEGALBLOCK, ''), DEEDLEGALBLOCK),
DEEDLEGALUNIT = coalesce(nullif(@DEEDLEGALUNIT, ''), DEEDLEGALUNIT),
DEEDLEGALCITY = coalesce(nullif(@DEEDLEGALCITY, ''), DEEDLEGALCITY),
DEEDLEGALSUBDIVISION = coalesce(nullif(@DEEDLEGALSUBDIVISION, ''), DEEDLEGALSUBDIVISION),
DEEDLEGALBRIEFDESCRIPTION = coalesce(nullif(@DEEDLEGALBRIEFDESCRIPTION, ''), DEEDLEGALBRIEFDESCRIPTION),
DEEDRECORDINGDATE = coalesce(@DEEDRECORDINGDATE, DEEDRECORDINGDATE),
MORTRECORDINGDATE = coalesce(@MORTRECORDINGDATE, MORTRECORDINGDATE),
BORROWERS2 = coalesce(nullif(@BORROWERS2, ''), BORROWERS2),
VESTINGCODE = coalesce(nullif(@VESTINGCODE, ''), VESTINGCODE),
BORROWERMAILINGADDRESS = coalesce(nullif(@BORROWERMAILINGADDRESS, ''), BORROWERMAILINGADDRESS),
ORIGINALCONTRACTDATE = coalesce(@ORIGINALCONTRACTDATE, ORIGINALCONTRACTDATE),
MORT2_RECORDINGDATE = coalesce(@MORT2_RECORDINGDATE, MORT2_RECORDINGDATE),
MORT2_BORROWERS = coalesce(nullif(@MORT2_BORROWERS, ''), MORT2_BORROWERS),
MORT2_BORROWERS2 = coalesce(nullif(@MORT2_BORROWERS2, ''), MORT2_BORROWERS2),
MORT2_VESTINGCODE = coalesce(nullif(@MORT2_VESTINGCODE, ''), MORT2_VESTINGCODE),
MORT2_BORROWERMAILINGADDRESS = coalesce(nullif(@MORT2_BORROWERMAILINGADDRESS, ''), MORT2_BORROWERMAILINGADDRESS),
MORT2_ORIGINALCONTRACTDATE = coalesce(@MORT2_ORIGINALCONTRACTDATE, MORT2_ORIGINALCONTRACTDATE),
MORT2_LOANAMOUNT = coalesce(nullif(@MORT2_LOANAMOUNT, 0), MORT2_LOANAMOUNT),
MORT2_LOANTYPE = coalesce(nullif(@MORT2_LOANTYPE, ''), MORT2_LOANTYPE),
MORT2_TYPEOFMORTGAGE = coalesce(nullif(@MORT2_TYPEOFMORTGAGE, ''), MORT2_TYPEOFMORTGAGE),
MORT2_DUEDATE = coalesce(@MORT2_DUEDATE, MORT2_DUEDATE),
MORT2_LENDER = coalesce(nullif(@MORT2_LENDER, ''), MORT2_LENDER),
ESTIMATEDVALUE = coalesce(nullif(@ESTIMATEDVALUE, 0), ESTIMATEDVALUE),
ESTIMATEDVALUEDATE = coalesce(@ESTIMATEDVALUEDATE, ESTIMATEDVALUEDATE),
CONFIDENCESCORE = coalesce(nullif(@CONFIDENCESCORE, 0), CONFIDENCESCORE),
CONFIDENCESCOREDATE = coalesce(@CONFIDENCESCOREDATE, CONFIDENCESCOREDATE),
SUMMARYSALESVALUE = coalesce(nullif(@SUMMARYSALESVALUE, 0), SUMMARYSALESVALUE),
SUMMARYSALESRECORDINGDATE = coalesce(@SUMMARYSALESRECORDINGDATE, SUMMARYSALESRECORDINGDATE),
PROPERTYVALUATION = coalesce(nullif(@PROPERTYVALUATION, 0), PROPERTYVALUATION),
FIPSCODE = coalesce(nullif(@FIPSCODE, ''), FIPSCODE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
CONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then 1 else CONFIRMED end,
CONFIRMEDBYAPPUSERID = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @CONFIRMEDBYAPPUSERID else CONFIRMEDBYAPPUSERID end,
DATECONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @DATECONFIRMED else DATECONFIRMED end,
HISTORICCODE = case when HISTORICSET = 0 then 0 else HISTORIC end
where
WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH;
end
else
begin
update
dbo.WPREALESTATE
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
HISTORICCODE = 0
where
WEALTHID=@WEALTHID and FULLHASH = @FULLHASH and HISTORICSET = 0;
end
end
else
begin
--Record is new and needs to be Added
set @NEWRECORD = 1
if @ID is null
set @ID = newid();
insert into dbo.WPREALESTATE (
ID,
WEALTHID,
SOURCE,
LXID,
REVISION,
ORIGINALID,
NEWROW,
CDATE,
CVALUE,
CNOTES,
FULLHASH,
PARTIALHASH,
MC,
DEEDTYPE,
DOCUMENTNUMBER,
SALEPRICE,
RECORDEDDATE,
SALEDATE,
BUYERMAILINGADDRESS,
SELLERMAILINGADDRESS,
SELLER,
BUYER,
LEGALDESCRIPTION,
PROPERTYUSE,
LOTSIZE,
ROOF,
FOUNDATION,
EXTERIORWALLS,
BASEMENT,
CONSTRUCTION,
HEATING,
AIRCONDITIONING,
STYLE,
ELEVATOR,
NOOFBUILDINGS,
BUILDINGAREA,
GARAGETYPE,
FIREPLACE,
TOTALROOMS,
PARTIALBATHS,
BATHS,
BEDROOMS,
UNITS,
STORIES,
POOLSPA,
YEARBUILT,
OWNER,
BB_COUNTY,
MAILINGADDRESS,
TITLECOMPANY,
TERM,
LOANAMOUNT,
TYPEOFMORTGAGE,
LENDER,
LENDERTYPE,
LOANTYPE,
DUEDATE,
ASSESSORSPARCELNUMBER,
LANDUSE,
TAXRATECODE,
MARKETIMPROVEMENTVALUE,
TOTALMARKETVALUE,
MARKETLANDVALUE,
MARKETVALUEYEAR,
ASSESSEDMULTIPLIER,
ASSESSMENTYEAR,
ASSESSEDIMPROVEMENTVALUE,
ASSESSEDTOTALVALUE,
ASSESSEDLANDVALUE,
PROPADDRESS,
PROPCITY,
PROPSTATE,
PROPZIP,
BB_VALUE,
INPUT_BBAID,
PROPERTYRECORDFOR,
PROPERTYTRANSFERRECORDFOR,
BORROWERS,
MORTGAGERECORDFOR,
TAPECUTDATE,
OWNERCAREOFNAME,
ASSESSEENAME,
ASSESSEENAME2,
ASSESSEECAREOFNAME,
ASSESSEEMAILINGADDRESS,
ASSESSMENTRECORDINGDATE,
DOCUMENTTYPE,
ASSESSMENTSALEPRICE,
ASSESSMENTSALEPRICECODE,
LEGALLOTNUMBER,
LEGALBLOCK,
LEGALUNIT,
LEGALCITY,
LEGALSUBDIVISION,
LANDUSECODE,
PARKINGNUMBEROFCARS,
BUILDINGCLASS,
BUYER2,
BUYERVESTING,
BUYERCAREOFNAME,
SELLER2,
PROPUSECODE,
CONCURRENTLOANAMOUNT,
CONCURRENTLOANTYPE,
CONCURRENTTYPEFINANCING,
CONCURRENTDUEDATE,
CONCURRENTLENDERNAME,
DEEDTITLECOMPANY,
DEEDLEGALLOTCODE,
DEEDLEGALLOTNUMBER,
DEEDLEGALBLOCK,
DEEDLEGALUNIT,
DEEDLEGALCITY,
DEEDLEGALSUBDIVISION,
DEEDLEGALBRIEFDESCRIPTION,
DEEDRECORDINGDATE,
MORTRECORDINGDATE,
BORROWERS2,
VESTINGCODE,
BORROWERMAILINGADDRESS,
ORIGINALCONTRACTDATE,
MORT2_RECORDINGDATE,
MORT2_BORROWERS,
MORT2_BORROWERS2,
MORT2_VESTINGCODE,
MORT2_BORROWERMAILINGADDRESS,
MORT2_ORIGINALCONTRACTDATE,
MORT2_LOANAMOUNT,
MORT2_LOANTYPE,
MORT2_TYPEOFMORTGAGE,
MORT2_DUEDATE,
MORT2_LENDER,
ESTIMATEDVALUE,
ESTIMATEDVALUEDATE,
CONFIDENCESCORE,
CONFIDENCESCOREDATE,
SUMMARYSALESVALUE,
SUMMARYSALESRECORDINGDATE,
PROPERTYVALUATION,
FIPSCODE,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
RECURSIVEMATCH,
REPORTTYPE,
HISTORICCODE,
HISTORICSET
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@LXID, 0),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
@CDATE,
coalesce(@CVALUE, 0),
coalesce(@CNOTES, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@DEEDTYPE, ''),
coalesce(@DOCUMENTNUMBER, ''),
coalesce(@SALEPRICE, 0),
coalesce(@RECORDEDDATE, ''),
@SALEDATE,
coalesce(@BUYERMAILINGADDRESS, ''),
coalesce(@SELLERMAILINGADDRESS, ''),
coalesce(@SELLER, ''),
coalesce(@BUYER, ''),
coalesce(@LEGALDESCRIPTION, ''),
coalesce(@PROPERTYUSE, ''),
coalesce(@LOTSIZE, ''),
coalesce(@ROOF, ''),
coalesce(@FOUNDATION, ''),
coalesce(@EXTERIORWALLS, ''),
coalesce(@BASEMENT, ''),
coalesce(@CONSTRUCTION, ''),
coalesce(@HEATING, ''),
coalesce(@AIRCONDITIONING, ''),
coalesce(@STYLE, ''),
coalesce(@ELEVATOR, ''),
coalesce(@NOOFBUILDINGS, ''),
coalesce(@BUILDINGAREA, ''),
coalesce(@GARAGETYPE, ''),
coalesce(@FIREPLACE, ''),
coalesce(@TOTALROOMS, ''),
coalesce(@PARTIALBATHS, ''),
coalesce(@BATHS, ''),
coalesce(@BEDROOMS, ''),
coalesce(@UNITS, ''),
coalesce(@STORIES, ''),
coalesce(@POOLSPA, ''),
coalesce(@YEARBUILT, 0),
coalesce(@OWNER, ''),
coalesce(@BB_COUNTY, ''),
coalesce(@MAILINGADDRESS, ''),
coalesce(@TITLECOMPANY, ''),
coalesce(@TERM, ''),
coalesce(@LOANAMOUNT, 0),
coalesce(@TYPEOFMORTGAGE, ''),
coalesce(@LENDER, ''),
coalesce(@LENDERTYPE, ''),
coalesce(@LOANTYPE, ''),
@DUEDATE,
coalesce(@ASSESSORSPARCELNUMBER, ''),
coalesce(@LANDUSE, ''),
coalesce(@TAXRATECODE, ''),
coalesce(@MARKETIMPROVEMENTVALUE, 0),
coalesce(@TOTALMARKETVALUE, 0),
coalesce(@MARKETLANDVALUE, 0),
coalesce(@MARKETVALUEYEAR, 0),
coalesce(@ASSESSEDMULTIPLIER, ''),
coalesce(@ASSESSMENTYEAR, 0),
coalesce(@ASSESSEDIMPROVEMENTVALUE, 0),
coalesce(@ASSESSEDTOTALVALUE, 0),
coalesce(@ASSESSEDLANDVALUE, 0),
coalesce(@PROPADDRESS, ''),
coalesce(@PROPCITY, ''),
coalesce(@PROPSTATE, ''),
coalesce(@PROPZIP, ''),
coalesce(@BB_VALUE, 0),
coalesce(@INPUT_BBAID, ''),
coalesce(@PROPERTYRECORDFOR, ''),
coalesce(@PROPERTYTRANSFERRECORDFOR, ''),
coalesce(@BORROWERS, ''),
coalesce(@MORTGAGERECORDFOR, ''),
coalesce(@TAPECUTDATE, '00000000'),
coalesce(@OWNERCAREOFNAME, ''),
coalesce(@ASSESSEENAME, ''),
coalesce(@ASSESSEENAME2, ''),
coalesce(@ASSESSEECAREOFNAME, ''),
coalesce(@ASSESSEEMAILINGADDRESS, ''),
@ASSESSMENTRECORDINGDATE,
coalesce(@DOCUMENTTYPE, ''),
coalesce(@ASSESSMENTSALEPRICE, 0),
coalesce(@ASSESSMENTSALEPRICECODE, ''),
coalesce(@LEGALLOTNUMBER, ''),
coalesce(@LEGALBLOCK, ''),
coalesce(@LEGALUNIT, ''),
coalesce(@LEGALCITY, ''),
coalesce(@LEGALSUBDIVISION, ''),
coalesce(@LANDUSECODE, ''),
coalesce(@PARKINGNUMBEROFCARS, ''),
coalesce(@BUILDINGCLASS, ''),
coalesce(@BUYER2, ''),
coalesce(@BUYERVESTING, ''),
coalesce(@BUYERCAREOFNAME, ''),
coalesce(@SELLER2, ''),
coalesce(@PROPUSECODE, ''),
coalesce(@CONCURRENTLOANAMOUNT, 0),
coalesce(@CONCURRENTLOANTYPE, ''),
coalesce(@CONCURRENTTYPEFINANCING, ''),
@CONCURRENTDUEDATE,
coalesce(@CONCURRENTLENDERNAME, ''),
coalesce(@DEEDTITLECOMPANY, ''),
coalesce(@DEEDLEGALLOTCODE, ''),
coalesce(@DEEDLEGALLOTNUMBER, ''),
coalesce(@DEEDLEGALBLOCK, ''),
coalesce(@DEEDLEGALUNIT, ''),
coalesce(@DEEDLEGALCITY, ''),
coalesce(@DEEDLEGALSUBDIVISION, ''),
coalesce(@DEEDLEGALBRIEFDESCRIPTION, ''),
@DEEDRECORDINGDATE,
@MORTRECORDINGDATE,
coalesce(@BORROWERS2, ''),
coalesce(@VESTINGCODE, ''),
coalesce(@BORROWERMAILINGADDRESS, ''),
@ORIGINALCONTRACTDATE,
@MORT2_RECORDINGDATE,
coalesce(@MORT2_BORROWERS, ''),
coalesce(@MORT2_BORROWERS2, ''),
coalesce(@MORT2_VESTINGCODE, ''),
coalesce(@MORT2_BORROWERMAILINGADDRESS, ''),
@MORT2_ORIGINALCONTRACTDATE,
coalesce(@MORT2_LOANAMOUNT, 0),
coalesce(@MORT2_LOANTYPE, ''),
coalesce(@MORT2_TYPEOFMORTGAGE, ''),
@MORT2_DUEDATE,
coalesce(@MORT2_LENDER, ''),
coalesce(@ESTIMATEDVALUE, 0),
@ESTIMATEDVALUEDATE,
coalesce(@CONFIDENCESCORE, ''),
@CONFIDENCESCOREDATE,
coalesce(@SUMMARYSALESVALUE, 0),
@SUMMARYSALESRECORDINGDATE,
coalesce(@PROPERTYVALUATION, 0),
coalesce(@FIPSCODE, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@RECURSIVEMATCH,
coalesce(@REPORTTYPE, ''),
case when @SETASCURRENT = 1 then 0 else 2 end,
@SETASCURRENT
);
end
if @NEWRECORD = 1 or @UPDATEDRECORD = 1
begin
--Log record into the history table
insert into dbo.WPREALESTATEHISTORY (
WPREALESTATEID,
SOURCE,
FULLHASH,
PARTIALHASH,
DEEDTYPE,
DOCUMENTNUMBER,
SALEPRICE,
SALEDATE,
BUYERMAILINGADDRESS,
SELLERMAILINGADDRESS,
SELLER,
BUYER,
LEGALDESCRIPTION,
PROPERTYUSE,
LOTSIZE,
ROOF,
FOUNDATION,
EXTERIORWALLS,
BASEMENT,
CONSTRUCTION,
HEATING,
AIRCONDITIONING,
STYLE,
ELEVATOR,
NOOFBUILDINGS,
BUILDINGAREA,
GARAGETYPE,
FIREPLACE,
TOTALROOMS,
PARTIALBATHS,
BATHS,
BEDROOMS,
UNITS,
STORIES,
POOLSPA,
YEARBUILT,
OWNER,
BB_COUNTY,
MAILINGADDRESS,
TITLECOMPANY,
LOANAMOUNT,
TYPEOFMORTGAGE,
LENDER,
LENDERTYPE,
LOANTYPE,
DUEDATE,
ASSESSORSPARCELNUMBER,
LANDUSE,
MARKETIMPROVEMENTVALUE,
TOTALMARKETVALUE,
MARKETLANDVALUE,
MARKETVALUEYEAR,
ASSESSMENTYEAR,
ASSESSEDIMPROVEMENTVALUE,
ASSESSEDTOTALVALUE,
ASSESSEDLANDVALUE,
PROPADDRESS,
PROPCITY,
PROPSTATE,
PROPZIP,
BORROWERS,
TAPECUTDATE,
OWNERCAREOFNAME,
ASSESSEENAME,
ASSESSEENAME2,
ASSESSEECAREOFNAME,
ASSESSEEMAILINGADDRESS,
ASSESSMENTRECORDINGDATE,
DOCUMENTTYPE,
ASSESSMENTSALEPRICE,
ASSESSMENTSALEPRICECODE,
LEGALLOTNUMBER,
LEGALBLOCK,
LEGALUNIT,
LEGALCITY,
LEGALSUBDIVISION,
LANDUSECODE,
PARKINGNUMBEROFCARS,
BUILDINGCLASS,
BUYER2,
BUYERVESTING,
BUYERCAREOFNAME,
SELLER2,
PROPUSECODE,
CONCURRENTLOANAMOUNT,
CONCURRENTLOANTYPE,
CONCURRENTTYPEFINANCING,
CONCURRENTDUEDATE,
CONCURRENTLENDERNAME,
DEEDTITLECOMPANY,
DEEDLEGALLOTCODE,
DEEDLEGALLOTNUMBER,
DEEDLEGALBLOCK,
DEEDLEGALUNIT,
DEEDLEGALCITY,
DEEDLEGALSUBDIVISION,
DEEDLEGALBRIEFDESCRIPTION,
DEEDRECORDINGDATE,
MORTRECORDINGDATE,
BORROWERS2,
VESTINGCODE,
BORROWERMAILINGADDRESS,
ORIGINALCONTRACTDATE,
MORT2_RECORDINGDATE,
MORT2_BORROWERS,
MORT2_BORROWERS2,
MORT2_VESTINGCODE,
MORT2_BORROWERMAILINGADDRESS,
MORT2_ORIGINALCONTRACTDATE,
MORT2_LOANAMOUNT,
MORT2_LOANTYPE,
MORT2_TYPEOFMORTGAGE,
MORT2_DUEDATE,
MORT2_LENDER,
ESTIMATEDVALUE,
ESTIMATEDVALUEDATE,
CONFIDENCESCORE,
CONFIDENCESCOREDATE,
SUMMARYSALESVALUE,
SUMMARYSALESRECORDINGDATE,
PROPERTYVALUATION,
FIPSCODE,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED
) values (
@ID,
coalesce(@SOURCE, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@DEEDTYPE, ''),
coalesce(@DOCUMENTNUMBER, ''),
coalesce(@SALEPRICE, 0),
@SALEDATE,
coalesce(@BUYERMAILINGADDRESS, ''),
coalesce(@SELLERMAILINGADDRESS, ''),
coalesce(@SELLER, ''),
coalesce(@BUYER, ''),
coalesce(@LEGALDESCRIPTION, ''),
coalesce(@PROPERTYUSE, ''),
coalesce(@LOTSIZE, ''),
coalesce(@ROOF, ''),
coalesce(@FOUNDATION, ''),
coalesce(@EXTERIORWALLS, ''),
coalesce(@BASEMENT, ''),
coalesce(@CONSTRUCTION, ''),
coalesce(@HEATING, ''),
coalesce(@AIRCONDITIONING, ''),
coalesce(@STYLE, ''),
coalesce(@ELEVATOR, ''),
coalesce(@NOOFBUILDINGS, ''),
coalesce(@BUILDINGAREA, ''),
coalesce(@GARAGETYPE, ''),
coalesce(@FIREPLACE, ''),
coalesce(@TOTALROOMS, ''),
coalesce(@PARTIALBATHS, ''),
coalesce(@BATHS, ''),
coalesce(@BEDROOMS, ''),
coalesce(@UNITS, ''),
coalesce(@STORIES, ''),
coalesce(@POOLSPA, ''),
coalesce(@YEARBUILT, 0),
coalesce(@OWNER, ''),
coalesce(@BB_COUNTY, ''),
coalesce(@MAILINGADDRESS, ''),
coalesce(@TITLECOMPANY, ''),
coalesce(@LOANAMOUNT, 0),
coalesce(@TYPEOFMORTGAGE, ''),
coalesce(@LENDER, ''),
coalesce(@LENDERTYPE, ''),
coalesce(@LOANTYPE, ''),
@DUEDATE,
coalesce(@ASSESSORSPARCELNUMBER, ''),
coalesce(@LANDUSE, ''),
coalesce(@MARKETIMPROVEMENTVALUE, 0),
coalesce(@TOTALMARKETVALUE, 0),
coalesce(@MARKETLANDVALUE, 0),
coalesce(@MARKETVALUEYEAR, 0),
coalesce(@ASSESSMENTYEAR, 0),
coalesce(@ASSESSEDIMPROVEMENTVALUE, 0),
coalesce(@ASSESSEDTOTALVALUE, 0),
coalesce(@ASSESSEDLANDVALUE, 0),
coalesce(@PROPADDRESS, ''),
coalesce(@PROPCITY, ''),
coalesce(@PROPSTATE, ''),
coalesce(@PROPZIP, ''),
coalesce(@BORROWERS, ''),
coalesce(@TAPECUTDATE, '00000000'),
coalesce(@OWNERCAREOFNAME, ''),
coalesce(@ASSESSEENAME, ''),
coalesce(@ASSESSEENAME2, ''),
coalesce(@ASSESSEECAREOFNAME, ''),
coalesce(@ASSESSEEMAILINGADDRESS, ''),
@ASSESSMENTRECORDINGDATE,
coalesce(@DOCUMENTTYPE, ''),
coalesce(@ASSESSMENTSALEPRICE, 0),
coalesce(@ASSESSMENTSALEPRICECODE, ''),
coalesce(@LEGALLOTNUMBER, ''),
coalesce(@LEGALBLOCK, ''),
coalesce(@LEGALUNIT, ''),
coalesce(@LEGALCITY, ''),
coalesce(@LEGALSUBDIVISION, ''),
coalesce(@LANDUSECODE, ''),
coalesce(@PARKINGNUMBEROFCARS, ''),
coalesce(@BUILDINGCLASS, ''),
coalesce(@BUYER2, ''),
coalesce(@BUYERVESTING, ''),
coalesce(@BUYERCAREOFNAME, ''),
coalesce(@SELLER2, ''),
coalesce(@PROPUSECODE, ''),
coalesce(@CONCURRENTLOANAMOUNT, 0),
coalesce(@CONCURRENTLOANTYPE, ''),
coalesce(@CONCURRENTTYPEFINANCING, ''),
@CONCURRENTDUEDATE,
coalesce(@CONCURRENTLENDERNAME, ''),
coalesce(@DEEDTITLECOMPANY, ''),
coalesce(@DEEDLEGALLOTCODE, ''),
coalesce(@DEEDLEGALLOTNUMBER, ''),
coalesce(@DEEDLEGALBLOCK, ''),
coalesce(@DEEDLEGALUNIT, ''),
coalesce(@DEEDLEGALCITY, ''),
coalesce(@DEEDLEGALSUBDIVISION, ''),
coalesce(@DEEDLEGALBRIEFDESCRIPTION, ''),
@DEEDRECORDINGDATE,
@MORTRECORDINGDATE,
coalesce(@BORROWERS2, ''),
coalesce(@VESTINGCODE, ''),
coalesce(@BORROWERMAILINGADDRESS, ''),
@ORIGINALCONTRACTDATE,
@MORT2_RECORDINGDATE,
coalesce(@MORT2_BORROWERS, ''),
coalesce(@MORT2_BORROWERS2, ''),
coalesce(@MORT2_VESTINGCODE, ''),
coalesce(@MORT2_BORROWERMAILINGADDRESS, ''),
@MORT2_ORIGINALCONTRACTDATE,
coalesce(@MORT2_LOANAMOUNT, 0),
coalesce(@MORT2_LOANTYPE, ''),
coalesce(@MORT2_TYPEOFMORTGAGE, ''),
@MORT2_DUEDATE,
coalesce(@MORT2_LENDER, ''),
coalesce(@ESTIMATEDVALUE, 0),
@ESTIMATEDVALUEDATE,
coalesce(@CONFIDENCESCORE, ''),
@CONFIDENCESCOREDATE,
coalesce(@SUMMARYSALESVALUE, 0),
@SUMMARYSALESRECORDINGDATE,
coalesce(@PROPERTYVALUATION, 0),
coalesce(@FIPSCODE, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
if @WPSEARCHHISTORYID is not null
begin
--Add entry to keep track that this record was present in this set of search results, so we can set the historical value later
insert into
dbo.WPREALESTATE_WPSEARCHHISTORY (WPREALESTATEID, WPSEARCHHISTORYID)
values
(@ID, @WPSEARCHHISTORYID);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end