How to Schedule and Fetch output of Oracle BIP Report using Webservice

We have likely encountered a scenario where a report call fails because it exceeds the online viewing timeout or the maximum allowed file size limit for online rendering. In these situations, the standard runReport method from the ExternalReportWSSService simply isn't enough, the call will time out, leaving us with an error instead of data.

To handle these high-volume requirements, we need to shift from synchronous execution to an asynchronous scheduling model.

In this post, we will do a deep dive into how to use the ScheduleReportWSSService to programmatically schedule a report, manage its execution, and successfully fetch the output once it's ready.

Comparison: Synchronous vs Asynchronous Report calls 

		Feature ExternalReportWSSService-runReport (Online) ScheduleReportWSSService (Scheduled) Best For Small, quick data pulls Large datasets and time taking reports  Timeout Risk High (usually 300 seconds) None (runs in background) File Size Limit Strict limits (around 500MB of XML) Much higher thresholds as it considers formatted output like csv Execution Synchronous (wait for response) Asynchronous (polling) Sample WSDL:  https://abcd-saasfademo1.ds-fa.oraclepdemos.com:443/xmlpserver/services/ScheduleReportWSSService?WSDL Shedule a report: scheduleReport operation Request Payload: <soap:Envelope     xmlns:soap="http://www.w3.org/2003/05/soap-envelope"     xmlns:sch="http://xmlns.oracle.com/oxp/service/ScheduleReportService">     <soap:Header/>     <soap:Body>         <sch:scheduleReport>             <scheduleRequest>                 <reportRequest>                     <attributeFormat>csv</attributeFormat>                     <parameterNameValues>                         <item>                             <name>query1</name>                             <values>          <item>c2VsZWN0ICogZnJvbSBnbF9qZV9saW5lcyB3aGVyZSByb3dudW08MTAwMA==                      </item>                             </values>                         </item>                     </parameterNameValues>                     <reportAbsolutePath>/Custom/FAH/Sumit/QBReport.xdo</reportAbsolutePath>                     <sizeOfDataChunkDownload>-1</sizeOfDataChunkDownload>                 </reportRequest>                 <saveDataOption>true</saveDataOption>                 <saveOutputOption>true</saveOutputOption>                 <schedulePublicOption>true</schedulePublicOption>             </scheduleRequest>         </sch:scheduleReport>     </soap:Body> </soap:Envelope>    Response:   <env:Envelope     xmlns:env="http://www.w3.org/2003/05/soap-envelope">     <env:Header/>     <env:Body>         <ns0:scheduleReportResponse             xmlns:ns0="http://xmlns.oracle.com/oxp/service/ScheduleReportService">             <ns3:scheduleReport                 xmlns:ns3="http://xmlns.oracle.com/oxp/service/ScheduleReportService">534382             </ns3:scheduleReport>         </ns0:scheduleReportResponse>     </env:Body> </env:Envelope>    When we successfully submit a request via the ScheduleReportWSSService,  the jobId returned in the initial response is actually the Parent Job ID. While this ID confirms submission, it cannot be used for tracking progress or downloading the final output.  For all downstream activities—such as checking status or fetching the report,we must retrieve the Child Job ID. Note: There is often a 10–15 second lag between the submission of a parent job and the spawning of the child job within the BIP scheduler.  When automating this process, our code must include a retry mechanism or a "sleep" timer to account for this delay,  otherwise, our lookup for the child ID will return empty.   How to Retrieve the Child Job ID

We can retrieve the child instance using two primary operations. Both require the Parent Job ID as an input:

1. getAllJobInstanceIDs: We pass the submittedJobId (Parent ID) in the request to receive a list of associated instance IDs.

2. getAllScheduledReportHistoryInfo: We pass both the jobid (Parent ID) and the owner (user ID) 

   to get detailed history, including the child ID.

In the following example, we will focus on getAllJobInstanceIDs

Sample Logic Flow

1. Submit report $\to$ Get Parent Job ID.

2. Wait ~15-20 seconds (to allow BIP to spawn the instance) or add retry.

3. Call getAllJobInstanceIDs using the Parent ID.

4. Extract the Child Job ID from the response.

5. Monitor the Child Job ID for completion.

 

Request Payload:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:sch="http://xmlns.oracle.com/oxp/service/ScheduleReportService">
   <soap:Header/>
   <soap:Body>
      <sch:getAllJobInstanceIDs>
         <submittedJobId>534382</submittedJobId>
      </sch:getAllJobInstanceIDs>
   </soap:Body>
</soap:Envelope>

 

Response:

<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
   <env:Header/>
   <env:Body>
      <ns0:getAllJobInstanceIDsResponse xmlns:ns0="http://xmlns.oracle.com/oxp/service/ScheduleReportService">
         <ns3:getAllJobInstanceIDs xmlns:ns3="http://xmlns.oracle.com/oxp/service/ScheduleReportService">534383</ns3:getAllJobInstanceIDs>
      </ns0:getAllJobInstanceIDsResponse>
   </env:Body>
</env:Envelope>

How to Extract and Analyze Oracle Fusion User Audit Data for Security

In Oracle Fusion, we  many times need to debug or analyze user activity via audit trails. Whether for security compliance, incident or troubleshooting, querying the audit tables directly provides more flexibility than standard UI reports.

Below are the most effective SQL queries for extracting specific user activity data.

Monitoring Password Resets

If we need to identify who initiated a password reset, use the following query. This example retrieves the 10 most recent records, but we can adjust the filters to target specific users or date ranges.

SELECT *
FROM   (SELECT *
        FROM   fusion.ase_pwd_reset_req
        ORDER  BY creation_date DESC)
WHERE  rownum < 10  

User Modifications - Who,When, How
SELECT *
FROM   fusion.ase_audit_user_changes
ORDER  BY audit_user_changes_id desc 




To track the lifecycle of a security event

Example: 

SELECT SA.action_type,
       SA.action_name,
       SAV.action_id,
       SAV.attribute_name,
       SAV.attribute_value_text,
       SAV.created_by,
       SAV.last_updated_by,
       SAV.last_update_date
FROM   fusion.ase_staged_action SA
       JOIN fusion.ase_staged_action_value SAV
         ON SA.action_id = SAV.action_id
WHERE  SA.action_type LIKE 'USER'
       AND SA.action_name = 'DELETE'
       AND To_char(SAV.last_update_date, 'YYYY-MM-DD')>  '2024-05-14' 
ORDER  BY SA.last_update_date DESC 

For All users  - Suspended/Activate and ldap request completed

SELECT pu.username          "USERNAME",
       plu.request_type     "ACCOUNT STATUS",
       plu.last_update_date "DATE",
       plu.last_updated_by  "BY"
FROM   fusion.per_ldap_users plu,
       fusion.per_users pu
WHERE  plu.user_guid = pu.user_guid
       AND plu.request_type IN ( 'ACTIVATE', 'SUSPEND' )
       AND plu.request_status = 'COMPLETE'
ORDER  BY pu.username,
          plu.last_update_date DESC   

We can filter based on specific user or date range:

SELECT pu.username          "USERNAME",
       plu.request_type     "ACCOUNT STATUS",
       plu.last_update_date "DATE",
       plu.last_updated_by  "BY"
FROM   fusion..per_ldap_users plu,
       fusion.per_users pu
WHERE  plu.user_guid = pu.user_guid
       AND plu.request_type IN ( 'ACTIVATE', 'SUSPEND' )
       AND plu.request_status = 'COMPLETE'
       AND PU.username = '<ENTER USERNAME>' 

How to use Oracle Fusion Rest API's

Oracle Fusion Applications provide a comprehensive set of REST APIs that act as gateway for essential operations: Getting data out (GET), Creating new records (POST), Updating existing data (PATCH/PUT), and Deleting (DELETE).

While Oracle's documentation serves as a necessary foundation, the sheer number of possible use cases and data combinations means it can't cover every specific scenario. It is up to us, the developers and integrators, to master the nuances of these APIs to extract maximum value.It is on us to explore them and use for our purpose. 

In this Post we will focus on how we can effectively use GET operation for Oracle Fusion rest API's.

To execute precise and efficient data requests, we need to understand the powerful query parameters available for any Fusion resource. We will be exploring the following key parameters in this post, using the purchaseOrders REST API for our examples:

1. q - Query Filter (The WHERE Clause)

2. finder - Predefined Optimized Searches

3. expand - Nested/Related Data Retrieval

4. onlyData - Suppressing Metadata( Child Links)

5. hasMore - Pagination Indicator

6. offset - Controlling Paging

7. limit - Controlling Paging

8. totalResults - Getting the Total Count

9. fields - Data Projection (Selecting specific columns)

1. q - query parameter

The q parameter is the most fundamental and frequently used method for filtering our data. It acts exactly like the WHERE clause in a traditional SQL query, allowing us to define one or more expressions to restrict the resource collection returned.

The resource collection will be queried using the provided expressions. The value of this query parameter is one or more expressions

Format: ?q=expression1;expression2

Let us take example of purchase order.

We want to query by purchase order number.

Example : https://abcd-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=OrderNumber=US164932

Now let us see how we can add more query parameters.
https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=SupplierId=300000047507499;StatusCode=CLOSED

2. Finder

Used as a predefined finder to search the collection.

Format: ?finder=<finderName>;<variableName>=<variableValue>,<variableName2>=<variableValue2>

The following are the available finder names and corresponding finder variables:

• PrimaryKey: Finds a purchase order according to the purchase order identifier.
  Finder Variables:
  • POHeaderId; integer; Value that uniquely identifies a purchase order.
• findByIntent: Finds purchase orders according to the intent.
  Finder Variables:
• Intent; string; Value that uniquely identifies the intent. Valid values are APUser, POUser, LOBUser, SSPUser, and NonAgentUser.
• POHeaderId; integer; Value that uniquely identifies the purchase order.
• SysEffectiveDate; object.

Let us take example for primary key finer with POHeaderID

3. Expand

By default, an Oracle Fusion REST endpoint, such as /purchaseOrders, is designed to return only the primary or Header details of the resource. This minimizes the initial payload size.

However, in real-world integration, we frequently need the related Child resources (like lines, distributions, or attachments) in the same API call. This is where the powerful expand parameter comes in.

The expand parameter tells the server to fetch and embed related resources directly within the main resource's JSON response, saving us from making multiple round-trip API calls.

Note: In our example we are  using query parameter for one Po to restrict the result : POHeaderId= 4, it is not mandatory

We can see in te below screenshot that lines data of the queried po also came in response:

expand=lines

We can use expand=all as well which will give us expanded response with lines, dff etc.

We've learned that expand=lines brings the immediate child elements (the PO lines) into our response. However, often we need data that is nested even deeper, such as the Schedules associated with each PO Line. 

To selectively retrieve data that is nested multiple levels deep—for example, getting the Schedules (a child of a Line, which is a child of the Header)—we need to use dot notation to define the path in the expand parameter.

Hierarchy Example: 

PurchaseOrder Header -> lines -> schedules

Goal: Get the Purchase Order Header, its Lines, and the Schedules for each line in a single, efficient call.

Syntax for Deep Expansion: We specify the parent resource (lines) and then the child resource (schedules) separated by a dot.

expand=lines,lines.schedules

https://abcd-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=POHeaderId= 4&expand=lines,lines.schedules

If we want distributions as well which is child of schedules then we can simply write like below and we will get expanded response of lines, schedules and distributions:

https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=POHeaderId= 4&expand=lines.schedules.distributions

4. onlyData

The response payload gets filtered in order to contain only data (eg. no links section for child elements )

After we use onlyData=true we get respone like below.

If we do not use onlyData=true we get the default response like below.

5. Hasmore

By default Oracle Fusion Rest API's returns 25 records in the result and if there are more records in the result then it mentions hasMore as true like below:

We can use this along with limit and offset to iterate to get remaining data untill it becomes false.

6. offset

We use this to iterate over to get the remaining records, the offset value has to be changed in every iteration and it becomes the next value to start from and get the reults with the specified limit.
By default the offset will be 0 to fetch records from starting.

We can use offset like below:

7. limit

This parameter restricts the number of records returned inside the resource collection. If the limit exceeds the record count then it will only return the available records.
It is useful in iterating to get the remaining data in subsequent calls.
We can specify limit upto 500 to bring records in one call.

If we use limit value mre than 500, for eg. 1000 then Oracle Fusion makes it to 500 by default as we can see in below example.

8. Total results

By default the value is set to false.
The totalResults provide us the total count of records we have for the resource we are making the call for.
If we set the value to true in the request then we get the totalResults in the response:

totalResults=true

As mentioned in the below screenshot.

9. Fields

This parameter filters the resource fields. Only the specified fields are returned.

It cannot be combined with expand query parameter. If both are provided, only fields will be considered.

Format: ?fields=Attribute1,Attribute2

Format for fields in child resource: ?fields=Accessor1:Attribute1,Attribute2

Now lets see how we can use child elements along with the parent element , like few elements from line and few from headers.

We can request like below:

https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?fields=OrderNumber,Supplier;lines:Description,LineType,LineNumber

As we can see in the blow screenshot,
OrderNumber and Supplier fields are from headers and Description,LineType and LineNumber are from line level.

Now that we have seen all the different types of parameters we can easily combine these and use in our rest calls as required.

Here is one example to fetch all POs for supplier EDF, selected fields from headers and lines, get only data to remove child links, set limit of 200 records per page and display totalResults

example request:

https://abcd.fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?q=Supplier=EDF &fields=OrderNumber,Supplier;lines:Description,LineType,LineNumber&onlyData=true&limit=200&totalResults=true

The resonse payload will look like below:

How to Work with OCI CLI

 The CLI provides the same core functionality as we have in the Console, with additional commands.
Such as the ability to run scripts, extend Console functionality.

We can use CLI from Cloud Shell or from our Local machine.

The CLI is pre-configured with our credentials and ready to use immediately from within Cloud Shell.

Some of the usecases will be :

Working on OCI functions:

We can use OCI and Fn Project  CLI  to work on OCI functions , create function,push the docker image to OCR, invoke function etc.

Working on OCI Object Storage Bulk Operation:

Many a times we need to perform bulk operation like bulk download, bulk delete , Bulk restore objects from object storage bucktes. In such cases OCI cli is really helpful.

There are many such usecases where OCI CLI works as a very powerful tool.

In this blog we will see:

• How to install OCI CLI locally
• How to Authenticate to OCI
• How to use the profile which  gets created locally
• Run few commands from Local machine for OCI Object storage

In the next post we will see more about OCI functions setup, working and testing.

 

How to install OCI CLI locally

We can follow below Oracle doc  to install CLI based on our OS:

https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm

Before installing oci-cli if we type oci and press enter in terminal it will give below message:

 

To install oci cli Run the below command:

brew update && brew install oci-cli

 It will download and install all the dependency.

 It will take few mins to complete.

 If the link has not happened then run:

 brew link oci-cli  

Once the cli is installed :

type the command oci and press enter:

we should see like below:

Now let us run the actual commands.

To do this we need to first authenticate to the OCI

so type the below command and run:

oci session authenticate 

It will ask for region to select:

Ours is us-ashburn-1 so we just need to type 50 and press enter

We may get popup like below, click on allow:

This will open to authenticate from browser.

Select your tenancy and click continue:

We can have multiple sign in options based on the identity provider setup we have in OCI.

we can select the option we want to login with:

We will take the Onelogin option, this is sso  federate one:

Once successfully logged in from browser we will get below message in browser:

Inter terminal it will be like below:

When we are doing for the first time then we it creates a DEFAULT profile in config

 which can be used to run OCI CLi commands:

Copy the below command (this is a sample command to test oci connectivity with the profile that 

got created):

oci iam region list --config-file /Users/sumit_kumar/.oci/config --profile DEFAULT --auth security_token 

We can see this in .oci folder

We can go see config contents to see the profile details :

type less config and press enter

When the session expires then we need to refresh the token with below command:

oci session refresh 

We will get message like below when token is successfully refreshed:

Now let us see how we can run commands to access OCI services.

Run the command we had copied after authentication:

oci iam region list --config-file /Users/sumit_kumar/.oci/config --profile DEFAULT --auth security_token 

This will give region list also ensures the oci connectivity from local machine.

Every time we run any command we need to give the profile for security token detail like above.

Since it is constant for every command run we can create alias for oci profile and use.

alias op='oci --profile DEFAULT --auth security_token' 

Then we just need to type the below command for region list :

op iam region list 

 Now when we authenticate next time with oci session authenticate

 , it will ask to create new profile:

We can give different profile name or same name and use accordingly.

Now let us see how we we can use oci cli to download object store objects in bulk:

 We will download the below files in bulk:

Since we are already authenticated we just need to run the command ,

with alias or with full command:

We will try with full command:

oci os object bulk-download --config-file /Users/sumit_kumar/.oci/config --profile DEFAULT 

--auth security_token --namespace-name tenancy_name  --bucket-name cusa-prod-xyz-integrations 

--prefix xyz/Alteryx/Aug23New --download-dir . 

We get response like below:

Now we will verify from directory if all 22 files were downloaded successfully:

All the files were downloaded successfully.

Thus we see that how useful OCI CLI was in this example. It helped us downlaod all the files from 

the given folder from bucket in a single click. Otherwise we would have had to download one by one 

from UI.

Like this there are many other usecases where OCI CLI helps.

One example is working on OCI Function.

In the next post we will see how we can install fn project cli, work on OCI fuctions, 

login to docker and push our OCI function docker image to OCR and invoke these functions.

Running Dynamic SQL Queries Against Oracle Fusion from a VBCS App

The Problem

Pulling ad-hoc data out of Oracle Fusion usually means writing a query inside a BI Publisher data model. That works, but it has two annoying limitations:

• Every query change means editing the data model. There's no way to just type a new query and run it.
• XML output caps out at 200 rows. Anything larger requires generating a full report from the data model instead of a quick preview.

This post walks through a small custom app that removes both restrictions: a VBCS front end where you type any SQL query, run it, and see the results in a table — with no data model edits and no row limit — via a BI Publisher data model + report that accepts the query dynamically, and an OIC integration that glues the two together.

Architecture at a Glance

Component	Role
BIP Data Model	Accepts a Base64-encoded SQL string, decodes it, opens a ref cursor, and returns the result set
BIP Report	Wraps the data model so it can be run as a proper report (no row cap)
OIC Integration	Receives the encoded query from VBCS, runs the BIP report, and returns the report bytes
VBCS App	UI for entering a query, triggering the integration, and rendering the results as a table (with CSV export)

1. BI Publisher Data Model

The core trick is a data model built on a PL/SQL block that decodes a Base64 string into a query and opens it as a ref cursor:

 

 

The data model needs two parameters:

• query1 — a string parameter that holds the Base64-encoded SQL query. String parameters cap out at 32,767 characters; 32,000 is used here to leave some headroom.
• xdo_cursor — the ref cursor returned by the PL/SQL block.

Why Base64? Passing a raw SQL string as a data model parameter tends to error out (special characters, quotes, line breaks, etc. all cause problems). Encoding it sidesteps all of that.

The data model query block is:

DECLARE

  TYPE refcursor IS REF CURSOR;

  xdo_cursor refcursor;

  var VARCHAR2(32000);

BEGIN

  var := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(:query1)));

  OPEN :xdo_cursor FOR var;

END;

 

Testing it

Now lets run a query and save the sample data so that we can use the saved sample data to create report.

Lets write a simple query(There should not be any semicolon at the end):

select invoice_id, invoice_num from ap_invoices_all

Now we need to convert this to base64 because if we pass the query directly then it will give error.

 We can use any text editor or use online tool to convert to base64.

The encoded string for above query is:

c2VsZWN0IGludm9pY2VfaWQsIGludm9pY2VfbnVtIGZyb20gYXBfaW52b2ljZXNfYWxs

Now lets pass this in our datamodel query1 parameter and click on view:

 

We will get the output like below:

 

Save this as sample data.

Now lets create a report.

2). A BIP report.

Select the data model for report:  

No need to have a layout as it will give data XMl as output. We can create template and give output type as well.

Save the report and then view report.

 

 

We can export this report as Data xml.

 

 

3). An OIC integration to run report and provide output to VBCS app

We will create a simple OIC integration which will accept the base64 encoded query from VBCS ,runs the BIP report and returns base64 report bytes to VBCS.

 

 

 

 

 

 Now call BIP report using a soap adapter:

 

 

Give attributeFormat as xml

 

 

Finally send the response back to VBCS:

 

 

4). A VBCS app to provide UI for running query and seeing results.

This is the start and end of the whole process.

In the sample app we have given a text area to enter the query ad then a run icon.

There is a progress bar, to display the progress of steps in query execution, a table to display the results and an export button to export the result in csv.

The UI looks like below:

 

Now lets run a query:

 

 

 The Result will look like below:

We can export this output to csv.

Another query example with commenting the previous one:  

 

We need to do below things in VBCS:

• From the text area whatever query we are having , we need to encode that to base64 through javascript:

Sample Function:

EncodeToBase64(arg1) {

let encoded = window.btoa(arg1);

return encoded;

}

 

The argument will be the query text from text area. 

• Call rest endpoint(the OIC integration) by passing this base64 string

• The response from integration will be base64 report byte . We need to decode that using JS.

The sample Function: 

DecodeOutput(arg1) {

let decoded = window.atob(arg1);

return decoded;

}

 

• This will return the xml, we need to parse this xml and convert to json.
•  We will use fxp(fast-xml-parser) library.

We can get that from here: https://naturalintelligence.github.io/fast-xml-parser/lib/fxp.min.js 

For the import in VBCS we need to do below things:

• Place the library in our resources/js folder.

Make an entry in index.html as follows : <script src="resources/js/fxp.min.js"></script>

 

The sample js function to convert xml to json will be:

ConvertXmlToJson(arg1) {

return new Promise(function(resolve, reject) {

let data = arg1;

const parser = new fxp.XMLParser();

let result;

try {

result = parser.parse(data);

//console.log(result);

resolve(result);

} catch (err) {

console.log(err);

reject(err);

}

});

}

 

Now we have the json. We need to display this data tin a table.

We need to have column headers and data.

We will create two variables for table column and data:

Table Column data var as any type and  table data var as ADP.

        

 

Below is the sample function to create column header for the table:

 

ParseReportJsonReturnTableHeaders(arg1) {

var ColumnArrayOfJsonObject = [];

var columnAttributes = ["headerText", "field"];

var valueVar = {};

var columnObjArray = [];

var i = 0;

const obj = JSON.parse(arg1);

var MaxHdrCounter = 1;

for (var key in obj) {

if (key == "ROWSET") {

// console.log("Rowset Key is: " + key);

//console.log("Objects under Rowset :" + obj[key]);

var count1 = Object.keys(obj[key]).length;

//console.log("Number of row item : " + count1);

for (var rowkey in obj[key]) {

if (MaxHdrCounter > 1)

break;

if (Array.isArray(obj[key][rowkey])) {

if (MaxHdrCounter > 1)

break;

console.log("it is an array");

for (var actualrowkeyArray in obj[key][rowkey]) {

if (MaxHdrCounter > 1)

break;

for (var innermostkeyArray in obj[key][rowkey][actualrowkeyArray]) {

//console.log("Actual Key is: " + innermostkeyArray);

//console.log("Actual value: " + obj[key][rowkey][actualrowkeyArray][innermostkeyArray]);

valueVar.headerText = innermostkeyArray;

valueVar.field = innermostkeyArray;

ColumnArrayOfJsonObject.push({

...valueVar

});

//console.log(JSON.stringify(ColumnArrayOfJsonObject));

MaxHdrCounter = MaxHdrCounter + 1;

}

}

} else {

for (var actualrowkeyNonArray in obj[key][rowkey]) {

//console.log("Actual Key in Non Array : " + actualrowkeyNonArray);

//console.log("Actual value in Non Array: " + obj[key][rowkey][actualrowkeyNonArray]);

valueVar.headerText = actualrowkeyNonArray;

valueVar.field = actualrowkeyNonArray;

ColumnArrayOfJsonObject.push({

...valueVar

});

console.log(JSON.stringify(ColumnArrayOfJsonObject));

MaxHdrCounter = MaxHdrCounter + 1;

}

}

}

}

}

return ColumnArrayOfJsonObject;

}

 

Similarly we need to do for data in table.

We will be using Array Data Provider.

By default it will not be available to use in the JS function. We need to import it like below:

 

At the begining(first line) we need to update like this: 

 

define(['ojs/ojarraydataprovider'], (ArrayDataProvider) => {

 

Below is  the sample function to create data for the table:

 

ParseReportJsonReturnTableData(arg1) {

var DataArrayOfJsonObject = [];

var JsonObjVar = {};

var columnName = [];

var dataRowArray = [];

var i = 0;

const obj = JSON.parse(arg1);

var MaxHdrCounter = 1;

for (var key in obj) {

if (key == "ROWSET") {

//console.log("Rowset Key is: " + key);

// console.log("Objects under Rowset :" + obj[key]);

var count1 = Object.keys(obj[key]).length;

//console.log("Number of row item : " + count1);

for (var rowkey in obj[key]) {

if (Array.isArray(obj[key][rowkey])) {

//console.log("it is an array");

for (var actualrowkeyArray in obj[key][rowkey]) {

for (var innermostkeyArray in obj[key][rowkey][actualrowkeyArray]) {

//console.log("Actual Key is: " + innermostkeyArray);

//console.log("Actual value: " + obj[key][rowkey][actualrowkeyArray][innermostkeyArray]);

columnName[i] = innermostkeyArray;

//console.log("Current Column Name is : "+columnName[i]);

dataRowArray[columnName[i]] = obj[key][rowkey][actualrowkeyArray][innermostkeyArray];

i = i + 1;

}

JsonObjVar = dataRowArray;

DataArrayOfJsonObject.push({

...JsonObjVar

});

}

} else {

for (var actualrowkeyNonArray in obj[key][rowkey]) {

// console.log("Actual Key in Non Array : " + actualrowkeyNonArray);

//console.log("Actual value in Non Array: " + obj[key][rowkey][actualrowkeyNonArray]);

columnName[i] = actualrowkeyNonArray;

//console.log("Current Column Name is : "+columnName[i]);

dataRowArray[columnName[i]] = obj[key][rowkey][actualrowkeyNonArray];

i = i + 1;

}

JsonObjVar = dataRowArray;

DataArrayOfJsonObject.push({

...JsonObjVar

});

// console.log("Data Array :"+dataRowArray);

}

}

}

}

//console.log("Data Array for Table: "+JSON.stringify(DataArrayOfJsonObject));

return new ArrayDataProvider(DataArrayOfJsonObject, {

keyAttributes: '@index'

});

}

 

 

In the action chain we will update table column data var and table data var with the response of above two functions.

 

Now in table we can map table column and data separately like below:

 

In the code mode it will look like below: 

 

Note:-  We can combine some funtions together which we saw above, to reduce total number of functions.

This is a simple app which explains flow of how we can query dynamically from VBCS  , present the data in tabular form after parsing and also providing option to download data in csv( We need to seacrh and install export data component from components).

The mapping for export data will look like below:

We can Enhance this application to have features like 

• Format Query
• Query history - Store  the executed  queries in ATP with env, query,user, unique id and status
• Rerunning the past queries - Fetch the query from ATP and allow to rerun
• Running queries for different Fusion Environments from the same page  - Create  OIC connection for diff ENV and have branches in the int for each env. Have LOV in VBCS page and accordingly pass to integration.
  
• Having links for fusion tables etc - This will help making easy to find the Fusion tables, We can give OER link for each module tables.
  

 
as shown in below screenshots.

 Before Formatting:

 

 After Formatting:

 

 

 Get the past run queries, Which we stored in ATP

 

  Select the Query which we want to rerun and click on highlighted rerun button

 

Run  queries in Different Fusion Environments , from single page without needing to open BIP of each env.

Link for Oracle OER tables for Different Modules