Saturday, 28 June 2025

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

Go to Catalog → Create → Data Model
Choose SQL Query as your data source
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'

Click View Data to test
Optionally, add Parameters (e.g., supplier, date range)
Save the Data Model

Step 3: Create a Report Layout

Click on the Data Model → Create Report
Choose:
- Use Existing Template (RTF, Excel, PDF) or
- Create a Blank Report for layout
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
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:

Navigate to Reports and Analytics > Create > Analysis.
Select a Subject Area (e.g., “Workforce Management – Worker Assignment Real Time”).
Drag fields into Selected Columns and apply filters.
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:

Go to Reports and Analytics > BI Publisher.
Create Data Model: Use SQL query, Web Service, or Data Sets.
Upload an RTF/XSL/Excel layout using MS Word Plugin.
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:

Install Oracle Smart View Add-In in Excel.
Connect to Oracle Fusion Cloud URL.
Select cube (e.g., Financials Cloud - General Ledger).
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.

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.