Oracle Fusion Cash Management: Key Tables and Sample SQL Queries

Oracle Fusion Cash Management (CE) plays a critical role in managing and reconciling an organization’s bank accounts, cash positions, and liquidity. 

This post will help in understanding the core Cash Management tables and relationships is essential for building reports and integrations.

In this blog post, we will discuss about :

• Core Cash Management Tables

• Table Relationships

• Sample SQL Queries

• Common Use Cases

---

Key Oracle Fusion Cash Management Tables

Table Name	Description
CE_BANK_ACCOUNTS	Stores bank account definitions (shared with Payables).
CE_BANK_ACCT_USES_ALL	Links bank accounts to uses (Payables, Receivables, Payroll, etc.).
CE_STATEMENT_HEADERS	Stores bank statement header details.
CE_STATEMENT_LINES	Contains transaction lines from bank statements (debits/credits).

Table Relationships

CE_BANK_ACCOUNTS → CE_BANK_ACCT_USES_ALL → CE_STATEMENT_HEADERS → CE_STATEMENT_LINES
                                                                                             

---

Sample SQL Queries

---

1. List of Bank Accounts

SELECT
  cba.bank_account_id,
  cba.bank_account_name,
  cba.bank_account_num,
  cba.currency_code,
  cba.bank_id,
  cba.branch_id
FROM
  ce_bank_accounts cba
WHERE
  cba.enabled_flag = 'Y';

---

2. Bank Account Uses (e.g., Payables/Receivables)

SELECT
  cba.bank_account_name,
  cau.uses_type,
  cau.start_date,
  cau.end_date
FROM
  ce_bank_acct_uses_all cau,
  ce_bank_accounts cba 
WHERE cau.bank_account_id = cba.bank_account_id
AND
  cau.uses_type IN ('PAYABLES', 'RECEIVABLES');

---

3. Bank Statement with Line Items

SELECT
  csh.statement_header_id,
  csh.statement_num,
  csh.bank_account_id,
  csl.line_number,
  csl.trx_date,
  csl.amount,
  csl.trx_code,
  csl.description
FROM
  ce_statement_headers csh,
  ce_statement_lines csl 
WHERE csh.statement_header_id = csl.statement_header_id
AND
  csh.creation_date >= TO_DATE('2024-01-01','YYYY-MM-DD');

---

4. Cash Balance by Bank Account

SELECT
  cb.bank_account_id,
  cb.balance_date,
  cb.balance_amount,
  cb.currency_code
FROM
  ce_balances cb
WHERE
  cb.balance_date = TO_DATE('2024-06-30', 'YYYY-MM-DD');

---

Real-World Use Cases

• ✅ Cash Position Reporting: Determine current balances across accounts.

• 🔁 Bank Reconciliation: Track unreconciled transactions or mismatches.

• 🏦 Bank Statement Review: Analyze incoming/outgoing transactions from the bank feed.

---

Tips

• Check CE_BALANCES for real-time vs as-of-date balances.

• Use CE_STATEMENT_LINES for tracking bank fees, interest, or ACH deposits.

• For multi-currency environments, ensure you're filtering by currency_code.

---

Oracle Fusion Fixed Assets Tables – Key Tables and Sample Queries

Oracle Fusion Fixed Assets (FA) is a comprehensive module used for managing an organization's fixed assets lifecycle—from acquisition to retirement. 

For technical developers and report builders, understanding the underlying Fusion FA tables is crucial to extract and analyze data accurately.

In this blog, we will dive into:

• Key Oracle Fusion Fixed Assets Tables

• Table Relationships

• Sample SQL Queries

• Real-world Use Cases

---

Key Oracle Fusion Fixed Assets Tables

Table Name	Description
FA_ASSET_HISTORY	Stores asset-level information like asset number, description, and tag.
FA_BOOKS	Maintains asset financial details per asset book (e.g., depreciation method, cost, YTD depreciation).
FA_CATEGORIES_B	Contains asset category definitions.
FA_ADDITIONS_B	Used during the asset addition process.
FA_DISTRIBUTION_HISTORY	Tracks accounting and distribution information by cost center/project.
FA_RETIREMENTS	Records asset retirement data.
FA_DEPRN_SUMMARY	Stores summarized depreciation data for each asset per period.
FA_LOCATIONS	Holds the location details where assets are deployed.

---

Table Relationships with Other tables

• FA_ASSET_HISTORY ➝ Asset core info
  joins to FA_BOOKS on ASSET_ID
  joins to FA_CATEGORIES_B on CATEGORY_ID
  joins to FA_LOCATIONS on LOCATION_ID
  joins to FA_DISTRIBUTION_HISTORY for cost center info
  joins to FA_DEPRN_SUMMARY for depreciation data

---

Sample SQL Queries

Below are some of the Sample Queries.

1. Basic Asset Details

SELECT 
  fah.asset_number,
  fah.description,
  fb.book_type_code,
  fb.date_placed_in_service,
  fb.cost,
  fb.ytd_deprn,
  fcb.category_description,
  fl.location_description
FROM
  fa_asset_history fah,
  fa_books fb,
  fa_categories_b fcb,
  fa_locations fl 
WHERE fah.asset_id = fb.asset_id
AND fb.category_id = fcb.category_id
AND fah.location_id = fl.location_id
AND fb.book_type_code = 'CORPORATE'
  AND fb.date_placed_in_service BETWEEN TO_DATE('2024-01-01','YYYY-MM-DD') AND TO_DATE('2024-12-31','YYYY-MM-DD');

---

2. Asset Retirement Report

SELECT 
  fah.asset_number,
  fb.book_type_code,
  fr.retirement_date,
  fr.retirement_type_code,
  fr.proceeds_of_sale,
  fr.gain_loss
FROM 
  fa_asset_history fah,
  fa_books fb,
  fa_retirements fr  
WHERE fah.asset_id = fb.asset_id
AND fb.asset_id = fr.asset_id
AND 
  fr.retirement_date >= TO_DATE('2024-01-01','YYYY-MM-DD');

---

3. Depreciation Summary for a Period

SELECT 
  fah.asset_number,
  fb.book_type_code,
  fds.period_name,
  fds.deprn_amount,
  fds.ytd_deprn
FROM 
  fa_asset_history fah,
  fa_books fb,
  fa_deprn_summary fds
WHERE fah.asset_id = fb.asset_id
AND  fb.asset_id = fds.asset_id
AND 
  fds.period_name = 'JUN-24'
  AND fb.book_type_code = 'CORPORATE';

---

4. Assets by Cost Center or Project

SELECT 
  fah.asset_number,
  fb.cost,
  fdh.code_combination_id,
  gl.segment1 AS cost_center
FROM 
  fa_asset_history fah,
  fa_books fb,
  fa_distribution_history fdh,
  gl_code_combinations gl  
WHERE fah.asset_id = fb.asset_id
AND fb.asset_id = fdh.asset_id
AND fdh.code_combination_id = gl.code_combination_id
AND 
  gl.segment1 = '1001'; -- Replace with your cost center

---

Use Cases for These Queries

• Internal Audits: Asset existence, placement, depreciation status

• Finance Reporting: Capital expenditure, asset additions/retirements

• Cost Allocation: Cost center or project-based distribution

• Fixed Asset Reconciliation: Verify system balances with physical checks

---

Notes : 

• Always validate the Book Type Code (CORPORATE, TAX, etc.).

• Use Asset Status to filter retired, transferred, or fully depreciated assets.

All about Cursors in Oracle PL/SQL

 In this blog post, we will see how to use cursors to fetch multiple rows from a query result set

Implicit Cursor:

Here is the example for the Implicit Cursor

BEGIN

   FOR rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = 100) LOOP

      DBMS_OUTPUT.PUT_LINE('Employee: ' || rec.first_name);

   END LOOP;

END;

Explicit Cursor:

Here is the example for the Implicit Cursor

DECLARE

   CURSOR c_emp IS SELECT employee_id, first_name FROM employees;

   v_emp_id employees.employee_id%TYPE;

   v_name employees.first_name%TYPE;

BEGIN

   OPEN c_emp;

   LOOP

      FETCH c_emp INTO v_emp_id, v_name;

      EXIT WHEN c_emp%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id || ', Name: ' || v_name);

   END LOOP;

   CLOSE c_emp;

END;

How to write a Anonymous Block in PL/SQL

An anonymous block is a PL/SQL program without a name. It is used for quick tasks and testing.

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World');

END;

SQL Query to Fetch Price List Lines for Items

 Here is the sample SQL Query to fetch the Price List Lines for the Items. Please modify the query as per the requirements.

SELECT h.name AS price_list,

       l.list_line_id,

       l.product_id,

       l.uom_code,

       l.start_date_active,

       l.end_date_active,

       l.line_type_code

FROM qp_list_headers_b h

JOIN qp_list_lines_b l ON h.list_header_id = l.list_header_id

WHERE h.name = 'Standard Price List'

  AND l.list_line_type_code = 'PRICE';

SQL Query to fetch the Active Price Lists

 Here is the sample SQL Query to fetch the Price Lists in Oracle fusion.

SELECT qlb.list_header_id, qlb.name, qlb.description, qlb.currency_code,qlb.*

FROM qp_list_headers_b qlb

WHERE qlb.list_type_code = 'PRL'

AND SYSDATE BETWEEN qlb.start_date_active AND NVL(qlb.end_date_active, SYSDATE);

 

Building a Custom Report in Oracle Fusion Using BI Publisher

Oracle Fusion BI Publisher (BIP) is a powerful reporting tool that allows you to create highly formatted, data-rich reports based on Oracle Cloud data. 

Whether you need custom invoices, payroll slips, financial summaries, or data extracts, BIP gives you control over both data and layout.

In this blog post, you'll learn how to create a custom report in Oracle Fusion using BI Publisher — step-by-step.

---

What You’ll Need

• Oracle Fusion access with BI Administrator or Report Developer roles

• Familiarity with SQL queries and subject areas

• BI Publisher Desktop (MS Word plugin) for template design (optional but recommended)

---

Step-by-Step: Create a Custom Report

---

Step 1: Define Your Report Requirements

Before you begin, clearly identify:

• What data you need (tables, views, business objects)

• How often the report should run

• Who needs access (users, roles)

• Output format (Excel, PDF, RTF, CSV)

---

Step 2: Create the Data Model

Navigation: Tools > Reports and Analytics > Browse Catalog

1. Go to Catalog → Create → Data Model

2. Choose SQL Query as your data source

3. Example Query (e.g., open purchase orders):

SELECT poh.segment1 AS PO_NUMBER,
       pol.line_num,
       pol.item_description,
       pol.unit_price,
       pol.quantity_ordered,
       poh.creation_date
FROM po_headers_all poh
JOIN po_lines_all pol ON poh.po_header_id = pol.po_header_id
WHERE poh.authorization_status = 'APPROVED'

4. Click View Data to test

5. Optionally, add Parameters (e.g., supplier, date range)

6. Save the Data Model

---

Step 3: Create a Report Layout

1. Click on the Data Model → Create Report

2. Choose:

   • Use Existing Template (RTF, Excel, PDF) or

   • Create a Blank Report for layout

3. To use MS Word-based design:

   • Download BI Publisher Desktop plugin

   • Open MS Word → Load Sample XML → Design template using form fields

   • Upload .RTF layout back to BI Publisher

4. Save report in a shared folder like:

   /Shared/Custom/<ReportName>

---

Step 4: Run or Schedule the Report

• From the Catalog, click More > Schedule

• Choose output format: PDF, Excel, CSV

• Set recurrence: daily, weekly, monthly

• Define output destination: Online, Email, FTP

---

Step 5: Assign Roles and Permissions

Ensure appropriate access:

• Navigate to the report > Click More > Permissions

• Grant view/run/schedule permissions to roles (e.g., Buyer, AP Analyst)

---

Optional Features You Can Add

• Bursting: Distribute report to multiple users based on data (e.g., each department gets its own report)

• Sub-Templates: Reuse header/footer in multiple reports

• Translation Templates: Create multi-language versions of the same report

---

Tips and Best Practices

• Use database views or subject areas for data stability

• Keep layout clean and avoid large embedded images

• For large data sets, prefer CSV or Excel output

• Always test with filters to prevent full-table scans

---

Oracle Fusion Reporting - OTBI vs BI Publisher vs Smart View

 

Oracle Fusion Reporting: How to Use OTBI vs BI Publisher vs Smart View

Oracle Fusion Cloud provides multiple tools for reporting and analytics, but selecting the right one depends on what kind of data you need, how often you need it, and who needs it.

This blog post will help you understand the differences, use cases, and best practices when working with the three most common reporting tools:

• OTBI (Oracle Transactional Business Intelligence)

• BI Publisher

• Smart View for Excel

---

What are the tools available ?

Tool	Type	Best For
OTBI	Self-service, real-time dashboards	Business users
BI Publisher	Pixel-perfect, scheduled reports	IT & power users
Smart View	Excel-based ad hoc analysis	Finance & analysts

1. OTBI – Oracle Transactional Business Intelligence

Best For:

• Real-time reporting directly from transactional data

• Drag-and-drop dashboards

• Functional users (e.g., HR, Finance, Procurement)

Use Cases:

• List of active employees in HR

• Open Purchase Orders by Buyer

• Payables invoices by status

How to Use:

1. Navigate to Reports and Analytics > Create > Analysis.

2. Select a Subject Area (e.g., “Workforce Management – Worker Assignment Real Time”).

3. Drag fields into Selected Columns and apply filters.

4. Save and share as dashboard or export to Excel/PDF.

Limitations:

• Cannot join across subject areas

• Complex logic is limited

• No pixel-perfect formatting

---

2. BI Publisher (BIP)

Best For:

• Bursting/scheduled reports

• Reports that require templates and formatting (invoices, payslips, letters)

• Extracts for integrations

Use Cases:

• Payslip generation

• Vendor remittance advice

• Custom FBDI data extract

How to Use:

1. Go to Reports and Analytics > BI Publisher.

2. Create Data Model: Use SQL query, Web Service, or Data Sets.

3. Upload an RTF/XSL/Excel layout using MS Word Plugin.

4. Link layout to data model and schedule or run manually.

Key Features:

• Supports bursting by email, FTP, printer

• Templates can be translated and customized

• Can run SQL from multiple sources

Limitations:

• Requires more technical skill

• Slower with large volumes compared to OTBI

---

3. Smart View for Excel

Best For:

• Excel-based analysis

• Ad hoc querying with pivoting and formulas

• Financial reporting and planning

Use Cases:

• Budget variance reports

• Profit & Loss comparison by department

• GL balances extraction

How to Use:

1. Install Oracle Smart View Add-In in Excel.

2. Connect to Oracle Fusion Cloud URL.

3. Select cube (e.g., Financials Cloud - General Ledger).

4. Drag and drop dimensions to Excel sheet.

Key Features:

• Drill-down into balances

• Supports Excel formulas, charts, macros

• Fast for finance teams familiar with Excel

Limitations:

• Needs configuration for first use

• Only works well with Essbase/cube data (not transactional)

---

Best Practices

• Use OTBI for dashboards, metrics, or on-screen real-time data.

• Use BI Publisher for formal reports (invoices, extracts, employee letters).

• Use Smart View for Excel-savvy teams needing deep financial analysis.

---

So based on the features available, this options can be explored further.