USP_MERGETWOCONSTITUENTS_REALESTATE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.USP_MERGETWOCONSTITUENTS_REALESTATE
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null
)
as
begin
set nocount on;
declare @CURRENTDATE datetime = getdate();
-- If Source and Target match on hash code and Source is Rejected/Confirmed, but Target is not Rejected/Confirmed, update the Target record values with Source record values. Notes will always be appended.
update TARGETREALESTATE
set
[CONFIRMED] = SOURCEREALESTATE.CONFIRMED,
[DATECONFIRMED] = SOURCEREALESTATE.DATECONFIRMED,
[CONFIRMEDBYAPPUSERID] = SOURCEREALESTATE.CONFIRMEDBYAPPUSERID,
[REJECTED] = SOURCEREALESTATE.REJECTED,
[DATEREJECTED] = SOURCEREALESTATE.DATEREJECTED,
[REJECTEDBYAPPUSERID] = SOURCEREALESTATE.REJECTEDBYAPPUSERID,
[SOURCE] = SOURCEREALESTATE.SOURCE,
[LXID] = SOURCEREALESTATE.LXID,
[REVISION] = SOURCEREALESTATE.REVISION,
[ORIGINALID] = SOURCEREALESTATE.ORIGINALID,
[NEWROW] = SOURCEREALESTATE.NEWROW,
[CDATE] = SOURCEREALESTATE.CDATE,
[CVALUE] = SOURCEREALESTATE.CVALUE,
[CNOTES] = case SOURCEREALESTATE.CNOTES when '' then TARGETREALESTATE.CNOTES else TARGETREALESTATE.CNOTES + (case when TARGETREALESTATE.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEREALESTATE.CNOTES end,
[FULLHASH] = SOURCEREALESTATE.FULLHASH,
[PARTIALHASH] = SOURCEREALESTATE.PARTIALHASH,
[MC] = SOURCEREALESTATE.MC,
[DEEDTYPE] = SOURCEREALESTATE.DEEDTYPE,
[DOCUMENTNUMBER] = SOURCEREALESTATE.DOCUMENTNUMBER,
[SALEPRICE] = SOURCEREALESTATE.SALEPRICE,
[RECORDEDDATE] = SOURCEREALESTATE.RECORDEDDATE,
[SALEDATE] = SOURCEREALESTATE.SALEDATE,
[BUYERMAILINGADDRESS] = SOURCEREALESTATE.BUYERMAILINGADDRESS,
[SELLERMAILINGADDRESS] = SOURCEREALESTATE.SELLERMAILINGADDRESS,
[SELLER] = SOURCEREALESTATE.SELLER,
[BUYER] = SOURCEREALESTATE.BUYER,
[LEGALDESCRIPTION] = SOURCEREALESTATE.LEGALDESCRIPTION,
[PROPERTYUSE] = SOURCEREALESTATE.PROPERTYUSE,
[LOTSIZE] = SOURCEREALESTATE.LOTSIZE,
[ROOF] = SOURCEREALESTATE.ROOF,
[FOUNDATION] = SOURCEREALESTATE.FOUNDATION,
[EXTERIORWALLS] = SOURCEREALESTATE.EXTERIORWALLS,
[BASEMENT] = SOURCEREALESTATE.BASEMENT,
[CONSTRUCTION] = SOURCEREALESTATE.CONSTRUCTION,
[HEATING] = SOURCEREALESTATE.HEATING,
[AIRCONDITIONING] = SOURCEREALESTATE.AIRCONDITIONING,
[STYLE] = SOURCEREALESTATE.STYLE,
[ELEVATOR] = SOURCEREALESTATE.ELEVATOR,
[NOOFBUILDINGS] = SOURCEREALESTATE.NOOFBUILDINGS,
[BUILDINGAREA] = SOURCEREALESTATE.BUILDINGAREA,
[GARAGETYPE] = SOURCEREALESTATE.GARAGETYPE,
[FIREPLACE] = SOURCEREALESTATE.FIREPLACE,
[TOTALROOMS] = SOURCEREALESTATE.TOTALROOMS,
[PARTIALBATHS] = SOURCEREALESTATE.PARTIALBATHS,
[BATHS] = SOURCEREALESTATE.BATHS,
[BEDROOMS] = SOURCEREALESTATE.BEDROOMS,
[UNITS] = SOURCEREALESTATE.UNITS,
[STORIES] = SOURCEREALESTATE.STORIES,
[POOLSPA] = SOURCEREALESTATE.POOLSPA,
[YEARBUILT] = SOURCEREALESTATE.YEARBUILT,
[OWNER] = SOURCEREALESTATE.OWNER,
[BB_COUNTY] = SOURCEREALESTATE.BB_COUNTY,
[MAILINGADDRESS] = SOURCEREALESTATE.MAILINGADDRESS,
[TITLECOMPANY] = SOURCEREALESTATE.TITLECOMPANY,
[TERM] = SOURCEREALESTATE.TERM,
[LOANAMOUNT] = SOURCEREALESTATE.LOANAMOUNT,
[TYPEOFMORTGAGE] = SOURCEREALESTATE.TYPEOFMORTGAGE,
[LENDER] = SOURCEREALESTATE.LENDER,
[LENDERTYPE] = SOURCEREALESTATE.LENDERTYPE,
[LOANTYPE] = SOURCEREALESTATE.LOANTYPE,
[DUEDATE] = SOURCEREALESTATE.DUEDATE,
[ASSESSORSPARCELNUMBER] = SOURCEREALESTATE.ASSESSORSPARCELNUMBER,
[LANDUSE] = SOURCEREALESTATE.LANDUSE,
[TAXRATECODE] = SOURCEREALESTATE.TAXRATECODE,
[MARKETIMPROVEMENTVALUE] = SOURCEREALESTATE.MARKETIMPROVEMENTVALUE,
[TOTALMARKETVALUE] = SOURCEREALESTATE.TOTALMARKETVALUE,
[MARKETLANDVALUE] = SOURCEREALESTATE.MARKETLANDVALUE,
[MARKETVALUEYEAR] = SOURCEREALESTATE.MARKETVALUEYEAR,
[ASSESSEDMULTIPLIER] = SOURCEREALESTATE.ASSESSEDMULTIPLIER,
[ASSESSMENTYEAR] = SOURCEREALESTATE.ASSESSMENTYEAR,
[ASSESSEDIMPROVEMENTVALUE] = SOURCEREALESTATE.ASSESSEDIMPROVEMENTVALUE,
[ASSESSEDTOTALVALUE] = SOURCEREALESTATE.ASSESSEDTOTALVALUE,
[ASSESSEDLANDVALUE] = SOURCEREALESTATE.ASSESSEDLANDVALUE,
[PROPADDRESS] = SOURCEREALESTATE.PROPADDRESS,
[PROPCITY] = SOURCEREALESTATE.PROPCITY,
[PROPSTATE] = SOURCEREALESTATE.PROPSTATE,
[PROPZIP] = SOURCEREALESTATE.PROPZIP,
[BB_VALUE] = SOURCEREALESTATE.BB_VALUE,
[INPUT_BBAID] = SOURCEREALESTATE.INPUT_BBAID,
[PROPERTYRECORDFOR] = SOURCEREALESTATE.PROPERTYRECORDFOR,
[PROPERTYTRANSFERRECORDFOR] = SOURCEREALESTATE.PROPERTYTRANSFERRECORDFOR,
[BORROWERS] = SOURCEREALESTATE.BORROWERS,
[MORTGAGERECORDFOR] = SOURCEREALESTATE.MORTGAGERECORDFOR,
[VIEWED] = SOURCEREALESTATE.VIEWED,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[RECURSIVEMATCH] = SOURCEREALESTATE.RECURSIVEMATCH,
[REPORTTYPE] = SOURCEREALESTATE.REPORTTYPE,
[TAPECUTDATE] = SOURCEREALESTATE.TAPECUTDATE,
[OWNERCAREOFNAME] = SOURCEREALESTATE.OWNERCAREOFNAME,
[ASSESSEENAME] = SOURCEREALESTATE.ASSESSEENAME,
[ASSESSEENAME2] = SOURCEREALESTATE.ASSESSEENAME2,
[ASSESSEECAREOFNAME] = SOURCEREALESTATE.ASSESSEECAREOFNAME,
[ASSESSEEMAILINGADDRESS] = SOURCEREALESTATE.ASSESSEEMAILINGADDRESS,
[ASSESSMENTRECORDINGDATE] = SOURCEREALESTATE.ASSESSMENTRECORDINGDATE,
[DOCUMENTTYPE] = SOURCEREALESTATE.DOCUMENTTYPE,
[ASSESSMENTSALEPRICE] = SOURCEREALESTATE.ASSESSMENTSALEPRICE,
[ASSESSMENTSALEPRICECODE] = SOURCEREALESTATE.ASSESSMENTSALEPRICECODE,
[LEGALLOTNUMBER] = SOURCEREALESTATE.LEGALLOTNUMBER,
[LEGALBLOCK] = SOURCEREALESTATE.LEGALBLOCK,
[LEGALUNIT] = SOURCEREALESTATE.LEGALUNIT,
[LEGALCITY] = SOURCEREALESTATE.LEGALCITY,
[LEGALSUBDIVISION] = SOURCEREALESTATE.LEGALSUBDIVISION,
[LANDUSECODE] = SOURCEREALESTATE.LANDUSECODE,
[PARKINGNUMBEROFCARS] = SOURCEREALESTATE.PARKINGNUMBEROFCARS,
[BUILDINGCLASS] = SOURCEREALESTATE.BUILDINGCLASS,
[BUYER2] = SOURCEREALESTATE.BUYER2,
[BUYERVESTING] = SOURCEREALESTATE.BUYERVESTING,
[BUYERCAREOFNAME] = SOURCEREALESTATE.BUYERCAREOFNAME,
[SELLER2] = SOURCEREALESTATE.SELLER2,
[PROPUSECODE] = SOURCEREALESTATE.PROPUSECODE,
[CONCURRENTLOANAMOUNT] = SOURCEREALESTATE.CONCURRENTLOANAMOUNT,
[CONCURRENTLOANTYPE] = SOURCEREALESTATE.CONCURRENTLOANTYPE,
[CONCURRENTTYPEFINANCING] = SOURCEREALESTATE.CONCURRENTTYPEFINANCING,
[CONCURRENTDUEDATE] = SOURCEREALESTATE.CONCURRENTDUEDATE,
[CONCURRENTLENDERNAME] = SOURCEREALESTATE.CONCURRENTLENDERNAME,
[DEEDTITLECOMPANY] = SOURCEREALESTATE.DEEDTITLECOMPANY,
[DEEDLEGALLOTCODE] = SOURCEREALESTATE.DEEDLEGALLOTCODE,
[DEEDLEGALLOTNUMBER] = SOURCEREALESTATE.DEEDLEGALLOTNUMBER,
[DEEDLEGALBLOCK] = SOURCEREALESTATE.DEEDLEGALBLOCK,
[DEEDLEGALUNIT] = SOURCEREALESTATE.DEEDLEGALUNIT,
[DEEDLEGALCITY] = SOURCEREALESTATE.DEEDLEGALCITY,
[DEEDLEGALSUBDIVISION] = SOURCEREALESTATE.DEEDLEGALSUBDIVISION,
[DEEDLEGALBRIEFDESCRIPTION] = SOURCEREALESTATE.DEEDLEGALBRIEFDESCRIPTION,
[DEEDRECORDINGDATE] = SOURCEREALESTATE.DEEDRECORDINGDATE,
[MORTRECORDINGDATE] = SOURCEREALESTATE.MORTRECORDINGDATE,
[BORROWERS2] = SOURCEREALESTATE.BORROWERS2,
[VESTINGCODE] = SOURCEREALESTATE.VESTINGCODE,
[BORROWERMAILINGADDRESS] = SOURCEREALESTATE.BORROWERMAILINGADDRESS,
[ORIGINALCONTRACTDATE] = SOURCEREALESTATE.ORIGINALCONTRACTDATE,
[MORT2_RECORDINGDATE] = SOURCEREALESTATE.MORT2_RECORDINGDATE,
[MORT2_BORROWERS] = SOURCEREALESTATE.MORT2_BORROWERS,
[MORT2_BORROWERS2] = SOURCEREALESTATE.MORT2_BORROWERS2,
[MORT2_VESTINGCODE] = SOURCEREALESTATE.MORT2_VESTINGCODE,
[MORT2_BORROWERMAILINGADDRESS] = SOURCEREALESTATE.MORT2_BORROWERMAILINGADDRESS,
[MORT2_ORIGINALCONTRACTDATE] = SOURCEREALESTATE.MORT2_ORIGINALCONTRACTDATE,
[MORT2_LOANAMOUNT] = SOURCEREALESTATE.MORT2_LOANAMOUNT,
[MORT2_LOANTYPE] = SOURCEREALESTATE.MORT2_LOANTYPE,
[MORT2_TYPEOFMORTGAGE] = SOURCEREALESTATE.MORT2_TYPEOFMORTGAGE,
[MORT2_DUEDATE] = SOURCEREALESTATE.MORT2_DUEDATE,
[MORT2_LENDER] = SOURCEREALESTATE.MORT2_LENDER,
[ESTIMATEDVALUE] = SOURCEREALESTATE.ESTIMATEDVALUE,
[ESTIMATEDVALUEDATE] = SOURCEREALESTATE.ESTIMATEDVALUEDATE,
[CONFIDENCESCORE] = SOURCEREALESTATE.CONFIDENCESCORE,
[SUMMARYSALESVALUE] = SOURCEREALESTATE.SUMMARYSALESVALUE,
[SUMMARYSALESRECORDINGDATE] = SOURCEREALESTATE.SUMMARYSALESRECORDINGDATE,
[CONFIDENCESCOREDATE] = SOURCEREALESTATE.CONFIDENCESCOREDATE,
[PROPERTYVALUATION] = SOURCEREALESTATE.PROPERTYVALUATION,
[FIPSCODE] = SOURCEREALESTATE.FIPSCODE,
[HISTORICCODE] = SOURCEREALESTATE.HISTORICCODE,
[HISTORICSET] = SOURCEREALESTATE.HISTORICSET
from dbo.WPREALESTATE TARGETREALESTATE
inner join dbo.WPREALESTATE SOURCEREALESTATE ON SOURCEREALESTATE.PARTIALHASH = TARGETREALESTATE.PARTIALHASH
where SOURCEREALESTATE.WEALTHID = @SOURCEID
and TARGETREALESTATE.WEALTHID = @TARGETID
and not (TARGETREALESTATE.CONFIRMED = 1 or TARGETREALESTATE.REJECTED = 1)
and (SOURCEREALESTATE.CONFIRMED = 1 or SOURCEREALESTATE.REJECTED = 1)
and SOURCEREALESTATE.PARTIALHASH <> ''
-- For all other cases when Source and Target match on hash codes, only notes will be appended.
update TARGETREALESTATE
set
[CNOTES] = TARGETREALESTATE.CNOTES + (case when TARGETREALESTATE.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEREALESTATE.CNOTES
,[CHANGEDBYID] = @CHANGEAGENTID
,[DATECHANGED] = @CURRENTDATE
from dbo.WPREALESTATE TARGETREALESTATE
inner join dbo.WPREALESTATE SOURCEREALESTATE ON SOURCEREALESTATE.PARTIALHASH = TARGETREALESTATE.PARTIALHASH
where SOURCEREALESTATE.WEALTHID = @SOURCEID
and TARGETREALESTATE.WEALTHID = @TARGETID
and ((TARGETREALESTATE.CONFIRMED = 1 or TARGETREALESTATE.REJECTED = 1)
or (SOURCEREALESTATE.CONFIRMED = 0 and SOURCEREALESTATE.REJECTED = 0))
and SOURCEREALESTATE.PARTIALHASH <> ''
and SOURCEREALESTATE.CNOTES <> ''
-- If unique record in Source that came from wealth screening (will have hash code), move it to Target.
update SOURCEREALESTATE
set
[WEALTHID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
from dbo.WPREALESTATE SOURCEREALESTATE
left join dbo.WPREALESTATE TARGETREALESTATE ON TARGETREALESTATE.PARTIALHASH = SOURCEREALESTATE.PARTIALHASH and TARGETREALESTATE.WEALTHID = @TARGETID
where SOURCEREALESTATE.WEALTHID = @SOURCEID
and (SOURCEREALESTATE.PARTIALHASH is not null and SOURCEREALESTATE.PARTIALHASH <> '')
and TARGETREALESTATE.ID is null
-- If unique record in Source which is entered manually, move it to Target.
update dbo.WPREALESTATE
set
[WEALTHID] = @TARGETID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where WEALTHID = @SOURCEID
and (PARTIALHASH is null or PARTIALHASH = '')
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_REALESTATE @TARGETID, @CHANGEAGENTID;
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_REALESTATE @SOURCEID, @CHANGEAGENTID;
end