Fusion SaaS: Add additional parameter to standard report without duplicating the ESS job

General

Oracle Fusion SaaS provides seeded BIP reports which can be customized by using the Customize option in BIP catalog.
This option allows to use standard ESS Job provided by Oracle, utilize the existing built-in report executions from UI like View Image in Receivables or Procurement and still modify the report layout and associate with a copy of vanilla data model if needed.
Although the report can be customized, the report parameters passed from the ESS job can not be modified, they can only be modified if the ESS is cloned but this will not support built-in report executions and in some cases due to dedicated parameters region the clone of standard ESS job is not possible.
This post will show a workaround how to access the Submission Notes field passed to an ESS job within the data model and then use it within the requested business logic modification.

Use Case

Modify standard Italy localization report and add a new parameter that should be displayed in the layout.
This particular report’s ESS can not cloned due to unique parameter’s region.

Solution Overview

The solution is based on the fact that there is a standard parameter passed to every report called from ESS job – fusionapps_request_id.
The idea behind the solution is to add such a parameter to the customized data model and from the data model to query the Fusion table containing the ESS job details – ESS_REQUEST_HISTORY.
The field called Submission Notes in standard report submission screen will be stored in the NAME field of the ESS_REQUEST_HISTORY table.

Here is the example:

1. Submit the report and add the submission notes:
   
2. Query the table to see the results – use our free Chrome extension tool:
   
3. Add to your data model query like this:
   select name from ESS_REQUEST_HISTORY where requestid = :fusionapps_request_id
4. Use the result of the query in other data sets and then in the layout.