How to Configure Cascading LOV Parameters in Oracle Fusion ESS job Using Value Sets

In this blog, I will explain the step by step process to implement cascading LOV parameters in Oracle Fusion ESS Job using Value Sets. In the end, we will have a custom ESS Job with two parameters where Parameter 2 value will dynamically change based on the value selected in Parameter 1.

Let's dive in.


Register a new List of Values Source

- Navigate to Setup & Maintenance

- Search for the task 'Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications'

- Go to 'Manage List of Values Sources' Tab

- Create a new LOV Source

    - Application: Application Toolkit

    - Name: Give desired name (I'm using AJ_LOV_Source as an example)

    - LOVType: Predefined

    - LOV Source definition Name: oracle.apps.fnd.applcore.flex.vst.model.publicView.ValueSetValuePVO

- Save and close


Create custom Value Sets

- We are going to create 3 Value Sets in my example:

    - Value Set 1 (AJ_Vset_1): This will be the main Value Set our Parameter 1 will refer to. This Value Set will have two values - AJ_BU and AJ_LE, which are essentially 2 separate Value Sets.

    - Value Set 2 (AJ_BU): This is the Value Set that holds Business Units

    - Value Set 3 (AJ_LE): This is the Value Set that holds Legal Entities


Create Value Set 1 (AJ_Vset_1):

- Navigate to Setup & Maintenance and search for Manage Value Sets

- Create a new Value Set named AJ_Vset_1

- Set the desired Module, set Data Type to Character and SubType to Test with length 150

- Assign two values to this Value Set - AJ_BU and AJ_LE

Create Value Set 2 (AJ_BU):

- Create a new Value Set named AJ_BU

- Set the desired Module, set Data Type to Character and SubType to Test with length 150

- Assign two values to this Value Set - BU1 and BU2

Create Value Set 3 (AJ_LE):

- Create a new Value Set named AJ_LE

- Set the desired Module, set Data Type to Character and SubType to Test with length 150

- Assign two values to this Value Set - LE1 and LE2

ESS Job Configuration

- Navigate to Setup & Maintenance

- Search for the task 'Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications'

- Create the custom ESS Job, enter the essential details like Display name, Path, Report ID (for BIP report) etc.

- In my example, I've created a test job named AJ Test as shown below

- Navigate to Parameters section

- Create Parameter 1 as follows:

    - Data Type: String

    - Page Element: List of Values

    - List of Values Source: AJ_LOV_Source (from Step 1 above)

    - Attribute: Value

    - Display Attributes: Value

- Save and close

- Select Parameter 1 and click on Manage Dependencies icon.

- Move 'ValueSetCodeCriteria' to the Selected View Criteria section

- Set the Default Value to 'AJ_Vset_1'. This is our main value set that will show list of values for other 2 value sets.

- This configuration tells the ESS engine that Parameter 1 should refer to 'AJ_LOV_Source' LOV which is a generic LOV that refers to ValueSetValuePVO source and the dependency configuration adds a where clause indicating that the engine should display values from the Value Set AJ_Vset_1


- Now create Parameter 2 as follows (similar to Parameter 1 definition):

    - Data Type: String

    - Page Element: List of Values

    - List of Values Source: AJ_LOV_Source (from Step 1 above)

    - Attribute: Value

    - Display Attributes: Value

- Select Parameter 2 and click on Manage Dependencies icon.

- Move 'ValueSetCodeCriteria' to the Selected View Criteria section

- This time, we will set the Mapped Parameter value to Parameter 1.

This configuration tells the ESS engine that Parameter 2 should refer to 'AJ_LOV_Source' LOV which is a generic LOV that refers to ValueSetValuePVO source and the dependency configuration adds a where clause indicating that the engine should display values from the Value Set returned by Parameter 1

- Save and close the ESS Job definition.

Test the output

- Navigate to Tools -> Scheduled Processes

- Search for our custom job (AJ Test in my case)

- The Process Details popup will show us the two parameters - Parameter 1 and Parameter 2

- When we click on Parameter 1, it shows us the two values AJ_BU and AJ_LE

- If we select the value AJ_BU in Parameter 1 then the Parameter 2 automatically shows us the 2 BUs from the dependent Value Set

- And if we select the value AJ_LE in Parameter 1 then the Parameter 2 automatically shows us the 2 LEs from the dependent Value Set

And that's it! With these configurations in place, we've established the cascading LOV parameters in Oracle Fusion ESS job using custom Value Sets.
The example I provided uses simple Independent Value Sets with static values but this pattern can be extended to fit other types of LOVs as per your actual requirements.

Read More

Fusion Query Studio: Run SQL queries against Oracle Fusion from within VS Code

If you are a developer or a power user who has spent real time in building queries, reports or even troubleshooting data in Oracle Fusion, you are already know that there's no easy way to run a SQL query against Fusion database and see the results. The only out-of-the-box way is to create a data model in BI Publisher and test your queries, lookup and analyze the data.
This is because Fusion is SaaS and we don't get a database connection like other on-prem systems might be able to provide; but this limitation slows us down since we have to go through multiple steps just to query the data.

I'd been wondering what if there's a simple tool that lets you connect to Fusion environments, run your queries against it and simply see the results. A simple basic framework that just works.

I'd been working on building such a solution and it's now live for everyone to use.

Introducing Fusion Query Studio - A VS Code extension that lets you connect to a Fusion environment and run your queries against it from within the editor.

Fusion Query Studio features

Multiple saved connections: Store your multiple Fusion environments (Dev, Test, and Prod etc.) side by side with passwords encrypted in the Windows Credential Vault.

Live results table: The results table provides features such as sort by columns, null values highlighting etc.

Query history: The extension stores your last 50 queries automatically. You can access them from Query History panel and re-run without retyping it.

Simple CSV export: Export the query results to a file with a single click.

AI Chat integration: Ask VS Code's native AI Chat (GitHub Copilot) to build and run a query for you through the extension. AI will ask/tell you which connection it used, show you the actual SQL it built, and it will run it in background using the Fusion Query Studio extension and present you the results. You can also copy-paste the AI-generated query in the Query Editor panel, run it and see the results in the results panel.

SSO-based login: This feature is not available in the current version but it's on the roadmap.

Getting Started

1. Install the extension:

Search for Fusion Query Studio in VS Code Marketplace  and Install the extension

Install:

2. Launch the extension:

You'll see the Fusion Query Studio icon show up in the activity bar on the left side of VS Code.

3. Add a connection: 

In the Connections panel, click the + button.

Follow the 4 step wizard and provide below details:

Connection name: Connection name of your choice.

Base URL: Your Fusion environment's URL. e.g. https://fa-ebfa-dev1.us1234.oraclecloud.com

Username: User name of the account under which the SQL queries will be executed.

Password: Password of the above user. This gets encrypted and stored in the Windows Credential Vault. It's never written to disk in plaintext.

You will see a confirmation that says the connection is saved.

Please Note: In case you see an error message that says 'Could not verify/deploy utility report', then you may need to verify a few things:

- Verify if your connection details are correct (Base URL, Username and Password)
- Verify that the User account you are using has roles/permissions to create/execute BI publisher reports.

4. Test the connection:

Click ▶ icon in front of your connection name (Fusion_Dev in my example) to test the connection.

If there are no errors, we should see the confirmation message.

5. Edit / Delete the connection:

You can Edit or Delete the connection by clicking the respective icons in front of your connection name.

Edit:

Delete:

6. Open the Query Panel:

Click Open Query Panel icon ▶ in the toolbar to open the Query Panel. This is the most easy and common way to access the query panel.

Optionally, you can run the command Fusion Query Studio: Open Query Panel from the Command Palette (Ctrl+Shift+P) to do the same.

From Toolbar:

From Command Palette:

You should be presented with the Query Panel as shown below:

7. Run a query: Select the desired connection from drop-down, write your SQL in the top panel and hit the Run button (or press Ctrl+Enter).This will execute your SQL query and present the results to you in the results panel.

Things to remember:

- You can control how many rows come back by adjusting the Limit value.

- Please skip the trailing semicolon. Don't end your query with ;

- Click Cancel to stop any running query.

- Adjust the divider between the editor and the results pane to resize it.

8. Let AI Chat write the query for you:

Open VS Code's built-in AI Chat (using GitHub Copilot) and describe your requirements. Fusion Query Studio seamlessly plugs into the AI Chat of VS Code.
It will ask you which saved connection to use, will build the query and show it to you before running it,  and finally present you the results.

- Describe the requirements of your query and ask it to run it using using Fusion Query Studio:

- Review the Connection details, verify the query and allow AI Chat to run the query using Fusion Query Studio:

- AI Chat will show you the entire query, run it against the selected connection and show you the results:

Query:

 Results:

- You can also copy the AI-generated query and run it manually in the Fusion Query Studio

Copy the query:

Paste it in Query Panel:

Run the query and see the results:

- You can click on CSV button to download the query result in a CSV format

- Query History panel stores your last 50 queries. You can click on any of them and they will be automatically pasted in Query Panel so that you can rerun without rewriting them.

Try it out

If you're building against Oracle Fusion Cloud environments and you're constantly following multi-step process in BI Analytics, just to test your query or validate the data - give Fusion Query Studio a shot. It's available for free on the VS Code Marketplace. If you run into anything or have ideas for what it should do next, please reach out to me at amodjjoshi@gmail.com. Happy Querying 😊

Read More

Oracle Fusion AI Agent Studio Implementation Guide: Security and Initial Configuration

Oracle Fusion AI Agent Studio is a platform that enables us to create and deploy AI Agents from within the Fusion environment.

With this platform, we can easily utilize the preconfigured agents and also build our own custom agents from scratch.

Since this platform is implicitly integrated into the Oracle Fusion applications environment, it facilitates the seamless access to Fusion business objects, APIs, and other tools pertaining to Fusion applications.

In this blog, I will cover the initial security configurations that help enable the AI Agent Studio within a given Fusion Environment.


Enable Profile Option

- Navigate to Setup and Maintenance

- Search for the task 'Manage Administrator Profile Values'

- Search for Profile Option Code 'ORA_ASE_SAS_INTEGRATION_ENABLED'

- Set the Site level value to Yes

- Save and Close


Submit ESS Jobs


- Navigate to Tools -> Scheduled Processes

- Submit the job Import Resource Application Security Data

- Once completed, submit the job Import User and Role Application Security Data

Create Custom Role


- Now, let's create a custom role that would give administrator access for HCM business area

- Navigate to Tools -> Security Console

- Create a new role and give a desired name

- Make sure to Enable Permission Groups for this role

- You will be shown a pop-up asking for confirmation. Click Enabled Permission Groups

- Once enabled, you should see a grayed-out checkbox confirming that the permission groups are enabled

- Click Next

- Go to Role Hierarchy (5) tab in the train

- Under Roles and Privileges tab, click Add Role

- In the popup window, search for role code ORA_HRC_HCM_AI_AGENT_MANAGEMENT_DUTY

- Select and click Add Role Membership

- Click Cancel to close the popup

- Navigate to the Roles and Permission Groups tab

- Click Add Role

- In the popup window, search for role code ORA_DR_FAI_GENERATIVE_AI_AGENT_HCM_ADMINISTRATOR_DUTY

- Select and click Add Role Membership

- Click Cancel to close the popup


- Navigate to Users (7) tab in the train

- Assign this role to the desired user

- Click Next

- Save and Close


With this configuration, we have successfully configured the security around AI Agent Studio for HCM business area and provided access to the desired user.

But we are not done yet. We need to do a few more configurations to make sure the administrator can access all the features of the studio, such as External REST API and AI Chat capabilities.


External REST API access

- External REST API configuration access is abled via TRAP (Token Relay And Proxy). TRAP handles the external REST calls with secure access.

- To enable this, navigate to Security Console

- Edit the custom role we just created 'AI AGENT STUDIO HCM ADMINISTRATOR'

- Go to Function Security Policies (2) tab in the train

- Under Privileges tab, click Add Function Security Policy

- Search for privilege ORA_FND_TRAP_PRIV

-  Select and click Add Privilege to Role

- Click cancel to close the popup


AI Chat Access

- To provide AI Chat access, while you are in Function Security Policies tab, click Add Function Security Policy again

- Search for policy HRC_ACCESS_AI_AGENT_CHAT_PRIV and click Add Privilege to Role

- Click cancel to close the popup

- Now, let's navigate to Role Hierarchy (5) tab in the train



- Go to Role and Permission Groups tab

- Click Add Role

- Search for ORA_DR_FAI_GENERATIVE_AI_AGENT_RUNTIME_DUTY and click Add Role Membership

- Click cancel to close the popup


With these steps, we've successfully provided all the necessary administration access to the desired admin user for the HCM business area in Fusion AI Agent Studio.


Custom Admin roles for other Business Areas

If we want to create custom admin roles for other business areas such as Procurement and Supply Chain etc. then we essentially have to follow the same steps as above but we'll have to use different Privileges and Permission Groups.


Supply Chain Administration Configuration

- Follow the same steps as mentioned in 'Create Custom Role' section with following changes

- In Role Hierarchy section, under Roles and Privileges tab, assign below roles:

    - ORA_RCS_SCM_AI_AGENT_MANAGEMENT_DUTY

    - ORA_RCS_SCM_AI_AGENT_MANAGEMENT_DUTY_HCM

- Under Role and Permission Groups tab, assign below role:

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_SCM_ADMINISTRATOR_DUTY

Procurement Administration Configuration

- Follow the same steps as mentioned in 'Create Custom Role' section with following changes

- In Role Hierarchy section, under Roles and Privileges tab, assign below roles:

    - ORA_PO_PRC_AI_AGENT_MANAGEMENT_DUTY

    - ORA_PO_PRC_AI_AGENT_MANAGEMENT_DUTY_HCM

- Under Role and Permission Groups tab, assign below role:

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_PRC_ADMINISTRATOR_DUTY

All Pillars Administration Configuration

- If we want to create a custom Administrator role that gives access to all business areas/pillars in the AI Agent Studio, then we need to use following roles and privileges.

- Follow the same steps as mentioned in 'Create Custom Role' section with following changes

- In Role Hierarchy section, under Roles and Privileges tab, assign below role:

    - ORA_FAI_MANAGE_ALL_AI_AGENTS

- Under Role and Permission Groups tab, assign below role:

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_CX_ADMINISTRATOR_DUTY

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_FIN_ADMINISTRATOR_DUTY

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_GRC_ADMINISTRATOR_DUTY

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_HCM_ADMINISTRATOR_DUTY

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_PRC_ADMINISTRATOR_DUTY

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_PRJ_ADMINISTRATOR_DUTY

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_PSC_ADMINISTRATOR_DUTY

    - ORA_DR_FAI_GENERATIVE_AI_AGENT_SCM_ADMINISTRATOR_DUTY


End User AI Chat Access (Explorer Role)

- We need to create a separate custom role to provide only the AI Chat access to the end users, so that they can use the published AI Agents and interact with them.

- For this, simply follow the steps mentioned in 'AI Chat Access' section and assign the role to the desired end users.


With this, we have covered all the essential configurations to configure the security around AI Agent Studio administration as well as end user access and we should be able to access the studio to create and publish new agents.

We can access the AI Agent Studio from Tools menu

And we should be able to enter and access the platform as shown below:

The end users can access the published agents by navigating to Me -> AI Chat

The published agents will be ready to use and can be accessed from Explore tab in AI Chat

Read More