Using the Social Signon capabilities in APEX, you can authenticate your users with a wide range of identity providers such as OKTA, Auth0 or Azure/MS365.  We've blogged about that here: its-time-for-a-new-name-for-apex-social-sign-in.html

But what if you need to maintain those users from APEX - for example, to enroll a new user?   That's where Oracle APEX's built-in APEX_WEB_SERVICE package comes in handy.   This blog post shows you how to achieve this.

{okta_domain} is your Okta domain and {user_id} is the internal user ID of the specific user, returned by the first REST API call.

As other REST API calls depend upon this, you'll need a mapping table between the user ID e-mail address and Okta user ID (see later).
​
The REST API returns HTTP 200 OK for success, HTTP 400 Bad Request for errors with JSON details about the error.

You cannot delete a given user in one step, the user needs to be deactivated first. ​

​Once again, {okta_domain} is your Okta domain and {user_id} is the internal user ID of the specific user, returned by the create user REST API call.
The REST API returns HTTP 200 OK/204 No Content for success, HTTP 400 Bad Request for errors with JSON details about the error.

The REST API calls can then be wrapped up in a PL/SQL package using APEX_WEB_SERVICE.   

  

APEX_WEB_SERVICE makes it simple to call REST APIs and integration your APEX apps with third party identity providers.

On #JoelKallmanDay, we remain grateful to Joel Kallman and the APEX team for the amazing Oracle product and community they built.

Teams allows you to bring together people from around your organization into permanent or Ad-Hoc teams for increased collaboration. It also combines with other Office 365 services like Calendar and SharePoint to further enhance collaboration. None of the Teams integrations I am going to describe utilize MS Graph APIs in any way. You don’t even need to create an Active Directory ‘App registration’. Instead, communication between Teams and your APEX application is handled using Inbound and Outbound Webhooks.
 
Teams Terminology (Teams and Channels)
The screen shot below shows a team called ‘APEX Integration Team’ which has two Channels ‘General’ and ‘Stock Bot’. Channels allow you to break out communication within a team between different subjects.

Outbound webhooks allow you to create a simple Chatbot for a Team. In Teams, you configure the URL you want Teams to call when the Webhook is invoked. In our case, this URL will be an ORDS web service (more on that later). Whenever a user @mentions the Webhook from within a Channel, Teams sends a JSON payload to the URL endpoint specified in the Outbound Webhook definition. The JSON payload contains details of the user who invoked the Webhook, the conversation ID, the channel and of course the text that was entered by the user.
 
The diagram below shows the flow of information from the point the user sends a message to the Outbound Webhook (step 1) up to the point where the response from the ORDS Service is displayed to the user in Teams (step 6).

​In our case we are using ORDS to create an endpoint by creating a Module, Template and POST Handler. The PL/SQL code in the POST handler is responsible for parsing the JSON sent by Teams, running PL/SQL code to perform the action requested by the user, and then responding with a reply in the REST service response. Teams calls your endpoint synchronously and gives you 5 seconds to complete your response. Teams then takes your response and displays it to the user in the Teams client application.
 

Here is a screen shot of an example interaction. The User @mentioned an outbound Webhook called ‘JMJ ServReq’ and sent the message ‘Hello SR Chatbot’.

The ORDS Webhook was called by Teams, our PL/SQL code parsed the JSON sent by Teams and pulled out the user’s name. Finally, the PL/SQL generated a JSON response which included the user’s name.
 
Example Use Cases

• Get information from Oracle EBS or ERP Cloud. A user types “@ebs How much of item XYZ is left in stock” into a Teams channel created for a particular warehouse. Your ORDS end point would then get the on-hand inventory for the item in that warehouse and responds to the user with the quantity.
  
• Perform actions in EBS or ERP Cloud. A user types “@erpcloud cancel order #102202” into a Teams channel. Your ORDS service checks if the order is open and replies, “Are you sure?” you then respond “@erpcloud Yes”. Your ORDS service recognizes it is the same conversation and you have confirmed cancellation and proceeds to cancel the order using ERP Cloud Web Servcies. Note: Because Teams keep track of a “conversation” you can respond to the user asking them questions about their inquiry (just like a chatbot does).

 
Security
When a user messages your Webhook, you can assume they have been authenticated by Microsoft. You cannot, however, assume they are authorized to use your chatbot. You should include your own code to verify that the user which sends the message is allowed to use the Chatbot. In addition, to ensure that your service is receiving calls only from actual Teams clients, Teams provides an HMAC Code in the HTTP ‘hmac’ header.
 
Teams Setup
Here is a screen shot showing the setup of the Outbound Webhook in Teams. It is as simple as providing the URL for your ORDS Endpoint.

​ORDS POST Handler Setup
In our very simple example, the ORDS handler PL/SQL code looks like the below code block. In real life, you are going to need additional code to validate the sender is Teams, determine if the user is authorized to perform the required action in your system, and build a JSON document to respond to the user.

Inbound Webhooks are a special type of Connector in Teams that provide a simple way for an external app to share content in Teams. Teams provides a unique URL to which you POST a JSON payload with details of the message. The timing of when the message is posted is completely up to you so it can be based on an event that occurs in your application.

​The URL generated when you create the connector is the URL you will POST messages to. These messages will then show up in the channel which you created the connector in.

​Message Formatting Options
There are several options for formatting the messages you send to Teams. The most flexible is to use Cards. Cards provide the ability for users to respond to the message using multiple input options like free form text, date pickers and select lists. The layout of the card and the actions a user can perform on the card are all defined in the JSON payload you send to Teams (step 1 in flow diagram above).

​Actionable Message Cards Vs Adaptive Cards
It seems that Microsoft is in the middle of transitioning their cards interface from legacy Actionable Message Cards to Adaptive Cards. At the time of writing this post, Adaptive Cards do not allow you to post a user’s response to a card to your own http endpoint (see step 4 and 5 in the flow diagram above). In view of this, if you need a response to the card in Teams then for now you must use Actionable Message Cards. If you do not need a response then I recommend using Adaptive cards, as these are surely the future. From here on in, I will simply use the term ‘Cards’.
 
For design ideas, check out the Adaptive Card Samples which provide several sample card layouts and the Designer, which allows you to enter your own JSON and see what your cards will look like. You can find sample Actionable Message Cards here.
 
Other Card Features
The power of cards in the Microsoft ecosystem does not stop there. For example, you can send cards in emails and make them part of your approvals workflow using Power Automate. When a user responds to a card you can have your API send back a new card with refreshed information based on the users response. You can even pro-actively send updated information to a card that you already sent to prevent the data from becoming stale. 
 
Example Inbound Webhook Use Case with User Response Via Card
Inventory Level Monitor. Let’s say you wanted to inform management whenever on-hand inventory in Oracle e-Business Suite (EBS) for a product went below a certain level. You can create an ‘Incoming Webhook Connector’ and attach it to a channel within your Purchasing Team. In EBS we could create a Concurrent Program which runs intermittently and posts a JSON payload to the Webhook whenever a product’s inventory drops below a certain level. The post is performed by calling the APEX_WEB_SERVICE PL/SQL API. Here is an example of a card that appears in a Teams Channel when the Inbound Webhook is invoked.

In the JSON message we send to the Webhook (see sample below), we included details about the card such as the title, sub-title, custom entry fields, the icon to display and an action button for the user to respond with. When the users enter notes, the need by date and clicks ‘Respond’, their responses are posted back to an ORDS POST web service that we created. In this example, Your ORDS web service could then create a PO in Oracle EBS.
 
Example JSON payload for an Actionable Message Card
This is an example payload posted to an Inbound Webhook Connector URL. It is the exact JSON used to create the card examples above. It contains details of the card to show the user in Teams in addition to the ORDS service to POST user responses to.

​Rate Limits
As with many MS services, there are rate limits for Connectors. At time of writing this post they are as follows. If you exceed these limits, you could start getting http ‘429’ errors.

​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.