Service Requests

A Service Request (SR) is a homeowner-reported issue. The header lives in dbo.ServiceRequest (~75,927 rows in test). Each request can have many specific issues attached — each is a ServiceRequestLine.

At a glance

• A Service Request is one homeowner report; a Service Request Line is each individual issue inside it.
• 5 line statuses: o Open → q QA/Builder Notified → d Denied / r Resolved (no pay) / c Completed.
• 13 request types covering Emergency, 1-3-11, Construction promise, Structural, QA Checklist, etc. (010, 020, 040, 045, 050, 070, 100, 110, 120, 130, plus 3 inactive).
• Once an SR line needs a paid trade visit, it’s dispatched as a Service Order — see Service Orders.

The flow

1Homeowner reports via portal, phone, email, etc. (RequestSource).

2CSR creates a ServiceRequest tied to the closed Contract / Lot.

3Each issue is added as a ServiceRequestLine with a status (o/q/d/r/c).

4Notes & photos attached via ServiceRequestLineNote and ServiceRequestFile.

5Service Order dispatched to a vendor (see Service Orders).

6Resolution — line marked Completed (c) or Resolved (r).

Tables

The header is ServiceRequest; the workhorse is ServiceRequestLine (one row per issue). Notes, files, and audit logs hang off of those.

Full Service Request table reference

Table	What
ServiceRequest	Header.
ServiceRequestLine	One row per specific issue (the workhorse).
ServiceRequestLineNote	Each comment / status change.
ServiceRequestFile	Photos, attachments.
ServiceRequestLineStatus	Lookup of line statuses.
RequestType	High-level request category (Emergency, 1‑3‑11, Construction promise, etc.).
RequestSource	How the request came in (Portal, Email, Phone, Sales Agent, Online form, etc.).
ServiceRequestTransLog	Audit log.
vwServiceRequest, vwServiceRequestLine, vwLotServiceRequests, vwContactsServiceRequests	Reporting views.

Service Request Line Statuses

Code	Name	Meaning
o	Open	Just created. Awaiting CSR action.
q	QA / Builder Notified	Sent to QA or the field for review.
d	Denied	Out of warranty / not our responsibility.
r	Resolved (No Pay)	Resolved without dispatching a paid trade.
c	Completed	Trade finished the work; line closed.

Request Types

13 types in total — 10 active and 3 inactive (kept for historical reporting). The active ones are: 010 Maintenance, 020 Emergency, 040 Warranty Period Expired, 045 Construction promise, 050 1-3-11, 070 Supply Chain, 100 VPO Substitute, 110 Lowes / Community Expense, 120 Structural, 130 QA Checklist / Report.

Full Request Type table (with inactive entries)

Code	Description	Active
010	Model & Prod Inv Maintenance	Yes
020	Emergency Request	Yes
040	Warranty Period Expired	Yes
045	Construction promise	Yes
050	1‑3‑11 Request	Yes
070	Supply Chain	Yes
100	VPO Substitute	Yes
110	Lowes / Community Expense	Yes
120	Structural	Yes
130	QA Checklist / Report	Yes
030	Important but not urgent	No
046	Survived Closing	No
090	Winter Kill	No

Request Sources

Phone Call, Builder Request, Walk In Office, Sales Agent, CSR Request, Emergency Request, Grasshopper / Voice Mail, Online form, Service Request form, Home Buyer Portal, Email, Survey Response, Mail, Construction request, Winter Kill.

Where in the apps

App	Where
Home Owner Portal	Warranty.aspx, Warranty-imageUpload.aspx (this is the buyer-facing form)
SMART 2.0	service/ — primary CSR / warranty workspace
Blazor Warranty	Pages/ServiceRequests/ — used in some divisions instead of or alongside SMART 2.0
BCIntegration	sends paid SO bills to BC just like construction POs

Common queries

Open service request lines, oldest first

SELECT  sr.RequestDate, sr.ProjectID, sr.BuildingID, sr.UnitID,
        srl.LineDescription, srls.ServiceRequestLineStatusName
FROM    dbo.ServiceRequest          sr
JOIN    dbo.ServiceRequestLine      srl  ON srl.ServiceRequestSysID = sr.ServiceRequestSysID
JOIN    dbo.ServiceRequestLineStatus srls ON srls.ServiceRequestLineStatusID = srl.ServiceRequestLineStatusID
WHERE   srl.ServiceRequestLineStatusID = 'o'
ORDER  BY sr.RequestDate;