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
{
"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 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>2024-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>2024-03-28T09:37:35</created>
<!-- string 20 -->
<account>CSR01</account>
<!-- string 3 -->
<channel>200</channel>
<!-- string 24 -->
<work_date>2024-04-02T17:01:00</work_date>
<!-- string 1 -->
<response_required>Y</response_required>
<!-- string 24 -->
<response_due>2024-04-02T17:01:00</response_due>
<!-- string 24 -->
<replace_by_date>2024-04-25T23:59:59</replace_by_date>
<!-- string 24 -->
<expires>2024-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 80 -->
<email>JIM@DOMAIN.COM</email>
<!-- string 30 -->
<contact>JAMES WINGATE</contact>
<!-- string 11 -->
<contact_phone>1231231234</contact_phone>
<!-- string 80 -->
<contact_email>JAMES@DOMAIN.COM</contact_email>
<!-- 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/2024 09:34 AM]</comments>
<memberlist>
<!-- each code = string 20, but unlimited codes -->
<member>MBR001</member>
<member>MBR020</member>
<member>MBR039</member>
<member>LONGMBR058</member>
<member>MBR153</member>
<member>MBR172</member>
<member>MBR191</member>
<member>LONGMBR210</member>
<member>MBR324</member>
<member>MBR343</member>
<member>LONGMBR362</member>
<member>MBR077</member>
<member>MBR096</member>
<member>MBR115</member>
<member>LONGMBR134</member>
<member>MBR229</member>
<member>MBR248</member>
<member>MBR267</member>
<member>LONGMBR286</member>
<member>WBMWD</member>
</memberlist>
<!-- text unlimited -->
<map_url>https://somelink.doamin.com/linkinformationhereandthiscouldbealongstring</map_url>
<!-- text unlimited -->
<response_url>https://somelink.domain.com/eprlinkwithinformationthatislong</response_url>
<!-- text unlimited -->
<printable_text xml:space="preserve">
MBRCD01 00008Y USAS 03/28/24 22:42:02 A222310033-01A REMK NORM POLY LREQ
Ticket: A222310033 Rev: 01A Created: 03/28/24 09:37 User: CSR01 Chan: 200
Work Start: 04/02/24 17:01 Legal Start: 04/02/24 17:01 Expires: 04/25/24 23:59
Response required: Y Priority: 2
Re-Marks Required: ALL
Excavator Information
Company: ABC COMPANY
Co Addr: 123 MAIN ST
City : SOMEWHERE State: NY Zip: 12345
Created By: JIM SCHWILK Language: ENGLISH
Office Phone: 123-123-1234 x12345 SMS/Cell: 123-123-1234 Office Email: JIM@DOMAIN.COM
Site Contact: JAMES WINGATE
Site Phone: 123-123-1234 Site SMS/Cell: 123-123-1234
Site Email: JAMES@DOMAIN.COM
Excavation Area
State: CA County: LOS ANGELES Place: CARSON
Zip: 12345
Location: Address/Street: 12345 MAIN ST
: X/ST1: 1ST ST
:
: DOG IN YARD
Searchable Tags:
Job size: sq-feet: 123456 sq-miles: 0.123456
Delineated Method: WHITEPAINT
Work Type: REPAIR AND REPLACE SOMETHING
Work For : JOES RESTAURANT
Permit: 123456789ABC Job/Work order: AB123456
Project ID:
Project Name:
1 Year: N Boring: N Street/Sidewalk: Y Vacuum: Y Explosives: Y
Lat/Long
Center Generated (NAD83): 12.123456/-118.280453 33.844393/-118.279476
: 12.123456/-123.123456 12.123456/-123.123456 Excavator Provided:
Map link:
https://somelink.doamin.com/linkinformationhereandthiscouldbealongstring
Comments:
**REQUEST RE-MARKS** FROM ALL - WORK CONTINUING PER JIM SCHWILK--[CSR01 03/28/2024 09:34 AM]
Members:
MBR001 MBR020 MBR039 LONGMBR058 MBR153 MBR172
MBR191 LONGMBR210 MBR324 MBR343 LONGMBR362 MBR077
MBR096 MBR115 LONGMBR134 MBR229 MBR248 MBR267
LONGMBR286 WBMWD
(c) Copyright 2022 Underground Service Alert of Southern California.
All rights reserved.
</printable_text>
</tickets>
</NewDataSet>