VBCS Excel Plugin To Load Data into Oracle SaaS

In this article i will explain how to make use of the Oracle VBCS Plugin for Excel and Oracle SaaS REST Services to load data into Oracle SaaS cloud

For those who are not aware of what VBCS is, i would recommend reading through the below article
https://blogs.oracle.com/smb/what-is-visual-builder-and-why-is-it-important-for-your-business

Oracle SaaS Cloud does offer different mechanisms for importing the the data from various systems.
Some of the commonly used approaches are as given below

• FBDI Templates 
• SOAP / REST Services

In this example i will make use of the SCM REST Service and VBCS Plugin to create serial numbers in inventory. The steps are as detailed below.

1. Download the VBCS Excel Plug-in and install it. The plugin can be downloaded from https://www.oracle.com/downloads/cloud/visual-builder-addin-downloads.html
2. Open excel and after the successful installation of the plugin, you should be able the menu item in the excel.
   
3. Click on the Designer icon and enter the SCM REST Service Details as shown below and click on ok.
   
4. Enter the Cloud login credentials 
   
5. After signing in, Select the business object as shown and click on ok.
   
6. Select the Table Layout and click on ok.
   
7. A layout would be generated by the VBCS Plugin as shown below.
   
8. Edit the layout if required and redraw the layout to view the changes. For this example there are only 3 mandatory values required by the service.
   
9.  I have edited the layout to include the mandatory fields and entered the details of the OrganizationId, InventoryItemId and SerialNumber (which is to be created in Oracle) as shown below.
   
10. Click on Upload Changes to create the serial number in SCM.
    
11. Once the data is posted successfully the status would be updated in the record.
    
12. Now to navigate to SCM Cloud and view the details of the serial number created.
    

Expose Web Service as REST API using Oracle Integration Cloud

Exposing Web Service as REST API

Oracle Fusion ERP Cloud provides numerous Web / REST Services for integration and with each release Oracle is adding more and more services.  I have come across scenarios where you have a Web Service available for integration but don't have the equivalent REST Service available.

This might be a problem when you are developing custom applications in VBCS as VBCS can only work with REST Services at this point of time. To overcome this problem, we can leverage Integration Cloud and expose the Web Service as REST API.

In this post I will provide details around how to expose Web Services as REST API using OIC.

Create a REST Connector and Configure the Connection / Security details.

Create an Oracle ERP Cloud adapter and Configure the Connection / Security details.

Create a new Integration and Select Basic Routing Style.

Enter the relevant integration details after selecting the style (Integration Name, Identifier, Description and Package Details)

Select the REST Connector and drop it on the Trigger Section and Enter the details as shown below.

Click on next to configure the Request details of the service.

I have used the below JSON data to configure request.

{ "OrganizationCode" : "XX", "ItemClass" : "Root Item Class", "ItemNumber" : "Test Demo Item - 001", "ItemDescription" : "Test Demo Item - 001", "ItemStatusValue" : "Active", "LifecyclePhaseValue" : "Production", "PrimaryUOMValue" : "Each" }

Click on Next to configure the Response details.

I have used the below JSON data for configuring the response.

{  "ItemId" : 999994000402981,     "OrganizationId" : 111,     "OrganizationCode" : "XX",     "ItemCatalogGroupId" : 100000011369001,     "ItemClass" : "Root Item Class",
..................}

Now select the Oracle ERP Cloud adapter and drop it in the Invoke section and add the details as shown below.

Now add the mapping for request (mapping data between REST and ERP Cloud connector) and response (mapping data from ERP Cloud to REST Connector) as shown below.

Add the business identifier details for tracking.

Save the changes and activate the application.
Copy the Endpoint details as shown below (this endpoint will provide the metadata details).

Open the browser and enter the copied end point from previous step. The Endpoint will display the details below.

Copy the details under Endpoint URL.

Open SOAP UI and copy the Endpoint URL.
Select the method as POST and enter the details of the item to be created in the Request section.
Submit the request and the item will be created successfully in Oracle ERP Cloud.

The details of the item created in Oracle ERP Cloud are as shown below.

Create RESTful services with Oracle DBCS

In this post I will provide an overview of how to expose the data from Oracle DBCS via RESTful services. This is a common scenario where the customer would like to store large volumes of data in DBCS and leverage other Oracle PaaS products to build custom applications (ex : VBCS comes with a limited internal storage capacity to overcome this limitation we can leverage DBCS and expose the data via RESTful services)

I have created a schema and added the EMP table for the table as shown below.

EMP

===

create table emp(  

  empno    number(4,0),  

  ename    varchar2(10),  

  job      varchar2(9),  

  mgr      number(4,0),  

  hiredate date,  

  sal      number(7,2),  

  comm     number(7,2),  

  deptno   number(2,0),  

  constraint pk_emp primary key (empno),  

  constraint fk_deptno foreign key (deptno) references dept (deptno)  

);

Populate the EMP table with some sample data.

Now navigate to APEX and select SQL Workshop > RESTful Services

Create a new module by providing the relevant details.

After the module is created click on the Create Template button at the bottom of the page.

Enter the details as shown below and create the template

After the template has been created, click on Create Handler.

Under Handler section specify the method, source type, format and source options and click on create handler.

Now copy the Full URL Path and navigate to a browser and place the copied path.

The GET method will query the underlying EMP Table exposes the data in JSON format.
Similarly, we can created handlers for other methods as well.

By default authentication is not enabled in ORDS. Do refer the Authentication section for the options available.

Visual Builder Cloud Service - Web Application Design

Designing Forms in Visual Builder Cloud Service

In this post I will give you an overview of how to design a form in VBCS and make use of the available REST services in Fusion ERP Cloud to view the data.

VBCS can only work with REST services as of now but we can leverage Integration cloud to convert web services into REST services and make them available to VBCS.

The example provided below designs a simple form with a table and connect to ERP Cloud to get the inventory item details.

Create a new application and add the relevant details and click on Finish.

Now click on Service Connections and add the relevant details.

Create a new Service Connection and select the source as Define by Specification

Enter the relevant details and click on next

select the items end point and click on create

Now navigate to Web Applications and create a new Web Application as shown below.

Design the form and add the table component to the form as shown below.

Click on the Add Data property to map the underlying source data for the table. Select the item end point and click on next.

Select the columns which are to be displayed on the form.

Click on Next and associate any query parameters if required. In this example we don't have any query parameters to add so click on Finish.

We can now view the data in the Design View as shown below.

We can also change the column name by editing the headerText tag as shown below.

Click on Run icon to run the application.

The form would be displayed in a new window.