Oracle Order Management Extensions is a powerful tool allowing adding business logic to predefined events like Save or Submit by using Groovy language.
The documentation provides accessible methods and examples for getting data from database by using public view objects and setters extension methods.
The official documentation can be found here https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/23b/faiom/methods-you-can-use-with-order-management-extensions.html#s20064223.
Sometimes it is easier to perform direct SQL query against the database, for example copying notes/attachments from customer to order on save.
The public view objects accessible in the extension Groovy code are implemented in Oracle ADF framework, which allows accessing the underlying database connection by using getDBTransaction() method.
Here is a example of invoking an SQL statement on order save:
And the code below:
As best practice – while developing the extension use context.getUserName() to limit invocation of the code for your username.
Enjoy using SQL inside your Order Management Extensions!