Estimates, Std Plans & Cost Books

An Estimate is the predicted cost of every resource needed to build a single Lot. Every Purchase Order, every variance, every job‑cost report, and every Lot budget number derives from it. Estimates are the most confusing subsystem in SMART — this page starts with the high‑level picture, and you can click into the deep details only where you need them.

At a glance

• An estimate is a per‑Lot snapshot that joins three master libraries: Std Plan (schedule template), Std Book / Cost Book (resources per activity), and Vendor Bid (current prices).
• It runs when a Lot is queued via WorkLotProcess — almost always because a contract just ratified or a change order ratified.
• The output lives in two tables: EstimateActivity (one row per activity) and EstimateResource (one row per resource).
• Only EstimateActivity.Type='y' rows ever turn into Purchase Orders.
• Estimates are point‑in‑time snapshots — changing the cost book or vendor bid does not automatically reflow already‑estimated lots.

The mental model

There are three master libraries that get assembled into an estimate:

Library	What it stores	”Owns the answer to…”
Std Plan (StdPlan*)	The schedule template — which Activities run for a Plan, in what order, how long they take.	When does the framing crew show up?
Cost Book (StdBook*)	The cost template — which Resources each Activity consumes, in what quantity.	How much rebar does activity 23500 use?
Vendor Bid (VendorBid*)	The price list — what each Resource costs from each Vendor on each Project.	What does that rebar actually cost us this quarter?

The estimate is the per‑Lot snapshot that joins all three.

Full data flow diagram (libraries → estimate → POs)

   Std Plan (StdPlanActivity)  ─┐                                   ┌─►  LotSelectionActivity (schedule per Lot)
                                │                                   │
   Std Plan (StdBookPlanOption) ─┐                                  │
                                  │                                  │
   Cost Book (StdBookActivity) ───┼──►  Estimate (per Lot)  ─────────┼─►  EstimateActivity   (one row / activity / lot)
                                  │       (snapshot at ratify)       │       └─► PO ('y'-type only)
   Cost Book (StdBookResource) ──┘                                  │
                                  │                                  │
   Vendor Bid (VendorBidResource) ┘                                  └─►  EstimateResource   (one row / resource / activity / lot)
                                                                           └─► drives PO line items

StdPlan vs StdBook — same words, different jobs

“Std Plan” and “Std Book” sound similar and overlap heavily, but they’re separate trees:

• StdPlan* answers schedule questions: activities, durations, predecessors, critical path.
• StdBook* answers money questions: which resources each activity consumes and at what quantity.

A given plan (like KESWICK-A) appears in both. A change to StdPlanActivity does not move money. A change to StdBookResource does not move dates.

Numbers from the test database (Nov 2026): 7.0M EstimateActivity rows, 12.5M EstimateResource rows, 44.6M StdBookResource rows, 5,626 active VendorBid rows, 595k ChosenOption rows. Estimates are big.

What an estimate looks like on disk

After spProcessEstimateCreate runs for a Lot, the estimate is two child tables hanging off the Lot’s (ProjectID, BuildingID, UnitID) key: EstimateActivity (one row per activity) and EstimateResource (one row per resource consumed by an activity).

EstimateActivity columns (one row per activity on the Lot)

Field	Meaning
ActivityID (5‑char)	E.g. 23500 (Rough Framing). Joins to dbo.Activity for the human name.
Type	s standard activity, y “buyable” activity that becomes a PO, n no‑PO/manual, c critical only. Only y-type activities ever generate POs.
OriginalBudget / CurrentBudget	Starting and current dollar budget. CurrentBudget moves with VPOs and budget changes.
OriginalTax / CurrentTax	Sales tax portion.
Duration, DurationAdj, StartAdj	Days the activity takes; adjustments roll into schedule date math.
OriginalSchDate, CurrentSchDate, SchCompleteDate	Scheduled dates (start, current expected, completed).
PONumber, POVendorID, POReleaseDate	Once a PO is cut for this activity, it’s stamped here.
BalanceOnOrder	Outstanding PO dollars not yet invoiced.
VariancesRecorded	Variance dollars already booked against this activity.
InvoiceTotalAmount	Dollars actually invoiced to date.
LeadTime	Days the vendor needs (copied from VendorBid.LeadTime).
BuilderID	Who’s responsible for managing this activity.

EstimateResource columns (one row per resource consumed)

Field	Meaning
EstimateActivitySysID	FK to the parent activity row.
ResourceID (12‑char)	The thing being purchased (joins to dbo.Resource).
Quantity	How many units. Already multiplied by the option quantity.
VendorPrice	Snapshot from VendorBidResource.NewPrice at ratify time.
PurchaseUnit / EstimateUnit / ConversionFactor	Lets the same resource be purchased in one unit (cubic yards) and estimated in another (square feet).
Taxable, TaxRate	Whether sales tax is added; tax rate snapshotted from project.
Type	s / y / n mirroring activity type.

Estimates are point-in-time copies

EstimateActivity and EstimateResource are snapshots. A change to StdBookResource (the master) does not automatically reflow already‑estimated lots. You have to flag the lot for re‑estimate (ProcessEstimate=1) or use a global replace utility. This is why “I changed the cost book but the lot didn’t update” is the most common estimate question developers get.

What triggers an estimate

There is no nightly job that estimates everything. An estimate runs only when a Lot has been queued for it. Three things queue a lot: a contract ratifying, a change order ratifying, or a manual re‑estimate from SMART.

The work-queue mechanics (WorkLotProcess + E01/E02)

Trigger	What gets set	Who sets it
Contract ratifies	Lot.ProcessEstimate = 1, Lot.PendingStatus = 'sale' / 'pstart' / 'pchng', and a WorkLotProcess row with ProcessCodeID='E01'	spProcessContractRatify (sale path) and spProcessContractAccept (inventory path)
Change order ratifies	A WorkLotProcess row with ProcessCodeID='E02'; lot’s EstimateRan stays 1	Change order workflow + spProcessChangeRatify
Manual re‑estimate	Lot.ProcessEstimate = 1 set via SMART (Project Mgmt → Estimate Inquiry → Re‑estimate) or a CSR script	A user; goes through the same E01/E02 paths

WorkLotProcess is the work queue. The estimate batch — driven by spProcessEstimateCreate (SMART Process Monitor or a scheduled SQL Agent job per area) — picks up the next row where ProcessCodeID='E01'/'E02' and UserInControl = @UserID and processes it.

SELECT * FROM dbo.WorkLotProcess
WHERE ProcessCodeID IN ('E01','E02')
ORDER BY WorkLotProcessSysID;

If the queue stays large, estimates are backlogged. The Process table (2.8M rows) records every batch run and any errors.

How an estimate is built

spProcessEstimateCreate (Kenny Norton, 2002) is the master proc. It walks the queue, validates each lot, lays down the base plan, layers in chosen options, applies global replacements, and commits the snapshot to disk.

Full algorithm of spProcessEstimateCreate (13 steps)

The high‑level flow is the same whether the trigger is E01 (original) or E02 (change request):

1Pop the next Lot from WorkLotProcess where ProcessCodeID=‘E01’ and UserInControl = @UserID. Set InProcess=1. Open a Process row for audit.

2Validate the Lot has a StartDate and a unique Slot within its RTeam. Otherwise log E11/E14/E15 in EstimateErrorLog and skip.

3Choose the books: Project.ActiveCostBookID, Project.ActivePriceBookID, and Project.BidProjToUse drive every join below.

4Decide original vs change: if Lot.EstimateRan = 0 ⇒ ProcessCodeID=‘E01’ (Original Estimate); else ⇒ ‘E02’ (Change Request). Original runs first wipe prior EstimateActivity/EstimateResource rows via spProcessEstimateDeleteActivity.

5Lay down the base plan: spProcessEstimateActivity with OptionsID=” and QtyMultiplier=1. This copies StdBookActivity rows for the Plan into EstimateActivity, then for each activity calls spProcessEstimateResource to copy StdBookResource rows into EstimateResource with vendor‑bid prices joined.

6Apply chosen options: loop through every distinct ChosenOption.OptionsID on the lot where RatifiedQuantity <> 0. For each, call spProcessEstimateActivity with QtyMultiplier = SUM(RatifiedQuantity). Each option layers additional activities and resources on top of the base.

7Global Replace: spProcessEstimateGlobalRepl swaps out any StdBookResource rows flagged GlobalReplace/GlobalInsert for the project. This is how brand‑new resources or company‑wide swaps are forced into in‑flight estimates.

8Mark options estimated: UPDATE ChosenOption SET EstimatedQuantity = RatifiedQuantity, RatifiedQuantity = 0, EstimatedDate = today. The option won’t be re‑estimated unless a new ratified quantity comes in.

9Roll up duplicates: if the lot is sold (f/i) and the same OptionsID appears more than once at ChangeOrderNum=0 (left over from inventory + sale layering), sum quantities and consolidate to one row.

10Update the Lot: TotalBudgetCurrent, TotalBudgetOriginal, EstimateCostPlan, VariancesUnrecorded are incremented; EstimateRan=1, ProcessEstimate=0, PendingStatus=”, ErrorsExist set if anything was logged.

11Audit: insert into LotTransLog with ProcessCodeID=‘E01’ or ‘E02’ and a description like “Processed using Plan: KESWICK-A (KESWICK-A)“.

12First‑run only: call spProcessLotAdjust to apply Lot‑level adjustments (lot premiums, builder/area adjustments) on top.

13Drop the work row (DELETE FROM WorkLotProcess). Loop back to step 1 for the next lot in the area.

How StdBook* is structured

The cost book is 3 nested levels, and you have to join all of them to get from “Plan + Option” down to “Resource + Quantity”:

StdBookPlanOption  →  StdBookActivity  →  StdBookResource

This is the part that bites everyone: filter on the outer level first (StdBookPlanOptionSysID) — never on ActivityID alone, since StdBookActivity has 28M rows.

Walking the cost book end to end (with SQL)

StdBookPlanOption          (CostBookID, PlansID, OptionsID)         ──┐
   │                                                                  │
   └─► StdBookActivity      (StdBookPlanOptionSysID, ActivityID, Type)│   "for this plan+option, run these activities"
          │                                                            │
          └─► StdBookResource (StdBookActivitySysID, ResourceID, Qty) ─┘   "and each activity needs these resources"

SELECT
    spo.PlansID,
    spo.OptionsID,
    sba.ActivityID,
    sba.Type        AS act_type,
    sbr.ResourceID,
    sbr.Quantity,
    sbr.VendorPrice,
    sbr.Total
FROM   dbo.StdBookPlanOption AS spo
JOIN   dbo.StdBookActivity   AS sba ON sba.StdBookPlanOptionSysID = spo.StdBookPlanOptionSysID
JOIN   dbo.StdBookResource   AS sbr ON sbr.StdBookActivitySysID   = sba.StdBookActivitySysID
WHERE  spo.PlansID    = 'KESWICK-A   '
  AND  spo.CostBookID = 12          -- the project's ActiveCostBookID
  AND  spo.OptionsID  = ''          -- '' means "the base plan"
ORDER BY sba.ActivityID, sbr.ResourceID;

Things this query reveals once you run it:

1. OptionsID = '' is the base plan. Every other OptionsID is an additional option that layers on top.
2. Type='y' is the only activity type that becomes a Purchase Order. s/n/c activities have schedule and budget impact but no PO is ever cut.
3. StdBookResource.VendorPrice is the snapshotted price stored in the cost book, filled in from the vendor bid the last time the cost book was refreshed (PriceBookRefreshLog records every refresh — 637k rows of history).
4. GlobalReplace / GlobalInsert flags on StdBookResource are how a company‑wide change is propagated into already‑live cost book rows.

Where vendor pricing actually comes from (VendorBid joins)

When spProcessEstimateActivity is laying down EstimateResource rows, it joins through to the active VendorBid for the project + vendor + resource:

VendorBid          (VendorID, ProjectID, EffectiveDate, ExpirationDate)
   │
   └─► VendorBidResource (ResourceID, NewPrice, OldPrice)

The proc tries the lot’s actual ProjectID first, and falls back to Project.BidProjToUse if there’s no project‑specific bid. That fallback is critical: most communities share a “master bid project” so they don’t have to re‑bid every line on every new community.

VendorBid.EffectiveDate / ExpirationDate is what makes a bid “active”. If both bids are expired, the resource ends up at VendorPrice = 0 and an error is written to EstimateErrorLog.

Re‑estimates and change orders

A re‑estimate (E02) does not wipe the lot’s estimate. It surgically applies just the rows that changed — adding activities for new options, removing activities for cancelled options, and updating budgets. Existing snapshotted prices stay put unless you explicitly flag a re‑pull.

What changes (and what doesn’t) on a re-estimate

What changed	What happens on re‑estimate
New ChosenOption (added on a change order)	A new EstimateActivity set is layered in for that option, sized by the change order’s RatifiedQuantity.
Removed ChosenOption	The activities/resources that came from that option are removed via spProcessEstimateDeleteActivity. Budget rolls back.
Vendor bid price update	Has no effect on existing EstimateResource rows — these are snapshots. To re‑pull the price, set Lot.ProcessEstimate=1 so the lot re‑runs.
StdBookResource change	Same as above; only future estimates pick it up unless GlobalReplace=1 is set on the new row.
Variance posted	EstimateActivity.VariancesRecorded and Lot.VariancesUnrecorded move. The estimate dollars don’t, but CurrentBudget does on a VPO.

Dump one lot’s full estimate (SQL recipe)

SELECT  ea.ActivityID, a.ActivityName, ea.Type, ea.OriginalBudget,
        ea.CurrentBudget, ea.PONumber, ea.POReleaseDate
FROM    dbo.EstimateActivity ea
JOIN    dbo.Activity a ON a.ActivityID = ea.ActivityID
WHERE   ea.ProjectID  = '01001000'
  AND   ea.BuildingID = '001'
  AND   ea.UnitID     = '0001'
ORDER BY ea.ActivityID;

-- and for the resource detail under any one activity:
SELECT  er.ResourceID, r.ResourceDesc, er.Quantity, er.VendorPrice,
        (er.Quantity * er.VendorPrice) AS line_total
FROM    dbo.EstimateResource er
JOIN    dbo.Resource r ON r.ResourceID = er.ResourceID
WHERE   er.EstimateActivitySysID = 12345678;

When the estimate goes away

A cancelled contract with no replacement wipes the estimate via spUtilityDeleteEstimate. A cancellation that auto-creates an inventory contract (a near‑complete spec) keeps the estimate so we don’t re‑order materials. A closing (clo) leaves the estimate in place forever as the historical job‑cost record.

Errors and the E‑codes

Every Estimate run that hits a problem writes a row to EstimateErrorLog. This is the first table to check when an estimate “didn’t work”. The blocking codes are E11 (no Lot StartDate), E14 (slot not unique), and E15 (slot required).

SELECT TOP 100 *
FROM   dbo.EstimateErrorLog
WHERE  ProjectID = '01001000'
ORDER BY EntryDate DESC;

All 17 E-codes (full reference)

ProcessCodeID	Meaning
E01	Estimate Processed (success log)
E02	Change Processed (success log on a re‑estimate)
E03	Production Adjustment
E04	Estimate Deleted
E05	PO(s) Released
E06	PO Cancelled
E07	VPO Issued
E08	Vendor Reassigned
E09	Compare to Standards
E10	Reassign Builder
E11	No Lot Start Date (lot needs a StartDate to estimate)
E12	SO Released
E13	Preliminary Plan Processed
E14	Slot Number Is Not Unique within the RTeam
E15	Slot Number Required
E16	Permanently Deleted PO
E17	Slot Realignment Trade Change

Bold codes (E11, E14, E15) are the ones that block the estimate. The others are informational or success markers used to power the audit timeline in SMART.

Diagnostics: lots that need an estimate

SELECT * FROM dbo.vwLookupLotsToBeEstimated;

It returns every lot where ProcessEstimate=1, plus the reason it’s queued. If it ever has more rows than expected, an estimate batch hasn’t run for a while or a lot is stuck because of E11/E14/E15.

Where this lives in the apps

• SMART → Project Management → Estimate Inquiry — view, drill, and re‑estimate a single lot.
• SMART → Product Development → Std Plan Management / Cost Book Management — edit StdPlan* and StdBook* rows.
• SMART → Project Management → Vendor Bids — manage VendorBid / VendorBidResource rows.
• SMART 2.0 → product‑development — same data, in the browser (SMART 2.0 web).
• SMART → Administration → Process Monitor — watch spProcessEstimateCreate runs and errors.
• CSR scripts (SOURCECODE/CSR/) — many ready‑made estimate maintenance scripts.

Common gotchas

The questions developers actually ask, in order of frequency:

1. “I changed the cost book and the lot didn’t update.”

Existing EstimateResource rows are snapshots. Set Lot.ProcessEstimate=1 for the affected lots (or use vwLoadProcessCopyStdPlan to sweep), or set GlobalReplace=1 on the new StdBookResource row before the next batch.

2. “The lot has zero estimate even though it ratified.”

Check EstimateErrorLog for E11 (no StartDate) or E14/E15 (slot issues). The lot was queued, then rejected.

3. “Why does this resource cost $0?”

No active VendorBid for the project + resource. Check VendorBid.EffectiveDate/ExpirationDate against the ratify date, and confirm Project.BidProjToUse points at a project with a real bid.

4. “Why is there no PO for this activity?”

Only Type='y' activities generate POs. Type='s' activities have a budget but never become POs.

5. “Estimate ran twice and budget is doubled.”

Look at LotTransLog for two E01 entries close together. Usually a developer ran a re‑estimate without first calling spProcessEstimateDeleteActivity. Fix is rollback or spUtilityDeleteEstimate + re‑estimate.

6. “StdBookActivity has 28M rows but my query takes forever.”

Always filter on StdBookPlanOptionSysID first via StdBookPlanOption (CostBookID, PlansID, OptionsID). Filtering on ActivityID alone scans the whole table.

7. “ChosenOption.RatifiedQuantity is 0 for an option I just added.”

That’s normal after the estimate has run — the proc moves it to EstimatedQuantity. To force a re‑estimate of just that option, set RatifiedQuantity = EstimatedQuantity and set Lot.ProcessEstimate=1.

Related procs

spProcessEstimateCreate is the entry point; the others are called by it or by cleanup paths.

Full proc reference

Proc	What it does
spProcessEstimateCreate	Master loop. Walks WorkLotProcess, drives the whole estimate.
spProcessEstimateActivity	For one (Plan, Option) pair on one lot, copies StdBookActivity rows into EstimateActivity and calls spProcessEstimateResource per row.
spProcessEstimateResource	Copies StdBookResource rows into EstimateResource, joining VendorBidResource for current price.
spProcessEstimateGlobalRepl	Applies GlobalReplace / GlobalInsert swaps after the base + options pass.
spProcessEstimateDeleteActivity	Surgically removes activities (used on re‑estimate when an option is removed).
spProcessLotAdjust	First‑run adjustments: lot premiums, builder/area variances.
spUtilityDeleteEstimate	Nuke the entire estimate for a lot (called by spProcessContractCancel).
spProcessBPMEstimateCreate	Preliminary “BPM” estimate used during product development before a real lot exists.
spInsertTransmissionEstimate	Pushes finalized estimate dollars to the Business Central transmission queue.
spInsertTransmissionEstimateVsActual	Job‑cost variance feed (estimate vs actual invoice posted) to BC.

See Schedules & Activities for how EstimateActivity durations turn into actual calendar dates, and Purchase Orders for how Type='y' activities become POs.