​Microsoft Office has an overwhelming share of the Cloud based corporate email market and a significant share of the Cloud based corporate office suite market. It is the Go-To collaboration technology for most large companies. Being able to interact with MS Office 365 is becoming essential for many business workflows. The corner stone for integrating with Office 365 is Microsoft’s Graph API. The MS Graph API provides a unified REST interface that you can use to programmatically interact with the many services contained in the Microsoft Office 365 suite. Having a unified interface makes working with the MS Graph API much easier for developers. Once you get the hang of one API, you are halfway there to understanding all of them.

Tip: Check out the MS Graph Explorer to try out the MS Graph APIs.

​Before you dive into the code, you will need to make yourself familiar with some aspects of Microsoft Azure Active Directory, specifically, ‘App registrations’. An App registration is set of configurations that control things like which users/domains can login to your App and what permissions your application has (e.g., read or write files to SharePoint).
 
For example, here is a list of Applications Registrations we have at JMJ Cloud.

​API permissions
API permissions allow users of the ‘App registration’ to perform certain actions with Office 365 services. Here is an example of ‘API permissions’ which are used by one of our internal APEX applications to automatically process inbound email.

These types of permissions (Application permissions) are global to your whole Office 365 tenant. For the above example, this means you can read and write email for any user.
 
The below permissions are utilized for an App registration that is used purely for Authenticating users into our internal applications. These are ‘Delegated permissions’ and are specific to each logged in user. This means that you can only perform actions in Office 365 on behalf of the logged in user.

‘Application (client) ID’ and a ‘Client secret’
When you create an App registration, Microsoft generates a ‘Application (client) ID’ and you can then generate one or more ‘Client secrets’. The combination of a ‘Application (client) ID’ and a ‘Client secret’ is used by APEX to get a token which is required to call the MS Graph APIs. APEX has you covered here because it automatically keeps track of tokens (and their expirations) and fetches a new one when it needs to.
 
Securing ‘App registrations’
Once someone has the ‘Application (client) ID’ and a ‘Client secret’, they can effectively perform any actions allowed in the ‘API permissions’ section. It is a good idea to create separate App registrations for each Office 365 service you want to access (SharePoint, Email, Sign-On etc.). This way if the credentials for one App registration were to get into the wrong hands, the damage is limited to that one service.
 
Once you have created the appropriate App registrations in Azure AD, you can get started configuring APEX to integrate with Office 365.

This is perhaps the easiest way to get started with APEX and the MS Graph APIs. It also offers arguably the biggest bang for the buck. These days, most users do not expect to have to remember different passwords for different applications. Being able to have users login to your APEX applications using their Office 365 credentials adds credibility and reduces friction to your application. The ability to do this in APEX is made possible by APEX Social Sign-on. Social Sign-on allows you do declaratively configure an APEX authentication scheme to use any number of OAuth2 Providers (including Active Directory). Also, don’t forget that APEX allows you to have multiple Authentication schemes. So, if it makes sense, you can offer users a choice of signing-in with their  Office 365 credentials as well as their credentials from other providers such as Google, Facebook and Okta.

​APEX Authentication Scheme
Here is a screen shot of an example APEX Authentication Scheme used for authenticating users using their Office 365 Credentials.

APEX Credential Store  / Web Credentials
A key part of the above Authentication scheme is the ‘Credential Store’ entry. BTW, ‘Credential Store’ was renamed to ‘Web Credentials’ in APEX 20.2 so you may see me use them interchangeably in this post. In the context of Office 365, Web Credentials store the Client ID and Client Secret that APEX uses to link your Authentication scheme to a specific Azure AD ‘App registration’.

Authorization
When using a third-party authentication provider, you need to be extra careful. If you only create an Authentication scheme, you are essentially giving anyone with an Office 365 account the ability to sign-in to your application. You need to also include APEX Authorization scheme(s) which makes(s) sure users are allowed to access your application and also define what they are allowed to do if they are allowed in.

Example Use Cases

• Uploading and downloading file Attachments to and from SharePoint in your APEX Application. In this scenario you control the entire interaction with SharePoint from APEX.
• Storing Oracle e-Business Suite attachments (instead of bloating your database by storing them in the database). In this scenario you intercept EBS and store files in SharePoint instead of the FND_LOBS table. Files appear to the user as an attachment and they are taken to SharePoint when they want to download a file.

 
Azure AD App registration
You will need to start by creating an App registration in Azure AD and make sure you assign the ‘Sites.ReadWrite.All’ permission. This permission will allow you full access to traverse the folder structure, create folders, upload, and download files etc.
​
APEX Web Credentials
As with sign-on, you will need to create Web Credentials that provide APEX the information it needs to access the ‘App registration’ and authenticate you so you can call the Graph APIs.

SharePoint and OneDrive Graph APIs
The MS Graph APIs you use for SharePoint and OneDrive are pretty much the same. There is an extra step in SharePoint to get from the SharePoint Site to the Documents folder (Drive) for the SharePoint Site. Once you have the Drive ID, the steps for traversing folders, uploading, and downloading files are the same for both. MS Graph APIs allow you to do the following:

• List Files and Folders
• Create and Delete Folders
• Upload and Delete Files
• Get a File

 
Calling MS Graph APIs from APEX
Interacting with SharePoint & OneDrive files from APEX is trickier than setting up single sign-on, but not much.
 
You have a couple of options; one is to use APEX ‘REST Data Sources’ and the other is to call the apex_web_service PL/SQL APIs. Creating a ‘REST Data Source’ is the best option, especially for data that is flat (e.g., a list of folders or a list of emails). One key advantage is that ‘REST Data Sources’ can be used as data sources declaratively throughout APEX (IRs, IGs, Charts, LOVs etc.).
 
If the payload from Microsoft contains embedded arrays, then you can use apex_web_service to get the JSON then parse the JSON and traverse the arrays yourself.
 
The function below shows an example of using apex_web_service to call an MS Graph API. The function fetches a file from SharePoint into a BLOB in PL/SQL. Developers do not need to worry about fetching tokens etc. from Microsoft. APEX uses the referenced Credential Store (identified by ‘p_credential_static_id’) to automatically get a token from Microsoft (if it needs to). The token is then automatically passed to the Graph API.

​At the time of writing, Microsoft limits file uploads to a maximum of 60MB in size. Also, if you are uploading files larger than 4MB, then you need to upload your file in chunks.

Email is still the most familiar user interface for most users. According to statista.com , there are over 300 billion emails sent every day. Because of this, allowing users to interact with your system using email can be very powerful. Being able to process email automatically, requires that you have programmatic access to the email inbox. MS Graph provides all the APIs you need to read and write email from Office 365.
 
Example Use Cases

• APEX ticketing system - Allow users to create and update tickets in your ticketing system by sending email to an email account dedicated to processing tickets. Use APEX 20.2 Automations functionality to poll the Inbox every five minutes and create/update the appropriate tickets.
  
• Information requests - Allow users to send an email to an email account dedicated to handling requests for information from your ERP system. Use APEX automations to poll the account and fetch requested data from your ERP and respond with an email containing the requested information.

 
Note: Performing actions in your system based on the receipt of an email should be done with great care. You must verify the email sender, check that they are an authorized user of your application and that they have access to perform the requested action.
 
Azure AD App registration
You will need to create an App registration in Azure AD and make sure you assign the ‘Mail.Read’ permission. This permission will allow you to read email from any inbox.
 
APEX Web Credentials
The APEX Web Credential will look very similar to the one you created for SharePoint and OneDrive, except you will need to use the client and client secret from the App registration you created for handling email.
 
Email Graph APIs
There are MS Graph APIs that allow you to do the following with email:

• Get a list of emails
• See who sent the email, who was on the CC list etc.
• Get the full body of an email
• Get email attachments
  
• Follow email ‘conversations’
• Send email

There are a few things you should know before you get started with MS Graph APIs.
 
Make Use of API Query Parameters
There are several query parameters that can be used to influence the response you get from MS Graph APIs. Here is a screen shot showing a call to the GET email API where we are passing a number of these parameters.

• $select allows you to just select the fields you need. By selecting just, the fields you need, you can dramatically reduce the size of the response payload and the time it will take you to parse it.
• $filter allows you to filter the data set that is returned. In this case we are fetching only emails received since ‘2020-09-01T11:32:44Z’. This can also dramatically reduce the size of the response payload returned.
• @orderby allows you to pre-sort the data returned by the API.
• @top allows you to limit the response to the top X records. This is another mechanism you can use to limit the size of the response.

 
Utilize Delta Queries
Delta Query enables applications to discover newly created, updated, or deleted entities without performing a full read of the target resource with every request. This can be especially useful for things like polling for new emails created since the last time you checked.
 
Understand Limits and Throttling
Microsoft does impose certain limits and will throttle access if you exceed them.
 
For example, at time of writing, the Outlook mail API limits are as follows:

• 10,000 API requests in a 10-minute period
• 4 concurrent requests
• 15 megabytes (MB) upload (PATCH, POST, PUT) in a 30 second period

​I realize I have covered a lot in this post and to be honest, I have barely scratched the surface of what the MS Graph APIs are capable of. There are many other Office 365 services that you can integrate with including Calendar, To Do, OneNote, Users & Groups, Planner and more. As the APEX development team continues to add native REST integration capabilities, integrating with well thought out APIs like MS Graph continues to become easier and easier. A world of data and cloud services are available to you, many of which can be consumed by APEX in a completely declarative manner.

The Pre-Production Release of APEX 20.2 is now available on apex.oracle.com!   There are lots of new and useful features that we can't wait to start using.   In this article, we'll have a look at the new REST Data Source Synchronization feature will be super handy when using APEX to extend ERP Cloud.   We gave this a runout on apex.oracle.com, using it to pull down ERP Cloud Organizations into an APEX table on a set schedule.

We configured the REST Data Source as below, using APEX Web Credentials to connect to the Vision Demo ZRNS environment, configuring the organizations HCM rest service to return the following fields for all active departments:  OrganizationId, Name and Active .

Query String variables are used to configure the selection criteria and data returned.

Before attempting synchronization, we test the web service call to make sure it is functioning correctly.

We can check this for accuracy against the source data in ERP Cloud.   

Note, Vision is shown  below with the Redwood theme; we're looking forward to trying out the new APEX 20.2 Redwood Light Theme for our ERP Cloud Integrations.

An important step is to identify the Primary Key of the returned data; this is used if you're Merging the data every time it is synchronized.

We clicked on the new Manage Synchronization link to start the configuration process.

Here we choose a target table.   

In the next step, we have to choose the Synchronization Type:
​

• Append - rows are added to the end of the table.
• Merge - rows are merged using the primary key defined earlier.
• Replace - the table is truncated (or deleted) before rows are inserted.

The Synchronization Schedule is expressed using same syntax as DBMS_SCHEDULER, so tables can be synchronized every 15 mins, 2 hours, day etc.  

All done - now we can save the Schedule and we're told when this will run.   

​We can also run it On Demand using the Save and Run button.

Looking at the new APEX_REST_SOURCE_SYNC package, we should  also be able to start synchronization programmatically using a PL/SQL procedure call.

Once Synchronization has run, the log entries give us rowcounts, message sizes, and execution times.

Data is now available in our custom table.   We used the APEX 20.2 Declarative Cards layout to show the data.

And just for fun, this is what it looks like with the Redwood theme:

We can also use the new APEX 20.2 Excel downloads to get our newly synchronized data:

Behind the scenes, the JMJ_ERP_CLOUD_DEPARTMENTS table contains the columns we requested, along with a couple that APEX uses to keep track of synchronization.

This was our first look at this new feature in APEX 20.2 and in about 15 minutes we were able to create a new workspace, ERP Cloud data source, and synchronize data into a custom table.   

​We didn't have to write a single line of code to call web services, map returned data into tables, or record web service calls and responses.  

This is fantastic functionality, and we look forward it using it in our work using APEX to extend and integrate with ERP Cloud.    We hope ERP Cloud follows the industry and continues to migrate old SOAP services to REST.  This will make integrations like this much simpler and help customers gain the maximum benefit from their ERP Cloud Investment.

This is yet another example of expectations being exceeded by the APEX Development Team!   Thanks to all those who worked hard behind the scenes to make this happen.

​With the evolution of ERP Cloud and Oracle Cloud Infrastructure (OCI), we are starting to see ERP Cloud utilize OCI features. This is a positive move and it will allow ERP Cloud customers to gain more value from their ERP Cloud investment. One area where we have seen this partnership blossom is with ERP Cloud BI Cloud Connector (BICC) and OCI Object Storage. In this post I will cover how you can leverage more from this integration using OCI Events and Notifications.

​Oracle BI Cloud Connector (BICC) provides a robust mechanism for performing bulk exports of data from ERP Cloud. Exported CSV files can be directed to Universal Content Management (UCM) (contained within the ERP Cloud environment), or to an OCI Object Storage Bucket. BICC exports are scheduled through the ERP Cloud Enterprise Scheduler Service (ESS). For those of you familiar with e-Business Suite, this is like the Concurrent Manager. So, how do we know when a scheduled job has finished so we can go fetch the file? This is the problem we will address in this post.
 
Although the example below describes a scenario using ERP Cloud, this pattern can be re-used for any scenario where you need to perform an action when a file is created in an OCI bucket, but you don’t know when the file will be created.

Oracle Cloud Infrastructure includes two integration tools that can help us with this problem. These are Events and Notifications.
 
Events Service
Events fire when certain things happen on your OCI infrastructure. In our case we want to launch an event whenever a file is created in a specific OCI Object Storage bucket. An event on its own, however, is not very useful. We need the Event to do something and this is where Notifications come in.
 
Notifications Service
A Notification can perform one of the following operations:

• Email
• Function (Cloud Based Function e.g. Java)
• HTTPS (Custom URL)
• PagerDuty
• Slack

 
The key notification for us is ‘HTTPS (Custom URL)’. This notification type is further described in section 3 below. When this type of Notification fires, it posts a JSON payload to the HTTPS Endpoint that you define. The content of the payload is specific to the OCI service that causes the Event to fire. In our case, it will contain information about the file that was created.

1 - File from ERP Cloud
In our example this file is coming from BI Cloud Connector (BICC) on ERP Cloud. As I mentioned earlier, BICC allows you to perform high volume extracts of data from ERP Cloud to either local Universal Content Manager (UCM) or an OCI Object Store Bucket. We will be using OCI Object store as this gives us more options and easier access to the files once created. BICC allows you to either call a web service to launch an extract job or to use ERP Cloud to schedule the extract. In view of this, files could appear in our OCI Object Store Bucket at any time. We need to know when the file is created so we can go and fetch it.
 
Object Store Bucket
You will need to create an Object Store bucket to receive your file. When creating your Object Store bucket, be sure the ‘Emit Object Events’ option is checked. This is what allows us to subscribe to events on the bucket.

2 - Event
As soon as the file is created in our object store bucket, the event fires. Events consist of an Event Type (this describes the action that should trigger the event) and an Event Action (what you want to happen when the event occurs). In our case the event type is 'Object - Create' and the Event Action is to call a Notification.

3 - Notification
​The Notification is the operation we want to perform when our event fires. We are using the ‘HTTPS (Custom URL)’ operation. Notifications consist of Topics which allow us to categorize our notifications. For each Topic, we can create multiple Subscriptions. This allows us to let multiple systems know when an Event occurs. In this example, we are referencing our ORDS Endpoint.

If your ORDS end point is unavailable when it is called by the Notification, OCI will re-try to post to your service for a period of up to 2 hours.

​The Endpoint service needs to have a POST handler to accept the payload from the Notification. In our case, our Endpoint is an ORDS REST service running on an APEX environment (see step 4 for details).

When you first create a notification, it will go into a status of ‘Pending confirmation’.

At the time you create the Notification, OCI sends a payload to your Endpoint containing a confirmation URL. The confirmation step ensures that you have control over the Endpoint that you define in your Notification.

4 – ORDS Service
The ORDS web service definition is pretty simple. Here is a screen shot (from SQLDeveloper) of the Module, Template and Handler for a service that can receive a payload from an OCI Notification.

The PL/SQL code you add to the handler can do anything you want. In our case it submits a Database Scheduler Job to fetch and process the file asynchronously.
 
5 – Process File
In our DB Scheduler Job, we use the OCI Object Store REST services to fetch the file from the OCI Object Store bucket. This connectivity between APEX and OCI was made much easier in APEX 19.2 when Web Credentials were enhanced to include the ‘Oracle Cloud Infrastructure (OCI)’ Authentication Type. This allows us to configure a native Web Credential for connecting to OCI Web Services. I recommend this Oracle A-Team Blog which describes in detail the OCI and APEX setups that are required to get this integration working.
 
After you have fetched the file (using APEX_WEB_SERVICE), you can then parse it (using APEX_DATA_PARSER) and process it as necessary, making the data available for your APEX Applications.

​I have been using APEX and ORDS on Amazon Web Services (AWS) for a number of years and until recently, I was not sure Oracle was ever going to compete. Oracle Cloud Infrastructure has come a long way in the past 12 months. While it still falls far short of AWS in terms of functionality, the functionality that is there is intuitive and works well. If you add great initiatives like ‘Always Free’ ATP, ORDS and APEX then you have a platform that is worth getting to know..
 
As APEX/ORDS developers and architects I encourage you to explore OCI. I also think we should start  to think differently about how we build our APEX solutions to utilize OCI where it makes sense.