Fusion Blog

EBS Blog


Contact Us

Fusion Blog
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive


Many a times we have a BI Report which has more than one input parameters and the data value is returned only when at least one of the parameter value is provided. In such cases an end user would expect to get prompted when they do not provide any input but as of today (release 13 20C application version of Oracle HCM Cloud Application) this feature is not delivered out of the box.

But no worries, we have an alternative solution and we would demonstrate the same here. Hopefully it would be useful.

So, without further ado let’s get started.

Configuration / Setup Steps

We would have to have a perform a series of steps to accomplish the same. The steps are:

  1. Main Data Set Query (SQL Query to fetch actual Data)
  2. Creating Message ( User Friendly Message to be displayed)
  3. Message Data Set Query ( SQL Query to fetch the message from Message Dictionary)
  4. Configure the BI Template 


Main Data Set Query

For this example, we will create a very simple sql query which will have two input parameters and it will fetch details from per_all_people_f table.

SQL Query

select papf.person_number,


       TO_DATE(papf.effective_start_date,'YYYY-MM-DD') effective_start_date,

       TO_DATE(papf.effective_end_date,'YYYY-MM-DD') effective_end_date

from   per_all_people_f papf 

where  trunc(sysdate) between papf.effective_start_date and papf.effective_end_date 

and    papf.person_number = nvl(:p_person_number,papf.person_number)

and    papf.person_id     = nvl(:p_person_id,papf.person_id)

and    (:p_person_number is NOT NULL OR :p_person_id is NOT NULL)


Creating Message

We will create a new custom message within Oracle HCM Cloud Application which  will be displayed when the parameter values are null. One may decide to use a static text too but one of the advantage of using a message is that one can dynamically change the message content from the application as per their requirement.

Navigation to create new custom message:

Login to Application -> Navigator -> Setup and Maintenance -> Search (Manage Messages)-> Create

Attribute Name

Attribute Value

Message Name



Business Intelligence for Risk Management


Business Intelligence for Risk Management

Message Number


*Message Type






Logging Enabled


*Short Text

Please provide a value for at least one of the input parameters. Please add value for at least one of the parameter and re-submit.



Message Data Set Query

We will create a separate data set to fetch message text.

SQL Query

select message_text message

from   fnd_new_messages fnm

where message_name = 'BI_ATLEAST_ONE_PARAMVALUE_REQD'

and     language_code = 'US'  

and     (:p_person_number IS NULL AND :p_person_id IS NULL)


One point to note here is that this data set will only return data if both the parameter values are NULL.

Once the data sets are created we will create a data model and the data model diagram would appear as below:


Configure BI Template

We will now create a BI Template which will have three sections namely Layout Grid (Report Header which contains Report Name, Run Date and Parameter Values passed) , Repeating Section 1 ( Details of Actual Data) and Repeating Section 2 (Error Message Data)

The completed template would appear as below:


And with this report creation is complete.


Verifying Results

We would have to run three different scenarios to ascertain whether the report meets business requirement.

Scenario 1 : Pass Value for One Parameter (Person Number)

We will only pass a valid value for person number and the person id field should be left blank


Scenario 2 : Pass Value for Second Parameter (Person ID)

We will only pass a valid value for person id and the person number field should be left blank


Scenario 3: Do Not Pass Any Value for Parameters

In this scenario, we will not pass any value for any input parameter


Summary / Conclusion

So basis the above results we can conclude that we can configure some setups to ensure the end user gets a actionable message when NULL values are passed to BI input parameters.

The solution also provides an opportunity to change the message as per specific needs as the same can be changed within the application using “Manage Messages” task.

Hope, the workaround is useful.

Thanks all for your time and have a nice day ahead.


Downloadable Files:



Ashish Harbhajanka

About the Author

Ashish Harbhajanka


Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK


Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  May 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
       1  2
  3  4  5  6  7  8  9

Enquire For Training

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner