CREATE PROCEDURE VALUATIONS_CreateNewValuation

@CreationDate datetime,

@CompletionDate datetime,

@PropertyID int,

@Version int,

@Revision int,

@ContactLegalEntityID int,

@ContactVersion int,

@ContactRevision int,

@PropPlantSubTypeID int,

@ClientSubTypeID int,

@CompanyLegalEntityID int,

@CompanyVersion int,

@CompanyRevision int,

@ClientAddressPropertyID int,

@ClientAddressVersion int,

@ClientAddressRevision int,

@ClientLegalEntityID int,

@ClientVersion int,

@ClientRevision int,

@Revaluation# nvarchar(255),

@PurposeID int,

@FormatID int,

@TypeID int,

@RiskEvaluationID int,

@LocationID int,

@JobProbabilityID int,

@CreatedBy int,

@WorkflowID int,

@OfficeID int

as

-- *------------------------*

-- * Create a new valuation *

-- *------------------------*

insert into VALUATIONS_Valuation

(DateInitiated

,PropertyID

,Version

,Revision

,ContactLegalEntityID

,ContactVersion

,ContactRevision

,PropPlantSubTypeID

,ClientSubTypeID

,CompanyLegalEntityID

,CompanyVersion

,CompanyRevision

,ClientAddressPropertyID

,ClientAddressVersion

,ClientAddressRevision

,ClientLegalEntityID

,ClientVersion

,ClientRevision

,Revaluation#

,PurposeID

,FormatID

,TypeID

,RiskEvaluationID

,LocationID

,JobProbabilityID

,CreatedByPersonID

,StatusID

,OfficeID)

values

(@CreationDate,

@PropertyID,

@Version,

@Revision,

@ContactLegalEntityID,

@ContactVersion,

@ContactRevision,

@PropPlantSubTypeID,

@ClientSubTypeID,

@CompanyLegalEntityID,

@CompanyVersion,

@CompanyRevision,

@ClientAddressPropertyID,

@ClientAddressVersion,

@ClientAddressRevision,

@ClientLegalEntityID,

@ClientVersion,

@ClientRevision,

@Revaluation#,

@PurposeID,

@FormatID,

@TypeID,

@RiskEvaluationID,

@LocationID,

@JobProbabilityID,

@CreatedBy,

2,

@OfficeID)

-- Set up new file number

declare @MaxID int

declare @QuoteMaxID int

declare @InvoiceMaxID int

declare @Code nvarchar(255)

set @MaxID = (select max(ID) from VALUATIONS_Valuation)

update VALUATIONS_CodePrefixAndNumberOfItems set numberofitems = ((select numberofitems from VALUATIONS_CodePrefixAndNumberOfItems where StateID = @LocationID and OfficeID = @OfficeID and type = 1) + 1) where StateID = @LocationID and OfficeID = @OfficeID and type = 1

set @Code = ((select CodePrefix from VALUATIONS_CodePrefixAndNumberOfItems where StateID = @LocationID and OfficeID = @OfficeID and type = 1) + convert(char,(select numberofitems from VALUATIONS_CodePrefixAndNumberOfItems where StateID = @LocationID and OfficeID = @OfficeID and type = 1)))

--Check for null code

set @Code = isnull(@Code,'NoValCd')

insert into VALUATIONS_ValuationCodes(ValuationID,Code) values(@MaxID,@Code)

insert into VALUATIONS_ValuationType(ValuationID,WorkflowID) values(@MaxID,@WorkflowID)

declare @FirstStep as integer

set @FirstStep = (select min(StepID) from WORKFLOWS_WorkflowSteps where WorkflowID = @WorkflowID)

insert into VALUATIONS_WorkflowPosition(ValuationID,Position) values(@MaxID, @FirstStep)

-- Create Real Path

declare @RealPath as nvarchar(255)

set @RealPath = '\' + (select State.Name from State where State.ID = (select Office.State_ID from VALUATIONS_Valuation,Office where Office.ID = VALUATIONS_Valuation.OfficeID and VALUATIONS_Valuation.ID = @MaxID))

+ '\' + (select Office.Name from VALUATIONS_Valuation,Office where Office.ID = VALUATIONS_Valuation.OfficeID and VALUATIONS_Valuation.ID = @MaxID)

+ '\' + ltrim(rtrim(@Code)) + '\'

insert into VALUATIONS_AssociatedItemsRealPath (ValuationID,RealPath) values (@MaxID,@RealPath)

-- Create Quote

insert into VALUATIONS_Quote(DueDate,Agreed,ValuerFee,GrossFee,ApproxValue,GSTIncluded) values(@CompletionDate,2,0,0,0,1)

set @QuoteMaxID = (select max(ID) from VALUATIONS_Quote)

Update VALUATIONS_Valuation set QuoteID = @QuoteMaxID where ID = @MaxID

update VALUATIONS_CodePrefixAndNumberOfItems set numberofitems = ((select numberofitems from VALUATIONS_CodePrefixAndNumberOfItems where StateID = @LocationID and OfficeID = @OfficeID and type = 2) + 1) where StateID = @LocationID and OfficeID = @OfficeID and type = 2

set @Code = ((select CodePrefix from VALUATIONS_CodePrefixAndNumberOfItems where StateID = @LocationID and OfficeID = @OfficeID and type = 2) + convert(char,(select numberofitems from VALUATIONS_CodePrefixAndNumberOfItems where StateID = @LocationID and OfficeID = @OfficeID and type = 2)))

--Check for null code

set @Code = isnull(@Code,'NoQutCd')

insert into VALUATIONS_QuoteCodes(QuoteID,Code) values(@QuoteMaxID,@Code)

-- Create Invoice

insert into VALUATIONS_Invoice(datePaid) values(null)

set @InvoiceMaxID = (select max(ID) from VALUATIONS_Invoice)

Update VALUATIONS_Valuation set InvoiceID = @InvoiceMaxID where ID = @MaxID

-- GST Input credits

insert into VALUATIONS_ExpensesGSTInputCredits (ValuationID,GSTInputCredits) values (@MaxID,0)

-- Set dates

insert into VALUATIONS_StepDueDates(ValuationID,StepID)

select @MaxID,StepID

from WORKFLOWS_WorkflowSteps

where WorkflowID = @WorkflowID

-- Iterate through StepDueDates and insert appropriate dates

declare @StepID as integer

declare @CurrentDate as Datetime

declare @WeekDay as integer

set @CurrentDate = @CreationDate

declare curSteps cursor READ_ONLY for select StepID from VALUATIONS_StepDueDates where ValuationID = @MaxID

open curSteps

fetch next from curSteps into @StepID

while @@FETCH_STATUS != -1

begin

set @CurrentDate = @CurrentDate + (select NumberOfDays

from WORKFLOWS_WorkflowSteps

where WorkflowID = @WorkflowID and StepID = @StepID)

set @WeekDay = (Select DATEPART(dw, @CurrentDate))

-- check for Saturday

If @WeekDay = 6

begin

set @CurrentDate = @CurrentDate + 2

end

-- check for Sunday

if @WeekDay = 7

begin

set @CurrentDate = @CurrentDate + 1

end

update VALUATIONS_StepDueDates set DueDate = @CurrentDate

where StepID = @StepID and ValuationID = @MaxID

fetch next from curSteps into @StepID

end

deallocate curSteps

GO