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"

    }

]