Exhibit C - OneODA CRM Licensing System Integrations_posted.xlsx
XLSX spreadsheet 20 KB Posted
- Attached to
- RFQ One ODA Core CRM & Licensing
- Solicitation number
- S-DASOBO-00016964
- Issued by
- Oregon
About this file
This is a system integrations exhibit for the Oregon Department of Agriculture's OneODA Core Customer Relationship Management and Licensing system modernization project. The document outlines current and future system integration requirements, detailing how the new Dataverse-based OneODA system will replace or supplement legacy Oracle and FileMaker databases. The exhibit catalogs 15 distinct system integrations ranging from low to high complexity, including critical functions such as shadow table synchronization from Oracle to FileMaker, SFMS vendor payment processing, US Bank online payment collection, WMPI device tracking, CAFO designations, sanitarian records, OACS licensing data, fertilizer product tracking, mail list management, burn program accounting, pesticide investigation tools, and field-based miniPIPS applications. Integration timing varies between real-time, nightly batch processes, and as-needed updates, with most requiring ODBC connections to existing Oracle databases or FileMaker interfaces. The majority of integrations involve read-only access to license, address, and person data tables, with a smaller number requiring bidirectional data synchronization.
The technical architecture demonstrates a phased migration strategy where legacy FileMaker applications will initially maintain shadow tables from Oracle before transitioning to direct Dataverse connections in the OneODA environment. The SFMS integration operates as a one-way process requiring coordination within vendor approval windows, while US Bank online payments involve a nightly batch process with a 6 p.m. cutoff for receipt processing. Notable system dependencies include Jason's implementation of a new FileMaker-based WaM database to replace WMPI prior to the first phase cutover, and the exclusion of MySQL ODBC connections from the OneODA Core phase scope. The miniPIPS field application requires additional research and currently operates outside the standard System Access Menu, having been identified during process inventory meetings as an undocumented tool used by pesticide investigators on mobile devices for offline registration status access.
View the file
Other files
| File | Type | Posted |
|---|---|---|
| Exhibit D - OneODA CRM Licensing Oracle Tables_posted.xlsx | XLSX spreadsheet | |
| Exhibit B - CRM Licensing High-level Solution Requirements_posted.xlsx | XLSX spreadsheet | |
| Exhibit E - Price Quote_posted.docx | DOCX document | |
| S-DASOBO-00016964.RFQ CRM and Licensing.posted.docx | DOCX document | |
| Exhibit A - Scope of Work_posted.docx | DOCX document |
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
System Integrations
| Current | Future | ||||||||||
| Current System Integration Name | Complexity (H/M/L) | Timing | IT Developer | Current Source | Current Destination | OneODA Source | OneODA Destination | Data Elements or Logic Notes | Description | Notes | |
| FileMaker (FM) Shadow Tables from CRM & Licensing | Low | Nightly | ODA | Oracle | FileMaker Shadow Tables | Dataverse | FileMaker Shadow Tables | Select all from 10 Oracle tables. | FileMaker script runs to “select all” from each of 10 Oracle tables. The table.element names are similar in both Oracle and FM schemas. | ||
| SFMS | High: | ||||||||||
| Must test within SFMS team approval windows | Nightly | D365 Vendor | Oracle | SFMS / R*Stars | Dataverse | SFMS / R*Stars | PCA, object code, and payment amount | Written with Ruby. This is a one-way integration. We do not receive data from SFMS / R*Stars | |||
| US Bank online payments | Medium | Nightly | D365 Vendor | US Bank | Oracle | US Bank | Dataverse | 18 fields are in file: includes Receipt #, dollar amount | ODA collects online payments through US Bank-hosted portal. 6pm cutoff. Insert rows into table, then Financial Services applies those payments accordingly. | ||
| WMPI Device Counts | Low | Real-time | ODA | WMPI (FM) | Oracle Device Count table | WMPI (FM) | Dataverse Device Count table | License # and device count | Insert single row in Device Count table? |
| 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. | |||||||||
| CAFO Designations | Low | Real-time | ODA | CAFO (FM) | Oracle CAFO Size table | CAFO (FM) | Dataverse CAFO Size table | license #, designation | Insert single row in Oracle CAFO Size table? |
CAFO FM UI: user enters designation in a pop-up window, clicks “send to L2K” (or something similar), then FileMaker script runs.
| Food Safety Inspectors (Sanitarians) | Low | Nightly | ODA | BFS CO (FM) | Oracle Sanitarian table | BFS CO (FM) | Dataverse Sanitarian table | 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. |
| OACS | Low | Real-time | 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.
| Fertilizer Product Tracking (FIPS) | Low | Real-time | 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) |
| Mail List Master | Low | Real-time | 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: certificatiolns l2k.oda.state.or.us (oracle) Burn Program Manager Low Real-time 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)
| PDDL | Low | Real-time | ODA | Oracle | Read-only PDDL (FM) | Dataverse | Read-only PDDL (FM) | License, address, and person tables. | odbc: l2kprd (oracle) | |
| Pesticide Investigation (Pest Track) | Low | Real-time | 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)
| Certification (Pesticides) | Low | Real-time | 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) | |
| miniPIPS | Low | As needed | ODA | Oracle | miniPIPS (FM) | Dataverse | miniPIPS (FM) | Pesticide investigators use this FileMaker tool in the field to look at pesticide registration status when there is no or poor cell service. It downloads registration data and is coded to look like our public facing page and works on their iPads. | More research is needed. This database is not on the System Access Menu, so was uncovered during process inventory meetings. |
&"Helvetica Neue,Regular"&12&K000000&P
File details come from the government source that posted it. Updated .