Introduction

The Requirement
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.
A Little Bit about Twillio

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.
Architecture
Outbound Flow
- 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.
- A scheduled Outbound Queue processing concurrent program runs every hour (during the day). This program performs the following actions:
- Calls the Twillio SMS API to Queue the message with Twillio.
- Calls the Twillio message status API to confirm previously queued messages were actually delivered.
- Logs Twilio’s unique message id to the outbound queue table.
- Logs any errors (e.g. invalid mobile number) to the outbound queue table
- Sends an email with failed deliveries to all interested parties.
- When Twillio receives a request, it will:
- 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.
- Perform basic validations on the recipients’ number and return an error if applicable.
- 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.
Response / Inbound Flow
- The inbound flow starts with an employee responding to an SMS. Possible reasons for responding to an SMS are:
- Request to opt out of future SMS messages.
- Response to a question as part of a ‘conversation’ with one of your systems.
- When Twillio receives the response, it does the following:
- 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.
- 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).
- The ORDS POST service adds the response to a queue table sitting in Exadata Express.
- 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.
- 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.
Considerations
Data Cleansing and Managing Errors

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.
Handling SMS Conversation

Using Different From Numbers
One other thing to note is that the recipient is not going to recognize the number the SMS is being sent from. The first 15-20 characters of your SMS needs to clearly indicate who it is from and its urgency. For example, start your message with ‘From: ABC INC…’
When to Send SMS Message

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

Conclusion
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!