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