USP_DATAFORMTEMPLATE_ADDSAVE_WPREALESTATE_2
The save procedure used by the add dataform template "WealthPoint Real Estate Add Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @WEALTHID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @SOURCE | nvarchar(100) | IN | Source |
| @CNOTES | nvarchar(1024) | IN | Notes |
| @DEEDTYPE | nvarchar(100) | IN | Deed type |
| @DOCUMENTNUMBER | nvarchar(200) | IN | Document number |
| @SALEPRICE | money | IN | Sale price |
| @RECORDEDDATE | nvarchar(200) | IN | Recorded date |
| @SALEDATE | datetime | IN | Sale date |
| @BUYERMAILINGADDRESS | nvarchar(200) | IN | Buyer mailing address |
| @SELLERMAILINGADDRESS | nvarchar(200) | IN | Seller mailing address |
| @SELLER | nvarchar(300) | IN | Seller |
| @BUYER | nvarchar(300) | IN | Buyer |
| @LEGALDESCRIPTION | nvarchar(2000) | IN | Legal description |
| @PROPERTYUSE | nvarchar(100) | IN | Property use |
| @LOTSIZE | nvarchar(50) | IN | Lot size |
| @ROOF | nvarchar(30) | IN | Roof |
| @FOUNDATION | nvarchar(30) | IN | Foundation |
| @EXTERIORWALLS | nvarchar(30) | IN | Exterior walls |
| @BASEMENT | nvarchar(30) | IN | Basement |
| @CONSTRUCTION | nvarchar(30) | IN | Construction |
| @HEATING | nvarchar(30) | IN | Heating |
| @AIRCONDITIONING | nvarchar(30) | IN | Air conditioning |
| @STYLE | nvarchar(30) | IN | Style |
| @ELEVATOR | nvarchar(5) | IN | Elevator |
| @NOOFBUILDINGS | nvarchar(5) | IN | No. of buildings |
| @BUILDINGAREA | nvarchar(10) | IN | Building area |
| @GARAGETYPE | nvarchar(30) | IN | Garage type |
| @FIREPLACE | nvarchar(5) | IN | Fireplace |
| @TOTALROOMS | nvarchar(5) | IN | Total rooms |
| @PARTIALBATHS | nvarchar(5) | IN | Partial baths |
| @BATHS | nvarchar(5) | IN | Baths |
| @BEDROOMS | nvarchar(5) | IN | Bedrooms |
| @UNITS | nvarchar(10) | IN | Units |
| @STORIES | nvarchar(50) | IN | Stories |
| @POOLSPA | nvarchar(30) | IN | Pool/Spa |
| @YEARBUILT | UDT_YEAR | IN | Year built |
| @OWNER | nvarchar(300) | IN | Owner |
| @BB_COUNTY | nvarchar(255) | IN | County |
| @MAILINGADDRESS | nvarchar(200) | IN | Mailing address |
| @TITLECOMPANY | nvarchar(50) | IN | Title company |
| @TERM | nvarchar(20) | IN | Term |
| @LOANAMOUNT | money | IN | Loan amount |
| @TYPEOFMORTGAGE | nvarchar(100) | IN | Mortgage type |
| @LENDER | nvarchar(100) | IN | Lender |
| @LENDERTYPE | nvarchar(50) | IN | Lender type |
| @LOANTYPE | nvarchar(50) | IN | Loan type |
| @DUEDATE | datetime | IN | Due date |
| @ASSESSORSPARCELNUMBER | nvarchar(100) | IN | Assessor's parcel number |
| @LANDUSE | nvarchar(100) | IN | Land use |
| @TAXRATECODE | nvarchar(50) | IN | Tax rate code |
| @MARKETIMPROVEMENTVALUE | money | IN | Market improvement value |
| @TOTALMARKETVALUE | money | IN | Total market value |
| @MARKETLANDVALUE | money | IN | Market land value |
| @MARKETVALUEYEAR | UDT_YEAR | IN | Market value year |
| @ASSESSEDMULTIPLIER | nvarchar(5) | IN | Assessed multiplier |
| @ASSESSMENTYEAR | UDT_YEAR | IN | Assessed value year |
| @ASSESSEDIMPROVEMENTVALUE | money | IN | Assessed improvement value |
| @ASSESSEDTOTALVALUE | money | IN | Assessed total value |
| @ASSESSEDLANDVALUE | money | IN | Assessed land value |
| @PROPADDRESS | nvarchar(150) | IN | Address |
| @PROPCITY | nvarchar(50) | IN | City |
| @PROPSTATE | nvarchar(100) | IN | State |
| @PROPZIP | nvarchar(10) | IN | ZIP |
| @PROPERTYRECORDFOR | nvarchar(50) | IN | Property record for |
| @PROPERTYTRANSFERRECORDFOR | nvarchar(50) | IN | Property transfer record for |
| @BORROWERS | nvarchar(200) | IN | Borrower(s) |
| @MORTGAGERECORDFOR | nvarchar(50) | IN | Mortgage record for |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADDSAVE_WPREALESTATE_2(
@ID uniqueidentifier = null output,
@WEALTHID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SOURCE nvarchar(100),
@CNOTES nvarchar(1024) = '',
@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) = '',
@PROPERTYRECORDFOR nvarchar(50) = '',
@PROPERTYTRANSFERRECORDFOR nvarchar(50) = '',
@BORROWERS nvarchar(200) = '',
@MORTGAGERECORDFOR nvarchar(50) = '',
@CURRENTAPPUSERID uniqueidentifier
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @ID is null
set @ID = newid();
exec dbo.USP_WEALTH_CREATE @WEALTHID, @CHANGEAGENTID;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
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
select
@REJECTED = case MANUALCONFIDENCE
when 5 then 0
when 0 then 1
end,
@DATEREJECTED = case MANUALCONFIDENCE
when 5 then NULL
when 0 then @CURRENTDATE
end,
@REJECTEDBYAPPUSERID = case MANUALCONFIDENCE
when 5 then NULL
when 0 then @CURRENTAPPUSERID
end,
@CONFIRMED = case MANUALCONFIDENCE
when 5 then 1
when 0 then 0
end,
@DATECONFIRMED = case MANUALCONFIDENCE
when 5 then @CURRENTDATE
when 0 then NULL
end,
@CONFIRMEDBYAPPUSERID = case MANUALCONFIDENCE
when 5 then @CURRENTAPPUSERID
when 0 then NULL
end
from
dbo.WEALTHPOINTCONFIGURATION
where
MANUALCONFIDENCE in (0,5)
end
insert into dbo.WPREALESTATE (
ID,
WEALTHID,
CHANGEDBYID,
ADDEDBYID,
SOURCE,
CNOTES,
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,
PROPERTYRECORDFOR,
PROPERTYTRANSFERRECORDFOR,
BORROWERS,
MORTGAGERECORDFOR,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED
) values (
@ID,
@WEALTHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@SOURCE,
@CNOTES,
@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,
@PROPERTYRECORDFOR,
@PROPERTYTRANSFERRECORDFOR,
@BORROWERS,
@MORTGAGERECORDFOR,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED
);
if (select
count(WEALTHSOURCE.SOURCE)
from
dbo.WEALTHSOURCE
where
WEALTHSOURCE.SOURCE = @SOURCE
) = 0
begin
insert into dbo.WEALTHSOURCE (
[SOURCE],
[ISBUILTIN],
[ADDEDBYID],
[CHANGEDBYID]
)values(
@SOURCE,
0,
@CHANGEAGENTID,
@CHANGEAGENTID
);
end
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_REALESTATE @WEALTHID, @CHANGEAGENTID;
exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;
return 0;
end;