Released: 4/1/24
Effective: 1/1/23
Updated: 3/28/24
WARNING
Not all fields shown below are sent on every ticket.
To ensure your system process the ticket correctly, program the input parser to parse fields IF they appear in the data set sent. Some fields will not be sent under certain conditions and also doing this future proofs your system if the center ever were to remove fields from the output ticket format.
Database Scheme
|
Database Field Name |
Display Name |
Data Type |
|---|---|---|
|
ticket |
Ticket |
varchar(10) |
|
revision |
Revision |
varchar(3) |
|
completed |
Created |
varchar(24) |
|
type |
Type |
varchar(4) |
|
priority |
Priority |
varchar(4) |
|
category |
Category |
varchar(4) |
|
lookup |
Lookup |
varchar(4) |
|
expires |
Expires |
varchar(24) |
|
account |
User |
varchar(20) |
|
channel |
Channel |
varchar(3) |
|
caller_type |
Caller Type |
varchar(4) |
|
name |
Company Name |
varchar(40) |
|
address1 |
Address |
varchar(40) |
|
city |
City |
varchar(30) |
|
cstate |
State |
varchar(2) |
|
zip |
Zip |
varchar(9) |
|
phone |
Phone |
varchar(11) |
|
phone_ext |
Ext |
varchar(15) |
|
caller |
Caller Name |
varchar(30) |
|
caller_phone |
Caller Phone |
varchar(11) |
|
caller_phone_ext |
Caller Ext |
varchar(15) |
|
caller_language |
Caller Language |
varchar(15) |
|
contact |
Site Contact |
varchar(30) |
|
contact_phone |
Site Phone |
varchar(11) |
|
contact_phone_ext |
Site Phone Ext |
varchar(15) |
|
contact_email |
Site Email |
varchar(80) |
|
contact_cell |
Site Cell |
varchar(11) |
|
cell |
Caller Cell |
varchar(11) |
|
|
Caller Email |
varchar(80) |
|
state |
State |
varchar(2) |
|
county |
County |
varchar(40) |
|
place |
City/Place |
varchar(40) |
|
st_from_address |
Address |
varchar(11) |
|
st_to_address |
n/a |
varchar(11) |
|
street |
Street |
varchar(40) |
|
cross1 |
Cross Street 1 |
varchar(40) |
|
cross2 |
Cross Street 2 |
varchar(40) |
|
area_in_miles |
Area in Sq Miles |
decimal(9,6) |
|
area_in_sqft |
Area in Sq Feet |
int |
|
work_date |
Work Date/Time |
varchar(24) |
|
response_due |
Response Due |
varchar(24) |
|
work_type |
Type of Work |
varchar(80) |
|
done_for |
Work Done For |
varchar(40) |
|
permit |
Permit |
varchar(30) |
|
work_order |
Work Order |
varchar(30) |
|
explosives |
Exploisives |
varchar(1) |
|
vacuum |
Vacuum |
varchar(1) |
|
priority_number |
Priority |
tinyint |
|
minfit_rectangle |
Center Generated GPS |
varchar(100) |
|
remarks_requested |
Re-marks Requested |
varchar(1) |
|
remarks_type |
Request Re-marks From |
varchar(200) |
|
how_delineated |
Delineation Method |
varchar(60) |
|
project_id |
Project ID |
varchar(15) |
|
project_name |
Project Name |
varchar(65) |
|
work_area_zip_codes |
Work Area Zip Codes |
varchar(30) |
|
boring |
Boring |
bit |
|
emergency |
Emergency |
bit |
|
excav_st_walk |
Excavation Enters Street/Sidewalk |
bit |
|
response_required |
Response Required |
bit |
|
eml_confirm (email_allowed) |
Caller Email Confirmation |
bit |
|
sms_confirm (sms_allowed) |
Caller SMS Confirmation |
bit |
|
contact_eml_confirm (email_allowed) |
Site Email Confirmation |
bit |
|
contact_sms_confirm (sms_allowed) |
Site SMS Confirmation |
bit |
|
one_year |
One Year |
bit |
|
pavement_only |
Excavation in Pavement Only |
bit |
|
hashtags |
Searchable Tags |
text |
|
caller_gps |
Caller Provided GPS |
text |
|
location |
Location |
text |
|
comments |
Comments |
text |
Schemes by Format Type
JSON (Request Re-marks shown for all possible fields)
NOTE
field, data followed by comment with data type then max length
JSON
{
"delivery": {
"format_version": "1.05.01.8", //string 20
"center": "USAS", //string 4
"sys_id": "A", //string 1
"member": "MBRCD01", //string 20
"transmitted": "2022-08-09T10:58:14-0700", //string 24
"transmission_type": "TKT", //string 4
"sequence": 15 //int
},
"ticket": {
"ticket": "A121231234", //string 10
"revision": "01A", //string 3
"created": "2022-08-02T10:57:19-0700", //string 24
"type": "REMK", //string 4
"priority": "NORM", //string 4
"category": "LREQ", //string 4
"lookup": "POLY", //string 4
"expires": "2022-08-30T23:59:59-0700", //string 24
"account": "CSR01", //string 20
"channel": "200", //string 3
"work_date": "2022-08-04T17:01:00-0700", //string 24
"response_due": "2022-08-04T17:01:00-0700", //string 24
"work_type": "REPAIR AND REPLACE SOMETHING", //string 80
"done_for": "JOES RESTAURANT", //string 40
"permit": "123456789ABC", //string 30
"work_order": "AB123456", //string 30
"project_id": "", //string 15
"project_name": "", //string 65
"explosives": true, //boolean
"vacuum": true, //boolean
"remarks_type": "ALL", //string 200 (only shown if type = REMK)
"how_delineated": ["WHITEPAINT"], //array 60
"boring": false, //boolean
"excav_st_walk": true, //boolean
"response_required": true, //boolean
"one_year": false, //boolean
"pavement_only": false, //boolean
"searchable_tags": [
"#this",
"#field",
"#contains",
"#various",
"#binary",
"#data",
"#with",
"#unrestricted",
"#length"
], //text unlimted
"comments": "**REQUEST RE-MARKS** FROM ALL-WORK CONTINUING PER JAMES WINGATE--[CSR01 08/02/2022 10:57:06 AM]", //text unlimted
"caller": {
"name": "JIM SCHWILK", //string 30
"language": "ENGLISH", //string 15
"phone": "1231231234", //string 11
"phone_ext": "12345", //string 15
"cell": "1231231234", //string 11
"sms_allowed": true, //boolean
"email": "JIM@DOMAIN.COM", //string 80
"email_allowed": false //boolean
},
"excavator": {
"name": "ABC COMPANY", //string 40
"type": "CONT", //string 4
"address": "123 MAIN ST", //string 40
"city": "SOMEWHERE", //string 30
"state": "NY", //string 2
"zip": "12345" //string 9
},
"site_contact": {
"name": "JAMES WINGATE", //string 30
"phone": "1231231234", //string 11
"email": "JAMES@DOMAIN.COM", //string 80
"email_allowed": false, //boolean
"cell": "1231231234", //string 11
"sms_allowed": true //boolean
},
"excavation_area": {
"state": "CA", //string 2
"county": "LOS ANGELES", //string 40
"place": "CARSON", //string 40
"work_area_zip_codes": "90745", //string 30
"st_from_address": "12345", //string 11
"st_to_address": "12345", //string 11
"street": "MAIN ST", //string 40
"cross1": "1ST ST", //string 40
"cross2": "", //string 40
"location": "DOG IN YARD", //text unlimted
"job_size": {
"area_in_sqft": 137100,
"area_in_miles": 0.004918
}, //decimal 9,6
"work_area": {
"type": "Feature", //string
"properties": null, //string
"geometry": {
"type": "Polygon", //string
"coordinates": [ //array decimal 10,6
[
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456],
[-123.123456, 12.123456]
]
]
}
}
},
"links": {
"map": "https://somelink.doamin.com/linkinformationhereandthiscouldbealongstring", //text unlimted
"epr": "https://somelink.domain.com/eprlinkwithinformationthatislong" //text unlimited
},
"members": [ //each code = string 20 but unlimited codes
"MBR001",
"MBR020",
"MBR039",
"LONGMBR058",
"MBR153",
"MBR172",
"MBR191",
"LONGMBR210",
"MBR324",
"MBR343",
"LONGMBR362",
"MBR077",
"MBR096",
"MBR115",
"LONGMBR134",
"MBR229",
"MBR248",
"MBR267",
"LONGMBR286"
] //text unlimited
}
}
XML
NOTE
comment with data type then max length, followed by field
XML
<?xml version="1.0"?>
<NewDataSet>
<delivery>
<!-- string 20 -->
<format_version>1.05.01.8</format_version>
<!-- string 4 -->
<center>USASA</center>
<!-- string 20 -->
<recipient>MBRCD01</recipient>
<!-- string 4 -->
<transmission_type>TKT</transmission_type>
<!-- string 24 -->
<transmitted>2026-03-28T22:42:02</transmitted>
<!-- int -->
<seq_num>8</seq_num>
<!-- string 10 -->
<ticket>A121231234</ticket>
<!-- string 3 -->
<revision>01A</revision>
</delivery>
<tickets>
<!-- string 10 -->
<ticket>A222310033</ticket>
<!-- string 3 -->
<revision>01A</revision>
<!-- string 24 -->
<created>2026-03-28T09:37:35</created>
<!-- string 20 -->
<account>CSR01</account>
<!-- string 3 -->
<channel>200</channel>
<!-- string 24 -->
<work_date>2026-04-02T17:01:00</work_date>
<!-- string 1 -->
<response_required>Y</response_required>
<!-- string 24 -->
<response_due>2026-04-02T17:01:00</response_due>
<!-- string 24 -->
<replace_by_date>2026-04-25T23:59:59</replace_by_date>
<!-- string 24 -->
<expires>2026-04-25T23:59:59</expires>
<!-- string 1 -->
<one_year>N</one_year>
<!-- string 4 -->
<priority>NORM</priority>
<!-- string 4 -->
<type>REMK</type>
<!-- string 4 -->
<category>LREQ</category>
<!-- string 4 -->
<lookup>POLY</lookup>
<!-- string 2 -->
<state>CA</state>
<!-- string 40 -->
<county>LOS ANGELES</county>
<!-- string 40 -->
<place>CARSON</place>
<!-- string 30 -->
<work_area_zip_codes>12345</work_area_zip_codes>
<!-- string 11 -->
<st_from_address>12345</st_from_address>
<!-- string 11 -->
<st_to_address>12345</st_to_address>
<!-- string 40 -->
<street>MAIN ST</street>
<!-- string 40 -->
<cross1>1ST ST</cross1>
<!-- string 40 -->
<cross2>2ND ST</cross2>
<polygon>
<coordinate>
<!-- decimal 10,6 -->
<latitude>12.123456</latitude>
<longitude>-123.123456</longitude>
</coordinate>
<coordinate>
<latitude>12.123456</latitude>
<longitude>-123.123456</longitude>
</coordinate>
<coordinate>
<latitude>12.123456</latitude>
<longitude>-123.123456</longitude>
</coordinate>
<coordinate>
<latitude>12.123456</latitude>
<longitude>-123.123456</longitude>
</coordinate>
</polygon>
<!-- int -->
<area_in_sqft>123456</area_in_sqft>
<!-- decimal 9,6 -->
<area_in_miles>0.132456</area_in_miles>
<!-- string 60 -->
<how_delineated>WHITEPAINT</how_delineated>
<!-- string 1 -->
<excav_st_walk>Y</excav_st_walk>
<!-- string 80 -->
<work_type>REPAIR AND REPLACE SOMETHING</work_type>
<!-- string 1 -->
<boring>Y</boring>
<!-- string 1 -->
<explosives>N</explosives>
<!-- string 1 -->
<vacuum>Y</vacuum>
<!-- string 1 -->
<pavement_only>N</pavement_only>
<!-- string 30 -->
<permit>123456789ABC</permit>
<!-- string 30 -->
<work_order>AB123456</work_order>
<!-- string 40 -->
<done_for>JOES RESTAURANT</done_for>
<!-- string 15 -->
<project_id>12345</project_id>
<!-- string 65 -->
<project_name>JOES PROJECT</project_name>
<!-- string 200 (only shown if type = REMK) -->
<remarks_type>ALL</remarks_type>
<!-- string 40 -->
<name>ABC COMPANY</name>
<!-- string 4 -->
<caller_type>CONT</caller_type>
<!-- string 40 -->
<address1>123 MAIN ST</address1>
<!-- string 30 -->
<city>SOMEWHERE</city>
<!-- string 2 -->
<cstate>NY</cstate>
<!-- string 9 -->
<zip>12345</zip>
<!-- string 11 -->
<phone>1231231234</phone>
<!-- string 30 -->
<caller>JIM SCHWILK</caller>
<!-- string 15 -->
<caller_language>ENGLISH</caller_language>
<!-- string 11 -->
<cell>1231231234</cell>
<!-- string 1 -->
<sms_allowed>Y</sms_allowed>
<!-- string 80 -->
<email>JIM@DOMAIN.COM</email>
<!-- string 1 -->
<email_allowed>Y</email_allowed>
<!-- string 30 -->
<contact>JAMES WINGATE</contact>
<!-- string 11 -->
<contact_phone>1231231234</contact_phone>
<!-- string 11 -->
<contact_cell>1231231234</contact_cell>
<!-- string 1 -->
<contact_sms_allowed>Y</contact_sms_allowed>
<!-- string 80 -->
<contact_email>JAMES@DOMAIN.COM</contact_email>
<!-- string 1 -->
<contact_email_allowed>Y</contact_email_allowed>
<!-- text unlimited -->
<location xml:space="preserve">DOG IN YARD</location>
<!-- text unlimited -->
<comments xml:space="preserve">**REQUEST RE-MARKS** FROM ALL - WORK CONTINUING PER JIM SCHWILK--[CSR01 03/28/2026 09:34 AM]</comments>
<memberlist>
<!-- unlimited members -->
<member name="MBR001">MBR001 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR001</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR001@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR020">MBR020 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR020</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR020@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR039">MBR039 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR039</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR039@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="LONGMBR058">LONGMBR058 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>LONGMBR058</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>LONGMBR058@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR153">MBR153 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR153</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR153@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR172">MBR172 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR172</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR172@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR191">MBR191 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR191</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR191@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="LONGMBR210">LONGMBR210 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>LONGMBR210</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>LONGMBR210@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR324">MBR324 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR324</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR324@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR343">MBR343 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR343</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR343@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="LONGMBR362">LONGMBR362 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>LONGMBR362</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>LONGMBR362@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR077">MBR077 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR077</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR077@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR096">MBR096 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR096</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR096@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR115">MBR115 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR115</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR115@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="LONGMBR134">LONGMBR134 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>LONGMBR134</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>LONGMBR134@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR229">MBR229 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR229</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR229@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR248">MBR248 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR248</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR248@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="MBR267">MBR267 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>MBR267</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>MBR267@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="LONGMBR286">LONGMBR286 <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>LONGMBR286</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>LONGMBR286@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
<member name="WBMWD">WBMWD <member_contacts>
<mc_item>
<!-- string 4 -->
<mc_type>EMER</mc_type>
<!-- string 40 -->
<mc_contact>WBMWD</mc_contact>
<!-- string 11 -->
<mc_phone>1231231234</mc_phone>
<!-- string 80 -->
<mc_email>WBMWD@DOMAIN</mc_email>
</mc_item>
</member_contacts>
</member>
</memberlist>
<!-- text unlimited -->
<map_url>https://somelink.doamin.com/linkinformationhereandthiscouldbealongstring</map_url>
<!-- text unlimited -->
<response_url>https://somelink.domain.com/eprlinkwithinformationthatislong</response_url>
</tickets>
</NewDataSet>