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.