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. 

Oracle Fusion Sub Ledger Accounting Tables with Sample SQL Queries

Oracle Fusion SLA Tables with SQL Examples

🔍 Introduction

Oracle Subledger Accounting (SLA) is the rules-based engine that generates accounting entries for transactions from subledgers such as Payables, Receivables, and Assets. It provides a unified accounting model that feeds into the General Ledger (GL).

This post explores key SLA tables, explains how they interrelate, and provides practical SQL queries for reconciliation, reporting, and debugging.

📂 Key SLA Tables Overview

Table Name	Description
XLA_EVENTS	Stores business events triggered in subledgers
XLA_AE_HEADERS	Header-level journal entry info
XLA_AE_LINES	Line-level accounting entries
XLA_DISTRIBUTION_LINKS	Maps SLA lines to source distributions like invoice lines
XLA_TRANSACTION_ENTITIES	Information about subledger transactions
XLA_SUBLEDGER_SOURCES	Defines source systems like Payables, AR, Assets
XLA_LEDGER_OPTIONS	Stores ledger-specific SLA options and setups

🔗 How SLA Tables Are Related

• Transaction Entity: Stored in XLA_TRANSACTION_ENTITIES
• Event: Created in XLA_EVENTS
• Header & Line Entries: XLA_AE_HEADERS and XLA_AE_LINES
• Distribution Mapping: via XLA_DISTRIBUTION_LINKS

🧪 Sample SQL Queries

1. 📘 SLA Journal Entries for a Payables Invoice

SELECT xah.ae_header_id,
       xal.ae_line_num,
       xal.accounted_cr,
       xal.accounted_dr,
       xal.code_combination_id,
       gcc.segment1 AS company,
       gcc.segment2 AS account,
       xah.accounting_date,
       xah.description
FROM xla_ae_headers xah
JOIN xla_ae_lines xal
  ON xah.ae_header_id = xal.ae_header_id
JOIN gl_code_combinations gcc
  ON xal.code_combination_id = gcc.code_combination_id
WHERE xah.entity_id IN (
    SELECT xte.entity_id
    FROM xla_transaction_entities xte
    WHERE xte.source_id_int_1 = :invoice_id
      AND xte.application_id = 200
)
AND xal.accounting_class_code IS NOT NULL;

2. 🔄 Distribution Line Mapping

SELECT xdl.source_distribution_type,
       xdl.source_distribution_id_num_1,
       xal.accounted_dr,
       xal.accounted_cr,
       xal.code_combination_id
FROM xla_distribution_links xdl
JOIN xla_ae_lines xal
  ON xdl.ae_line_id = xal.ae_line_id
WHERE xdl.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND xdl.source_distribution_id_num_1 = :invoice_distribution_id;

🛠️ Use Cases for These Queries

• Trace invoice entries to General Ledger
• Reconcile SLA data with GL balances
• Debug incorrect journal entries or posting failures
• Build reports using BI Publisher or OTBI

📚 Conclusion

Oracle Fusion Subledger Accounting forms the foundation for robust financial tracking across subledgers. By understanding the XLA table structure and using SQL efficiently, technical users can generate powerful audit trails, improve reconciliation, and build end-to-end financial insights.

Oracle Fusion Payments Tables with Sample Queries

Oracle Fusion Payments is the centralized payment engine behind Oracle Financials. It processes disbursements and receipts across modules like Payables, Receivables, and Expenses. This post will help provide the underlying tables can help technical and functional consultants can use for the reports and troubleshoot paymenting issues, and support customizations.

🔍 Key Oracle Fusion Payments Tables

Here are the most commonly used tables in Oracle Fusion Payments:

Table Name	Description
IBY_PAY_SERVICE_REQUESTS	Stores payment service request details for each disbursement
IBY_DOCS_PAYABLE_ALL	Contains invoice or refund records selected for payment
IBY_PAY_INSTRUCTIONS_ALL	Payment instructions sent for formatting and transmission
IBY_PAYMENTS_ALL	Details of actual payments created by the system
IBY_PAYMENT_DETAILS	Payment breakdowns linked to instructions
IBY_PMT_INSTR_USES_ALL	Payment instruments like bank accounts or credit cards
IBY_EXTERNAL_PAYEES_ALL	Supplier or employee payee information
CE_PAYMENT_TRANSACTIONS	Cash Management view of payment transactions
AP_INVOICES_ALL	Source invoices from Oracle Payables

🧩 How These Tables Are Related

The relationships between these tables are essential for linking invoice to payment:

• Invoices from AP_INVOICES_ALL become documents payable in IBY_DOCS_PAYABLE_ALL.
• Each document is tied to a payment service request in IBY_PAY_SERVICE_REQUESTS.
• Requests are grouped into payment instructions via IBY_PAY_INSTRUCTIONS_ALL.
• Payments are recorded in IBY_PAYMENTS_ALL and detailed in IBY_PAYMENT_DETAILS.

🧪 Sample SQL Queries

1. Invoices Selected for Payment

SELECT dp.payment_service_request_id,
       dp.invoice_id,
       ai.invoice_num,
       ai.invoice_amount,
       dp.payment_status
FROM iby_docs_payable_all dp
JOIN ap_invoices_all ai
  ON dp.invoice_id = ai.invoice_id
WHERE dp.payment_status = 'SELECTED';

2. Payment Instruction Details

SELECT pi.payment_instruction_id,
       pi.status,
       pi.payment_profile_id,
       pi.creation_date,
       COUNT(pd.payment_id) AS total_payments
FROM iby_pay_instructions_all pi
LEFT JOIN iby_payment_details pd
  ON pi.payment_instruction_id = pd.payment_instruction_id
GROUP BY pi.payment_instruction_id, pi.status, pi.payment_profile_id, pi.creation_date;

3. Payment Summary by Supplier

SELECT epa.supplier_name,
       SUM(p.amount) AS total_paid,
       COUNT(p.payment_id) AS payments_made
FROM iby_payments_all p
JOIN iby_external_payees_all epa
  ON p.payee_party_id = epa.payee_party_id
GROUP BY epa.supplier_name
ORDER BY total_paid DESC;

4. Trace Invoice to Payment

SELECT ai.invoice_num,
       ai.invoice_amount,
       dp.payment_service_request_id,
       psr.calling_app_doc_ref_number,
       pi.payment_instruction_id,
       p.payment_id,
       p.amount,
       p.status
FROM ap_invoices_all ai
JOIN iby_docs_payable_all dp
  ON ai.invoice_id = dp.invoice_id
JOIN iby_pay_service_requests psr
  ON dp.payment_service_request_id = psr.payment_service_request_id
JOIN iby_pay_instructions_all pi
  ON psr.payment_instruction_id = pi.payment_instruction_id
JOIN iby_payments_all p
  ON pi.payment_instruction_id = p.payment_instruction_id
WHERE ai.invoice_num = 'INV12345';

📌 Tips for Working with Payments Tables

• Check IBY_PAYMENTS_ALL.status for values like ISSUED, VOIDED, or CANCELLED
• Use CE_PAYMENT_TRANSACTIONS to track reconciled bank transactions
• Always apply ORG_ID or LEDGER_ID in multi-org setups

📚 Conclusion

Understanding the Oracle Fusion Payments data model enables you to extract insights, troubleshoot effectively, and support automation. These queries are just a starting point. You can extend them for BI Publisher, OTBI, or external reporting integrations.

Oracle 23ai Database - Features and capabilities

 Here’s a technical blogpost on the key technical features of Oracle Database 23ai.

  Feature                               What It Enables                                

1. AI Vector Search               Semantic search, LLM integration, RAG apps     

2. JSON Relational Duality  API-ready relational data                      

3.SQL Firewall                    Threat protection at query level               

4. AI Optimizer                     Smart execution plans, less DBA tuning         

5. Multicloud Native            Deploy Oracle in AWS & Azure natively          

6. Property Graphs               Advanced analytics and recommendations         

7. Dev Experience               Better language support, REST APIs, JavaScript 

Oracle Database 23ai: AI‑Powered Database

 This blog post provides some details of the Oracle Database 23ai.

Oracle’s latest long-term-support release, 23ai (“ai” for Artificial Intelligence), launched last year 2024, introduces powerful native AI capabilities—most notably AI Vector Search—combined with over 300 new features across data, development, and infrastructure

This post help provide basic understanding Oracle Database 23ai and its features.

AI Vector Search

A flagship capability: search unstructured data semantically by converting text/images into vectors using the new VECTOR datatype

Key features:

• VECTOR column support in tables

• Create vector indexes

• Perform semantic search via SQL functions

• Optional accuracy tuning in queries

• Exadata optimizations for fast vector processing

Oracle Database 23ai transforms your Oracle environment into an AI-first, semantic engine:

• Install via Free edition or containers in minutes.

• Define VECTOR columns, populate embeddings, and build vector indexes.

• Query unstructured & structured data semantically.

• Integrate with RAG flows using SQL/PLSQL/Python.

• Scale easily using Python/JDBC clients and Exadata.

AI-Assisted Optimizer & Adaptive Query Execution

Oracle 23ai enhances the Cost-Based Optimizer (CBO) with AI/ML features that help:

• Predict better execution plans

• Learn from historical workloads

• Adapt plans at runtime using AI-enhanced estimation

• Self-adjusting SQL plans without human tuning

• Supports real-time statistics, feedback-based optimization