AskHareesh.com

Extract Fusion data using BQL in Oracle RODS

This article explains how to extract Oracle Fusion data using Business Query Language (BQL) queries through Oracle RODS REST APIs..

1. Prepare the BQL Query

You can construct a BQL query directly using Oracle Business Views and their attributes, or use an Oracle AI Agent to convert standard SQL into BQL.

(Refer to this article on how to Transform SQL Queries to BQL Using Oracle AI Agent Studio for step-by-step instructions.)

2. REST API structure

HTTP Method: POST
Endpoint URL: https://fa-test1.fa.ocs.oraclecloud.com/api/boss/extraction/extract
Request Body:

{

"parameters": {

"pHeaderId": "300005169788183"

"viewQueries": {

"SalesOrderBO": {

"module": "oraScmCoreOrderMgmtOrders",

"businessObject": "SalesOrder",

"view": "salesOrdersExtract",

"fieldAliases": {

"headerId": "headerIdValue",

"orderNumber": "orderNumberValue"

}

"namedSelects": [

{

"name": "dooHeadersAllNamedSelect",

"select": "SELECT salesOrderBO.headerIdValue, salesOrderBO.orderNumberValue FROM SalesOrderBO salesOrderBO WHERE salesOrderBO.headerIdValue = :pHeaderId"

}

"select": "SELECT dooHeadersAllNamedSelect.headerIdValue, dooHeadersAllNamedSelect.orderNumberValue FROM dooHeadersAllNamedSelect",

"return": [

{

"name": "headerIdValue",

"type": "int64"

{

"name": "orderNumberValue",

"type": "string"

}

]

}

3. Send the Request & Review Response

A successful request returns the extracted field data in JSON format:

Response:

[

{

"headerIdValue": 300005169788183,

"orderNumberValue": "24022004658"

}

]

Transform SQL Queries to BQL using Oracle AI Agent

This article explains how to use the built-in, template-driven workflow in Oracle Fusion AI Agent Studio to convert standard SQL queries into Business Query Language (BQL) for data extraction with Oracle RODS APIs.

1. Navigate to AI Agent Studio

In Oracle Fusion Cloud, go to Tools -> AI Agent Studio.

2. Search for the Pre-Built Agent Template

On the home page, search for the Data Extraction Query Transformer Workflow agent.

3. Create the Agent Team from Template

Click Use Template.
Enter an Agent Name and Agent Code (a simple approach is to add a custom prefix or suffix to the standard name).

4. Review the Created AI Agent

Once created, your AI Agent workflow is ready for testing.

5. Test the Query Transformation

Provide an Oracle SQL query prompt to the agent. For example

SELECT header_id, order_number FROM doo_headers_all WHERE header_id = 123456;

The Agent validates the input query and requests human approval before proceeding.

Once approved, the Agent converts the SQL query into the equivalent BQL query.

6. Test the BQL using Oracle RODS REST APIs.

Refer this article How to Extract Fusion data using BQL in Oracle RODS

How to Create a Project in OIC (Oracle Integration Cloud)

This article explains how to create a new project in OIC.

1. Navigate to Projects

From the main left-hand navigation menu in OIC, go to Projects and click the Add button.

2. Start Project Creation

Choose the option to create a new project from scratch.

3. Configure Project Details & Permissions

Enter a descriptive Name and unique Identifier for your project.
Add an optional Description to summarize the project's purpose.
Select the checkbox to manage team access, allowing other users to edit, view, or monitor the project as needed.

4. Access the Project Workspace

Click on your newly created project from the list to open its workspace and begin adding integrations, connections, and lookups.

How to download Flexfiled Archives in Oracle Fusion

This article explains how to download Extensible Flexfield archives in Oracle Fusion.

1. Navigate to Setup and Maintenance

Click on your Profile Icon in the top-right corner, then select Setup and Maintenance --> Search.

2. Search for Flexfield Task

In the search bar, search for and select Manage Extensible Flexfields.

3. Locate the Target Flexfield

Search for the specific flexfield you need (e.g., DOO_HEADERS_ADD_INFO).

4. Download the Flexfield Archive

Select the flexfield row, click on the Actions menu, and select Download Flexfield Archive.

5. Save the Downloaded File

The flexfield archive will automatically download as a .zip file to your local computer.

Data Extract for Item Extensible Flexfields - Oracle RODS

This article explains how to extract Item Extensible Flexfields (EFF) data from Oracle Fusion using Oracle RODS.

1. Navigate to Flexfield Management

Go to Setup and Maintenance --> Search, and search for Manage Extensible Flexfields.

2. Deploy the Item Flexfield

Search for the Item Flexfield code EGO_ITEM_EFF.

Select it and click Deploy Flexfield to publish its attributes to RODS business views.

3. Open Data Extraction

In Oracle Fusion, navigate to Tools --> Data Extraction.

4. Create a New Data Extract

Click to create a new data extract definition.

5. Configure Extract Details & Select Attributes

Enter a Name and Description for the extract.
Specify an Initial Extract Date if required.
Under the Product Lifecycle Management category, select Item extended attribute views.
Choose the specific attributes you want to include in the extract.

6. Schedule the Extract

Set up the schedule options for running the data extract job.

7. Verify & Download Extracted Data

Monitor the scheduled job to confirm successful completion, then download the generated data files.

How to delete Extract Schedules in Oracle RODS using REST APIs

This article explains how to delete the extract schedules in Oracle RODS using REST APIs.

Deleting simple or immediate extract schedules manually—one by one—in the Oracle RODS UI can be tedious and time-consuming. Instead, you can automate this process by retrieving the extract schedule IDs and deleting them via REST APIs.

1. Retrieve the Extract Schedule ID

Fetch the list of extract schedules to find the specific schedule ID you want to delete.

(Refer to article on how to fetch extract schedules using REST APIs for full details.)

2. Locate the $id Field

From the JSON response payload of the extract schedule, locate and copy the $id attribute value (for example: 300005596387893).

3. Send the REST DELETE Request

Perform a DELETE HTTP request, passing the schedule's $id at the end of the API endpoint URL and use OAUTH2.0 with BOSS scope.

HTTP Method: DELETE
Endpoint URL: https://<your-instance>.oraclecloud.com/api/boss/data/objects/ora/scmCore/dataExtract/v1/extractSchedules/300005596387893

Verification

A successful request will return an HTTP 204 No Content status code, confirming that the extract schedule has been permanently removed.

Before delete:

After delete:

Dynamically update REST Adapater Invoke connections in OIC

This article explains how to dynamically select and switch REST Adapter connections at runtime in Oracle Integration Cloud (OIC).

1. Create REST Connections

Create two separate REST connections in your OIC instance, each configured with a different Fusion instance URL.

Use this article to create connections.

2. Configure the Trigger Interface

Create a new integration and add an input query parameter to the trigger endpoint so the caller can specify which target environment to use.

3. Define a Connection Identifier Variable

Create an integration variable to store the target connection identifier dynamically.

4. Assign Connection Identifier via Switch Case

Use a Switch activity to evaluate the input query parameter and assign the appropriate connection identifier to your variable (e.g., HP_REST_TEST_1 or HP_REST_TEST_2).