Attachment I - OneODA CRM Licensing System Integrations S-DASOBO-00017788.xlsx
XLSX spreadsheet 49 KB Posted
- Solicitation number
- S-DASOBO-00017788
- Issued by
- Oregon
About this file
This is a system integrations spreadsheet for the OneODA Customer Relationship Management (CRM) Licensing Solution, prepared by the State of Oregon's Department of Administrative Services (DAS) on behalf of the Oregon Department of Agriculture (ODA). The document outlines the technical integrations required to connect the new CRM and licensing system with existing state applications and databases. The project encompasses design, configuration, extension services, integration development, data migration, testing, and implementation support for a comprehensive CRM and licensing platform. Key integrations identified include connections to financial systems, human resources platforms, document management systems, licensing databases, and external agency interfaces. The scope requires the contractor to facilitate seamless data flow between the new CRM system and multiple legacy and current state systems to ensure operational continuity and data integrity across ODA's business functions.
The spreadsheet details specific technical requirements, system dependencies, data mapping specifications, and integration priorities necessary for successful implementation of the CRM licensing solution. The document serves as a technical reference for bidders to understand the complexity and scope of integration work required, including system compatibility assessments, testing protocols, and migration strategies. This integration blueprint is critical for evaluating contractor capabilities and ensuring proposed solutions can meet ODA's technical and operational requirements for system connectivity, data accuracy, and business process alignment across the entire licensing and customer relationship management ecosystem.
View the file
Other files
Show all 12
On GovTribe
Work with this file
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
Cover Page
| State of Oregon |
| Department of Agriculture |
| OneODA Core (CRM & Licensing) |
| System Integration Inventory |
| Version: 1.1: added file upload used for Food Safety to easily cancel licenses for customers who have gone out of business. Added A/R Customer Number import. Added second US Bank integration row to represent outbound total amount and receipt # to US Bank when we send customer to their E-Pay portal for payment. |
| Date: July 27, 2026 |
| Tab | Description |
| Cover Page | Cover page of System Integration Inventory |
| System Integrations | Represents both traditional real-time system integrations within the ODA environment, system integrations with 3rd parties, and file uploads or downloads (usually .csv file formats). |
&A
&P of &N
System Integrations
| Current | Future | ||||||||||||
| OneODA Phase | Current System Integration Name | Complexity (H/M/L) | Volume of Data Per Instance | Timing | IT Developer | Current Source | Current Destination | OneODA Source | OneODA Destination | Data Elements or Logic Notes | Description | Notes | |
| Core | FileMaker (FM) Shadow Tables from CRM & Licensing | Low | High; All license records | Nightly | ODA | Oracle | FileMaker Shadow Tables | Dataverse | FileMaker Shadow Tables | Select all from roughly 10 Oracle tables. | FileMaker script runs to “select all” from each of roughly 10 Oracle tables. The table.element names are identical in both Oracle and FM schemas. | FileMaker databases that access the Shadow Tables: Animal Health, BFS CO, Hemp Track, miniPIPS, NIMS, Pest Track, WMPI | |
| Core | Plant Health and Animal Health Lab invoice details to CRM & Licensing | High | Medium(?) | On demand; monthly | D365 Vendor & ODA | n/a, see notes | n/a, see notes | Eight FileMaker databases | Dataverse | Invoice header and details to be exported from following FileMaker databases: PDDL, Seed Sampling, MAFIS, Weed Free Forage, Hops, Seed Tags, Seed Testing, AH_LabInvoices | Export from the eight FileMaker databases into a common export format to be imported by a single D365 system integration instance that can be run on demand by laboratory staff who are responsible for invoicing. | This export/import does not exist today because the invoice details for these tools do not exist in the current LSM system. Due to high volume and limited resources, we cannot create a duplicate data entry task for lab staff so the solution is to create this export and import process. It is okay if this is done in two steps for each process: 1) export data from FileMaker database; 2) initiate import of invoice header/details to new CRM & Licensing system. | |
| Core | SFMS | High: | |||||||||||
| Must test within SFMS team approval windows | 1000 records during peak of renewal period | Nightly | D365 Vendor | Oracle | SFMS / R*Stars | Dataverse | SFMS / R*Stars | customer #, invoice header, PCA, object code, and payment amount | Written with Ruby. This is a one-way integration. We do not receive data from SFMS / R*Stars | ||||
| Core | US Bank online payments | Low | one record each time | on demand | D365 Vendor | Orace | US Bank | Dataverse | US Bank | ODA sends the total amount and the LSM-generated receipt # to US Bank when the customer is redirected so that the payment can be recorded against a receipt #. | If we want to receive success / error messages, then we pay extra. We don't do that, so we have to wait until the next day to know if a payment was successful. Additionally, we do not want to set up with US Bank a transaction-level interface (i.e., receive data for each transaction individually) because we need to wait for Treasury's total amount for a batch after 1-2 business days anyway. | ||
| Core | US Bank online payments | High: | |||||||||||
| Must test within US Bank approval windows, Treasury is involved, too. | 1000 records during peak of renewal period | Morning | D365 Vendor | US Bank | Oracle | US Bank | Dataverse | 18 fields are in file: includes Receipt #, dollar amount, batch id, transaction date, and payor details. | ODA collects online payments through US Bank-hosted portal. 6pm cutoff. Insert rows into table, then Financial Services applies those payments to licenses/registrations or invoices. | We know when all payments were successful because our total amount = the US Bank batch total amount. | |||
| Core | A/R Customer Number Import | Low | Every row in A/R Customer Number FM database | Morning | D365 Vendor | A/R Customer Number | Oracle | A/R Customer Number | Oracle | Customer #, Name, Address, Status, Contact Information | Currently Financial Services can do an on-demand sync from A/R Customer Number for a single customer record. Financial Services treats A/R Customer Number as the source of truth of customer data. | ||
| Core | WMPI Device Counts (new name WaM) | Low | Low | On demand | ODA | WMPI (FM) | Oracle | WMPI (FM) | Dataverse | License # and device count | After device count is updated in WMPI, FileMaker script is run to update license row in Oracle device count table with new device count. | Jason is implementing a new WaM FileMaker database to replace WMPI before we cutover first phase of OneODA. This new database will only view the FileMaker Shadow Tables. | |
| Core | CAFO Designations | Low | Low | On demand | ODA | CAFO (FM) | Oracle | CAFO (FM) | Dataverse | license #, designation | CAFO FM UI: user enters designation in a pop-up window, clicks “send to L2K” (or something similar), then FileMaker script runs. | ||
| Core | Food Safety Inspectors (Sanitarians) | Low | Low | On demand | ODA | BFS CO (FM) | Oracle | BFS CO (FM) | Dataverse | zip code, city, name, license type | SQL statement that creates new records for new sanitarians and update records for existing sanitarians. The ODA webpage for search runs off that Oracle table. | Design phase: This is just a table that is shown on a website. It is not directly related to licensing tables. Need to determine where this data will be stored for the future. | |
| Core | OACS | Low | Read-only | On demand | ODA | Oracle | Read-only OACS (FM) | Dataverse | Read-only OACS (FM) | License, address, and person tables. | odbc: L2KPRD l2k.oda.state.or.us (oracle) |
| Note: odbc connections to MySQL are outside the scope of OneODA Core phase. | Design phase: Consider updating this lookup to use the Shadow Tables rather than a direct lookup to Oracle. | |||||||||||
| Core | Fertilizer Product Tracking (FIPS) | Low | Read-only | On demand | ODA | Oracle | Read-only Fertilizer Product Tracking (FM) | Dataverse | Read-only Fertilizer Product Tracking (FM) | Uses a direct ODBC connection to link to license, person and address data from Oracle and pair it with their entity data in reports | Note: odbc connections to MySQL are outside the scope of OneODA Core phase. |
| odbc: l2kpd (oracle) | Design phase: Consider updating this lookup to use the Shadow Tables rather than a direct lookup to Oracle. | |||||||||||
| Core | Mail List Master | Low | Read-only | On demand | ODA | Oracle | Read-only Mail List Master (FM) | Dataverse | Read-only Mail List Master (FM) | License, address, and person tables. | Trade & Economic Development. Read-only from Oracle pulling license, address, person tables. |
odbc: l2kprd l2k.oda.state.or.us (oracle)
| odbc: certifications l2k.oda.state.or.us (oracle) | Design phase: Consider updating this lookup to use the Shadow Tables rather than a direct lookup to Oracle. | ||||||||||||
| Core | Burn Program Manager | Low | Read-only | On demand | ODA | Oracle | Read-only Burn Program Manager (FM) | Dataverse | Read-only Burn Program Manager (FM) | Uses accounting tables from Oracle to populate a grower payment report showing which invoices have been paid or not | Note: odbc connections to MySQL are outside the scope of OneODA Core phase. | Uses accounting tables from Oracle to populate a grower payment report showing which invoices have been paid or not |
arinvoice -> This table represents the invoice sent misc_allocations -> This table represents the amounts allocated to items in that invoice or cash paid odbc: l2kprd, l2k.oda.state.or.us (oracle)
| Core | PDDL | Low | Read-only | On demand | ODA | Oracle | Read-only PDDL (FM) | Dataverse | Read-only PDDL (FM) | License, address, and person tables. | odbc: l2kprd (oracle) | Design phase: Consider updating this lookup to use the Shadow Tables rather than a direct lookup to Oracle. | |
| Core | Pesticide Investigation (Pest Track) | Low | Read-only | On demand | ODA | Oracle | Read-only Pest Track (FM) | Dataverse | Read-only Pest Track (FM) | Uses a view set up in Oracle to view licensee information: license, address, and person tables. | odbc: pesttrackview l2k.oda.state.or.us (oracle) | ||
| odbc: pestTrackViewTest l2k.oda.state.or.us (oracle) | Design phase: Pest Track access Oracle view directly and FileMaker Shadow Tables. If it does not need the information in real-time, then this view could access the Shadow Tables. If so, then we may be able to update this before migration. | ||||||||||||
| Core | Certification (Pesticides) | Low | Read-only | On demand | ODA | Oracle | Read-only Certification (FM) | Dataverse | Read-only Certification (FM) | Uses a direct ODBC connection to view license, person, and user field license tables. | odbc: l2kprd (oracle) | Design phase: Consider updating this lookup to use the Shadow Tables rather than a direct lookup to Oracle. | |
| Core | Food Safety Out of Business (OB) Deletes | Low | Low | On demand | D365 Vendor | BFS CO (FM) | Oracle | BFS CO (FM) | Dataverse | Customer name, license #, and unique customer ID (programs sometimes refer to this as applicant ID) | Current State: .xlsx file emailed to as-licensing@oda.oregon.gov periodically (ideally weekly). Licensing then uploads the file using "Mass Change to Delete" option in LSM. |
Future State: allow Food Safety program staff to do this upload.
&"Helvetica Neue,Regular"&12&K000000&P image1.png
File details come from the government source that posted it. Updated .