Oracle Fusion SQL Query for the Business Unit

 Here is the Oracle SQL Query for the Business Unit

Please use this as a reference and modify this as per the requirements. 

select distinct

FBU.BU_NAME as "Name",

HBUL.Location_name as "Location Nane",

ppnf.full_NAME as "Manager",

FBU.status as "Active Flag",

FSS.set_name as "Default Set"

from 

FUN_ALL_BUSINESS_UNITS_V FBU,

HR_BU_LOCATIONS_X HBUL,

per_person_names_f ppnf,

FND_SETID_SETS FSS

where

FBU.LOCATION_ID = HBUL.LOCATION_ID (+)

and FBU.MANAGER_ID = ppnf.person_id (+)

and ppnf.name_type (+)= 'GLOBAL'

and sysdate between ppnf.EFFECTIVE_START_DATE(+) and ppnf.EFFECTIVE_END_DATE(+)

and FBU.Default_set_id = FSS.set_id

and FSS.language ='US'

ORDER BY 1

Oracle Fusion SQL Query for the Core HR - Person Types

Here is the Oracle SQL Query for the Person Types.

Please use this as a reference and modify this as per the requirements. 

select HL.MEANING as "System Person Type"

, PPTV.USER_PERSON_TYPE as "Assignment Person Type"

, DECODE(PPTV.ACTIVE_FLAG,'Y','Active','Inactive') as "Status"

, DECODE(PPTV.DEFAULT_FLAG,'Y','Yes','No') as "Default" 

from PER_PERSON_TYPES_VL PPTV, HR_LOOKUPS HL

where 1=1

and HL.Lookup_code = PPTV.SYSTEM_PERSON_TYPE

and HL.Lookup_type = 'SYSTEM_PERSON_TYPE'

order by PPTV.SYSTEM_PERSON_TYPE

Oracle Fusion SQL Query for the Core HR Legal Entity Details.

Here is the Sample Query for the Core HR - Legal Entity Details.

Please use this as a reference and modify as per the requirements accordingly.

select

HG.GEOGRAPHY_NAME as "Country",

XEP.NAME as "Name", 

to_char(' '||to_char(XEP.LEGAL_ENTITY_IDENTIFIER)||' ') as "Legal Entity Identifier",

TO_CHAR(XEP.EFFECTIVE_FROM,'DD-Mon-YYYY','nls_date_language=english') as "Start Date",

TO_CHAR(XEP.EFFECTIVE_TO,'DD-Mon-YYYY','nls_date_language=english') as "End Date",

XEP.PSU_FLAG as "Payroll Statutory Unit Flag",

XEP.LEGAL_EMPLOYER_FLAG as "Legal Employer",

XEP_PSU.NAME as "Payroll Statutory Unit",

HPS.OVERALL_PRIMARY_FLAG as "Primary",

(case when sysdate between HPS.START_DATE_ACTIVE and HPS.END_DATE_ACTIVE

then 'Current'

else 'Past Date'

end ) as "Current Status",

(HZ.ADDRESS1||','||HZ.CITY||','||HZ.STATE||' '||HZ.POSTAL_CODE||HZ.COUNTY) as "Address",

(select meaning from fnd_lookup_values_vl where lookup_type = 'PARTY_SITE_USE_CODE' and lookup_code= HPSU.SITE_USE_TYPE) as "Purpose",

HPS.PARTY_SITE_NUMBER as "Site Number",

HZ.TIMEZONE_CODE as "Timezone"

from

XLE_ENTITY_PROFILES XEP,

XLE_ENTITY_PROFILES XEP_PSU,

HZ_GEOGRAPHIES HG,

HZ_PARTY_SITES HPS,

HZ_LOCATIONS HZ,

HZ_PARTY_SITE_USES HPSU

where 

XEP.PARENT_PSU_ID = XEP_PSU.LEGAL_ENTITY_ID(+)

and XEP.GEOGRAPHY_ID = HG.GEOGRAPHY_ID

and HG.GEOGRAPHY_TYPE = 'COUNTRY'

and sysdate between HG.START_DATE and HG.END_DATE

and XEP.PARTY_ID = HPS.PARTY_ID(+)

and sysdate between HPS.START_DATE_ACTIVE(+) and HPS.END_DATE_ACTIVE(+)

and HPS.LOCATION_ID = HZ.LOCATION_ID(+)

and HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID(+)

AND 'Legal Entity' IN (:Data_Heads)

order by XEP.NAME

Oracle Fusion HCM - Locations SQL Query

Here is the Sample SQL Query to find the HCM Locations related to data.

Please modify accordingly as per your requirements.

select

PLDFT.LOCATION_NAME as "Loc_Name",

PL.INTERNAL_LOCATION_CODE as "Loc_Code",

PLDFT.DESCRIPTION as "Loc_Description",

PLDF.ACTIVE_STATUS as "Loc_Status",

HAOUF.NAME  as "Loc_Inventory Organization",

TO_CHAR(PLDF.EFFECTIVE_START_DATE,'DD-Mon-YYYY','nls_date_language=english') as "Loc_Effective Start Date",

PAR.ACTION_REASON as "Loc_Action Reason",

FSS.SET_NAME as "Loc_Location Set",

(select TERRITORY_SHORT_NAME from FND_TERRITORIES_VL where TERRITORY_CODE = PAF.COUNTRY) as "Loc_Country",

PAF.ADDRESS_LINE_1 as "Loc_Street Address/Address 1",

PAF.TOWN_OR_CITY as "Loc_City/Prefecture",

PAF.REGION_2 as "Loc_State/Province",

PAF.POSTAL_CODE as "Loc_Zip Code/Postal Code",

PAF.ADDL_ADDRESS_ATTRIBUTE4 as "Loc_Tax District",

PAF.REGION_1 as "Loc_County",

FTV.NAME as "Loc_Time Zone Code",

(select meaning from fnd_lookup_values_vl where lookup_type = 'PER_CORRESP_LANG' and lookup_code= PLDF.OFFICIAL_LANGUAGE_CODE) as "Loc_Official Language",

PLDF.EMAIL_ADDRESS as "Loc_E - Mail",

(select meaning from fnd_lookup_values_vl where lookup_type = 'ORA_PER_GEOGRAPHIC_TREE_NODES' and lookup_code= PLDF.GEO_HIERARCHY_NODE_VALUE) as "Loc_Geographic Hierarchy",

PLDF.TELEPHONE_NUMBER_1 as "Loc_Main Phone",

PLDF.TELEPHONE_NUMBER_2 as "Loc_Fax",

PLDF.TELEPHONE_NUMBER_3 as "Loc_Other Phone",

PLDF.SHIP_TO_SITE_FLAG as "Loc_Ship-to Site",

(

case when PLDF.SHIP_TO_SITE_FLAG = 'N' 

then PLDFT2.LOCATION_NAME

end

) as "Loc_Ship-to Location",

PLDF.RECEIVING_SITE_FLAG as "Loc_Receiving Site",

PLDF.BILL_TO_SITE_FLAG as "Loc_Bill-to Site",

PLDF.OFFICE_SITE_FLAG as "Loc_Office Site",

PPNF.LIST_NAME as "Loc_Designated Receiver"

from 

PER_LOCATIONS PL,

PER_LOCATION_DETAILS_F PLDF,

PER_LOCATION_DETAILS_F_TL PLDFT,

HR_ALL_ORGANIZATION_UNITS_F_VL HAOUF,

PER_ACTION_OCCURRENCES PAO,

PER_ACTION_REASONS_VL PAR,

FND_SETID_SETS FSS,

PER_ADDRESSES_F PAF,

FND_TIMEZONES_VL FTV,

PER_PERSON_NAMES_F PPNF,

PER_LOCATIONS PL2,

PER_LOCATION_DETAILS_F PLDF2,

PER_LOCATION_DETAILS_F_TL PLDFT2

WHERE

PL.LOCATION_ID = PLDF.LOCATION_ID

and PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)

and PLDFT.language(+) = 'US'

and PLDF.INVENTORY_ORGANIZATION_ID = HAOUF.ORGANIZATION_ID (+)

and PL.ACTION_OCCURRENCE_ID = PAO.ACTION_OCCURRENCE_ID (+)

and PAO.ACTION_REASON_ID = PAR.ACTION_REASON_ID (+)

and FSS.LANGUAGE  = 'US'

and PL.SET_ID = FSS.SET_ID 

and PLDF.MAIN_ADDRESS_ID = PAF.ADDRESS_ID 

and PLDF.TIMEZONE_CODE = FTV.TIMEZONE_CODE (+)

and PLDF.DESIGNATED_RECEIVER_ID = PPNF.PERSON_ID (+) 

and PPNF.NAME_TYPE (+) = 'GLOBAL'

and trunc(sysdate) between trunc(PPNF.EFFECTIVE_START_DATE(+)) and trunc(PPNF.EFFECTIVE_END_DATE(+))

and trunc(sysdate) between trunc(PLDF.EFFECTIVE_START_DATE(+)) and trunc(PLDF.EFFECTIVE_END_DATE(+))

and trunc(sysdate) between trunc(PLDFT.EFFECTIVE_START_DATE(+)) and trunc(PLDFT.EFFECTIVE_END_DATE(+))

and trunc(sysdate) between trunc(HAOUF.EFFECTIVE_START_DATE(+)) and trunc(HAOUF.EFFECTIVE_END_DATE(+))

and trunc(sysdate) between trunc(PAF.EFFECTIVE_START_DATE(+)) and trunc(PAF.EFFECTIVE_END_DATE(+))

and trunc(sysdate) between trunc(PLDF2.EFFECTIVE_START_DATE(+)) and trunc(PLDF2.EFFECTIVE_END_DATE(+))

and trunc(sysdate) between trunc(PLDFT2.EFFECTIVE_START_DATE(+)) and trunc(PLDFT2.EFFECTIVE_END_DATE(+))

and PLDF.SHIP_TO_LOCATION_ID = PL2.LOCATION_ID (+)

and PL2.LOCATION_ID = PLDF2.LOCATION_ID (+)

and PLDF2.LOCATION_DETAILS_ID = PLDFT2.LOCATION_DETAILS_ID (+)

and PLDFT2.language (+) = 'US'

order by PLDFT.LOCATION_NAME 

Oracle AI Agents - A Brief Summary

 🚀 Oracle AI Agents: A Technical Deep Dive with Practical Examples (2025 Edition)

Oracle continues to expand its AI-driven capabilities across the Fusion Cloud ecosystem, and one of the most transformative additions is Oracle AI Agents. These intelligent, task-oriented agents are designed to automate workflows, analyze data, support users conversationally, and integrate seamlessly with enterprise-grade business processes.

Whether you’re a technical consultant, architect, developer, or ERP functional specialist, understanding AI Agents is now critical — especially as organizations modernize their Oracle Cloud implementations with autonomous capabilities.

This blog explains what Oracle AI Agents are, how they work, their architecture, supported models, and technical examples you can adapt for real implementations.

---

🔍 What Are Oracle AI Agents?

Oracle AI Agents are domain-aware, context-driven, LLM-powered autonomous digital agents that can:

• Process natural language queries

• Retrieve and reason over enterprise data

• Take actions (create transactions, trigger workflows, validate data, update records)

• Integrate with Oracle Fusion Cloud apps (Financials, SCM, HCM, CX)

• Automate repetitive or complex tasks

• Respond conversationally to end users

Oracle AI Agents sit on top of:

• OCI Generative AI Service (LLM foundation models)

• OCI Functions & Integration

• Fusion Cloud REST APIs

• Oracle Knowledge Models & Industry Ontologies

This allows them to understand enterprise semantics such as:
“Create a Payables invoice for Supplier X based on this PDF”
or
“What are the failed journal import batches for period May-25?”

---

🏛️ Architecture Overview of Oracle AI Agents

A typical Oracle AI Agent interaction flows through the following layers:

User Prompt → Oracle Digital Assistant / OCI GenAI → Agent Orchestration Layer → 
Fusion Cloud Connectors → REST APIs / Data Access → Response → User

Key Components

1️⃣ OCI Generative AI Models

• LLMs for natural language understanding

• Embedding models for semantic search

• Fine-tuned enterprise reasoning models

2️⃣ Agent Orchestration Layer

• Tool calling

• Workflow sequencing

• Retrieval-augmented generation (RAG)

• Guardrails / constraints / auditing

3️⃣ Fusion Cloud Connectors

• Financials (AP, AR, GL)

• Procurement

• HCM

• SCM

• PaaS Custom Extensions

4️⃣ Integration with Business Processes

• REST APIs

• BIP/OTBI queries

• B2B Integrations

• Event triggers

---

🧠 How Oracle AI Agents Think: The Technical Flow

1. User enters a prompt
   e.g., “Find all unapproved invoices over $10,000 and prepare them for review.”

2. LLM processes intent + identifies required actions

3. Agent determines necessary data sources and tools
   (Fusion AP REST API, OTBI, BI Publisher)

4. Agent retrieves data using secure connectors

5. Agent executes workflow steps
   (e.g., flag invoices, assign reviewers, create summary)

6. Agent generates a final natural-language response
   Possibly with structured outputs, tables, or actionable steps.

---

📘 Real Technical Examples of Oracle AI Agents

Below are several practical examples illustrating how AI Agents operate in Oracle Cloud environments.

---

📝 Example 1: Oracle Financials – Automated Invoice Creation Agent

User Prompt

“Create a Payables invoice for ABC Supplier using this PDF attachment. The amount is $12,450, and the charge should go to cost center 510.”

Agent Workflow

1. Reads PDF using OCI Document Understanding.

2. Extracts vendor name, invoice amount, invoice date, and PO references.

3. Maps extracted values to Fusion AP Invoice fields.

4. Calls REST API:

POST /fscmRestApi/resources/latest/invoices

5. Validates distributions, accounting date, and cost center.

6. Returns confirmation.

Sample Agent Response

Invoice Created Successfully
-----------------------------------
Invoice Number: INV-88291
Supplier: ABC Supplier
Amount: $12,450
Cost Center: 510
Status: Validated and Ready for Approval

---

📘 Example 2: GL Journal Error Diagnostic Agent

User Prompt

“Why did my May ledger import fail? Show me the errors and recommend fixes.”

Agent Actions

• Calls REST API:
  /fscmRestApi/resources/latest/journalImportBatches

• Retrieves error lines from:
  /fscmRestApi/resources/latest/journalImportErrors

• Performs reasoning and categorization

• Suggests automated corrections

Sample Agent Output

Your May journal import failed for 42 lines.

Root Causes Identified:
1. Invalid account combination (17 lines)
2. Missing conversion rate (6 lines)
3. Closed period errors (19 lines)

Recommended Fixes:
• Auto-correct invalid combinations using Segment Rules  
• Reopen period MAY-25 if appropriate  
• Add USD-EUR conversion rate for 05/25

Would you like me to generate a corrected journal file?

---

📘 Example 3: Procurement Agent – Supplier Risk Assessment

User Prompt

“Run a risk assessment on Supplier InnoTech for the past 12 months.”

Agent Workflow

• Queries supplier transactions

• Combines ERP data with external risk score data via OCI Data Integration

• Uses an embedding model to compare patterns

• Generates a risk summary

Sample Output

Supplier: InnoTech Ltd
Risk Rating: MEDIUM

Findings:
• Payment Delays: 4 instances
• Contract Breach Alerts: 1 record
• External Risk Score Change: 20% increase due to credit downgrade

Suggested Actions:
- Review contract renewal
- Initiate quarterly performance tracking
- Enable risk alerts for high-value POs

---

📘 Example 4: HCM Agent – Employee Onboarding Automation

User Prompt

“Start onboarding for John Roberts in the Finance Department.”

Agent Workflow

• Creates a new hire request

• Generates tasks: equipment, training, payroll setup

• Assigns onboarding checkpoints

Sample Output

Onboarding Started for: John Roberts

Tasks Assigned:
✓ Laptop & Software Requests  
✓ Payroll Activation  
✓ Compliance Training  
✓ Manager Orientation Session

✅ Conclusion

Oracle AI Agents are transforming enterprise automation by combining:

• LLM intelligence

• Fusion Cloud integrations

• RAG and domain-aware reasoning

• Automated workflows

• Real-time decision assistance

They dramatically reduce manual effort, eliminate repetitive tasks, and improve data accuracy — powering the next generation of AI-driven ERP operations.

As organizations adopt Oracle Cloud at scale, AI Agents will become essential for efficiency, accuracy, and intelligent decision-making across all business functions.