Connecting Oracle Apex 20.2+ with Oracle Financials Cloud with Parameters via REST Services using no coding packages or scripts.
Oracle Apex is a wonderful tool for developing low code quick and easy applications that produce great POC’s to further develop solutions to final products. I had been out of Apex for a few years and was looking to connect an APEX to our Oracle ERP (Enterprise Resource Planning) Financials Cloud via their only their only accessible API’s. To my surprise, APEX now has built in a lot of functionality that allows it to seamlessly connect to the rest services. But with Oracle APEX being such a mature product now, there are a lot of old posts on how to do just about everything and most of those use code or other deprecated methods that made harder than it needed to be figure out how to do this. While those posts are still valid, they are not needed any longer for me and just got in the way of what I try to show in POC’s. I simply don't have a lot of time anymore and when I need to build stuff, it needs to be quick and in my spare time if I do develop. There are several posts that have parts of what I was looking to do, but none with a complete end of end picture of exactly what I was looking to do. Below at the bottom of this are links to the many articles and blogs that helped me find my way.
Let's start!
Prereqs for this are that you already have an Oracle APEX cloud account, an Oracle Financial ERP SaaS environment, and some APEX previous experience.
1) Let's start with a Basic Application where you want to query Sales Orders from the ERP. We just need to create a shell to begin with.
2) I just called mine Sales Orders Inquiry, feel free to name it what you want.
3) Now go to the Shared components panel of your application and click on REST Data Sources
4) Click on Create REST Data Source and pick the option to start from scratch
The following next few steps are just for reading purposes and informational. It's just to get to know the API documentation better. You can skip it if you understand it well enough.
Finding the API’s
You can make a list of REST APIs for Oracle Fusion from here: Oracle Cloud Applications
This link will change monthly or quarterly, so refresh the button up top to get the latest.
For our Sales orders, the path is:
Supply Chain Management -> Order Management -> APIs & Schema ->REST API for Oracle Supply Chain Management Cloud.
Here is the wrong way I went about things and why it wasn’t a waste of time.
From here the path is: Tasks -> Order Management -> Sales Orders for Order Hub -> Get one Sales Order. At least it seems logical that in order to navigate to an API to get one sales order, this is the starting point.
The way this documentation is laid out, you need to understand that there are several ways to find the exact example you can use. These are not bookmarked, so you will need to scroll through this chapter to find the numerous ways you can parametize the sales order or other API in this documentation.
Example 1: by OrderKey (this is a PK column made up of several columns. You will not know this if you are searching for an order.
Example 2: by HeaderID. Again, useful for certain cases, but not here.
Neither of these give us what we are looking for. But still this is a great resource, and we will come back to it. The next thing is to sign up for a free account on POSTMAN (web.postman.co) . Here we can see our own data and make sure we have the syntax right.
I went ahead and grabbed an orderkey and tried the service in Postman. You don’t need to do this step, just showing an extra step and hurdle I had to go through.
There are three parts to testing in Postman. The Base URL, the Endpoint, and the authorization.
Your BaseURL is your ERP Cloud URL ending at .com. Your endpoint is from the documentation above.
Your authorization is your ERP Credentials.
After I got my one result, I saw links to other ways to get more data. So, I want to go back to the “get all” Sales Orders Service. This is the right API.
And found these parametized options.
We will be using example 5 for this application.
The other thing to note is that there are query parameters in this documentation. Each link will smoothly open more information. Do not pass on this.
Another issue for me was figuring out the APEX methods for parsing. There are so many ways to do this, and I wanted to find the most optimal way.
Back to building the API
5) Give it a name for the required field.
Use your ERP URL for the URL Endpoint Name: Oraclecloud Servername ending at .com
fscmRestApi/resources/11.13.18.05/salesOrdersForOrderHub/?finder=findByOrderNumber;
6) Click next and you’ll get the opportunity to enter the Service URL Path
Our Service URL path will be
/fscmRestApi/resources/11.13.18.05/salesOrdersForOrderHub/?finder=findByOrderNumber;
7) Choose to enter new credentials and use Basic Authentication
Enter your Oracle Fusion ID’s or a service account you know has access to OM. Click on ADVANCED when done.
You can save these for future use.
8) Click Discover
It should find 117 columns 
If the discovery does not work, go back, and remove the finder parameter, we will add it back after discovery.
9) Click Create REST Data Source and on the next page Scroll down and add back the /finder= findByOrderNumber; to the URL path prefix and click apply and come back to the page.
Proceed to click on Add Operation
This is the part that may be the most confusing. I will try to break it out the simplest way possible.
1) URL Patterns – Click the question mark beside it, really is the easiest way to explain it. Short version is that if you use a ‘.’ dot, there will be no pattern. We do have a pattern. Use OrderNumber=:OrderNumber
2) HTTP Method – Use GET to retrieve information from your ERP
3) Database operation – Fetch rows
Click Add Parameter here. Do not apply changes on this page yet. There is another place to add parameters, but I do not really know why and could not get that to work.
Before we start here, let us recap the API a little bit. The API we’re building is to find an Oracle sales order by order number. The documentation points us to this, example # 5.
https://servername/fscmRestApi/resources/version/salesOrdersForOrderHub?finder=findByOrderNumber;OrderNumber='404087'
To build this service which ends at the salesordersForOrderHub, we need to build out the stuff after that.
We have added the finder= findByOrderNumber; already in a previous step. So, we only need to build OrderNumber=’xxxxx’ dynamically.
1) Type – We are using a URL pattern of which we define as OrderNumber.
2) Value – Can be empty, but easier to use a real value to test. It will get ignored in the app, it is a default.
3) Required – Yes
So, we are hardcoding OrderNumer= and then parametizing the OrderNumber and value
Now back to the application
On page 1, click to create an interactive report region.
Make it a rest source.
Pick your service
Add a Parm value (page item)
The built-in parameter we built in the service in the parameter for the region will get our Page item.
Add the item to the Region source. If you want to skip a button, just toggle the Submit when Enter pressed on the Page item
That is all folks. You can further enhance it with Detail modals, faceted search results for filtering and other goodies. If you use single sign on like I do, you can alter the connection in the REST to use the credentials of the user, so role permissions are properly passed to the ERP for access.
Page Break
Links
Integrating SSO between APEX and IDCS
Integrating SSO between APEX Cloud and Identity Cloud Service the Easy Way | A-Team Chronicles (ateam-oracle.com)
Setup and configure APEX in Paas OCI
Dimitri Gielis Blog (Oracle Application Express - APEX): Setup and configure the APEX Application Development Service (including email) in the Oracle Cloud (dgielis.blogspot.com)
Native drag and drop in 20.2
APEX 20.2 Native Dropzone Multiple File Upload. — oracle-tech
REST API’s via code
Building a REST API to Deploy APEX Apps (oracle.com)
Rest API’s in Oracle ERP Financial cloud - coding way
Create/Post Rest API in Oracle ERP Cloud | Create Invoice For Oracle Financials Cloud - YouTube
Local sync for REST via APEX
Synchronize Data from REST Services to Local Table. With no code at all! | Oracle Application Express Blog
Comments
Post a Comment