ORCA Lookup Data Tables
| Submitted | Nov. 16, 2019 |
MuckRock users can file, duplicate, track, and share public records requests like this one. Learn more.
Communications
From: Kevin Wallace
To Whom It May Concern:
Pursuant to the Washington Public Records Act, I hereby request the following records:
The contents of all ORCA "Lookup Data" tables (as described in SEA-04566 RFCS Ad Hoc Report Operations Manual, section B.6) in Crystal Reports Explorer or equivalent system. Tables that contain sensitive financial information or information pertaining to individual cardholders (e.g. AV_ACCOUNT_LOOKUP, AV_CARDHOLDER_LOOKUP) can be excluded. This should include the following tables, as well as any others named using the same "_LOOKUP" convention:
AV_ABORT_REASON_LOOKUP
AV_ADJUSTMENT_REASON_LOOKUP
AV_BLOCK_REASON_LOOKUP
AV_CARD_PLACE_LOCATION
AV_CARD_STATUS_LOOKUP
AV_CARD_TYPE_LOOKUP
AV_CLAIM_STATUS_LOOKUP
AV_DEVICE_STATUS_LOOKUP
AV_EVENT_TYPE_LOOKUP
AV_EXCEPTION_CODE_LOOKUP
AV_FEE_TYPE_LOOKUP
AV_INSTITUTION_LOOKUP
AV_INST_CARD_GROUP_LOOKUP
AV_LOGOFF_METHOD_LOOKUP
AV_LOGON_FAILURE_REASON_LOOKUP
AV_MODE_LOOKUP
AV_NON-FARECARD_EVENT_TYPE_LOOKUP
AV_ORDER_STATUS_LOOKUP
AV_ORDER_TYPE_LOOKUP
AV_PARTICIPANT_LOOKUP
AV_PARTICIPANT_ROLE_LOOKUP
AV_PASSENGER_TYPE_LOOKUP
AV_PASS_VEHICLE_LOOKUP
AV_PAYMENT_METHOD_LOOKUP
AV_PRODUCT_CATEGORY_LOOKUP
AV_PRODUCT_TYPE_LOOKUP
AV_PROGRAM_CATEGORY_LOOKUP
AV_PROGRAM_TYPE_LOOKUP
AV_RECREDIT_REASON_LOOKUP
AV_REFUND_REASON_LOOKUP
AV_RETURN_REASON_LOOKUP
AV_TRANSACTION_DESCRIPTION_LOOKUP
AV_VANPOOL_LOOKUP
AV_VEHICLE_TYPE_LOOKUP
The requested documents will be made available to the general public, and this request is not being made for commercial purposes.
In the event that there are fees, I would be grateful if you would inform me of the total charges in advance of fulfilling my request. I would prefer the request filled electronically, by e-mail attachment if available or CD-ROM if not.
Thank you in advance for your anticipated cooperation in this matter. I look forward to receiving your response to this request within 5 business days, as the statute requires.
Sincerely,
Kevin Wallace
From: Muckrock Staff
To Whom It May Concern:
I wanted to follow up on the following Washington Public Records Act request, copied below, and originally submitted on Nov. 16, 2019. Please let me know when I can expect to receive a response.
Thanks for your help, and let me know if further clarification is needed.
From: Central Puget Sound Regional Transit Authority
This will acknowledge receipt of your request for contents of all ORCA "Lookup Data" tables (as described in SEA-04566 RFCS Ad Hoc Report Operations Manual, section B.6) in Crystal Reports Explorer or equivalent system. Tables that contain sensitive financial information or information pertaining to individual cardholders (e.g. AV_ACCOUNT_LOOKUP, AV_CARDHOLDER_LOOKUP) can be excluded. This should include the following tables, as well as any others named using the same "_LOOKUP" convention:
AV_ABORT_REASON_LOOKUP
AV_ADJUSTMENT_REASON_LOOKUP
AV_BLOCK_REASON_LOOKUP
AV_CARD_PLACE_LOCATION
AV_CARD_STATUS_LOOKUP
AV_CARD_TYPE_LOOKUP
AV_CLAIM_STATUS_LOOKUP
AV_DEVICE_STATUS_LOOKUP
AV_EVENT_TYPE_LOOKUP
AV_EXCEPTION_CODE_LOOKUP
AV_FEE_TYPE_LOOKUP
AV_INSTITUTION_LOOKUP
AV_INST_CARD_GROUP_LOOKUP
AV_LOGOFF_METHOD_LOOKUP
AV_LOGON_FAILURE_REASON_LOOKUP
AV_MODE_LOOKUP
AV_NON-FARECARD_EVENT_TYPE_LOOKUP
AV_ORDER_STATUS_LOOKUP
AV_ORDER_TYPE_LOOKUP
AV_PARTICIPANT_LOOKUP
AV_PARTICIPANT_ROLE_LOOKUP
AV_PASSENGER_TYPE_LOOKUP
AV_PASS_VEHICLE_LOOKUP
AV_PAYMENT_METHOD_LOOKUP
AV_PRODUCT_CATEGORY_LOOKUP
AV_PRODUCT_TYPE_LOOKUP
AV_PROGRAM_CATEGORY_LOOKUP
AV_PROGRAM_TYPE_LOOKUP
AV_RECREDIT_REASON_LOOKUP
AV_REFUND_REASON_LOOKUP
AV_RETURN_REASON_LOOKUP
AV_TRANSACTION_DESCRIPTION_LOOKUP
AV_VANPOOL_LOOKUP
AV_VEHICLE_TYPE_LOOKUP
We received your request on November 18, 2019.
Given our staff's workload, holiday schedules and the number of requests we are currently handling, we estimate it will take us an additional 5-6 weeks to identify and gather responsive records. If we are able to gather the records sooner or if we need additional time, we will let you know.
Sincerely,
Q'Deene Nagasawa
Q'Deene Nagasawa
Paralegal | Public Records Officer
Legal Department
Sound Transit
401 South Jackson
Seattle, WA 98104
(206) 689-4924
Fax: (206) 398-5222
Connect with us
facebook.com/SoundTransit<http://www.facebook.com/SoundTransit>
twitter.com/SoundTransit<http://www.twitter.com/SoundTransit>
[cid:image001.png@01CFB081.D1850820]
-
image001
From: Central Puget Sound Regional Transit Authority
This is to provide you with a status of your request. We have a 1st installment ready for release. This 1st installment contains the following 28 responsive documents:
[cid:image002.png@01D5C24E.BD9A95C0]
Please send $4.10 to cover copy and mail charges. Make your check payable to Sound Transit, 401 South Jackson, Seattle, WA 98104 and reference PD19-613 on your check. Once we receive payment we will copy the records to a DVD and mail it to you. If you would rather review the records here at Sound Transit, please contact me to schedule an appointment during normal business hours.
We continue to work on your request. Given our staff's current workload, holiday schedules and the number of requests we are currently handling, we estimate we will need an additional 4-5 weeks to gather the next installment of records. If we are able to provide the records sooner or if we need more time, we will let you know.
However, if we do not receive payment for the 1st installment, we will conclude you no longer want access to the records and will close your request file.
Sincerely,
Q'Deene Nagasawa
Q'Deene Nagasawa
Paralegal | Public Records Officer
Legal Department
Sound Transit
401 South Jackson
Seattle, WA 98104
(206) 689-4924
Fax: (206) 398-5222
Connect with us
facebook.com/SoundTransit<http://www.facebook.com/SoundTransit>
twitter.com/SoundTransit<http://www.twitter.com/SoundTransit>
[cid:image001.png@01CFB081.D1850820]
From: Central Puget Sound Regional Transit Authority
To clarify if we do not receive payment by February 3, 2020, we will conclude you no longer want access to the records and will close your request file.
Sincerely,
Q'Deene Nagasawa
Q'Deene Nagasawa
Paralegal | Public Records Officer
Legal Department
Sound Transit
401 South Jackson
Seattle, WA 98104
(206) 689-4924
Fax: (206) 398-5222
Connect with us
facebook.com/SoundTransit<http://www.facebook.com/SoundTransit>
twitter.com/SoundTransit<http://www.twitter.com/SoundTransit>
[cid:image001.png@01CFB081.D1850820]
From: Kevin Wallace
To Whom It May Concern:
Please find enclosed a check for $4.10 to satisfy the fee associated with the attached public records request.
Thank you.
Central Puget Sound Regional Transit Authority
Washington Public Records Act Office
401 South Jackson Street
Seattle, WA 98104
From: Central Puget Sound Regional Transit Authority
Thank you for your email requesting status of your request. I have confirmed payment has been received and the 1st installment will be going out to you in the mail tomorrow.
We are working on the final installment which is estimated to completed in the next 1-2 weeks. We will contact you when the last installment is ready for release.
Sincerely,
Q’Deene Nagasawa
Q’Deene Nagasawa
Paralegal | Public Records Officer
Legal Department
Sound Transit
401 South Jackson
Seattle, WA 98104
(206) 689-4924
Fax: (206) 398-5222
Connect with us
facebook.com/SoundTransit<http://www.facebook.com/SoundTransit>
twitter.com/SoundTransit<http://www.twitter.com/SoundTransit>
[cid:image001.png@01CFB081.D1850820]
-
image001
From: Central Puget Sound Regional Transit Authority
A copy of documents responsive to the request.
There are too many files to display on this communication. See all files
From: Central Puget Sound Regional Transit Authority
A copy of documents responsive to the request.
There are too many files to display on this communication. See all files
From: Kevin Wallace
Hi,
I just wanted to note that I sent payment (check via Muckrock/USPS) on 1/3. If it hasn't arrived by 2/3, please let me know -- I'm still interested in the records, and can arrange for an another means of payment.
Thanks!
Kevin
From: Central Puget Sound Regional Transit Authority
We received payment on January 22, 2020 and mailed a DVD to you on January 24, 2020. Please let me know if you have not received it.
Sincerely,
Q’Deene Nagasawa
Q’Deene Nagasawa
Paralegal | Public Records Officer
Legal Department
Sound Transit
401 South Jackson
Seattle, WA 98104
(206) 689-4924
Fax: (206) 398-5222
Connect with us
facebook.com/SoundTransit<http://www.facebook.com/SoundTransit>
twitter.com/SoundTransit<http://www.twitter.com/SoundTransit>
[cid:image001.png@01CFB081.D1850820]
-
image001
From: Kevin Wallace
Thanks for the update! I haven't received it yet, but it also hasn't been very long since January 24. I'll let you know if I still haven't received it in a few weeks.
Kevin
From: Kevin Wallace
An update -- the DVD arrived! Confirming receipt of:
AV_ABORT_REASON_LOOKUP.pdf
AV_ADJUSTMENT_REASON_LOOKUP.pdf
AV_BLOCK_REASON_LOOKUP.pdf
AV_CARD_PLACE_LOCATION.pdf
AV_CARD_STATUS_LOOKUP.pdf
AV_CARD_TYPE_LOOKUP.pdf
AV_CLAIM_STATUS_LOOKUP.pdf
AV_DEVICE_STATUS_LOOKUP.pdf
AV_EVENT_TYPE_LOOKUP.pdf
AV_EXCEPTION_CODE_LOOKUP.pdf
AV_LOGOFF_METHOD_LOOKUP.pdf
AV_LOGON_FAILURE_REASON_LOOKUP.pdf
AV_MODE_LOOKUP.pdf
AV_NON-FARECARD_EVENT_TYPE_LOOKUP.pdf
AV_ORDER_STATUS_LOOKUP.pdf
AV_ORDER_TYPE_LOOKUP.pdf
AV_PARTICIPANT_LOOKUP.pdf
AV_PASSENGER_TYPE_LOOKUP.pdf
AV_PASS_VEHICLE_LOOKUP.pdf
AV_PAYMENT_METHOD_LOOKUP.pdf
AV_PRODUCT_CATEGORY_LOOKUP.pdf
AV_PROGRAM_CATEGORY_LOOKUP.pdf
AV_PROGRAM_TYPE_LOOKUP.pdf
AV_RECREDIT_REASON_LOOKUP.pdf
AV_REFUND_REASON_LOOKUP.pdf
AV_RETURN_REASON_LOOKUP.pdf
AV_TRANSACTION_DESCRIPTION_LOOKUP.pdf
AV_VANPOOL_LOOKUP.pdf
Most of these appear to contain a single table each, matching the file name. However, I'd like to confirm my understanding of a couple of them which appear to be different:
AV_PARTICIPANT_LOOKUP.pdf appears to be the results of a query that joins AV_PARTICIPANT_LOOKUP and AV_PARTICIPANT_ROLE_LOOKUP
AV_PRODUCT_CATEGORY_LOOKUP.pdf appears to be the results of a query that joins AV_PRODUCT_CATEGORY_LOOKUP and AV_PRODUCT_TYPE_LOOKUP (and AV_PARTICIPANT_LOOKUP again)
If this is the case, I'm happy to accept these synthesized queries in lieu of the originally-requested data for these tables, as long as the join process didn't remove anything (i.e. as long as this isn't excluding any rows that exist in one of the joined tables but aren't referenced in the other table, which can happen with some joins). Otherwise, I'd like to reiterate that my request was for the full contents of the tables, not for a joined view of a subset of their data.
I'm still missing the following tables:
AV_FEE_TYPE_LOOKUP
AV_INSTITUTION_LOOKUP
AV_INST_CARD_GROUP_LOOKUP
AV_VEHICLE_TYPE_LOOKUP
The data I've received so far is very helpful -- thank you!
Kevin
From: Central Puget Sound Regional Transit Authority
Thank you for your email. We are working on the next installment and I have forwarded your questions in your email to our ORCA staff. We should have a response to you by the end of the week.
Sincerely,
Q’Deene Nagasawa
Q’Deene Nagasawa
Paralegal | Public Records Officer
Legal Department
Sound Transit
401 South Jackson
Seattle, WA 98104
(206) 689-4924
Fax: (206) 398-5222
Connect with us
facebook.com/SoundTransit<http://www.facebook.com/SoundTransit>
twitter.com/SoundTransit<http://www.twitter.com/SoundTransit>
[cid:image001.png@01CFB081.D1850820]
-
image001
From: Central Puget Sound Regional Transit Authority
This will respond to your email of February 10, 2020 and provide you with the final installment to your request.
Attached are the following 8 documents:
· AV_INSTITUTION_LOOKUP
· AV_INST_CARD_GROUP_LOOKUP
· AV_VEHICLE_TYPE_LOOKUP
· SOURCE_WEBSITE_LOOKUP
· ACCOUNT_LOOKUP
· CALL_CATEGORY_LOOKUP
· CARDHOLDER_LOOKUP_PII
· CARDHOLDER_LOOKUP
We were unable to locate AV_FEE_TYPE_LOOKUP table.
Please note that personally identifying information in the CARDHOLDER _LOOKUP_PII and CARDHOLDER_LOOKUP tables has been redacted, as that information is exempt from public disclosure under RCW 42.56.330(5). The column fields remain and have not been redacted.
With respect to your specific questions, staff has provided the following responses:
Q1. AV_PARTICIPANT_LOOKUP.pdf appears to be the results of a query that joins AV_PARTICIPANT_LOOKUP and AV_PARTICIPANT_ROLE_LOOKUP
· We did not join any of the tables/data and the reports were not modified by Sound Transit. The reporting tool was created by Vix, so we are unaware if Vix joined the data. The reporting tool only gives us access to the AV_PARTICIPANT_LOOKUP table. The AV_PARTICIPANT_ROLE_LOOKUP was not an option for us to run.
Q2. AV_PRODUCT_CATEGORY_LOOKUP.pdf appears to be the results of a query that joins AV_PRODUCT_CATEGORY_LOOKUP and AV_PRODUCT_TYPE_LOOKUP (and AV_PARTICIPANT_LOOKUP again)
· Same response as Q1, that was the only report available to us and we did not make any modification.
This completes our response to your request and we are closing this request file.
Sincerely,
Q’Deene Nagasawa
Q’Deene Nagasawa
Paralegal | Public Records Officer
Legal Department
Sound Transit
401 South Jackson
Seattle, WA 98104
(206) 689-4924
Fax: (206) 398-5222
Connect with us
facebook.com/SoundTransit<http://www.facebook.com/SoundTransit>
twitter.com/SoundTransit<http://www.twitter.com/SoundTransit>
[cid:image001.png@01CFB081.D1850820]
-
image001
From: Kevin Wallace
Confirming receipt of the remaining 8 documents. Thank you for the explanation re: AV_PARTICIPANT_LOOKUP and AV_PRODUCT_CATEGORY_LOOKUP, and for your help with this request!
Kevin
Files
pages
There are too many files to display on the request page. See all files .