🔴 Oracle AI Agent Studio · Case Study

Oracle HCM AI Agents: Real-Time Leave Analytics & Smart Employee Workspaces — Live on Fusion HCM

After a full implementation cycle on Oracle Fusion Cloud, AI-powered HCM agents are now delivering real-time people insights to managers and employees — the moment they log in. No manual reports. No scheduled jobs. No configuration required from end users.

This post walks through what was built, how it works, and what the outputs look like — using representative sample data.

---

What the AI Agents Do

Three agents are deployed on Oracle Fusion HCM, each surfacing a different category of people data through natural language and dynamic visualisations:

📊

Manager Leave DashboardMonthly leave request breakdowns, annual balance tracking per direct report, and team leave rankings — generated on demand, rendered as live charts.

🏥

Employee Help WorkspaceA personalised priority inbox combining pending approvals, active learning assignments, and offboarding journeys — intelligently surfaced, not manually searched.

📈

Absence Trend AnalyticsAutomated charts showing leave taken by absence type across the year, with AI-generated key insights delivered without any report scheduling.

---

Sample Agent Outputs

⚠️ The outputs below use anonymised sample data. No real employee or client information is shown.

Manager View · Team Leave

Monthly Leave Requests — 2026

Direct report: E. Carter, Staff Nurse

Jun

2

Aug

1

Others

Jan–May, Jul, Sep–Dec: 0 requests

Manager View · Team Balance

Annual Leave Balance — 31 Dec 2026

Direct report leave balance summary

E. Carter

19.02 d

Hours152.16 hrs

Days19.02 days

As-of31/12/2026

Employee View · Help Workspace

Needs Attention & In Progress

Waiting for input

Orientation Video

Learning · 2026-08-13

Active

Clinical Preceptorship

Learning · 2026-07-04

Active

Offboarding Journey (10)

Journey

Initiated

Awaiting approval (5)

Annual Leave · 8 hrs

Leave of Absence

Pending

Time Permission · 1 hr

Leave of Absence

Pending

Employee View · Absence Trends

Leave by Absence Type — 2026

Approved & completed leave (Jan–Dec 2026)

Sick Leave

6

Annual Leave

4

Sick Leave: 9 calendar days total duration

---

Technical Stack

The entire solution runs on Oracle's native cloud stack — no custom middleware, no third-party tools:

• Oracle AI Agent Studio — Agent definition, NL query handling, data orchestration
• Oracle Fusion HCM REST APIs — Live leave, absence, learning, and journey data
• Oracle Redwood UI — Agent surface embedded in HCM pages
• BIP / OTBI — Report data sources for balance and trend analytics
• VBCS Extensions — Custom UI components where Redwood out-of-the-box falls short

The key insight: Oracle AI Agent Studio removes the need for a server-side integration layer. The agent itself handles data retrieval, NL-to-query mapping, and response rendering. Delivered to production with zero changes to the core HCM application.

---

What This Means for HCM Teams

Traditional HCM reporting requires users to navigate to reports, select parameters, run the report, and wait. AI Agents flip this: the right data is surfaced proactively, in context, in the format the user needs it — the moment they arrive on the page.

For managers this means instant visibility into team leave patterns without building a single query. For employees, it means one intelligent workspace that replaces multiple navigation steps across HCM modules. For HR teams, it means eliminating a class of ad-hoc reporting requests entirely.

Oracle HCM Extracts — A Practical Guide to Building Your First Integration Extract

 HCM Extracts are Oracle's built-in mechanism for extracting structured HR data for payroll interfaces, benefits carriers, and third-party systems. If you are integrating Oracle HCM with any external system, you will almost certainly use an extract.

The Three Components of an HCM Extract

1. Data Groups — define which database objects (tables/views) to query
2. Extract Attributes — the specific columns/fields to extract from each data group
3. Extract Definitions — the output format and delivery method (flat file, XML, CSV)

Step-by-Step: Create a Basic Employee Extract

1. Navigate to Data Exchange → Manage HCM Extract Definitions
2. Click Create → Name it (e.g. "Employee Master Extract")
3. Add a Data Group linked to the PER_ALL_PEOPLE_F view
4. Add attributes: Person Number, Name, Hire Date, Department, Grade, Email
5. Add a filter: Effective Date = Effective Date Parameter
6. Set output format to CSV, delivery to UCM (Universal Content Management)
7. Submit a test run in Sandbox with a small population

✦ Always filter your extract by effective date rather than pulling all history. Full-history extracts can take hours to process for large employee populations and will overwhelm downstream systems.

Payroll Interface Extracts — Special Considerations

• Use the delivered Payroll Interface extract templates as your starting point — do not build from scratch
• The payroll interface extract must run before each payroll processing run
• Include a checksum or record count in your output file so the downstream system can validate completeness
• Schedule the extract using HCM Extracts → Schedule Processes, not manually

OTBI in Oracle Fusion HCM — Five Tricks That Make Your Reports Faster and More Useful

 Oracle Transactional Business Intelligence (OTBI) is the self-service reporting layer inside Fusion HCM. Most users know the basics — drag columns, add filters, run the report. But these five techniques will significantly improve your reports.

Trick 1 — Use Repository Variables for Dynamic Dates

Instead of hardcoding a date filter, use a presentation variable or a repository variable like CURRENT_DATE. This means "last 30 days" stays accurate without manual updates.

Trick 2 — Add a Calculated Column for Tenure

OTBI does not have a built-in tenure column. Add it as a calculated measure:

TIMESTAMPDIFF(SQL_TSI_MONTH,
  "Worker"."Hire Date",
  CURRENT_DATE)

Trick 3 — Filter on Primary Assignment Only

Without this filter, employees with multiple assignments appear multiple times. Always add:

"Assignment"."Primary Assignment Flag" = 'Y'

Trick 4 — Use Narrate View for Management Reports

The Narrate view converts your OTBI table into a written summary — useful for executive reports where leadership wants prose, not a table. Enable it under View → Narrate on any analysis.

Trick 5 — Schedule Critical Reports as Agents

Use OTBI Agents to run heavy reports overnight and deliver results by email. This prevents users from running large reports during business hours and slowing the system.

Navigate to Catalog → New → Agent, set the delivery schedule, and link your analysis.

✦ Limit your OTBI analyses to a maximum of 15 columns for performance. Reports with 30+ columns often time out — split into two analyses and join them in a dashboard if needed.

Oracle Fusion Absence Management — Accrual Plan Design That Actually Works

 Absence Management in Oracle Fusion is powerful — and easy to get wrong. The accrual plan is where most of the complexity lives. Here is a design checklist based on real implementations.

Accrual Plan Design Decisions

Decision	Options	Typical Choice
Accrual frequency	Daily, Weekly, Bi-weekly, Monthly, Annual	Monthly or Annual
Carryover rule	None, Limited, Unlimited	Limited (with max days)
Proration on hire/termination	Yes / No	Yes
Ceiling (max balance)	None / Fixed / Formula-based	Formula-based for seniority tiers
Negative balance allowed	Yes / No / Formula	Depends on policy

The Seniority Tier Problem

Many organisations give more leave to longer-tenured employees: 15 days for <5 years, 20 days for 5–10 years, 25 days for 10+ years. In Oracle Fusion, this is handled via a Band Matrix on the accrual plan, linked to a Fast Formula that returns the employee's tenure band.

/* Tenure band formula skeleton */
DEFAULT FOR PER_ASG_SERVICE_MONTHS IS 0
l_months = PER_ASG_SERVICE_MONTHS
IF l_months < 60 THEN
  ACCRUAL_RATE = 15
ELSIF l_months < 120 THEN
  ACCRUAL_RATE = 20
ELSE
  ACCRUAL_RATE = 25
END IF
RETURN ACCRUAL_RATE

✦ Test the carryover rule at year-end with a Sandbox simulation before go-live. Carryover calculations run as a batch process — errors here affect every employee's opening balance for the new year.

Oracle Fusion Payroll — The Element Configuration Mistakes That Cost You Time

 Payroll element configuration in Oracle Fusion is where most implementations slow down. Small mistakes at the element design stage multiply into calculation errors, retroactive correction runs, and hours of investigation. Here are the ones I see most often.

Mistake 1 — Wrong Element Classification

Classification controls how an element feeds into gross pay, net pay, and statutory calculations. Setting it wrong means the element computes correctly but contributes to the wrong balances.

If the earning is...	Use classification...
Regular salary / wages	Regular Earnings
Overtime, bonus	Supplemental Earnings
Employer benefit contribution	Employer Charges
Employee statutory deduction	Employee Tax Deductions

Mistake 2 — Incorrect Proration Rule

If an employee joins mid-period, proration should automatically calculate the partial period payment. If the proration rule is missing or set to "None", the system pays the full period amount regardless of the hire date.

Always set the Proration Group on earnings elements, and verify the proration formula is linked.

Mistake 3 — Forgetting Retroactive Components

When a salary change is backdated, Oracle Fusion processes retro pay automatically — but only if a Retroactive Component is configured on the element. Without it, the backdated change is silently ignored in the retro run.

✦ Test every new element through a retro scenario in Sandbox before go-live. Retro issues discovered in Production are significantly harder to correct, especially after a payroll is finalised.

Fast Formula Tip

Use EFFECTIVE_DATE in your Fast Formulas rather than SYSDATE. Payroll processes can run for historical periods — SYSDATE will give you today's date, not the period date, which breaks retro and correction runs.

/* Correct approach */
l_date = GET_CONTEXT(EFFECTIVE_DATE, '0001/01/01')

/* Not this */
l_date = SYSDATE

Generating Multi-Sheet Excel Reports from Oracle HCM Using BI Publisher (BIP)

 Standard OTBI outputs are flat tables. When stakeholders need data across multiple dimensions in separate sheets — say, one sheet per department, or one sheet per absence type — BI Publisher with a correctly structured RTF template is the answer.

The Key — Native Excel Output with Group Breaks

BI Publisher supports a native Excel output format (different from HTML-rendered Excel). To enable multi-sheet output, you need two things in your RTF template:

1. A for-each-group loop around the grouping field (e.g. Department)
2. A sheet-break command using the native Excel XML tag

<?for-each-group:EMPLOYEES;./DEPARTMENT?>
<?if:position()!=1?><?xls-sheet-break?><?end if?>
Sheet: <?current-group():DEPARTMENT?>

  <?for-each:row?>
    <?EMPLOYEE_NAME?>  <?ABSENCE_DAYS?>  <?LEAVE_TYPE?>
  <?end for-each?>

<?end for-each-group?>

Naming Each Sheet Dynamically

Use the xls:sheet tag to name each sheet after the group value:

<?xls:sheet name="<?current-group():DEPARTMENT?>"?>

✦ The output format in the BIP report definition must be set to Excel (*.xlsx) — not Excel 2003. The sheet-break tag only works with the XLSX output type.

Common Errors

Error	Cause	Fix
All data on one sheet	Missing xls-sheet-break tag	Add tag between group iterations
Sheet named "Sheet1" always	Static sheet name in template	Use current-group() in xls:sheet
Empty sheets generated	Group field has nulls	Filter nulls in the data model query

Connecting External APIs to Oracle HCM Using VBCS Service Connections

 Oracle Visual Builder Cloud Service (VBCS) is a powerful tool for extending HCM with custom UI components. One of its most useful features is the Service Connection — it lets you call any REST API and display the results directly within your HCM pages.

When to Use VBCS Service Connections

• Displaying predictions from an ML model (e.g. attrition risk score)
• Pulling data from a third-party HR system in real-time
• Triggering an external workflow from within HCM
• Showing live leave balances from a custom calculation engine

Creating a Service Connection — Steps

1. Open VBCS → Navigate to your application → Services tab
2. Click + Service Connection → Choose Define by Endpoint
3. Enter your REST API URL (e.g. your Azure ML endpoint)
4. Set Authentication: Bearer Token or Basic Auth depending on your API
5. Define the Request schema (JSON body) and Response schema
6. Click Test to verify the connection returns expected data
7. Bind the response fields to your page components

// Example request payload for Azure ML attrition endpoint
{
  "input_data": {
    "columns": ["tenure_months","absence_count","perf_rating","grade"],
    "data": [[36, 8, 3, "M3"]]
  }
}

// Response
{
  "result": [0.73]   // 73% attrition risk
}

✦ Always handle the case where the external API is unavailable. Add a fallback value or a "Data not available" message in your VBCS component — otherwise the HCM page will show a blank tile with no explanation.