JMJ CLOUD
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us

Our Blog

Talk to Your ERP with Alexa and ORDS

9/28/2017

0 Comments

 

Introduction

Picture
Oracle REST Data Services (ORDS) is a no cost option of the Oracle Database. It allows you to securely expose data in your Oracle Database via REST web services. ORDS is available on-premise as well as in cloud offerings such as Exadata Express and Database Schema Service. We have used ORDS to integrate on-premise and Cloud based ERP systems for years. It is a proven method of integrating with an Oracle database via REST. Sounds pretty useful right? Well, that is just the start, there are many other use cases for ORDS that reach into other technologies such as IoT (think receiving feeds from connected devices) and mobile (providing REST end points for native mobile Apps). In this post, I am going to talk about AI and how ORDS can be used to build an Alexa service based on data in your ERP (e.g. Oracle e-Business Suite and even ERP Cloud).

An Alexa Interaction

Before going too much further, it is worth spending some time to understand how voice services work. The diagram below illustrates a typical voice based interaction between an Alexa device and a back-end service provided using ORDS.
Picture
  • Your Alexa device is always listening for one of its wake words (Alexa, Echo or Computer).
  • Once Alexa hears its wake word, it knows it needs to pay attention to any subsequent speech.
  • It passes this speech to the Alexa Voice Service (AVS) in the cloud.
  • Amazon converts the speech to text and attempts to determine the skill that was requested based on the invocation name. In the above example, ‘Ask My Database’.
  • Once it recognizes the skill, it forms as structured JSON request and sends it to our ORDS REST end-point.
  • The proxy receives the request over https and then passes it on to ORDS via http.
  • ORDS determines which handler to carry out the request based on the URL called by Amazon.
  • The handler performs PL/SQL logic then returns a JSON response to Amazon.
  • Amazon turns the response into speech and instructs the Alexa device to speak the response back to the end user.
  • All this happens in less than a second. 

Anatomy of an Alexa Skill

I am sure most of you are aware what Alexa is, but what is an Alexa Custom Skill? Let's take a look at the Anatomy of an Alexa Custom Skill:
Picture
Invocation Name
This is phrase that Alexa uses to identify your skill as opposed to any other skill. The various components of a Custom Skill are also packaged into an Invocation when you define the skill in the Amazon Alexa developer console.

Intent Schema
A set of Intents (the Intent Schema). Represent the actions your user can perform with your skill
In our example, we have one which is "getDatabaseSessions". The intent defined here is passed into our web service and we will use this to determine what code to run to fulfill the intent.

Custom Slots
Slots can be thought of as a way to inject variables into the intent. In our example, we are including the session status that we want to inquire on. These will be called out in the JSON sent to our web service

Utterances
Utterances are what the end user will make in order to execute an intent. An utterance ties together the Intent and the Custom Slots (or variables). Alexa identifies the intent based on the utterances defined
Utterances are also used to provide variations of phrases users may use to invoke your intent.

 Other Considerations for an Alexa Skill

Picture
​Now we understand the concepts, what other considerations do we need to make when configuring our custom skill?

Testing
Once you have a working service, you will need to perform formal testing. Amazon provides several options including:
  • On-Line Simulator. Type your sample utterance into a testing tool provided by Amazon and see the JSON that passes from Amazon to your ORDS service and the response your ORDS service passes back to Amazon.
  • Unit testing on your Alexa device linked to your Amazon developer account.
  • Share your services with unit testers or other developers via developer account sharing.
  • Invite up to 2,000 users to beta test your skill using the Beta testing feature. 
​Security
Before publishing your service you will need to consider security for your REST service which, by necessity is exposed to the internet.
  • Secure your ORDS REST service by verifying the unique Skill Id that that is passed to you in the JSON payload.
  • For added security, use Account Linking which utilizes OAUTH to authenticate against your ORDS REST service.

Publishing
  • Submit your skill for certification and publication.
  • If you do not want your skill published, another option is to keep your skill in Beta testing mode where you can invite up to 2,000 users to use your skill. You can manage users access and revoke access when necessary. This may be a good option for corporate users who don't want their skill published to the general public.

Secure ORDS Web Service End Point
One of the last steps in configuring your skill is to provide the URL for your ORDS REST service. For this, your service must be running SSL:
  • During development, you can upload a self-signed certificate.
  • From Beta Testing on, you must use a certificate from a trusted authority.
  • Tip: Lets Encrypt provides free SSL certificates which work fine with AWS.

It's Time to Talk ORDS

Picture
Amazon requires a POST service to POST the payload it generates from the Alexa request to. This means creating an ORDS module with a template and a POST handler. Read this previous POST which talks about how ORDS services are structured to find out more.

At a high level, this is what your handler will need to do:
  • Parse the JSON payload sent by Amazon
  • Verify caller using the application id contained in the payload
  • Determine the intent and custom slot values
  • Run PL/SQL logic to fulfill the user's intent
  • Build a JSON response to send back to Amazon

Voice Enable Your ERP

With a history of delivering solutions for Oracle EBS and Cloud ERP, the team at JMJ Cloud have the ability to bring together the worlds of AI and Voice Services with the world of ERP. It's the possibilities of using these technologies together that excite me the most. Think about it, if you are using Oracle EBS, you have an Oracle database already. If you are using APEX and ORDS to extend EBS (if not then you should be) then the chances are you have all the architecture in place to host a REST service that can be used for an Alexa Skill. You now have the opportunity to introduce your business to the wealth of information stored in your ERP via a new medium, voice. 

"Alexa ask EBS for my daily financial briefing"
Think of the CFO who wants to get a morning briefing on the state the company's financials. He simply asks Alexa, which in turn calls an ORDS service to pull the data from EBS and respond with:
  • Your total revenue for yesterday was 1.2 million US dollars
  • Your current cash on hand is 500,000 US dollars
  • Your current open receivables are 200,000 US dollars
  • <this list is just about endless>

Don't Stop There

We are really just getting started, why not voice enable all of the things? Using the APEX_WEB_SERVICE API, you can call out to both REST and SOAP web services. Now you have access to any data that is exposed via a web service (which is just about all of the data). This allows you to build Alexa Skills that not only pull data from EBS but also consolidate it with data from other sources.

"Alexa ask HQ for my daily corporate briefing"
  • Current headcount is 12,000 (from your ERP Cloud HR system)
  • There are 200,000 US Dollars in open quotes (from your Salesforce system)
  • There were 2 reported accidents yesterday (from your in-house accident reporting system)
  • The US trade deficit with China grew by 2% yesterday (from a data.gov web service)
  • XYZ Corp was mentioned 2,000 times yesterday (taken from the company twitter feed)
  • <again, the list is just about endless>

Conclusion

As you can see, the potential is fantastic. Just as importantly, the technology is here, it works and if you are running an Oracle database (on-premise or in the cloud) you won't have to pay a dime extra to fulfill this potential.

​It's just another reason to consider APEX and ORDS, both no cost options of the Oracle database.

​This Blog Post was developed from a presentation I did at KSCOPE and Open World in 2017. You can download the full presentation here.
0 Comments

ERP Cloud PaaS Extensions using APEX

9/11/2017

 
​As the popularity of Oracle ERP Cloud grows, it's natural that an increasing number of customers have system requirements outside the core functionality of the application suite.   Oracle suggests meeting these needs through the use of PaaS (platform-as-a-service) solutions,  building custom pages that can be embedded into ERP Cloud.    This is known as PaaS for SaaS.

When a customer purchases PaaS for SaaS, they are provisioned with an APEX Schema-as-a-Service instance and Java Cloud Service (JCS) instance matched to each of their ERP Cloud environments.   APEX holds the data model, and JCS is used to build applications.
Picture

​​That's the theory.   In practice, there is a choice as to whether Java Cloud Service is used.   If you have strong ADF and Java skills available in your organization then it may make sense to build the applications and screens using JCS.   However, data would still be stored in APEX database tables, and APEX functionality would be used for scheduling background jobs.  

A strong alternative is to consolidate all development in Oracle APEX, including the applications, forms, web service calls and scheduling.   This eliminates the need for JCS - in fact you can extend ERP Cloud with any cloud APEX instance on its own, eg. Exadata Express.  A significant plus is that you avoid any patching downtime for JCS - in a production system this is important.
Picture

​The Page Integration functionality allows us to the embed custom APEX pages within ERP Cloud.   Pages can be referenced from an icon on the Springboard or via the Navigator menu.   To an end user this is seamless -  it appears as if they are still within the application suite.   The result looks something like this:
​
​
Picture

​Page security is managed by a JSON Web Token (JWT) passed to the APEX page along with the user details - so there is no need to manage an additional set of user passwords.  The JWT token is then validated by the page to prevent unauthorized data access.   For more information on how this is implemented, read our prior blog here: oracle-cloud-erp-and-apex-paas-mashup.html

So how could your organization use one of these embedded pages and put this into practice?  Here's a few ideas:
​
​Integration Portal

Oracle provides various methods of integrating data into ERP Cloud - including two spreadsheet-based tools, ADFDi and File Based Data Import (FBDi).   Oracle recommends using FBDi for larger volumes of data and so this is commonly used for interfaces such as Journal Import to GL,  Bank Statements to Cash Management or Cost Transactions to PPM.   

The FBDi upload process typically involves three steps - transferring a zip file into UCM using File Import and Export, submitting the ESS job Load Interface File for Import to stage the transactions and finally running an import program such as Journal Import to complete the process.  

An APEX page can be used to streamline the process for an end-user - they can upload the CSV file from their computer and the page will zip it and co-ordinate the transfer and import processes.
​
Picture

Project Team Management

The PPM Project Parties screen can be unwieldy when projects have many team members, and assigning or end-dating multiple team members can take some time.   As PPM provides a REST API to automate this task, an embedded APEX page can be used to make this task quicker using a simple shuttle.

In the example below, a Shuttle is used to select employees for assignment as Project Administrators.   When the Submit button is pressed,  APEX calls the REST API to apply the changes.
​
Picture
  
Role and Privilege Analysis

Oracle provides an extract of all the user, roles and privileges through the User and Role Security Report.   This generates a large set of files that can be analyzed in detail to see who has access to the various duties and privileges within ERP Cloud.   This can help answer audit questions such as:

 - Which users have access to Functional Setup Manager?
 - Which roles provide access to the File Import and Export screens?

The report data can be extracted on demand to Oracle APEX and data analyzed using a simple UI.   In the example below, the page is being used to identify users who can maintain suppliers and enter invoices.
​
Picture

​Salesforce Opportunities

APEX can call external web services so why not use this feature to display data from your other cloud applications?   In the example below,  Sales Opportunities are synchronized to Exadata Express and displayed to the end-user within ERP Cloud.
​
Picture

​Summary

Hopefully these examples have given you a few ideas about how you could get even more out of your investment in ERP Cloud.

JMJ has designed and installed multiple APEX PaaS extensions, so please get in touch if you're interested in learning more.

Code your own PL/SQL HMAC-SHA1 function

9/2/2017

4 Comments

 
Important Note: Since this article was published - Oracle include the DBMS_CRYPTO package with Exadata Express - so you may not need this.  However, if you have an older, non-enterprise edition of the Oracle database this may still be useful to you!

At JMJ we use Oracle's Exadata Express cloud service to integrate and pull together all the data from the various applications we use - Atlassian Service Desk, Quickbooks etc. Exadata Express comes pre-built with APEX and ORDS and out of the box provides great functionality for building applications, calling third party SOAP and REST services and hosting REST web services. The APEX_WEB_SERVICE package provides a simple API for accessing external services from sites such as ERP Cloud and Quickbooks Online.


However, there is always a catch - and with Exadata Express it comes when trying to call a web service that uses OAUTH 1.0 and requires the HMAC-SHA1 signature algorithm. If that sounds like an alien language to you - you're not alone. A brief read through the HMAC-SHA1 standard located at https://tools.ietf.org/html/rfc2104 shows us that this isn't straightforward.

Put simply, sites requiring OAUTH 1.0 such as Quickbooks need your web service call (URL, method and parameters) to be encoded using a key to generate a signature. This signature is then sent over HTTPS along with your web service call so that the service provider knows that the message was sent by you.
Picture

​Many of the sites using OAUTH recommend using a standard library to calculate the signature, and this is sound advice. The Enterprise Editions of the Oracle Database come with the DBMS_CRYPTO built-in package. This contains the following handy MAC function:
DBMS_CRYPTO.mac(UTL_I18N.string_to_raw(l_oauth_base_string, 'AL32UTF8') 
               ,DBMS_CRYPTO.hmac_sh1 
               ,UTL_I18N.string_to_raw(l_oauth_key, 'AL32UTF8'));

​​All good - except this package is not available to us in Exadata Express!  That makes our task a little harder.

So where did we look next? Following the recommendation to re-use a pre-built library, there are several Java HMAC-SHA1 examples on Github. Here's an example of how Java can calculate an HMAC-SHA1 signature:
 publicstatic String calculateRFC2104HMAC(String data, String key)
 throwsSignatureException, NoSuchAlgorithmException, InvalidKeyException
 {
   SecretKeySpecsigningKey = new SecretKeySpec(key.getBytes()
                                             , HMAC_SHA1_ALGORITHM);
   Macmac = Mac.getInstance(HMAC_SHA1_ALGORITHM);
   mac.init(signingKey);
   returntoHexString(mac.doFinal(data.getBytes()));
 }

​This gave us hope - perhaps we could create a Java Stored Procedure and call this from PL/SQL in Exadata Express to generate our signature? Only - hopes dashed again - it turns out Exadata Express doesn't allow you to create Java Stored Procedures.  The only remaining option was to look at building our own procedure to do this task.

First we needed to be able to generate an SHA1 hash. Here's where we were fortunate as there are open-source packages available to use.   One is found here:
https://github.com/vadimonus/plsql-hash. The SHA1 function was placed into a package in Exadata Express:
CREATE OR REPLACE package jmj_hash_util_pkg AS
  FUNCTION sha1(p_buffer in raw) RETURN sha1_checksum_raw;
END jmj_hash_util_pkg;

Next we reviewed the HMAC-SHA1 standards document, and checked this against pseudocode and test cases on Wikipedia.  Here's the resulting HMAC_SHA1 function we built to generate the signatures:
FUNCTION hmac_sha1(p_message IN VARCHAR2, p_key IN VARCHAR2)
  RETURN VARCHAR2 IS

     -- Binaries
     v_ohsh   RAW(32767);
     v_kpad   RAW(32767);
     v_ipad   RAW(32767);
     v_opad   RAW(32767);
     v_ihsh   RAW(32767);
     v_skey   RAW(32767);
     v_key_in VARCHAR2(1000);
     v_key    RAW(32767);

     -- Result as hex in the form of text
     v_return VARCHAR2(2000);

  BEGIN

    v_key_in := p_key;

    -- Keys longer than 64 bytes must first be hashed
    IF utl_raw.length(sys.utl_raw.cast_to_raw(v_key_in)) > 64 THEN
      v_key := jmj_hash_util_pkg.sha1(sys.utl_raw.cast_to_raw(v_key_in));
    ELSE
      v_key := sys.utl_raw.cast_to_raw(v_key_in);
    END IF;

    -- Pad the key with zeroes to 64 bytes (128 characters)
    v_kpad := jmj_util_pkg.pad_key(64,'00');

    -- XOR with 64 characters of 0x36 HEX
    v_ipad := jmj_util_pkg.xor(v_ipad,'36');

    -- XOR with 64 characters of 0x5c HEX
    v_opad := jmj_util_pkg.xor(v_kpad,'5c');

    -- Inner HMAC1 hash with inner key and message
    SELECT jmj_hash_util_pkg.sha1(v_ipad || nvl(sys.utl_raw.cast_to_raw(p_message),'') )
    INTO   v_ihsh FROM dual;  

    -- Outer HMAC1 hash with inner hash
    SELECT jmj_hash_util_pkg.sha1(v_opad || v_ihsh )
    INTO   v_ohsh FROM dual;

    -- Convert back to HEX for return value
    SELECT lower(rawtohex(v_ohsh))
    INTO   v_return FROM dual;

    RETURN v_return;

  END;

​But was it right? We tested this against the Wikipedia sample use cases here:
https://en.wikipedia.org/wiki/Hash-based_message_authentication_code. ​
SELECT jmj_hmac_pkg.hmac_sha1('','') actual 
,      'fbdb1d1b18aa6c08324b7d64b71fb76370690e1d' answer
FROM   dual
UNION ALL
SELECT jmj_hmac_pkg.hmac_sha1('The quick brown fox jumps over the lazy dog','key') actual
,      'de7c9b85b8b78aa6bc8a7a36f70a90701c9db4d9' answer
FROM   dual
UNION ALL
SELECT jmj_hmac_pkg.hmac_sha1('ABC','iDho0Xmt0PT4J3Ke1BCMbg4voR58CvOJ5miMShLbYAgFegwtk2kJiJM2TOiJR1CzWyL3QUC1VTBdS6IU')
  ,   'f5191e56e2aa2c73f8be0c717478c47b6dcae8c5' -- LONG KEY TEST
FROM dual

ACTUAL                                   ANSWER                                  
---------------------------------------- ----------------------------------------
fbdb1d1b18aa6c08324b7d64b71fb76370690e1d fbdb1d1b18aa6c08324b7d64b71fb76370690e1d
de7c9b85b8b78aa6bc8a7a36f70a90701c9db4d9 de7c9b85b8b78aa6bc8a7a36f70a90701c9db4d9
f5191e56e2aa2c73f8be0c717478c47b6dcae8c5 f5191e56e2aa2c73f8be0c717478c47b6dcae8c5

​All good - everything matched. We were able to apply this signature to our web service call and successfully connect to Quickbooks. Problem solved, even without DBMS_CRYPTO, and importantly without the need for an Enterprise Edition of the database!

We hope you find this helpful if like us you're a big fan of Exadata Express and the endless possibilities it provides for integration. We're always interested in integration work so please reach out if you need a hand with getting your Apex integration up and running!
4 Comments

    RSS Feed

    Popular Posts

    - Exadata Express Integration
    - Cloud ERP & APEX Mashup
    - Modernizing EBS with APEX
    - Build APEX Responsibly
    - APEX 18.1 Wins the Cloud
    - ORDS What & Why?

    Categories

    All
    APEX
    AWS
    Fusion Cloud ERP
    ORDS
    PaaS
    RAD
    REST

    Archives

    August 2019
    July 2019
    June 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    September 2017
    August 2017
    July 2017
    June 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016

Company

About
Contact
Blog
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us