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.

Oracle Fusion Technical Interview Questions

 

Oracle Fusion Technical Interview Questions

Conversions (Data Migration)

1. What are the different approaches to load data into Fusion Applications?

2. Explain the role of FBDI (File-Based Data Import) and ADFdi in data migration.

3. What are the key steps in performing a conversion using FBDI?

4. What is HDL (HCM Data Loader) and when would you use it instead of FBDI?

5. How do you validate and troubleshoot FBDI template load failures?

6. How do you ensure data integrity during conversions (for example, in Suppliers or Customers)?

7. Can you explain the difference between Data Migration and Data Integration in Fusion?

---

Integrations

1. What are the different types of integration options available in Oracle Fusion (SOAP, REST, FBDI, BIP, ESS jobs)?

2. What is OIC (Oracle Integration Cloud) and how does it connect with Fusion?

3. Difference between Outbound Integration and Inbound Integration with examples.

4. How do you call Fusion REST APIs for data extraction or creation?

5. What are Business Events in Fusion, and how can they be used for integrations?

6. Explain the role of BI Publisher Reports + Web Services in integrations.

7. How do you handle error management in integrations (OIC monitoring, fault policies, error hospital)?

8. How do you secure integrations between Fusion and external applications?

---

Reports

1. What are the different reporting tools available in Oracle Fusion?

2. Difference between OTBI (Oracle Transactional Business Intelligence), BI Publisher, and Smart View.

3. How do you migrate BI reports from one environment to another?

4. What is a Data Model in BI Publisher? Explain components.

5. How do you use Bursting in BI Publisher?

6. How do you schedule BI Publisher reports and monitor them?

7. Difference between Real Time Subject Areas (OTBI) and BICC (BI Cloud Connector) extracts.

8. How do you create drill-down reports in OTBI?

How to Perform Journal Import Using File-Based Data Import (FBDI) in Oracle Fusion General Ledger

How to Perform Journal Import Using File-Based Data Import (FBDI) in Oracle Fusion General Ledger

FBDI is Oracle’s preferred way to load bulk data into Fusion Cloud. This technical post covers how to upload General Ledger journal entries using FBDI templates.

Step 1: Download the JournalImportTemplate.xlsm

Visit the Oracle Enterprise Repository or the Oracle Fusion Documentations (docs.oracle.com)

1. Select Cloud Applications

2. Select Financials

3. Select All Books

4. Under Implementation -> File-Based Data Import(FBDI) for financials and search for "General Ledger -> Journal Import". 

Download the FBDI template named JournalImportTemplate.xlsm.

Step 2: Fill the Template

• Go to the GL_INTERFACE tab
• Fill in Ledger ID, Accounting Date, Currency, Account segments, Debit/Credit

Example: This is below sample data, but please fill in all the required columns as per the FBDI template and required fields

LEDGER_ID: 123
Journal Source : Provide the appropriate Journal source as per the configurations

Journal Category : Provide the appropriate Journal source as per the configurations
CURRENCY_CODE: INR
ENTERED_DR: 5000
ENTERED_CR: 0

Click Generate CSV File in the template.

Step 3: Upload to UCM

1. Go to Tools → File Import and Export
2. Upload the generated ZIP file
3. Set account to fin/generalLedger/import

Step 4: Run Load Interface File for Import

• Navigate to Scheduled Processes
• Run Load Interface File for Import
• Select your ZIP file and choose Import Process: Journal Import

Step 5: Submit Journal Import Process

Run the Import Journals process. Specify source and category used during file generation.

Step 6: Review and Post Journals

1. Go to General Accounting → Journals → Manage Journals
2. Review and post imported journal entries

Troubleshooting

Error	Cause	Fix
Ledger ID missing	Invalid ID	Use SQL to find correct ledger
No journals imported	CSV mismatch	Re-generate from template

How to View and Unlock Locked Users in Oracle Fusion

How to View and Unlock Locked Users in Oracle Fusion

In Oracle Fusion Applications, user accounts can become locked due to multiple failed login attempts, inactivity, or administrative actions. As a security measure, Oracle Fusion automatically disables or locks accounts based on the configured authentication policies.

In this post, we will see how to identify locked users and unlock them using the Oracle Fusion UI and SQL queries.

🔍 Reasons Why Users Get Locked in Oracle Fusion

• Multiple incorrect login attempts
• Inactivity for a long period
• Expired password without reset
• Manual locking by admin or automated ESS jobs

🧭 Method 1: View and Unlock Users Using Fusion UI

Navigation: Tools → Security Console → Users

🔹 Steps to Identify Locked Users:

1. Open the Security Console
2. Use the Advanced Search
3. Add filter: Account Status = Locked
4. Click Search

🔓 To Unlock a User:

1. Click on the locked user's name
2. Click on Actions > Unlock User Account
3. Confirm and save

You must have IT Security Manager or User Administrator role to perform this action.

🧾 Method 2: View Locked Users via SQL Query

SELECT user_name,
       email_address,
       account_locked,
       last_logon_date,
       user_guid,
       start_date,
       end_date
FROM fnd_user
WHERE account_locked = 'Y'
ORDER BY last_logon_date DESC;

🔓 Method 3: Unlock User via SQL Update (For On-Prem or Non-SaaS Only)

⚠️ Warning: This is for non-SaaS environments only.

UPDATE fnd_user
SET account_locked = 'N'
WHERE user_name = 'USERNAME';

⚠️ Important Notes

• In Oracle SaaS (Cloud), backend updates are not allowed. Always use the Security Console.
• Make sure your actions comply with company IT policies.
• Frequent lockouts? Review password policy under Security Console > Password Policies.

✅ Conclusion

Oracle Fusion provides secure ways to manage user accounts. Use the Security Console to unlock users in SaaS. 

Oracle : How to obtain a details of the process that are scheduled?

 How to obtain details that will provide information about what process is scheduled?

In this blog post, will provide some sample technical queries which will provide details on the ESS process which are scheduled.

1. Find all the schedules

SELECT x.REQUESTID,x.NAME,x.DEFINITION,x.SCHEDULESTATE, x.LASTSCHEDULEINSTANCEID

FROM FUSION_ORA_ESS.REQUEST_HISTORY x where

x.parentrequestid = -1 and x.REQUESTTYPE=2 and x.state = 1

2. Find all the schedules for a specific ESS job.

SELECT x.REQUESTID,x.NAME,x.DEFINITION,x.SCHEDULESTATE, x.LASTSCHEDULEINSTANCEID

FROM FUSION_ORA_ESS.REQUEST_HISTORY x where

x.definition = '<Job Definition>'

and x.parentrequestid = -1 and x.REQUESTTYPE=2

Oracle Fusion OTBI (Oracle Transactional Business Intelligence)

Oracle Fusion OTBI (Oracle Transactional Business Intelligence)

Introduction
Oracle Fusion OTBI is a real-time, self-service reporting tool that enables business users to create ad-hoc reports from live transactional data. This is driven by the Subject Areas. 

🔍 Key Features of OTBI

• Drag-and-drop UI for report creation
• Real-time data access
• Role-based security (secured by Fusion roles)
• Seamless integration with dashboards
• Export to Excel, PDF, etc.

🧠 What Are Subject Areas?

Subject Areas are pre-built metadata views that organize Fusion data into logical business entities like:

• Financials – Payables Invoices, Receivables, Journals
• HCM – Person Real-Time, Assignment Real-Time
• SCM – Inventory Transactions, Purchase Orders

⚙️ Common OTBI Use Cases

Use Case	Subject Area
Employee Active Status Report	Workforce Management – Person Real Time
Pending Supplier Invoices	Payables Invoices – Transactions Real Time
Purchase Orders by Category	Procurement – Purchase Orders Real Time

🧾 Sample Query-Like Output (OTBI Report)

Report: Active Employees
Subject Area: Workforce Management - Person Real Time
Columns: Full Name, Employee Number, Status, Department
Filters: Status = ‘Active’

🛠️ Tips for Working with OTBI

• Use filters to reduce data volume and improve performance.
• Save reports in My Folders or Shared Folders for visibility.
• OTBI can be embedded in dashboards or exported automatically.

📌 Limitations

• Limited customization compared to BI Publisher
• Doesn’t support complex SQL logic or joins across subject areas
• Refresh issues in custom Subject Areas occasionally

✅ Conclusion

Oracle OTBI empowers end users to access live Fusion data without technical skills.