​Twillio has become the gold standard for automating the sending and receiving of SMS messages. They have a well-documented and easy to use API, with features designed to make sending and managing SMS messages easy. In our latest engagement, JMJ Cloud is using Twillio to help our customer provide important and timely information (from Oracle e-Business Suite) to their employees via SMS. In this post I will review the requirement and the architecture we put in place to make it all happen.

​Our customer needs to notify employees when certifications they hold are approaching their expiration date. Details of the employee’s certifications are maintained in Oracle e-Business Suite (EBS) on their HR record. The nature of their work dictates that these employees cannot perform their job without a current certificate. The work takes employees all over the world and being able to reach them when they are out of the office made SMS a great choice.
 
The customer also has plans to send a number of other SMS messages from EBS and other internal systems. Building a generic outbound and inbound queuing process was necessary to facilitate re-use of the solution by these other sources.

​If you have used Lyft, Airbnb, or Netflix, you have probably used Twilio. In fact, Twilio powers communications for over 40,000 businesses around the world. Simply put, Twilio is a developer platform for communications. Software teams use Twilio APIs to add capabilities like voice, video, and messaging to their applications.

There are a couple of things you need to setup in Twillio before you can start sending SMS messages:

• Signup for an account.
• Purchase a number. This is the outbound phone number that will appear on message recipients’ phones. It is also the number Twillio is watching to handle responses to SMS messages. Numbers range in price from $1 / month to thousands for shortened numbers. You do get to choose the number you want to use from a list of available numbers. The process is a little like choosing a cell phone number. You may end up needing multiple numbers based on your requirements, especially if you need to handle SMS responses differently for each requirement.
  
• Configure a Web Hook to receive responses. For each number you buy, you can configure a URL which Twillio will POST to whenever someone responds to a text message sent from that number. This web hook will post the number the response was sent from as well as the response message itself. As you will see from the architecture diagram below, we chose to configure an ORDS REST service to receive these responses.

1. The outbound flow starts with a feeder system (in our case Oracle HR) adding an SMS to the outbound queue table. Each feeder system is categorized by a ‘channel’ column on the table. The table also stores the SMS message, the to and from number, key identifying information for the recipient (name, internal id etc.) and status columns.
2. A scheduled Outbound Queue processing concurrent program runs every hour (during the day). This program performs the following actions:
   1. Calls the Twillio SMS API to Queue the message with Twillio.
   2. Calls the Twillio message status API to confirm previously queued messages were actually delivered.
   3. Logs Twilio’s unique message id to the outbound queue table.
   4. Logs any errors (e.g. invalid mobile number) to the outbound queue table
   5. Sends an email with failed deliveries to all interested parties.
3. When Twillio receives a request, it will:
   1. Check to see if the number the SMS is being sent to has opted out of receiving SMS messages from your sending number. If it has then it will return an error message and not send the SMS.
   2. Perform basic validations on the recipients’ number and return an error if applicable.
   3. If there are no errors, it will queue the message to be sent.

Note: Twillio does not send the SMS synchronously. Just because you get a success message back from the send API, does not mean the message was delivered. You need to call a second message status API to confirm successful delivery.

1. The inbound flow starts with an employee responding to an SMS. Possible reasons for responding to an SMS are:
   1. Request to opt out of future SMS messages.
   2. Response to a question as part of a ‘conversation’ with one of your systems.
2. When Twillio receives the response, it does the following:
   1. If the user responds with an opt out message e.g. ‘STOP’ then it will tag that number as blocked and prevent you from sending further messages to that number until they opt back in.
   2. Formats the message and information about the responder into a JSON document and posts the response to the web hook you configured (ORDS POST Service).
3. The ORDS POST service adds the response to a queue table sitting in Exadata Express.
4. A scheduled inbound queue Concurrent Program runs to pick up new responses from Exadata Express. It does this by calling another ORDS web service on Exadata Express which returns the responses. The Concurrent Program then copies the data to a local table in the EBS database.
5. A final concurrent program runs which handles responses received in the local table. This includes activities such as updating the employees HR record based on their response. This program also sends out an email digest of all received SMS responses to the managers of the employees who have responded.

Monitoring
A. An APEX dashboard provides administrators the ability to monitor both the inbound and outbound queues and respond to errors etc.

This section focuses on some key considerations when implementing an SMS solution.

​Chances are, the phone numbers you have stored in your ERP system are not being used by an automated solution such as this. When a human sees a number like +11234567890, 1234567890 or (123)456-7890 they can figure out the number and the country code.

A computer system, however needs a more exact format to follow. It is also likely you will not have mobile numbers for everyone. Twillio requires that you send the country code with each SMS message.  All of this means you will have to work at collecting mobile numbers for everyone and introduce a standard for storing these numbers that includes the international dialing code (e.g. +1 or +44). Twillio can help with the data cleansing aspect by providing APIs that you can call to validate a number.
 
As well as an initial data cleansing effort, you will need to provide mechanisms to make managers aware when SMS messages fail to get sent due to invalid numbers. A business process also needs to be put in place to correct or add numbers to keep the process flowing smoothly.

​Receiving timely information can make a huge impact. Having said that, SMS messages are a far more intrusive form of communication than email. You should not use it as the go-to method of communication (especially with employees). Use it sparingly and only when it really adds value.

​CAUTION:  do not schedule outbound messages to be sent in the middle of the night. Be careful when scheduling outbound messages, that you don’t send them in the middle of the night. The only thing more annoying than a text from your boss is one that is sent at midnight when your boss is sound asleep!

​​Opting out of receiving SMS messages is an important part of the end-to-end flow. Twillio handles opting out very well but you need to provide instructions to the recipient on how they can opt out (and opt back in). You will also need to make managers aware of those people who have opted out.

Sending automated SMS messages is by no means a new thing. The technology and services that are available today, however, are making it far easier to do. Used correctly, SMS can provide the information your employees or customers need right when they need it.
 
Yet again, the RAD stack provides many of the technologies you need to integrate modern and innovative technology solutions such as Twillio into your existing ERP. One more reason to install APEX and ORDS today!

A number of our clients have Oracle E-Business Suite (EBS) with some limited functionality available outside of their network. One of these clients challenged us with providing their employees with a more accessible, richer and more intuitive user experience than EBS alone can provide. In this post I will discuss an approach we employed recently to utilize Exadata Express to handle document of record acknowledgements in the Cloud whilst keeping data synchronized with EBS on-premise. This approach employs both APEX and ORDS on the Exadata Express SaaS platform.

The customer has EBS on-premise and uses the Human Resources module to manage their employees. They had a requirement for employees to review and acknowledge the receipt of certain key documents (e.g. Employee Handbook). The acknowledgements needed to be stored with the employee’s official HR record so that they can be referenced by the HR department.
 
The customer has a diverse set of employees including salaried as well as hourly workers. All employees have access to Oracle EBS but not all of them have a corporate (or even private) email address and even fewer have access to VPN. The initial notification asking an employee to acknowledge a new document had to be made via an Oracle Work list (through the Oracle Workflow module) notification as well as email (if available). All these factors made it important that we deliver an easy to access, easy to use and mobile friendly experience that is available outside of the firewall.

The approach we took was to utilize Document of Record standard functionality available in EBS to generate a document of record for each employee and document type. We developed a Concurrent Program to perform the following steps on a nightly basis:

1. Identify new employees that have not acknowledged certain documents of record.
2. Generate a document of record for the employee and document type (using Oracle Document of Record API).
3. Generate a unique token for each employee and document of record.
4. Call an ORDS REST service (hosted on Exadata Express) to POST the document of record to a table in Exadata Express.
5. Launch a workflow to notify the employee that there is a document he or she need to review and acknowledge. The notification contains a link to an APEX application in Exadata Express. The link includes the unique token created in step 3.

 
When the employee clicks the link, they are taken to the APEX App on Exadata Express, which validates the token and shows the employee the document that needs to be reviewed. As well as the document, the user is also presented with an Acknowledge button. Once clicked, the document is marked as acknowledged in a table in Exadata Express.
 
Some documents are displayed in-line in HTML format, while others are shown as an attached document, housed in an Amazon S3 bucket. This is configurable using an APEX administration App.
 
In addition to the End User Application, an Admin APEX App was created to maintain the text of each document type as well as links to the PDF documents on S3. This application allows an administrator to configure what the user sees for a particular document type. This includes the ability to have a variable number of sections for each document type and allow the document to be displayed in three different languages.
 
A second concurrent program was developed which performed the following steps:

1. Calls a 2nd ORDS REST service running on Exadata Express. This service fetches a list of documents that have been acknowledged since the last time the process ran.
2. For each record returned, update the related EBS document of record to indicate the date the document was accepted.

 
Regarding reporting, the HR department can report on which employees had accepted which document, directly in EBS. This is possible because we utilized standard Document of Record functionality in EBS.

Some of the benefits of this approach include:

1. The load of thousands of employees viewing and acknowledging various different documents and PDF files is off-loaded from the main transactional ERP database to the Cloud.
2. The APEX application is easily accessible outside the firewall without exposing sensitive HR data sitting in the EBS database.
3. APEX provides for a much more user (and mobile) friendly platform for delivering documents to employees, many of whom use computers very infrequently.
4.  Using APEX, we can capture more details about the user’s interaction with the document. For example, we can determine which users viewed the document but did not acknowledge it.

​Hopefully, the approach described in this document gives you some ideas on how you can utilize Cloud Services such as Exadata Express to extend the reach of your on-premise Oracle EBS environment. This gives you the ability to provide your users modern scalable solutions that may not be possible with just on-premise EBS.

First of all, let me say what a great conference KSCOPE is and what a great group of people attend it. The APEX and ORDS communities show up in force and you won't find a group of professionals that are more open to share their hard-earned knowledge. ​The purpose of this post is to share some of the things we found interesting that may also be interesting to you. I am sure we have missed a lot of great stuff, but until the recorded sessions are released you will have to make do with our thoughts on the sessions we were able to attend.

The Oracle Development Team introduced us to the Oracle RAD Stack.

From apex.oracle.com: "The Oracle RAD stack is an inclusive technology stack based on three core components: Oracle REST Data Services (ORDS), Oracle APEX, and Oracle Database".

There is no new technology here but creating a logo and a compelling story for these technologies  will help in attracting new developers and customers to APEX and ORDS. It will also give the existing community a catchy logo to identify with.

Automation is everywhere in technology these days and it was certainly there in full force at KSCOPE this year. Three areas that stood out for us were in testing automation,  continuous integration (CICD) and Low Code.
​
Testing
In testing, one open source tool that stood out was the APEX Test Automation Framework (ATAF). ATAF queries APEX Metadata for information about the Apps you are testing and allows you to build test scripts that generate Selenium code. The tool then allows you to deploy that selenium code to Cloud Based Selenium Grids which will run the tests and record results. Having an automated testing solution allows you to greatly increase the number of tests you can run and removes much of the pain and cost from regression testing.

Continuous Integration / Delivery
There was more than one presentation on Jenkins this year. Jenkins is an open source tool that offers a simple way to set up a continuous integration or continuous delivery environment for almost any combination of languages and source code repositories using pipelines, as well as automating other routine development tasks. While Jenkins doesn’t eliminate the need to create scripts for individual steps, it does give you a faster and more robust way to integrate your entire chain of build, test, and deployment tools than you can easily build yourself.

​Low Code
Oracle again demonstrated Application Blueprints. Application Blueprints are APEX's Low Code solution. Building from last year, this technology now allows you to create simple applications directly from your data model. This makes Quick SQL in conjunction with Application Blueprints a powerful combination. Low Code is certainly an area of focus for the APEX team and we expect to see more in this area in upcoming releases.

APEX 18.1 deprecates jQuery mobile but introduces a number of new mobile features to the Universal Theme. These include swipe gestures (care of hammer.js), mobile tabbed menus, sticky buttons, reflow reports and column selector reports. Along with the responsive nature of Universal Theme, these provide the basics for building some pretty impressive mobile Apps. These features were demonstrated by Shakeeb Raman from the APEX development team with his demo movie App which was stunning!

If you factor in the emergence of browser technologies that enable Progressive Web Applications (PWAs) we are fast approaching the time when we don't need to rely on containerization to deploy mobile web apps.

The current version of the Oracle XE database is a free to use (with limited functionality) version of the Oracle Database. The last version released was based on 11g R2. It seems that the long-awaited successor to this version is now imminent. While the CPU, memory and storage constraints (1 CPU, 2GB RAM and 12GB Disk) do not change much, the 18C version will come with all the bells and whistles that the Enterprise Edition has. This in addition to having the ability to create up to 4 pluggable databases, makes 18C a game changer. There was even a hint that an XE version would be part of the annual release cycle. With this release, RAD stack developers can finally replicate locally the same environment that they are deploying to in production.

We believe this new release of Oracle XE, which is free to use (even in production), opens the door for some new and creative open source products/solutions.

We learned that the Cloud based Exadata Express offering will eventually move to the Oracle Autonomous Database. This should help maintain Exadata Express as a compelling Cloud based RAD stack environment.

While I am on the subject of the Oracle Autonomous Database, Mike Hichwa from Oracle shared the following interesting factlet: To test the automated indexing functionality of the autonomous database, Oracle dropped all of the indexes on a NetSuite ERP environment. They then let the autonomous database autogenerate indexes and performance returned to within 2% of the manually tuned environment. Given this is a pre-production release of the database, we can expect even better in the production version.

Attending conferences takes time and money. KSCOPE 18 was time and money well spent. We are already looking forward to KSCOPE 2019 in Seattle!​
​
On a related note, an ODTUG membership gives you access to a lot of the sessions. So, if you are not a member of ODTUG then why not?