USP_DATAFORMTEMPLATE_EDITSAVE_WPREALESTATE_3

The save procedure used by the edit dataform template "WealthPoint Real Estate Edit Form 3".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@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	Document type
@SALEPRICE	money	IN	Sales price
@SALEDATE	datetime	IN	Date of contract
@BUYERMAILINGADDRESS	nvarchar(200)	IN	Buyer address
@SELLERMAILINGADDRESS	nvarchar(200)	IN	Seller address
@SELLER	nvarchar(300)	IN	Seller #1 name
@BUYER	nvarchar(300)	IN	Buyer #1 name
@LEGALDESCRIPTION	nvarchar(2000)	IN	Brief description
@PROPERTYUSE	nvarchar(100)	IN	County description
@LOTSIZE	nvarchar(50)	IN	Lot-size or area
@ROOF	nvarchar(30)	IN	Roof cover
@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. 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	No. baths
@BEDROOMS	nvarchar(5)	IN	No. of bedrooms
@UNITS	nvarchar(10)	IN	No. of units
@STORIES	nvarchar(50)	IN	No. of stories
@POOLSPA	nvarchar(30)	IN	Pool
@YEARBUILT	UDT_YEAR	IN	Year built
@OWNER	nvarchar(300)	IN	Owner
@BB_COUNTY	nvarchar(255)	IN	County
@MAILINGADDRESS	nvarchar(200)	IN	Mailing address
@LOANAMOUNT	money	IN	Loan amount
@TYPEOFMORTGAGE	nvarchar(100)	IN	Type of financing
@LENDER	nvarchar(100)	IN	Lender name
@LOANTYPE	nvarchar(50)	IN	Loan type
@DUEDATE	datetime	IN	Due date
@ASSESSORSPARCELNUMBER	nvarchar(100)	IN	APN
@LANDUSE	nvarchar(100)	IN	County 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
@ASSESSMENTYEAR	UDT_YEAR	IN	Assessed value year
@ASSESSEDIMPROVEMENTVALUE	money	IN	Assessed improvement value
@ASSESSEDTOTALVALUE	money	IN	Total assessed 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
@BORROWERS	nvarchar(200)	IN	Borrower #1 name
@TAPECUTDATE	UDT_FUZZYDATE	IN	Tape cut date
@OWNERCAREOFNAME	nvarchar(40)	IN	Care of name
@ASSESSEENAME	nvarchar(80)	IN	Assessee #1 name
@ASSESSEENAME2	nvarchar(60)	IN	Assessee #2 name
@ASSESSEECAREOFNAME	nvarchar(60)	IN	Care of name
@ASSESSEEMAILINGADDRESS	nvarchar(200)	IN	Assessee address
@ASSESSMENTRECORDINGDATE	datetime	IN	Recording date
@DOCUMENTTYPE	nvarchar(25)	IN	Document type
@ASSESSMENTSALEPRICE	money	IN	Sales price
@ASSESSMENTSALEPRICECODE	nvarchar(130)	IN	Sales price indicator
@LEGALLOTNUMBER	nvarchar(7)	IN	Lot number
@LEGALBLOCK	nvarchar(7)	IN	Block
@LEGALUNIT	nvarchar(6)	IN	Unit
@LEGALCITY	nvarchar(30)	IN	City/township
@LEGALSUBDIVISION	nvarchar(40)	IN	Subdivision name
@LANDUSECODE	nvarchar(4)	IN	Standardized code
@PARKINGNUMBEROFCARS	nvarchar(5)	IN	Parking # of cars
@BUILDINGCLASS	nvarchar(250)	IN	Building class
@BUYER2	nvarchar(300)	IN	Buyer #2 name
@BUYERVESTING	nvarchar(50)	IN	Buyer vesting
@BUYERCAREOFNAME	nvarchar(40)	IN	Care of name
@SELLER2	nvarchar(300)	IN	Seller #2 name
@PROPUSECODE	nvarchar(45)	IN	Property use
@CONCURRENTLOANAMOUNT	money	IN	Loan amount
@CONCURRENTLOANTYPE	nvarchar(35)	IN	Loan type
@CONCURRENTTYPEFINANCING	nvarchar(30)	IN	Type of financing
@CONCURRENTDUEDATE	datetime	IN	Due date
@CONCURRENTLENDERNAME	nvarchar(40)	IN	Lender name
@DEEDTITLECOMPANY	nvarchar(28)	IN	Title company
@DEEDLEGALLOTCODE	nvarchar(2)	IN	Lot code
@DEEDLEGALLOTNUMBER	nvarchar(10)	IN	Lot number
@DEEDLEGALBLOCK	nvarchar(7)	IN	Block
@DEEDLEGALUNIT	nvarchar(6)	IN	Unit
@DEEDLEGALCITY	nvarchar(30)	IN	City/township
@DEEDLEGALSUBDIVISION	nvarchar(50)	IN	Subdivision
@DEEDLEGALBRIEFDESCRIPTION	nvarchar(100)	IN	Brief description
@DEEDRECORDINGDATE	datetime	IN	Recording date
@MORTRECORDINGDATE	datetime	IN	Recording date
@BORROWERS2	nvarchar(200)	IN	Borrower #2 name
@VESTINGCODE	nvarchar(50)	IN	Borrower vesting
@BORROWERMAILINGADDRESS	nvarchar(200)	IN	Borrower address
@ORIGINALCONTRACTDATE	datetime	IN	Date of contract
@MORT2_RECORDINGDATE	datetime	IN	Recording date
@MORT2_BORROWERS	nvarchar(200)	IN	Borrower #1 name
@MORT2_BORROWERS2	nvarchar(200)	IN	Borrower #2 name
@MORT2_VESTINGCODE	nvarchar(50)	IN	Borrower vesting
@MORT2_BORROWERMAILINGADDRESS	nvarchar(200)	IN	Borrower address
@MORT2_ORIGINALCONTRACTDATE	datetime	IN	Date of contract
@MORT2_LOANAMOUNT	money	IN	Loan amount
@MORT2_LOANTYPE	nvarchar(50)	IN	Loan type
@MORT2_TYPEOFMORTGAGE	nvarchar(100)	IN	Type of financing
@MORT2_DUEDATE	datetime	IN	Due date
@MORT2_LENDER	nvarchar(100)	IN	Lender name
@SUMMARYSALESVALUE	money	IN	Sales amount
@SUMMARYSALESRECORDINGDATE	datetime	IN	Reported date
@PROPERTYVALUATION	money	IN	Property valuation

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_WPREALESTATE_3 (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @SOURCE nvarchar(100),
                        @CNOTES nvarchar(1024),
                        @DEEDTYPE nvarchar(100),
                        @SALEPRICE money,
                        @SALEDATE datetime,
                        @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,
                        @OWNER nvarchar(300),
                        @BB_COUNTY nvarchar(255),
                        @MAILINGADDRESS nvarchar(200),
                        @LOANAMOUNT money,
                        @TYPEOFMORTGAGE nvarchar(100),
                        @LENDER nvarchar(100),
                        @LOANTYPE nvarchar(50),
                        @DUEDATE datetime,
                        @ASSESSORSPARCELNUMBER nvarchar(100),
                        @LANDUSE nvarchar(100),
                        @MARKETIMPROVEMENTVALUE money,
                        @TOTALMARKETVALUE money,
                        @MARKETLANDVALUE money,
                        @MARKETVALUEYEAR dbo.UDT_YEAR,
                        @ASSESSMENTYEAR dbo.UDT_YEAR,
                        @ASSESSEDIMPROVEMENTVALUE money,
                        @ASSESSEDTOTALVALUE money,
                        @ASSESSEDLANDVALUE money,
                        @PROPADDRESS nvarchar(150),
                        @PROPCITY nvarchar(50),
                        @PROPSTATE nvarchar(100),
                        @PROPZIP nvarchar(10),
                        @BORROWERS nvarchar(200),
                        @TAPECUTDATE dbo.UDT_FUZZYDATE,
                        @OWNERCAREOFNAME nvarchar(40),
                        @ASSESSEENAME nvarchar(80),
                        @ASSESSEENAME2 nvarchar(60),
                        @ASSESSEECAREOFNAME nvarchar(60),
                        @ASSESSEEMAILINGADDRESS nvarchar(200),
                        @ASSESSMENTRECORDINGDATE datetime,
                        @DOCUMENTTYPE nvarchar(25),
                        @ASSESSMENTSALEPRICE money,
                        @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 ,
                        @CONCURRENTLOANTYPE nvarchar(35),
                        @CONCURRENTTYPEFINANCING nvarchar(30),
                        @CONCURRENTDUEDATE datetime,
                        @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,
                        @MORTRECORDINGDATE datetime,
                        @BORROWERS2 nvarchar(200),
                        @VESTINGCODE nvarchar(50),
                        @BORROWERMAILINGADDRESS nvarchar(200),
                        @ORIGINALCONTRACTDATE datetime,
                        @MORT2_RECORDINGDATE datetime,
                        @MORT2_BORROWERS nvarchar(200),
                        @MORT2_BORROWERS2 nvarchar(200),
                        @MORT2_VESTINGCODE nvarchar(50),
                        @MORT2_BORROWERMAILINGADDRESS nvarchar(200),
                        @MORT2_ORIGINALCONTRACTDATE datetime,
                        @MORT2_LOANAMOUNT money,
                        @MORT2_LOANTYPE nvarchar(50),
                        @MORT2_TYPEOFMORTGAGE nvarchar(100),
                        @MORT2_DUEDATE datetime,
                        @MORT2_LENDER nvarchar(100),
                        @SUMMARYSALESVALUE money,
                        @SUMMARYSALESRECORDINGDATE datetime,
                        @PROPERTYVALUATION money
                    ) as begin

                        if @CHANGEAGENTID is null  
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            declare @CURRENTDATE datetime = getdate();

            --wealth source updates

            if(select
                            count(WEALTHSOURCE.SOURCE)
                        from
                            dbo.WEALTHSOURCE 
            where SOURCE = @SOURCE
                        ) = 0
                        begin
                            insert into dbo.WEALTHSOURCE (
                                [SOURCE],
                                [ISBUILTIN],
                                [ADDEDBYID],
                                [CHANGEDBYID]
                            )values(
                                @SOURCE,
                                0,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID
                            );
            end

            if(
            not exists (select top 1 1 from dbo.WEALTHCAPACITYFORMULAREALESTATESOURCE where SOURCE = @SOURCE)
              and
            not exists (select top 1 1 from dbo.WPREALESTATE where SOURCE = @SOURCE)
            )
            begin
            insert into WEALTHCAPACITYFORMULAREALESTATESOURCE(                
              WEALTHCAPACITYFORMULAID,
              [SOURCE],
              [ADDEDBYID],
              [CHANGEDBYID],
              [DATEADDED],
              [DATECHANGED]
            )
            select 
              ID,
              @SOURCE,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE
              from WEALTHCAPACITYFORMULA;
                        end


                        update dbo.WPREALESTATE set
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE,
                            SOURCE = @SOURCE,
                            CNOTES = @CNOTES,
                            DEEDTYPE = @DEEDTYPE,
                            SALEPRICE = @SALEPRICE,
                            SALEDATE = @SALEDATE,
                            BUYERMAILINGADDRESS = @BUYERMAILINGADDRESS,
                            SELLERMAILINGADDRESS = @SELLERMAILINGADDRESS,
                            SELLER = @SELLER,
                            BUYER = @BUYER,
                            LEGALDESCRIPTION = @LEGALDESCRIPTION,
                            PROPERTYUSE = @PROPERTYUSE,
                            LOTSIZE = @LOTSIZE,
                            ROOF = @ROOF,
                            FOUNDATION = @FOUNDATION,
                            EXTERIORWALLS = @EXTERIORWALLS,
                            BASEMENT = @BASEMENT,
                            CONSTRUCTION = @CONSTRUCTION,
                            HEATING = @HEATING,
                            AIRCONDITIONING = @AIRCONDITIONING,
                            STYLE = @STYLE,
                            ELEVATOR = @ELEVATOR,
                            NOOFBUILDINGS = @NOOFBUILDINGS,
                            BUILDINGAREA = @BUILDINGAREA,
                            GARAGETYPE = @GARAGETYPE,
                            FIREPLACE = @FIREPLACE,
                            TOTALROOMS = @TOTALROOMS,
                            PARTIALBATHS = @PARTIALBATHS,
                            BATHS = @BATHS,
                            BEDROOMS = @BEDROOMS,
                            UNITS = @UNITS,
                            STORIES = @STORIES,
                            POOLSPA = @POOLSPA,
                            YEARBUILT = @YEARBUILT,
                            OWNER = @OWNER,
                            BB_COUNTY = @BB_COUNTY,
                            MAILINGADDRESS = @MAILINGADDRESS,
                            LOANAMOUNT = @LOANAMOUNT,
                            TYPEOFMORTGAGE = @TYPEOFMORTGAGE,
                            LENDER = @LENDER,
                            LOANTYPE = @LOANTYPE,
                            DUEDATE = @DUEDATE,
                            ASSESSORSPARCELNUMBER = @ASSESSORSPARCELNUMBER,
                            LANDUSE = @LANDUSE,
                            MARKETIMPROVEMENTVALUE = @MARKETIMPROVEMENTVALUE,
                            TOTALMARKETVALUE = @TOTALMARKETVALUE,
                            MARKETLANDVALUE = @MARKETLANDVALUE,
                            MARKETVALUEYEAR = @MARKETVALUEYEAR,
                            ASSESSMENTYEAR = @ASSESSMENTYEAR,
                            ASSESSEDIMPROVEMENTVALUE = @ASSESSEDIMPROVEMENTVALUE,
                            ASSESSEDTOTALVALUE = @ASSESSEDTOTALVALUE,
                            ASSESSEDLANDVALUE = @ASSESSEDLANDVALUE,
                            PROPADDRESS = @PROPADDRESS,
                            PROPCITY = @PROPCITY,
                            PROPSTATE = @PROPSTATE,
                            PROPZIP = @PROPZIP,
                            BORROWERS = @BORROWERS,
                            TAPECUTDATE = @TAPECUTDATE,
                            OWNERCAREOFNAME = @OWNERCAREOFNAME,
                            ASSESSEENAME = @ASSESSEENAME,
                            ASSESSEENAME2 = @ASSESSEENAME2,
                            ASSESSEECAREOFNAME = @ASSESSEECAREOFNAME,
                            ASSESSEEMAILINGADDRESS = @ASSESSEEMAILINGADDRESS,
                            ASSESSMENTRECORDINGDATE = @ASSESSMENTRECORDINGDATE,
                            DOCUMENTTYPE = @DOCUMENTTYPE,
                            ASSESSMENTSALEPRICE = @ASSESSMENTSALEPRICE,
                            ASSESSMENTSALEPRICECODE = @ASSESSMENTSALEPRICECODE,
                            LEGALLOTNUMBER = @LEGALLOTNUMBER,
                            LEGALBLOCK = @LEGALBLOCK,
                            LEGALUNIT = @LEGALUNIT,
                            LEGALCITY = @LEGALCITY,
                            LEGALSUBDIVISION = @LEGALSUBDIVISION,
                            LANDUSECODE = @LANDUSECODE,
                            PARKINGNUMBEROFCARS = @PARKINGNUMBEROFCARS,
                            BUILDINGCLASS = @BUILDINGCLASS,
                            BUYER2 = @BUYER2,
                            BUYERVESTING = @BUYERVESTING,
                            BUYERCAREOFNAME = @BUYERCAREOFNAME,
                            SELLER2 = @SELLER2,
                            PROPUSECODE = @PROPUSECODE,
                            CONCURRENTLOANAMOUNT = @CONCURRENTLOANAMOUNT,
                            CONCURRENTLOANTYPE = @CONCURRENTLOANTYPE,
                            CONCURRENTTYPEFINANCING = @CONCURRENTTYPEFINANCING,
                            CONCURRENTDUEDATE = @CONCURRENTDUEDATE,
                            CONCURRENTLENDERNAME = @CONCURRENTLENDERNAME,
                            DEEDTITLECOMPANY = @DEEDTITLECOMPANY,
                            DEEDLEGALLOTCODE = @DEEDLEGALLOTCODE,
                            DEEDLEGALLOTNUMBER = @DEEDLEGALLOTNUMBER,
                            DEEDLEGALBLOCK = @DEEDLEGALBLOCK,
                            DEEDLEGALUNIT = @DEEDLEGALUNIT,
                            DEEDLEGALCITY = @DEEDLEGALCITY,
                            DEEDLEGALSUBDIVISION = @DEEDLEGALSUBDIVISION,
                            DEEDLEGALBRIEFDESCRIPTION = @DEEDLEGALBRIEFDESCRIPTION,
                            DEEDRECORDINGDATE = @DEEDRECORDINGDATE,
                            MORTRECORDINGDATE = @MORTRECORDINGDATE,
                            BORROWERS2 = @BORROWERS2,
                            VESTINGCODE = @VESTINGCODE,
                            BORROWERMAILINGADDRESS = @BORROWERMAILINGADDRESS,
                            ORIGINALCONTRACTDATE = @ORIGINALCONTRACTDATE,
                            MORT2_RECORDINGDATE = @MORT2_RECORDINGDATE,
                            MORT2_BORROWERS = @MORT2_BORROWERS,
                            MORT2_BORROWERS2 = @MORT2_BORROWERS2,
                            MORT2_VESTINGCODE = @MORT2_VESTINGCODE,
                            MORT2_BORROWERMAILINGADDRESS = @MORT2_BORROWERMAILINGADDRESS,
                            MORT2_ORIGINALCONTRACTDATE = @MORT2_ORIGINALCONTRACTDATE,
                            MORT2_LOANAMOUNT = @MORT2_LOANAMOUNT,
                            MORT2_LOANTYPE = @MORT2_LOANTYPE,
                            MORT2_TYPEOFMORTGAGE = @MORT2_TYPEOFMORTGAGE,
                            MORT2_DUEDATE = @MORT2_DUEDATE,
                            MORT2_LENDER = @MORT2_LENDER,
                            SUMMARYSALESVALUE = @SUMMARYSALESVALUE,
                            SUMMARYSALESRECORDINGDATE = @SUMMARYSALESRECORDINGDATE,
                            PROPERTYVALUATION = @PROPERTYVALUATION,
              HISTORICCODE = case when HISTORICCODE = 2 then 0 else HISTORICCODE end
                        where
                            ID=@ID;

                        declare @WEALTHID uniqueidentifier;
                        select @WEALTHID=WEALTHID from dbo.WPREALESTATE where ID=@ID;

                        exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_REALESTATE @WEALTHID, @CHANGEAGENTID;
                        exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;

                        return 0;
                    end