How to use SQL in Groovy script Order Management Extensions (validations) Fusion Cloud

Oracle Order Management Extensions is a robust tool that enables adding custom business logic to predefined events, such as “Save” or “Submit,” using the Groovy programming language.

The official documentation provides methods and examples for accessing database data through public view objects and extension methods. You can access the documentation here: Oracle Documentation.

In certain cases, it may be simpler to execute a direct SQL query against the database. For instance, you might copy notes or attachments from a customer to an order on save.

Public view objects used within extension Groovy code are built on the Oracle ADF framework, enabling direct access to the underlying database connection via the getDBTransaction() method.

Below is an example of executing an SQL statement during the order save event:

import oracle.apps.scm.doo.common.extensions.ValidationException;
import oracle.apps.scm.doo.common.extensions.Message;

def headerId = header.getAttribute("HeaderId").toString();
def vo = context.getViewObject("oracle.apps.scm.doo.publicView.analytics.HeaderPVO");  
def rows = vo.findByKey(headerId, 1);
if (rows.size() == 0) return;
 
def originalHeader = rows[0];
def qvo = "select 5 V_NUM from dual";

def qvoAttr = originalHeader.getDBTransaction().createViewObjectFromQueryStmt(qvo);
qvoAttr.executeQuery();
def attrRec = qvoAttr.first(); 

if (attrRec.V_NUM == 5 ) {
  throw new ValidationException( new Message(Message.MessageType.ERROR, "The number is: ${attrRec.V_NUM}") );
}        

Explanation

Import Statements

import oracle.apps.scm.doo.common.extensions.ValidationException;
import oracle.apps.scm.doo.common.extensions.Message;        

These imports bring in specific Oracle classes needed for handling custom exceptions and messages in the Groovy script.

• ValidationException: This class is used to throw an exception if a validation check fails.
• Message: This class is used to create custom messages, which can be included in the exception to inform users of validation errors.

Main Code

1. Retrieving HeaderId Attribute

def headerId = header.getAttribute("HeaderId").toString();        

• header.getAttribute("HeaderId"): This fetches the HeaderId attribute from the header object, which represents the current order header.
• .toString(): Ensures that headerId is stored as a string, allowing it to be used consistently in other parts of the script.

2. Accessing a Public View Object

def vo = context.getViewObject("oracle.apps.scm.doo.publicView.analytics.HeaderPVO");        

• context.getViewObject(...): Retrieves a view object (VO) instance by name. This is a public view object that provides access to the database view HeaderPVO, containing order header details.

3. Finding a Row by Key

def rows = vo.findByKey(headerId, 1);        

• vo.findByKey(headerId, 1): Searches the HeaderPVO view object for a row matching the specified headerId. The 1 is the expected number of rows to return.
• rows: This variable holds the result, which will be a collection of rows (though, here, we expect one row at most).

4. Checking if Row Exists

if (rows.size() == 0) return;        

• rows.size() == 0: Checks if no rows were found with the specified headerId.
• return: Exits the script if no matching row exists, meaning there’s no further processing needed.

5. Accessing the Original Header Row

def originalHeader = rows[0];        

• rows[0]: If a row was found, it’s accessed as the first element in rows.
• originalHeader: This variable now references the specific header row retrieved.

6. Defining a SQL Query for a Site-Level Attribute

def qvo = "select 5 V_NUM from dual";        

• qvo: This defines a simple SQL query that selects a static value (5) and labels it V_NUM. This SQL is an example and could be replaced with a query relevant to the use case.
• V_NUM: This column alias is used to retrieve the result later.

7. Creating and Executing the Query View Object

def qvoAttr = originalHeader.getDBTransaction().createViewObjectFromQueryStmt(qvo);
qvoAttr.executeQuery();        

• originalHeader.getDBTransaction(): Gets the current database transaction for executing SQL queries.
• .createViewObjectFromQueryStmt(qvo): Creates a view object from the SQL query defined in siteLevelAttribute.
• qvoAttr.executeQuery(): Executes the SQL query, making the data accessible for retrieval.

8. Accessing the First Record of the Result Set

def attrRec = qvoAttr.first();        

• qvoAttr.first(): Retrieves the first record from the result set returned by the query.
• attrRec: This holds the first result row, which includes the V_NUM value defined in the query.

9. Checking the Attribute Value and Throwing an Exception

if (attrRec.V_NUM == 5 ) {
  throw new ValidationException( new Message(Message.MessageType.ERROR, "The number is: ${attrRec.V_NUM}") );
}        

• attrRec.V_NUM == 5: Checks if the V_NUM attribute from the query result equals 5.
• throw new ValidationException(...): If the condition is true, a ValidationException is thrown to halt further processing.
• new Message(...): A Message object is created with the message type ERROR and the text "The number is: ${attrRec.V_NUM}".
• ${attrRec.V_NUM}: This syntax injects the actual value of V_NUM into the message, making the error message dynamic.

This code essentially checks for a specific condition (in this case, if a number equals 5) and throws an exception to inform the user when the condition is met.

At Inlab Ltd, we provide comprehensive Oracle Fusion Cloud implementation services across all modules, including

• ????????????????????
• ??????????????????????
• ???????????? ?????????? ???????????????????? (??????)?
• ?????????? ?????????????? ???????????????????? (??????)?
• ???????????????? ???????????????????? (????)?
• ???????????????????? ?????????????????????? ???????????????????? (??????)

Our expertise also extends to:

• ?????? ?????????????????????? ??????????????????????
• ???????????? ?????????????? ???????????????????????
• ?????????????????????? ?????????????????
• ???????????? ???????????????????
• ???? ?????????????????? ???????????? ?????? ??????????????

For more information or inquiries, please get in touch with us via:

WhatsApp: +994 51 689 16 04

www.inlab.az