How to integrate Fusion Cloud REST APIs into Excel using VBCS

In earlier posts, we have seen how the REST APIs in Fusion Cloud work and how we can leverage them for various use cases. Normally we invoke REST APIs either via integration layers like Oracle Integration Cloud (OIC) or some other cloud platforms like Azure, APEX etc.

But, what if there's a requirement to fetch the Fusion Cloud data into an Excel sheet ? Well, technically in some cases we can use ADFDI to achieve some part of this but it's not a desired option as it also opens options like inserting or updating new data into Fusion Clous. What if the requirement is only to provide an option to the users where they can refresh/fetch the latest Fusion data into Excel sheet with just one click ? Well, we can achieve this by using Oracle Visual Builder Excel add-in. Let's see how we to do this.

- Download Oracle Visual Builder Add-in for Excel from here

https://www.oracle.com/downloads/cloud/visual-builder-addin-downloads.html

- Install the Add-in on your machine

- Once installed, you will be able to see the Add-in in Excel options as shown below. Make sure it's enabled.

- Open a new workbook in Excel

- Now we will see a new option named 'Oracle Visual Builder' in Excel menu

- Let's click on Designer option

- This will bring up a pop-up asking the Web Address of the Fusion REST API

- We have to enter the Web Address as Instance Link/ followed by REST API endpoint followed by 'describe'

- For example, let's assume we want to invoke Get All Suppliers REST API to fetch all suppliers from Fusion Cloud.

Get All Suppliers API:
Instance_URL/fscmRestApi/resources/11.13.18.05/suppliers

- Now, the Add-in will try to connect to your Fusion Instance and you will be asked to provide Login credentials for a valid Fusion user.

- After successful login, you will be shown all the business objects provided by the REST API

For example, for our Supplier REST API, we will see Business Objects pertaining to Suppliers, Business Classifications, Contacts, Sites etc.

- Select the business object you would like to populate in the Excel and click Next. Let's select Suppliers for our use case

- Select Table Layout on the next screen and click Next

- Now, we will be asked to select any Optional business objects on next screen. Let's not select any in our use case and click Next.

- Click Finish

- Once done, you will see a new Sheet named Suppliers has been created and you will also see Layout Designer section on the right side

- To avoid performance issues, Oracle restricts the download of bulk data. So we have to mention at least some search criteria. This is very much like the Standard Suppliers page/form.

Under Query, we must enter at least one search criteria. Once done, click on the Download Data option under the Add-in menu.

let's add a search criteria for Supplier name containing 'xyz'

-  Click on Download Data button

- Once download is completed, you will see the Status details in a new section on the right side and actual data on left side. In our case, it extracted 5 Suppliers from the REST API.

- As we can see, by default, it shows all the fields provided by REST API under business object. So let's remove additional fields and keep only the ones we need.

To do so, let's navigate to Columns section in Layout Designer, select the columns we want to remove and click Delete button.

- Now, let's click on Download Data option again and this time it will download all the eligible Suppliers based on our search criteria and will generate the sheet with only our selected columns.

So, this way we can leverage Visual Builder Excel Add-in to provide flexibility to users by creating some excel sheets to show certain information from Fusion Cloud. This can also be used to create or update information in Fusion Cloud, depending on which REST API we use and base on the user account selected to invoke the REST API.

Read More

How to create a Progressive Web App (PWA) in Oracle APEX

Alright, we all know the capabilities and benefits of Universal theme in Oracle APEX and the seamless user experience it delivers whether the application is accessed from a Desktop browser or a Smartphone browser. Due to its responsive nature, it certainly provides a native app like UI when accessed from a smartphone.

But what if we want to deliver an App to users which they can install and use just like a Native app on their phone as well as provide them additional benefits that come with a Native app such as Push notifications, some offline capabilities etc. ?

Well, look no further. Progressive Web App is here to solve all these problems. Let's take a look at that.

- Create a new Application

- Navigate to Shared Components

- Under 'User Interface' section, go to Progressive Web App option

- On PWA Settings page, we will need to enable below two options:

  - Enable Progressive Web App

  - Installable

- We can also set various other options which help us decide how the App should behave once it's installed on the Device.

Display: We can decide if the App should be displayed in a Standalone or Fullscreen mode etc.

We can even decide what should be the Orientation of the App on the mobile device. For example, if the App should operate in Landscape orientation or Portrait. 'Any' option will let you use the App baed on Device's orientation.

We also have iOS specific setting to set the Status Bar Style on iOS Device -

- Now, let's upload a sample screenshot in the PWA definition. The screenshots uploaded at this place are shown to the end users when they try to install the App. This is very similar to a App Store listing that we see on iOS App Store or Android Play Store.

- Let's upload a sample screenshot that shows how the App will look like

- Click Create

- Apply Changes

- Now our App is enabled for PWA. That means, the app can be 'Installed' on a smartphone and it will operate independently like other apps on the device.

- All we need to do is to open the App URL once in the mobile device's browser and we should be able to install the App from there.

- Here is the list of supported OS and their Browsers through which we can install a PWA app on a mobile device:

- Now, let's open the App URL on the smartphone browser and Sign In. I'm using Chrome browser on an Android phone but the same can be done using above listed browsers based on which OS you are using.

- Once we login, we can see an icon in top right corner that looks like a Cloud with a Download sign.

Let's Tap on that.

- Now, you will see below Pop Up offering you to install the App on your device.

Tap Install

- You will see a message that tells us the installation has begun

- Followed by a confirmation message

- Now, let's close the browser and go back to Home screen on our smartphone. We will see our newly installed App over here.

- Now, we can simple open the app like any other native app on our device. This not only eliminates the need of accessing the Apex app via a web-browser but also enables many native app specific features, which we will see in next few blogs.

Read More

How to Create Sub-Templates with BIP Reports in Oracle Fusion Cloud

Let's see how we can create a subtemplate in a BI Publisher report in Oracle Fusion Cloud.

The Custom Report and Layout:

Before delving into the world of subtemplates, let’s take a moment to understand the foundation of a simple BI Publisher report. Let's assume we're working on a custom report, which includes a few key columns from the AP invoices and has some standard conditions.

When running this query, we can see an output, typically, a few invoices displaying their numbers, dates, and amounts.

In the BI Publisher catalog, under 'Custom -> Financials -> Payables' folder, we have a custom report titled ‘Custom Invoices’ with an attached RTF layout based on our data model. 

Existing RTF Template:

Creating the Sub Template:

Step 1: Create Your RTF File

Begin with a plain Word document. You will need to save it as an RTF file, 'subtemplate.RTF', for example.

Step 2: Define the Template Tag

Within the Word document, define your template tag. Here’s how you do it:

<?template:cust_template?>... Your content here ...<?end template?>

We just named our template 'cust_template'. For illustrative purposes, let's say we add an image representing a logo within this tag.

Step 3: Uploading to BI Publisher

In BI Publisher:

Navigate to the 'New' menu.

Select 'Sub Template'

Upload your RTF file

Save it. 

Now, the Subtemplate resides in the catalog and it's ready for use.

Embedding the Subtemplate into our mail RTF:

To incorporate the Subtemplate into the main RTF file, we first need to locate the Subtemplate's path in the catalog. It usually ends with an XSB extension.

Navigate to More -> Properties on the Subtemplate

This will confirm that the Subtemplate is a XSB file

Note down the path from Location field (exclude /shared part)

So in this case, our path to XSB (Subtemplate) file is /Custom/Financials/Payables/SubTemplate.xsb

Now, we need to add the import tag within our main RTF template like so:

<?import:xdoxsl:///Custom/Financials/Payables/SubTemplate.xsb?>

<?call-template:cust_template?>

Important: 

- Do not include 'shared' in the path.

- Use double slashes after 'xdoxsl:' to denote the root of the path.

- Import as well Call to the SubTemplate is a must to make it work correctly.

- Remember, 'cust_template' corresponds to the template name we defined earlier.

Note: If you have multiple Subtemplates in a single XSB file, they can be included and called upon selectively, based on varying conditions.

Extending the Data Model and Subtemplate Usage:

What happens if we decide to extend the data model? 

Let's say we add a 'GL_DATE' column to the model and we want to show this column in the SubTemplate ?

Could we reference this new column in our Subtemplate? Absolutely! Simply add a placeholder for 'GL_DATE' in the Subtemplate, just like any other field. Since the Subtemplate is part of the main RTF template, it automatically has access to the entire data model.

Now, let's update our SubTemplate with the modified RTF file.

Re-upload the updated 'subtemplate.RTF'.

Overwrite the existing one if prompted.

To ensure your changes are reflected, clear the BI Publisher cache.

Navigate to 'Manage BI Publisher' section under 'Administration'.

Clear Object Cache:

Now, you're all set to run your reports with the new changes effectively displayed!

The Final Output:

As a result of these steps, the final report would showcase the company logo and any other elements defined in the Subtemplate. For instance, the 'GL_DATE' will appear alongside other invoice details, as demonstrated:

Conclusion:

Subtemplates in Oracle Fusion Cloud's BI Publisher are incredibly versatile. They allow us to incorporate reusable elements, such as logos or standard headers, fostering consistency and saving time when running multiple reports.

Read More

How to Find Base Tables related to Application Composer Custom Pages in Oracle Fusion Cloud

In the last post, we saw how to create custom pages in Fusion Cloud.

Now, lets see how to locate the base tables related to the custom pages/objects created using Application Composer.

Option 1:

-> Navigate to Application Composer

-> Navigate to Metadata Manager

-> Click Generate under Configuration Report section

You will see a pop-up asking for selecting objects and metadata types

Select all default values and click Generate

You will see this message

Once completed, Navigate to Export option and select Excel

This will download an excel file.

Open the excel file, Filter the data and look for your custom object e.g. Customer Details

Now let's click on the Object Name

This will take us to the tab associated with our custom object.

Here we will be able to see the base table and column details

Option 2:

Let's see how we can derive the table details via query approach.

- First let's find the usage_id based on our custom objects 'CustomerDetails%'

select *

from fusion.adf_extensible_table_usage

where ENTITYDEF_FULLNAME like '%CustomerDetails%'

- Link Table Ids and link to below query

select *

from fusion.adf_extensible_table where TABLE_ID IN (300000000030505,300000000051618)

Final Query:

select *

from adf_extensible_table where table_id in (select table_id from adf_extensible_table_usage where context_column_value like 'Customer%Detail%')

- Now if we query this table and filter for our custom object under attribute_category, we will see all the information entered via our custom page

We can use these tables to build custom BI reports in order to visualize / extract the data for end-users.

Read More