General
According to Oracle documentation, the objects developers can access inside Order Management (OM) extensions are limited. The full accessible list is described in this Oracle documentation.
In one of our previous posts, we demonstrated a solution that enabled the creation of SQL-based ViewObjects: Add power of SQL into SaaS Order Management Extensions. However, in a recent change, Oracle has closed this option. While “old” extensions using this approach continue to work and can be modified, new extensions with this approach cannot be created.
As the need for SQL-based logic remains essential for various use cases, we have designed a new solution. This approach leverages the ability of OM Extensions to perform outbound SOAP calls. It is based on the query utility described in this post and involves creating a SOAP endpoint on the Oracle Integration Cloud (OIC) side. This endpoint receives an SQL statement, uses the generic BIP report, and returns a JSON TEXT representation of the result.
Since OM extensions are “isolated” and cannot utilize functions from other extensions, the design pattern is to copy the “utility” code—which receives the SQL and returns a List of results—at the bottom of each new extension. The actual logic is then implemented by looping through the result list.
High Level Implementation Details
- Create a SOAP endpoint in OIC that accepts an SQL query and invokes the generic BIP report to execute it.
- In the OM Extension, build a SOAP payload with the SQL query.
- Invoke the OIC SOAP service from the OM Extension.
- Parse the JSON response returned by OIC.
- Process the results in the extension logic.
Detailed Implementation Details
WSDL for OIC SOAP Endpoint
Here is a sample WSDL file for the OIC SOAP service:
<wsdl:definitions
name="CustomSendSQL"
targetNamespace="https://custom.oraclecloud.com/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xmlns:tns="https://custom.oraclecloud.com/"
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
xmlns:ns1="http://schemas.xmlsoap.org/soap/http">
<wsdl:types>
<xs:schema
attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="https://custom.oraclecloud.com/"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns="https://custom.oraclecloud.com/">
<xs:complexType name="CustomSendSQLRequest">
<xs:sequence>
<xs:element name="query" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="CustomSendSQLResponse">
<xs:sequence>
<xs:element name="response" nillable="true" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:element name="CustomSendSQLRequest" nillable="true" type="CustomSendSQLRequest"/>
<xs:element name="CustomSendSQLResponse" nillable="true" type="CustomSendSQLResponse"/>
</xs:schema>
</wsdl:types>
<wsdl:message name="CustomSendSQL"> <wsdl:part element="tns:CustomSendSQLRequest" name="CustomSendSQLRequest"/> </wsdl:message>
<wsdl:message name="CustomSendSQLResponse"><wsdl:part element="tns:CustomSendSQLResponse" name="CustomSendSQLResponse"/></wsdl:message>
<wsdl:portType name="CustomSendSQL">
<wsdl:operation name="CustomSendSQL">
<wsdl:input message="tns:CustomSendSQL" name="CustomSendSQL"/>
<wsdl:output message="tns:CustomSendSQLResponse" name="CustomSendSQLResponse"/>
</wsdl:operation>
</wsdl:portType>
<wsdl:binding name="CustomSendSQLBinding" type="tns:CustomSendSQL">
<soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
<wsdl:operation name="CustomSendSQL">
<soap:operation soapAction="CustomSendSQL" style="document"/>
<wsdl:input name="CustomSendSQL"> <soap:body use="literal"/></wsdl:input>
<wsdl:output name="CustomSendSQLResponse"><soap:body use="literal"/></wsdl:output>
</wsdl:operation>
</wsdl:binding>
<wsdl:service name="CustomSendSQLService">
<wsdl:port binding="tns:CustomSendSQLBinding" name="CustomSendSQLPort">
<soap:address location="https://custom.oraclecloud.com/services"/>
</wsdl:port>
</wsdl:service>
</wsdl:definitions>Sample Extension Groovy Code
Below is a sample Groovy code for the OM Extension. The utility code for invoking the service and parsing the JSON is included at the bottom and should be copied into each new extension.
import oracle.apps.scm.doo.common.extensions.ValidationException
import oracle.apps.scm.doo.common.extensions.Message
// Start extension logic
String headerId = header.getAttribute("HeaderId");
// Build SOAP payload
String payLoad ="""
select order_number from DOO_HEADERS_ALL where header_id = ${headerId}
""";
// Parse JSON string
List resultList = getQueryResult(payLoad)
if (resultList == null || resultList.isEmpty()) {
throw new ValidationException(new Message(Message.MessageType.ERROR, "Parsed JSON is empty or invalid"))
}
for (row in resultList) {
Map dataRow = row as Map
def orderNum = dataRow.ORDER_NUMBER
}
// End Extension logic
// Start utility code - copy to each new extension
List getQueryResult(String payloadParam){
String payLoad ="<custom:CustomSendSQLRequest xmlns:custom=\"https://custom.oraclecloud.com/\">";
payLoad +="<custom:query><![CDATA[";
payLoad +=payloadParam;
payLoad +="]]></custom:query>";
payLoad += "</custom:CustomSendSQLRequest>";
// ======================
// JSON PARSER METHODS
// ======================
List parseJson(String json) {
if (json == null || json.trim().isEmpty()) return []
json = json.replaceAll(~/\n/, "").trim()
String startChar = json.substring(0,1)
if (startChar == "[") {
return parseJsonArray(json)
} else if (startChar == "{") {
return [parseJsonObject(json)]
} else {
return []
}
}
List parseJsonArray(String jsonArray) {
List list = []
int length = jsonArray.length()
int index = 1 // skip '['
while (index < length - 1) {
String c = jsonArray.substring(index, index + 1)
if (c == "{") {
int end = getNearestEnd(jsonArray, index, "{", "}")
list.add(parseJsonObject(jsonArray.substring(index, end + 1)))
index = end + 1
} else if (c == "[") {
int end = getNearestEnd(jsonArray, index, "[", "]")
list.add(parseJsonArray(jsonArray.substring(index, end + 1)))
index = end + 1
} else if (c == "," || c == " " || c == "\n" || c == "\t" || c == "\r") {
// skip separators and whitespace
index++
} else {
index++
}
}
return list
}
Map parseJsonObject(String jsonObject) {
Map map = [:]
int length = jsonObject.length()
int index = 1 // skip '{'
String key = ""
String state = "none" // "none" or "value"
while (index < length - 1) {
String c = jsonObject.substring(index, index + 1)
if (c == "\"") {
// Handle quoted string
if (state == "none") {
int keyStart = index + 1
int keyEnd = jsonObject.indexOf("\"", keyStart)
key = jsonObject.substring(keyStart, keyEnd)
index = keyEnd
} else if (state == "value") {
int valStart = index + 1
int valEnd = jsonObject.indexOf("\"", valStart)
map[key] = jsonObject.substring(valStart, valEnd)
state = "none"
key = ""
index = valEnd
}
} else if (c == ":") {
state = "value"
} else if (c == "{") {
int objEnd = getNearestEnd(jsonObject, index, "{", "}")
map[key] = parseJsonObject(jsonObject.substring(index, objEnd + 1))
state = "none"
key = ""
index = objEnd
} else if (c == "[") {
int arrEnd = getNearestEnd(jsonObject, index, "[", "]")
map[key] = parseJsonArray(jsonObject.substring(index, arrEnd + 1))
state = "none"
key = ""
index = arrEnd
} else if (state == "value" && (c == "-" || (c >= "0" && c <= "9"))) {
// Detect numeric value (integer or decimal)
int valStart = index
while (index < length - 1) {
String ch = jsonObject.substring(index, index + 1)
if (!((ch >= "0" && ch <= "9") || ch == "." || ch == "E" || ch == "e" || ch == "-")) break
index++
}
String numStr = jsonObject.substring(valStart, index)
try {
if (numStr.contains(".")) {
map[key] = Double.parseDouble(numStr)
} else {
map[key] = Long.parseLong(numStr)
}
} catch (Exception e) {
map[key] = numStr // fallback as string
}
state = "none"
key = ""
index--
} else if (state == "value" && jsonObject.substring(index).startsWith("true")) {
map[key] = true
state = "none"
key = ""
index += 3
} else if (state == "value" && jsonObject.substring(index).startsWith("false")) {
map[key] = false
state = "none"
key = ""
index += 4
} else if (state == "value" && jsonObject.substring(index).startsWith("null")) {
map[key] = null
state = "none"
key = ""
index += 3
} else if (c == "," || c == " " || c == "\n" || c == "\t" || c == "\r") {
// ignore whitespace
}
index++
}
return map
}
int getNearestEnd(String json, int start, String open, String close) {
int count = 1
int index = start
int length = json.length()
while (count > 0 && index < length - 1) {
index++
String c = json.substring(index, index + 1)
if (c == open) count++
else if (c == close) count--
}
return index
}
def serviceInvoker = context.getServiceInvoker()
try {
def response = serviceInvoker.invokeSoapService("CustomSendSQL", payLoad)
def nodeList = response.getSoapBody().getElementsByTagNameNS("*", "response")
if (nodeList == null || nodeList.getLength() == 0) {
return
}
String response_text = ''
response_text = nodeList.item(0).getTextContent()
// response_text = response_text.replaceAll(/(\r|\n)/, '')
// Unwrap JSON if enclosed in quotes
if (response_text.startsWith("\"") && response_text.endsWith("\"")) {
response_text = response_text.substring(1, response_text.length() - 1)
response_text = response_text.replaceAll('\\\\\"', '"')
}
// Parse JSON string
List resultList = parseJson(response_text)
if (resultList == null || resultList.isEmpty()) {
throw new ValidationException(new Message(Message.MessageType.ERROR, "Parsed JSON is empty or invalid"))
}
return resultList
}
catch(Exception e) {
throw new ValidationException(new Message(Message.MessageType.ERROR, "Failed to call service: " + e.getMessage()))
}
}This solution provides a robust workaround to the new limitations, enabling continued use of SQL logic in OM Extensions through integration with BIP and OIC. If you have any questions or need assistance with implementation, feel free to contact us.