Oracle E-Business Suite (EBS) provides powerful Natural Language Query (NLQ) capabilities that allow users to interact with data using conversational language (see EBS Natural Language Query – Ask your E-Business Suite anything with AI). This functionality is robust for querying standard EBS tables in the following pre-defined domains:

• Discrete Costing
• Discrete Manufacturing
• Dispute (AR)
• Logistics (Inventory)
• Maintenance
• Order Management (OM)
• Payment (AP)
• People (HR)
• Process Manufacturing (GME)
• Project Manufacturing (PJM)

However, organizations often have custom data models that require similar access. Extending the NLQ framework to support custom data models enhances user experience and improves efficiency in retrieving relevant insights. Let's have a look how (surprisingly easy) this can be achieved based on a custom contract management solution we've implemented for one of our PROMATIS customers.

Revisiting the architecture

As shown in my previous blog posts the overall architecture of the E-Business Suite NLQ Feature looks as follows:

Architecture of E-Business Suite NLQ (see 3059877.1)

If we want to add an additional domain, e.g. from a custom contract management system we have to perform 3 high level steps:

1. Provide one or multiple tables or (materialized) views in the EBS APPS Schema that contains relevant data
2. Make the views/tables accessible to the XX_NLQ schema in a Read-Only fashion
3. Replicate the data model as "empty tables" in the XX_NLQ_UTIL of the ADB
4. Create a new AI Profile in the Autonomous Database
5. Manage accessibility in EBS

Providing base data

First, you should evaluate which data to make available to the Natural Language Query model. I had good experiences with de-normalized data e.g. containing all contract data. You can provide this base data e.g. as a database view. If there are tons of data and the view has to access a complex and large base data model that may contain not so much relevant data, it might be helpful to create a dedicated materialized view that is e.g. refreshed on an hourly basis and that contains all relevant data in a quickly accessible manner.

Keep in mind that the Select AI approach shown here heavily relies on the names of tables and their columns: Remember to name things (especially columns) so that they can be understood through the Large Language Model (LLM).

After having identified the relevant tables/views/MVs for a domain create synonym and grant READ access to the XX_NLQ schema within EBS:

create synonym XX_NLQ.XX_CONTRACTS_V for APPS.XX_CONTRACTS_V;
exec AD_ZD.grant_privs('READ', ‘XX_CONTRACTS_V', 'XX_NLQ');

Verify that you can access the data e.g. by using the Oracle APEX "SQL Workshop".

Replicate data model in Autonomous Database

The next and very important step is to replicate the data model (but just the model, so without any data) within the Autonomous Database 23ai running in OCI. For this you might run:

create table XX_CONTRACTS_V (
CONTRACT_NUMBER NUMBER,
CONTRACT_STATUS VARCHAR2 (30),
CUSTOMER_NAME VARCHAR2(400),
CUSTOMER_NUMBER NUMBER,
CUSTOMER_CITY VARCHAR2(400),
CONTRACT_VOLUME NUMBER,
CREATION_DATE DATE,
...

If your data in E-Business Suite resides in a table, you can just copy the DDL from e.g. Oracle SQL Developer. If it is a view you might want to do "create table xx_test as select * from XX_CONTRACTS_V where rownum<5;" and then use that xx_test table to get the DDL.

Especially for columns containing a limited number of values it may be very beneficial to add a table comment, e.g. as follows:

comment on table XX_CONTRACTS_V is "This table contains information on customer contracts in the area of XYZ.";

comment on XX_CONTRACTS_V .CONTRACT_STATUS is "The column describes the contract status. Possible values are DRAFT for not yet signed contracts, ACTIVE for currently active contracts and TERMINATED for historic contracts.";

Creating an additional AI profile in ADB

After having made the metadata available in the Autonomous Database 23ai on OCI we have to create a new AI Profile for the newly created domain. This is straightforward:

Insert into FND_NLQ_DOMAIN_PROFILE_MAP (DOMAIN_NAME,BUSINESS_TERMS,PROFILE_NAME,PROFILE_PROMPT) values
('CONTRACTS','Contracts, Verträge, Kundenverträge','EBS_NLQ_CONTRACTS_PROFILE', NULL);

Insert into FND_NLQ_DOMAIN_TABLE_MAP (DOMAIN_NAME,OWNER,TABLE_NAME) values ('CONTRACTS','XX_NLQ_UTIL','XX_CONTRACTS_V');

Then run the nlq_util_ai_profiles.sql script provided from oracle again which will create a new SQL AI profile. Then check if the profile got created in USER_CLOUD_AI_PROFILES.

Manage accessibility in E-Business Suite

Create an additional Forms Function "EBS_NLQ_CONTRACTS_PROFILE":

New Forms Function for custom Contracts table

Then add the newly created function / permission to the permission sets of your choice. After compiling security this should bring the new domain into the Ask E-Business Suite UI:

New Contracts Domain available

Some words on security

The above-mentioned approach just controls overall access to the new domain. It is crucial that you consider row level access to the data you plan to expose to your users. For this have a look at the seeded functions defined in nlq_datasec_functions.sql and create an appropriate filter for your use cases (XX_CONTRACTS_V in my example): You definitely don't want to expose the salary of your CEO to everyone 😉 You can then assign a security function as follows:

DBMS_RLS.ADD_POLICY (
object_schema => 'APPS',
object_name => 'XX_CONTRACTS_V',
policy_name => 'XX_CONTRACTS_VPD',
function_schema => 'APPS',
policy_function => 'XX_CONTRACTS_FUNC1',
statement_types => 'select'
);

Summary

The E-Business Suite Natural Language Query feature is not just powerful "out-of-the-box". As described in this blog post it is quite easy to extend the functionality for your custom data models. In various PROMATIS projects we've seen that this gives impressive insights at very low efforts.