In this article, I will demonstrate how to create a simple BIP report in Oracle Fusion Application and then configure it as an ESS job so that it can be submitted from Scheduled Processes.
What Is Oracle BI Publisher?
Oracle BI Publisher is a reporting tool available in Oracle
Fusion SaaS that allows users and developers to extract data and present it in
multiple output formats such as PDF, Excel, CSV, XML, and HTML.
It is commonly used to build operational, analytical, and
custom reports based on business requirements.
Prerequisites
Before starting, make sure the following prerequisites are
met:
- Access
to an Oracle Fusion SaaS instance with the required roles and privileges
to create reports and ESS jobs
- Basic
knowledge of SQL
- Understanding
of Oracle Fusion navigation for Reports and Analytics
High-Level Steps to Create a BI Publisher Report
The report development and deployment process mainly
consists of the following three components:
1. Data Model
The Data Model is the data source of the report. It
contains the SQL query, parameters, and list of values required to fetch the
data.
2. Report
The Report is the presentation layer. It uses the
Data Model and applies a layout template such as RTF, Excel, XSLT, or E-Text
to display the extracted data in the required format.
3. ESS Job Configuration
The ESS Job configuration makes the BI Publisher
report available in Scheduled Processes. This allows users to submit the
report as a scheduled job, enter parameters, and generate the output through
the standard Oracle Fusion process submission framework.
Now let us go step by step and create the Data Model,
Report, and ESS Job configuration in Oracle Fusion.
Creating the Data Model
Step 1 – Open Reports and Analytics
Log in to Oracle Fusion SaaS and navigate to:
Tools > Reports and Analytics
Once the page opens, click Browse Catalog to open the
BI catalog.
Step 2 – Choose the Target Folder
The catalog typically contains two main folders:
- My
Folders – accessible only to the logged-in user
- Shared
Folders – accessible to users based on assigned permissions
For custom development, navigate to the required custom
folder, for example:
Custom > Procurement
Then click:
New > Data Model
Step 3 – Create a SQL Data Set
Once the Data Model page opens:
- Click
Data Sets
- Click
the + icon
- Select
SQL Query
Now enter the required details such as:
- Name
- Data
Source
- SQL
Type
Then paste the SQL query in the text area and validate it.
After validating the SQL, the system automatically creates
the parameters referenced in the query.
SQL: SELECT pha.segment1 PO_NUMBER,
pha.creation_date po_creation_date,
pha.document_status,
pha.APPROVED_FLAG,
PersonName.FULL_NAME buyer_name,
ps.vendor_name,
pss.vendor_site_code site_name,
PLA.line_num,
pla.UNIT_PRICE,
pla.QUANTITY,
pla.UOM_CODE po_line_uom,
pla.UNIT_PRICE* pla.QUANTITY lineValue,
'Receipt details Start' receipt_details,
RSH.RECEIPT_NUM,
rsl.LINE_NUM receipt_line_number,
rsh.RECEIPT_SOURCE_CODE,
rsh.SHIPPED_DATE,
rsh.EXPECTED_RECEIPT_DATE,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED,
rsl.QUANTITY_DELIVERED,
rsl.QUANTITY_RETURNED,
rsl.QUANTITY_ACCEPTED,
rsl.QUANTITY_REJECTED,
rsl.UOM_CODE,
rsl.ITEM_DESCRIPTION,
rsl.SHIPMENT_LINE_STATUS_CODE,
rsl.COMMENTS,
rct.TRANSACTION_TYPE,
(SELECT meaning
FROM FND_LOOKUP_VALUES_TL
WHERE LOOKUP_CODE =rct.TRANSACTION_TYPE
AND lookup_type =
'RCV_TRANSACTION_TYPE'
and language='US'
) TRANSACTION_TYPE_meaning,
rct.TRANSACTION_DATE
FROM RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
PO_HEADERS_ALL PHA,
PER_PERSON_NAMES_F_V PersonName,
PO_LINES_ALL pla,
PO_LINE_TYPES_B,
POZ_SUPPLIERS_V ps,
POZ_SUPPLIER_SITES_V pss,
RCV_TRANSACTIONS rct
WHERE RSH.SHIPMENT_HEADER_ID =RSL.SHIPMENT_HEADER_ID
AND RSL.po_header_id =PHA.po_header_id
AND PersonName.PERSON_ID =PhA.agent_id
AND pla.po_header_id =PHA.po_header_id
AND
PO_LINE_TYPES_B.line_type_id=pla.LINE_TYPE_ID
AND pla.PO_LINE_ID =rsl.PO_LINE_ID
AND pha.VENDOR_ID =ps.vendor_id
AND pss.VENDOR_ID =ps.vendor_id
AND rct.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND rsl.SHIPMENT_LINE_ID =rct.SHIPMENT_LINE_ID
and trunc(pha.creation_date) between :p_start_po_date and :p_end_po_date
and ps.vendor_name = nvl(:P_vendor,
ps.vendor_name)
and pss.vendor_site_code = nvl(:P_vendor_site,
pss.vendor_site_code)
Configuring Parameters
In this example, four parameters are generated:
- P_START_PO_DATE
- P_END_PO_DATE
- P_VENDOR
- P_VENDOR_SITE
Date Parameters
For Start Date and End Date:
- Set
the data type appropriately
- Enter
a suitable display label
- Keep
the default date format unless a specific format is required
.
Dependent LOV Parameters
The report also includes two dependent list of values
parameters:
- P_VENDOR
- P_VENDOR_SITE
These parameters require separate SQL queries.
Select psv.vendor_name from
POZ_SUPPLIERS_V psv
select vendor_site_code
from POZ_SUPPLIER_SITES_V
where VENDOR_ID = (select vendor_id
from POZ_SUPPLIERS_V where vendor_name =
:P_vendor)
Note that :P_vendor in the Vendor Site LOV query must exactly match the
parameter name used in the main SQL. If the parameter name is different, the
dependent LOV will not work correctly.
Assign LOVs to Parameters
For both Vendor and Vendor Site parameters:
- Set Parameter
Type to Menu
- Assign
the relevant List of Values
For the Vendor parameter, enable:
Refresh other parameters on change
This is required because Vendor Site depends on the
selected Vendor.
After completing the setup, validate and save the Data
Model.
Testing the Data Model
To preview the data:
- Select
the Data Set from the left panel
- Open
the Data tab
- Click
View
Before proceeding to the report
layout, it is recommended to click:
Save As Sample Data
This sample XML can later be used
for template development.
At this stage, the Data Model is complete.
Creating the Report
Step 1 – Create a New Report
From the same folder, click:
New > Report
Browse and select the Data Model created in the previous
steps, then click Next.
Step 2 – Prepare and Upload the RTF Template
To create the RTF template:
- Export
the sample XML from the Data Model
- Open
Microsoft Word with the BI Publisher add-in
- Design
the required layout
- Save
the file as an RTF template
Then upload the prepared template to the report.
Enter the report name and select the appropriate layout
type.
After saving, test the report by entering parameter values
and clicking Apply.
If everything is configured correctly, the report will
generate successfully.
Give it a name and type
After saving, test the report by entering parameter values
and clicking Apply.
If everything is configured correctly, the report will
generate successfully.
Configuring the Report as an ESS Job
To make the BI Publisher report available in Scheduled
Processes, it must be configured as an Enterprise Scheduler Service
(ESS) job.
Navigate to the following task:
Manage Enterprise Scheduler Job Definitions and Job Sets
for Financial, Supply Chain Management, and Related Applications
Create new
Click on New ->
Report as shown in the below screenshot:
Enter Job Definition Details
Provide the following information:
- Display
Name
- Name
- Path
- Application
- Job
Type = BIPJobType
For the Report ID, enter the complete report path
excluding /Shared Folders/, and suffix it with .xdo.
Example
If the report is stored in:
/Custom/Procurement
And the report name is:
XXGNE_PO_TO_RECEIPT
Then the Report ID should be:
/Custom/Procurement/XXGNE_PO_TO_RECEIPT.xdo
Example values:
- Path:
/Custom/Procurement
- Report
ID: /Custom/Procurement/XXGNE_PO_TO_RECEIPT.xdo
Save the job definition.
Defining ESS Parameters
Now define the same parameters in the ESS job that were used
in the BI Publisher report.
For this example, the parameters are:
- Vendor
- Vendor
Site
- From
PO Date
- To
PO Date
Step 1 – Define LOV Sources
Go to the next tab:
Manage List of Value Sources
Create LOV sources for Vendor and Vendor Site.
For both, you can define the LOV Type as User
Defined and provide the relevant SQL queries.
Step 2 – Assign Parameters in Job Definition
Return to the Job Definition and assign the parameters.
Make sure that the Parameter Prompt exactly matches
the Display Label of the report parameter. This is important for proper
mapping.
Create the Vendor and Vendor Site parameters using LOVs, and
define the date parameters with the correct date/time data type.
Once completed, save the configuration.
At this point, the ESS job is ready.
Testing the ESS Job
Now navigate to Scheduled Processes and submit the
newly created ESS job.
You should see all parameters displayed correctly as
configured.
Submit the process and review the output.
If everything is set up correctly, the report will complete
successfully and the output can be downloaded in the required format
Conclusion
In this article, we created a custom BI Publisher report
in Oracle Fusion and then configured it as an ESS job so that it can be
submitted from Scheduled Processes.
This approach is useful when a report needs to be run on
demand, scheduled periodically, or made available to business users through
standard Oracle Fusion process submission.
For additional details and best practices, refer to the
latest Oracle documentation.
Oracle Fusion BI Publisher Report
Oracle Fusion ESS Job
Configure BIP Report as ESS Job
Oracle Fusion Custom Report
Oracle Fusion Scheduled Process Report
BI Publisher Report in Oracle Fusion
Oracle Fusion Reports and Analytics
Oracle Fusion ESS Configuration