Oracle Visual Builder Excel Add-in Use Cases
Oracle Cloud Visual Builder Add-in for Excel helps in integrating Excel spreadsheets with Oracle Cloud REST services to retrieve, analyze, and edit business data from the service. You can download your data to an Excel spreadsheet, work with it, then upload your changes back to the service.
Key Concepts, Components, and Terms:
Term – Description
Integrated workbook – An Excel workbook configured to work with one or more business objects.
Service – A web service that provides access to application data. The add-in works with REST services/Web services.
OpenAPI – OpenAPI is a specification that defines a standard interface to REST APIs. It lets us explore the capabilities of of a web service without accessing the underlying source code.
Business object – A resource that has fields to hold your application’s data. A business object includes a collection path, an item path, a set of fields, and other properties.
Business object catalog – A set of one or more business objects with a common host and base path.
Path – A path identifies the specific resource in the host that a web client requests access to, for example:/fscmRestApi/resources/11.13.18.05/draftPurchaseOrders.
Collection path – A service path (endpoint) that can be used to fetch multiple rows of data from the business object and/or to perform operations on the collection.
Item path – A service path (endpoint) that can be used to fetch, or operate on, a single row from the business object.
Metadata path – A service path (endpoint) that can be used to fetch the service metadata for the business object.
Layout – A way to display a business object in an Excel worksheet. VB Excel worksheet supports Table or Form-over-Table. Layouts are created by workbook developers and are visible to business users in published workbooks.
Supported REST Frameworks:
REST Framework – Short Form
NetSuite SuiteTalk REST web services – NetSuite
Oracle ADF REST Resource – ADF REST
Oracle REST Data Services – ORDS
Visual Builder Business Objects – VBBO
Installation:
We can download plug-in installation file from below mentioned URL.
Download: https://www.oracle.com/downloads/cloud/visual-builder-addin-downloads.html
There are two types of installers (1) Current User (2) All Users. Current user type installer is preferred if the plugin will be used by only the user. Plugin is required for both end users and layout developers.
More instructions about installing the plugin can be found in the
Documentation: https://docs.oracle.com/en/cloud/paas/visual-builder-addin/4.0/excel-developer/index.html
Layout Builder in Detail:
Once the plugin is installed Oracle Visual Builder tab should be visible in excel sheet.
Menu Items:
Publish – Option to publish excel sheet for end user usage.
Download Data – To download data from the server
Table Row Changes – Select the rows you want to upload to server.
Upload Changes – Create / Update records in the server for the specific Business Object / REST API.
Incase if the tab is not enabled , then ensure that the plug-in is enabled and listed in Active Application Add-ins
Development Steps:
There are two basic components that helps in building excel layouts.
(i) Manage Catalogs
Under Manage Catalogs we can add Business Objects (BO), Business Object Catalogs and REST APIs. These catalogs behave like a mediator between end users and servers.
We can add the service in two ways, either we can add an empty BO catalog and define the UI path, parameters, request and response manually.
The other option is to import the BO Catalog from Service Descriptions.
Oracle Fusion REST APIs for each module can be found in below URLs.
https://docs.oracle.com/en/cloud/saas/sales/faaps/index.html – CX
https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/24a/fasrp/index.html – SCM
https://docs.oracle.com/en/cloud/saas/human-resources/24a/farws/index.html – HCM
https://docs.oracle.com/en/cloud/saas/financials/24a/farfa/index.html – FIN
https://docs.oracle.com/en/cloud/saas/applications-common/24a/farca/index.html – Common
In order to list all the business objects of a catalog, append the server’s name to base URI path of the application and append describe at the end.
e.g. https://<servername>/crmRestApi/resources/11.13.18.05/describe
We can choose Basic Authentication or let it be as Default
On clicking Next list of Business Objects defined under crm RestApi Business Catalog will be displayed.
Once the Business Object is selected then it will be added to the list of Business Objects Catalogs. We can click the edit option to drill down and look for more details about the business object added to the catalog.
Oracle by default adds all the related Business Objects along with Subscriptions Business Object selected in the list.
We can drill down further in each Business Object and view the Fields, Child Business Objects, Attributes , Constraints and also if any LOV is assigned to the Business Object fields.
(ii) Designer
Manage catalog option helps in integrating with data layer of the application through Business Objects / REST APIs, similarly Designer helps in building a UI Layout in Excel for the underlying Business Object / REST API, defined in the Business Object Catalog.
On clicking Designer menu item, list of Business Objects defined in the excel sheet is displayed. We can choose the Business Object for which the New layout has to be created.
We can create a table layout or Form -over-Table Layout (more ideal for Parent-Child relationships).
Table Layout:
Form-Over-Table Layout:
This layout is ideal when we have to extract data or do CRUD operations on Parent-Child relationship tables.
Define Query / Parameters for filtering the data from Business Objects
In the Advanced tab, we can control the behavior of the layouts and also call custom Macros if defined in the excel sheet.
Once the layout is designed, we can Publish the excel sheet and share with end users for extracting data or do CRUD operations. When the layout is published, designer and manage catalog options would be disabled, and user will not be able to make any changes in the Layout design.
Use Cases:
(i) Create Header – Table Layout for CRUD Operations
In this use case we will build a Header – Table Layout for maintaining Subscriptions and its Products data. Subscriptions details is maintained at header, whereas Product details are maintained at line level.
Oracle Fusion UI for maintaining Subscriptions and its Products data in Oracle Fusion Cloud CX applications.
Oracle Visual Builder Excel layout in VBCS plugin for the handling the same data.
(ii) Create Table Layout for Reporting / handling multiple records
Search Option to filter data
Multiple Header & Line Records:
Conclusion:
Oracle Visual Builder Excel Add-in is a handy tool for building Adhoc UI for maintaining data in the system and also build Reports. Capabilities of this add-in can be enhanced by adding Macros to handle data on custom basis.
