* This blog was last tested on OAC 105.8.0-133 (Version 5.8) + ADW 18c + Fusion 20D (11.13.20.10.0) *

 Background

This article provides a fresh approach on the subject of integrating Oracle Sales Cloud with Oracle Analytics Cloud (OAC).

Integrating Oracle Transaction Business Intelligence (OTBI) Soap API, showcased how to use Oracle Transactional Business Intelligence (OTBI) to extract data from Sales Cloud and load it into ADW.

This article tackles the reverse data movement pattern – loading data from a OAC dashboard into Sales Cloud.

Data is inserted into Sales Cloud using the REST API for Oracle Sales Cloud using these steps:

1)    PL/SQL is used for the ETL.

2)    A database Stored Procedure is triggered by a database Function.

3)    The database Function is referenced in the RPD using EVALUATE.

4)    The data-load is triggered from a Dashboard using an Action Link.

5)    Dashboard Prompts are used to pass selected values from the Dashboard

       to the Stored Procedure using Request and Session Variables.

The more ambitious component of this article is replicating the user experience of scraping data from a dynamically filtered Dashboard Analysis Request. Write-back is emulated by replicating what the user views on the Dashboard in a stored procedure SQL SELECT.

1)    The Dashboard Consumer refines the results on the Dashboard with a Prompt that represents a unique record identifier.

2)    The Dashboard Prompt Sections are passed to the Stored Procedure SELECT and used in a WHERE clause to replicate the refinement that the Dashboard Consumer makes on the Dashboard.

Note: When replicating dashboard SQL in the stored procedure, be cautious of data that has had row level security applied in the RPD. To avoid erroneous results, all customized permissions must be manually enforced through the stored procedure SQL WHERE clause.

The following steps walk-through the creation of the necessary OAC and PL/SQL artifacts needed to load data from a OAC Dashboard into Sales Cloud. The example provided interprets the contact information from the Dashboard and creates a new matching contact in Sales Cloud. This example could be easily modified to support other REST API methods.

Part A – Configure OAC Dashboard

1)    Create Table

2)    Insert Records

3)    Create Analysis Request

4)    Create Dashboard

Part B – Configure PL/SQL

5)    Review REST API Documentation

6)    Test POST Method

7)    Create Stored Procedure

8)    Test Stored Procedure

9)    Create Function

10)  Test Function

PART C – Configure Action Link Trigger

11)  Create DUMMY_PUSH Table

12)  Create Variable

13)  Reference Variable

14)  Create Model Expression

15)  Create DUMMY_PUSH Analysis Request

16)  Create Action Link

17)  Execute Update

Main Article

Part A – Configure OAC Dashboard

Step 1 – Create Table

From SQLDeveloper or SQL query tool of choice create a table called “CONTRACTS”.

Where CONTACT_KEY is the unique record identifier that will be used to refine the data on the Dashboard. This must be something that the Dashboard Consumer can easily recognize and decipher.

CREATE TABLE CONTACTS(
FIRST_NAME VARCHAR2(500),
LAST_NAME VARCHAR2(500),
ADDRESS1 VARCHAR2(500),
CITY VARCHAR2(500),
COUNTRY VARCHAR2(500),
STATE VARCHAR2(500),
CONTACT_KEY VARCHAR2(500));

Step 2 – Insert Records

Insert a selection of sample contact records into the contacts table.

INSERT INTO CONTACTS(FIRST_NAME,LAST_NAME,ADDRESS1,CITY,COUNTRY,STATE,CONTACT_KEY)
VALUES (‘Jay’,’Pearson’,’7604 Technology Way’,’Denver’,’US’,’CO’,’Pearson-Jay’);

Step 3 – Create Analysis Request

Add the CONTACTS table to the three layers of the RPD. In the Physical Layer join it to another table (create dummy table if required) -> Publish to the Cloud.

Create an Analysis Request based on the CONTACTS table.

Add a filter on CONTACT_KEY where Operator = “is prompted”.

Step 4 – Create Dashboard

Create a Dashboard. Add the Contacts Analysis and a Prompt on CONTACT_KEY. To keep the example simple, a List Box Prompt has been used. Additionally, “Include All Column Values” and “Enable user to select multiple values” are disabled. It is possible to use both these options, with extra manual SQL in the stored procedure.

In RPD uncheck Nullable on CONTACT_KEY and any other column that a prompt will be applied.

Part B – Configure PL/SQL

Step 5 – Review REST API Documentation

Begin by reviewing the REST API for Oracle Sales Cloud documentation. This article only covers using:

Task: Create a contact
Request: POST
URI: crmRestApi/resources/11.13.18.05/contacts

That said there are many other tasks / requests available in the API that may be useful for various integration scenarios.

Step 6 – Test POST Method

From Postman:

From Curl:

curl -u user:pwd -X POST -v -k -H “Content-Type: application/vnd.oracl
e.adf.resourceitem+json” -H “Cache-Control: no-cache” -d@C:\temp\contact.json https://fusion.oraclecloud.com/crmRestApi/resources/11.13.18.05/contacts

Where C:\temp\contact.json is:

{
“FirstName”: “John Barry”,
“LastName”: “Smith”,
“Address”: [
{
“Address1”: “100 Oracle Parkway”,
“City”: “Redwood Shores”,
“Country”: “US”,
“State”: “CA”
}
]
}

Confirm Contact was created in Sales Cloud.

Home -> Sales -> Contacts

Step 7 – Create Stored Procedure

Replace Sales Cloud Server Name, Username, and Password.

create or replace PROCEDURE PUSH_TO_SALES_CLOUD(p_selected_records varchar2, o_status OUT varchar2) IS
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := ‘https://fusion.oraclecloud.com/crmRestApi/resources/11.13.18.05/contacts’;
l_body CLOB;
v_array apex_application_global.vc_arr2;
v_first_name VARCHAR2(100);
v_last_name VARCHAR2(100);
v_address1 VARCHAR2(100);
v_city VARCHAR2(100);
v_country VARCHAR2(100);
v_state VARCHAR2(100);
v_status VARCHAR2(100);
—
v_array := apex_util.string_to_table(p_selected_records, ‘,’);
FOR j in 1..v_array.count LOOP
SELECT FIRST_NAME,LAST_NAME,ADDRESS1,CITY,COUNTRY,STATE
INTO v_first_name, v_last_name, v_address1, v_city, v_country, v_state
FROM CONTACTS
WHERE CONTACT_KEY = v_array(j);
l_body := ‘{
“FirstName”: “‘ || v_first_name ||
‘”,”LastName”: “‘ || v_last_name ||
‘”,”Address”: [{“Address1”: “‘ || v_address1 ||
‘”,”City”: “‘ || v_city ||
‘”,”Country”: “‘ || v_country ||
‘”,”State”: “‘ || v_state ||
‘”}]}’;
–dbms_output.put_line(‘Body:’ || dbms_lob.substr(l_body));
apex_web_service.g_request_headers(1).name := ‘Content-Type’;
apex_web_service.g_request_headers(1).value := ‘application/vnd.oracle.adf.resourceitem+json’;
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_body => l_body,
p_username => ‘User‘,
p_password => ‘Pwd‘,
p_http_method => ‘POST’
);
v_status := apex_web_service.g_status_code;
–dbms_output.put_line(‘Status:’ || dbms_lob.substr(v_status));
END LOOP;
o_status :=v_status;
COMMIT;
–;

Step 8 – Test Stored Procedure

declare
o_status integer;
—
PUSH_TO_SALES_CLOUD(‘Pearson-Jay’,o_status);
dbms_output.put_line(o_status);
–;

RETURNS: 201 – indicating successful creation of contact

Step 9 – Create Function

create or replace FUNCTION FUNC_PUSH_TO_SALES_CLOUD
(
p_selected_records IN VARCHAR2
) RETURN VARCHAR
IS PRAGMA AUTONOMOUS_TRANSACTION;
o_status VARCHAR2(100);
—
PUSH_TO_SALES_CLOUD(p_selected_records,o_status);
COMMIT;
RETURN o_status;
–;

Step 10 – Test Function

select FUNC_PUSH_TO_SALES_CLOUD(‘Pearson-Jay’)
from dual;

RETURNS: 201 – indicating successful creation of contact

Part C – Configure Action Link Trigger

Quick Re-Cap:

It may be useful to revisit the diagram provided in the intro to give some context to where we are at.

“Part A” covered building the Dashboard show in #4

“Part B” covered building items #1 & #2.

“Part C” will now cover the remaining artifacts show in #3, and #4.

Step 11 – Create DUMMY_PUSH Table

This tables main purpose is to trigger the database function. It must have a minimum of one column and maximum one row. It is important that this table only has one row as the function will be trigger for every row in this table.

CREATE TABLE DUMMY_PUSH (REFRESH_TEXT VARCHAR2(255));

INSERT INTO DUMMY_PUSH(REFRESH_TEXT) VALUES (‘Status:’);

Step 12 – Create Variable

From the RPD create a variable called “r_selected_records”. Provide a starting Value and define the SQL Query.

Step 13 – Reference the Variable

On the Dashboard Prompt (created in Part A – Step 4) set a “Request Variable” matching the name of the Variable (created in Part C – Step 12). i.e. r_selected_records

 

Step 14 – Create Expression

Add a new Logical Column called PUSH_TO_SALES_CLOUD to DUMMY_PUSH Logical Table to the Business Model Layer.

Expression: EVALUATE(‘OAC_USER.FUNC_PUSH_TO_SALES_CLOUD(%1)’,VALUEOF(NQ_SESSION.”r_selected_records”))

Drag Logical Column called PUSH_TO_SALES_CLOUD to the Presentation Layer

Step 15 – Create DUMMY_PUSH Analysis Request

              Reload Server Metadata if required.

Add both fields to the Analysis – hiding column headings if desired.

Results -> Table Edit View -> Column and Measure Properties -> Row Properties -> Column Labels = off

Step 16 – Create Action Link

Add an Action Link to the Dashboard. Choose Navigate to BI Content.

 Check “Run Confirmation” and customize message if needed.

Customize the Link Text and Caption (if desired).

17)  Execute Update

Select the user to insert into Sales Cloud.

*** Important ***

CLICK APPLY

Apply must be hit to set the variable on the prompt!

Click the “Push to Sales Cloud” Action Link”.

Confirm the Action

Status 201 is returned indicating the successful creation of the contact.

Confirm contact was created in Sales Cloud.

 Want to Learn More?

Click here for more A-Team OAC Blogs

Click here for more A-Team BICS Blogs

Click here for more A-Team OBIEE Blogs

Click here for the REST API for Oracle Sales Cloud guide.

Click here for the Application Express API Reference Guide – MAKE_REST_REQUEST Function.

 Summary

This article provided a set of examples that leverage the APEX_WEB_SERVICE_API to integrate Oracle Sales Cloud with Oracle Analytic Cloud (OAC) using the REST API for Oracle Sales Cloud.

The use case shown was for OAC and Oracle Sales Cloud integration. However, many of the techniques referenced could be used to integrate Oracle Sales Cloud with other Oracle and non-Oracle applications.

Similarly, the Apex MAKE_REST_REQUEST example could be easily modified to integrate OAC or standalone Oracle Apex with other REST web services.