Fusion Applications BI Publisher Parameter List Of Values Search Not Working - Search Does Not Return Values That Are In The Parameter List (Doc ID 1518332.1)

Last updated on MAY 27, 2023

Applies to:

Oracle Fusion Application Toolkit Cloud Service - Version 11.1.10.0.0 and later
Oracle Fusion Application Toolkit - Version 11.1.4.0.0 and later
Oracle Fusion Global Human Resources Cloud Service - Version 11.1.9.2.0 to 11.1.9.2.0 [Release 1.0]
Information in this document applies to any platform.

Symptoms

You have created a BI Publisher report. In the Data Model you added a parameter for the report. The parameter is based on a List of Values that is generated using a SQL statement. When you run the report you use the list of values for parameters and see values. There is a Search functionality for the parameter list. This search does not return values, even when you search on an item that you can clearly see in the parameter list.

Two SQL statements that prevent the search from working are:

SQL1

select '-Select-' from dual
union
select location_code from fusion.hr_locations_all
where trunc(sysdate) between effective_start_date and effective_end_date
order by 1

When you try to use the Search functionality for the parameter's list of values, it spins and then says no data found. There is nothing written in the bipublisher.log file.

Example:

When the list of values come up, you can see values there. If there are several that begin with FAST, do a "begins with" search that starts with FAST or FA. The clock spins and then it says "no data found".

SQL2

Select distinct nvl(CUSTOM_SEGMENT9,'Some Default Value')
from Fusion.Cmp_Cwb_Person_Info
order by 1

When you try to use the Search functionality for the parameter's list of values, it spins and then there are no results and no message that no data was found.

Example:

When the list of values comes up there are 2 items in the list, "All" and "Some Default Value" (this test system had no data configured for this table.column). Search on something that begins with A or S. The clock stops spinning with no results in the window, but it does not say "no data found". There is nothing written in to the bipublisher.log file.