Reaching the APEX – Mashing Up Geospatial, Environmental and Billing Data in Real Time
By Dermot Murray, VP of Ideation at Inoapps
A customer came to us recently with an interesting challenge.
“As part of our day to day operational activities, we need to be able to capture the temperature at each of our key sites at the time of a transaction, and then feed that into our billing processes. The price we charge the customer is based on the local temperature at the time of the sale.”
The product that the customer sells is directly impacted by the air temperature at the point of transaction. They needed a solution that would allow them to:
- Accurately collate, store and transact using the temperature reading from across a wide variety of locations, on a near real-time basis
- Use local air temperature data to derive billing calculations in Oracle Cloud ERP - the solution they use to process the transaction
Technology challenge #1 - Capturing the Temperature
The customer had started looking at a variety of technologies to support the capture of the data it needed:
- Providing their operators with handheld devices so they could key in the temperature every time they processed an order
- Having a centralised console at each of the business' key sites to allow manual data capture on a regular basis
- Setting up temperature sensors at each key location, and using IOT based tools to collect and collate the data from the sensors
However, upon evaluation each of these were deemed inelegant, high cost and prone to error, so they asked the Inoapps team for help.
The Solution
The first thing our team established was that each of the company sites could be linked to a specific latitude and longtitude. On that basis, a quick search identified a plethora of free data sets that could provide a wide range of environmental measures such as temperature, pressure and rainfall, provisioned as an API.
Using the latest APEX_WEB_SERVICE API capabilities in Oracle Application Express (APEX), we were able to produce a “mash-up” of company site locations, combined with environmental data from one of the many free data sets. However, the data sets that were available through the APIs did not always provide an exact match to the required location. In such cases, we used Oracle Spatial capabilities (now available for free as part of the Oracle Autonomous Database) to derive the nearest temperature reading to each of the locations.
The result was a near real-time visualisation of temperature data across multiple company locations, with all temperature readings recorded in the client's Oracle APEX database for further processing.
Technology Challenge #2 – Using temperature readings to calculate billings
The customer planned to use Oracle Cloud ERP to process the receivable transactions associated with every order, but the value of each order needed to be derived from the prevailing temperature at the location, at the time of the transaction.
Once again, we turned to Oracle APEX for the rapid delivery of a fully integrated solution. Key components included:
- Utilisation of REST web services from Oracle Cloud ERP to extract all of the key “Master Data” from Oracle Cloud ERP and enable billing calculations
- Oracle Spatial and Graph Functions to calculate distances between locations to establish the nearest weather station to each company site
- Oracle Database code built to retrieve temperature data for each location on demand. Historic temperatures logged at regular intervals by a batch job
- Oracle APEX to produce reports and provide a user-friendly web interface to access and view the data
- Google Maps Platform Javascript API to create an interactive map visualisation that integrates the site and temperature data
- Billing calculations for each transaction, derived from the environmental data that had been collated, were then posted back into the Oracle Cloud ERP Receivables ledger, via the API framework
The Best Part?
Using the combined power of the Oracle Autonomous Database and Oracle Application Express, we were able to deliver a prototype solution to the customer in just a few hours. This prototype can then be scaled into a production solution, using the same Cloud platform.
The Outcome?
Real-time environmental data integrated into Oracle Cloud ERP to derive complex billing rules and generate ledger postings. All delivered in a low code, Cloud enabled, secure and fully integrated delivery platform.
Another compelling example of the power the Oracle APEX platform has to extend the capabilities of Oracle Cloud to meet specific sector requirements, or support differentiated business processes that deliver competitive advantage.
Contact us today to find out how our team can help you!