Common Errors in Oracle Fusion and How to Fix Them

In this blog post, we will discuss on the common errors in Oracle fusion and how to fix them. Please note this covers basic required stuffs and may not cover all the required steps to fix. 

Oracle Fusion Applications are powerful but complex. Whether you're working with FBDI, HDL, BI Publisher, REST APIs, or ESS Jobs, errors are inevitable. 

This blog helps you identify common Oracle Fusion errors, understand why they occur, and apply quick fixes or best practices to resolve them.

---

1. FBDI Upload Errors (File-Based Data Import)

Error:

"A value is missing for the required column."

Fix:

• Open the .zip file and check the .csv inside.

• Ensure required fields are not empty (e.g., BUSINESS_UNIT, LEDGER_ID, etc.).

• Validate lookup values are accurate (refer to FND_LOOKUP_VALUES).

Pro Tip:

Use the latest FBDI template from Oracle documentation to ensure compatibility with the current version.

---

2. HDL Errors (HCM Data Loader)

Error:

The value for the attribute PersonId is invalid.

Fix:

• Check if the referenced PersonNumber exists using:

  SELECT person_id FROM per_all_people_f WHERE person_number = 'XYZ123';

• Make sure effective dates fall within valid ranges.

Pro Tip:

Enable diagnostics logging for HDL (via Scheduled Processes > View Logs) to see detailed error reasons.

---

3. BI Publisher Errors

Error:

ORA-00904: "COLUMN_NAME": invalid identifier

Fix:

• Go to Data Model and verify all SQL column names are valid and exist in the source table.

• Double-check if you are using an alias for complex expressions.

Pro Tip:

Use the "View Output" → "XML" option in BIP to debug missing or incorrect XML tags in the template.

---

4. ESS Job Failures (Enterprise Scheduler Service)

Error:

Process ended in error. Check log and output file for details.

Fix:

• Open Scheduled Processes > View Logs.

• Check for common issues like:

  • Missing parameters

  • Incomplete data setup

  • Invalid dates or values

Pro Tip:

Use the Diagnostic Test Framework or run "ESS Job Diagnostic Report" to analyze recurring job failures.

---

5. REST API Errors

Error:

401 Unauthorized or 403 Forbidden

Fix:

• Confirm the user has the correct roles and privileges (e.g., Integration Specialist, Application Implementation Consultant).

• Use Basic Auth or OAuth2 tokens properly in your request headers.

• API endpoint should use correct format like:

  https://<host>/fscmRestApi/resources/latest/employees

Pro Tip:

Use Postman or cURL to test endpoints before integrating into scripts.

---

6. Fusion UI Errors

Error:

"You do not have permission to access this page"

Fix:

• Check user's data access in Security Console.

• Verify roles assigned through Abstract, Job, and Data Roles.

Pro Tip:

Run "User and Role Access Audit Report" to debug missing privileges.

---

7. SQL Developer Errors with Fusion Views

Error:

ORA-00942: Table or View Does Not Exist

Fix:

• You might be querying a view that is secure (e.g., views prefixed with FND_, PER_, etc.).

---

Conclusion

Understanding and fixing Oracle Fusion errors is a skill that improves with time. Always:

• Check logs and XML outputs

• Use SQL queries to validate data

• Refer to Oracle Support notes (MOS) and documentation

---

Further Resources

• Oracle Support: https://support.oracle.com

• Fusion Data Models: Use Oracle BI Catalog > Shared Folders > Human Capital Management/Financials

• REST API Reference: https://docs.oracle.com/en/cloud/saas/index.html

How to automate Batch Jobs with DBMS_SCHEDULER in Oracle PL/SQL ?

In this blog post, we will discuss on the DBMS_SCHEDULERs in Oracle. Here are some of the code snippets in this post.

Managing background jobs is essential for any Oracle-based system — from data processing to scheduled reports. 

Oracle’s DBMS_SCHEDULER package is a powerful tool that helps you schedule, manage, and monitor jobs efficiently. 

In this blog post, we’ll walk through how to create, schedule, and maintain batch jobs using DBMS_SCHEDULER.

---

What is DBMS_SCHEDULER?

DBMS_SCHEDULER is a PL/SQL package that lets you schedule and run jobs like procedures, shell scripts, and external programs. It’s more advanced than the older DBMS_JOB, with better control, logging, and flexibility.

---

Basic Components of a Scheduler Job

• Program: Defines what to run (e.g., a stored procedure or script).

• Schedule: Defines when to run.

• Job: Links the program and schedule.

• Job Class, Windows & Chains: Advanced features for grouping and managing jobs.

---

Example: Create a Simple PL/SQL Job

1. Create a Sample Procedure

CREATE OR REPLACE PROCEDURE update_status_proc IS
BEGIN
  UPDATE my_table
  SET status = 'COMPLETED'
  WHERE status = 'PENDING';

  COMMIT;
END;

---

2. Create a Scheduler Job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'UPDATE_STATUS_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'UPDATE_STATUS_PROC',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2', -- Every day at 2 AM
    enabled         => TRUE,
    comments        => 'Daily status update batch job'
  );
END;

---

Monitoring the Job

Check Job Status

SELECT job_name, status, run_count, last_start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'UPDATE_STATUS_JOB';

Check Job Run History

SELECT job_name, status, actual_start_date, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name = 'UPDATE_STATUS_JOB'
ORDER BY actual_start_date DESC;

---

Modifying an Existing Job

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'UPDATE_STATUS_JOB',
    attribute => 'repeat_interval',
    value     => 'FREQ=HOURLY; INTERVAL=2'); -- Every 2 hours
END;

---

Dropping a Job

BEGIN
  DBMS_SCHEDULER.DROP_JOB('UPDATE_STATUS_JOB');
END;

---

Advanced Features

Using Programs and Schedules Separately

You can define them once and reuse:

-- Define a program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'my_batch_program',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'update_status_proc',
    enabled        => TRUE
  );
END;

-- Define a schedule
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name => 'daily_2am',
    start_date    => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2',
    comments      => 'Runs every day at 2 AM'
  );
END;

-- Create a job using both
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name      => 'update_status_job_v2',
    program_name  => 'my_batch_program',
    schedule_name => 'daily_2am',
    enabled       => TRUE
  );
END;

---

Granting Permissions

If you're not using SYS or DBA user, grant privileges:

GRANT CREATE JOB TO your_user;
GRANT MANAGE SCHEDULER TO your_user;

---

Tips and Best Practices

• Always log your job runs (custom or via dba_scheduler_job_log)

• Use job classes to group similar jobs (e.g., hourly jobs, daily jobs)

• Use disable/enable carefully when maintaining production jobs

• Avoid committing large volumes in one go — break logic into batches

---

Conclusion

Oracle’s DBMS_SCHEDULER is a robust tool that enables you to automate and manage recurring tasks efficiently. Whether you’re running ETL jobs, clean-up tasks, or reports, mastering it can save hours of manual work and ensure consistency.

Essential SQL Queries for Oracle Fusion Modules

 

Essential SQL Queries for Oracle Fusion Modules

Oracle Fusion Applications span across various modules such as HCM, Financials, Procurement, and Projects. Below are some practical SQL snippets that help in fetching data from key tables in each module. These queries can be used for reporting, troubleshooting, or data validation purposes.

---

1. Oracle Fusion HCM (Human Capital Management)

Fetch employee basic details:

sql
SELECT person.person_number,
       person.full_name,
       assignment.assignment_number,
       assignment.assignment_status_type,
       assignment.effective_start_date
FROM per_all_people_f person
JOIN per_all_assignments_f assignment
  ON person.person_id = assignment.person_id
WHERE SYSDATE BETWEEN person.effective_start_date AND person.effective_end_date
  AND SYSDATE BETWEEN assignment.effective_start_date AND assignment.effective_end_date;

Fetch absence records:

sql
SELECT person.full_name,
       absence.absence_type,
       absence.start_date,
       absence.end_date
FROM per_absence_entries absence
JOIN per_all_people_f person
  ON absence.person_id = person.person_id
WHERE absence.start_date >= TRUNC(SYSDATE) - 30;

---

2. Oracle Fusion Financials (Payables and Receivables)

AP Invoice Summary:

sql
SELECT invoice.invoice_num,
       invoice.invoice_date,
       invoice.invoice_amount,
       vendor.vendor_name
FROM ap_invoices_all invoice
JOIN ap_suppliers vendor
  ON invoice.vendor_id = vendor.vendor_id
WHERE invoice.invoice_date >= TRUNC(SYSDATE) - 90;

AR Customer Receipts:

sql
SELECT receipt.receipt_number,
       receipt.receipt_date,
       customer.customer_name,
       receipt.amount
FROM ar_cash_receipts_all receipt
JOIN hz_cust_accounts customer
  ON receipt.pay_from_customer = customer.cust_account_id
WHERE receipt.status = 'APPROVED';

---

3. Oracle Fusion Procurement

PO (Purchase Order) Headers and Lines:

sql
SELECT poh.po_header_id,
       poh.segment1 AS po_number,
       pol.line_num,
       pol.item_description,
       pol.quantity
FROM po_headers_all poh
JOIN po_lines_all pol
  ON poh.po_header_id = pol.po_header_id
WHERE poh.authorization_status = 'APPROVED';

Requisition Details:

sql
SELECT prh.requisition_header_id,
       prh.segment1 AS req_number,
       prl.line_num,
       prl.item_description,
       prl.quantity
FROM por_req_headers_all prh
JOIN por_req_lines_all prl
  ON prh.requisition_header_id = prl.requisition_header_id
WHERE prh.status = 'OPEN';

---

4. Oracle Fusion Projects (PPM)

Fetch project and task details:

sql
SELECT proj.project_number,
       proj.project_name,
       task.task_number,
       task.task_name
FROM pjf_projects_all_b proj
JOIN pjf_tasks task
  ON proj.project_id = task.project_id
WHERE proj.status_code = 'APPROVED';

Project expenditures summary:

sql
SELECT exp.expenditure_type,
       exp.amount,
       exp.expenditure_item_date,
       proj.project_number
FROM pjc_exp_items_all exp
JOIN pjf_projects_all_b proj
  ON exp.project_id = proj.project_id
WHERE exp.expenditure_item_date >= ADD_MONTHS(SYSDATE, -3);

FORALL Command in Oracle PL/SQL

 The FORALL command in Oracle PL/SQL is a helpful feature designed to optimize bulk data manipulation. It enhances performance by minimizing context switches between the PL/SQL engine and the SQL engine, making it ideal for large-scale data operations.

What is the FORALL Command?

FORALL is a loop construct that efficiently performs bulk INSERT, UPDATE, or DELETE operations. Unlike traditional FOR loops, FORALL processes entire collections in a single batch, reducing the overhead of multiple context switches.

Syntax of FORALL

FORALL index IN lower_bound .. upper_bound

    sql_statement;

Examples of Using FORALL

DECLARE

    TYPE emp_data IS TABLE OF employees%ROWTYPE;

    v_emps emp_data;

BEGIN

    SELECT * BULK COLLECT INTO v_emps FROM employees_data WHERE department_id = 100;

    FORALL i IN v_emps.FIRST .. v_emps.LAST

        INSERT INTO employees_data_table VALUES v_emps(i);

    COMMIT;

END;

/

The FORALL command is a vital tool for enhancing performance in Oracle PL/SQL bulk operations. By mastering its syntax, combining it with SAVE EXCEPTIONS, and following best practices, you can significantly improve the efficiency of data manipulation tasks in your Oracle database projects.

INSERT ALL Command in Oracle PL/SQL

The INSERT ALL command in Oracle PL/SQL is a powerful feature for inserting multiple data rows into one or more tables efficiently. 

This will simplify data insertion logic, reduces data redundancy, and improves performance when dealing with bulk data operations.

What is the INSERT ALL Command?

The INSERT ALL statement allows multiple INSERT operations to be performed in a single command, which  improves efficiency and minimizes network round-trips.

Syntax of INSERT ALL

INSERT ALL

    INTO table1 (column1, column2) VALUES (value1, value2)

    INTO table2 (column1, column2) VALUES (value3, value4)

SELECT * FROM dual;

Examples of Using INSERT ALL




1. Inserting Data into Multiple Tables

INSERT ALL

    INTO employees_dept (id, name, department) VALUES (1, 'ABC_123', 'Sales')

    INTO employees_old (id, name, department) VALUES (2, 'XYZ_567', 'Sales')

SELECT * FROM dual;

2. Conditional Insertion with WHEN




INSERT ALL also allows conditional logic for row insertion.

INSERT ALL

    WHEN salary > 10000 THEN

        INTO high_employee_earners (id, name, salary) VALUES (id, name, salary)

    WHEN salary <= 5000 THEN

        INTO normal_earners (id, name, salary) VALUES (id, name, salary)

SELECT id, name, salary FROM employees;

This syntax can be used and modified accordingly for your requirements.

Key Procedures in DBMS_CLOUD

Key Procedures in DBMS_CLOUD

1. DBMS_CLOUD.CREATE_CREDENTIAL

   • Registers cloud credentials for secure access.

2. DBMS_CLOUD.DROP_CREDENTIAL

   • Deletes a previously created credential.

3. DBMS_CLOUD.LIST_FILES

   • Lists files in cloud storage locations.

4. DBMS_CLOUD.GET_OBJECT

   • Downloads files from cloud storage to a database directory.

5. DBMS_CLOUD.PUT_OBJECT

   • Uploads files from the database to cloud storage.

6. DBMS_CLOUD.COPY_DATA

   • Imports data from cloud storage into a database table.

7. DBMS_CLOUD.COPY_FILES

   • Copies files from one cloud location to another.

8. DBMS_CLOUD.DELETE_FILE

   • Deletes files from a cloud storage location.

9. DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE

   • Validates the structure and data of an external table.

10. DBMS_CLOUD.CREATE_EXTERNAL_TABLE

    • Creates an external table that reads data directly from cloud storage.

Creating Global Temporary Table - Oracle Syntax

 

Creating Global Temporary Table

Here is the syntax for creating a GTT is similar to a regular table but includes the ON COMMIT clause to define its behavior.

Example:

CREATE GLOBAL TEMPORARY TABLE sales_gtt (
    sale_number NUMBER,
    product_name VARCHAR2(100),
    sale_date DATE
) ON COMMIT DELETE ROWS;  -- Data deleted at the end of each transaction

Alternatively, for session-specific data retention:

CREATE GLOBAL TEMPORARY TABLE temp_logs (
    log_id NUMBER,
    log_message VARCHAR2(255)
) ON COMMIT PRESERVE ROWS;  -- Data persists until the session ends