Passing Multiple Values to a Parameter in Oracle BI Publisher
When creating a List of Values (LOV) in Oracle Fusion Cloud Application BI Publisher and displaying a concatenated string (e.g., PERSON_NUMBER-DISPLAY_NAME), the search functionality does not work unless you add an alias to the concatenated column in your SQL query.
Why Aliasing Matters
When you use a concatenation expression in your SELECT clause (such as PersonDetailsPEO.PERSON_NUMBER ||'-'||PersonNamePEO.DISPLAY_NAME), you must assign it an explicit column alias for BI Publisher LOVs to function properly. Without the alias, the LOV will display data, but searching for substrings (like just the number or part of the name) will fail. This is because BI Publisher references columns by name, so unnamed expressions are not indexed for search within the LOV UI.
Example:
SELECT
PersonDetailsPEO.PERSON_NUMBER || '-' || PersonNamePEO.DISPLAY_NAME
, PersonNamePEO.PERSON_ID
FROM
PER_ALL_PEOPLE_F PersonDetailsPEO,
PER_PERSON_NAMES_F_V PersonNamePEO
WHERE
TRUNC(SYSDATE) BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE AND PersonDetailsPEO.EFFECTIVE_END_DATE
AND PersonDetailsPEO.PERSON_ID = PersonNamePEO.PERSON_ID
AND PersonNamePEO.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PersonNamePEO.EFFECTIVE_START_DATE AND PersonNamePEO.EFFECTIVE_END_DATE
ORDER BY
PersonNamePEO.DISPLAY_NAME ASC
How to Implement the Alias
Revise your SQL to give the concatenated column a user-friendly alias. Example below (PERSON_INFO):
SELECT
PersonDetailsPEO.PERSON_NUMBER || '-' || PersonNamePEO.DISPLAY_NAME AS PERSON_INFO
, PersonNamePEO.PERSON_ID
FROM
PER_ALL_PEOPLE_F PersonDetailsPEO,
PER_PERSON_NAMES_F_V PersonNamePEO
WHERE
TRUNC(SYSDATE) BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE AND PersonDetailsPEO.EFFECTIVE_END_DATE
AND PersonDetailsPEO.PERSON_ID = PersonNamePEO.PERSON_ID
AND PersonNamePEO.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PersonNamePEO.EFFECTIVE_START_DATE AND PersonNamePEO.EFFECTIVE_END_DATE
ORDER BY
PersonNamePEO.DISPLAY_NAME ASC
Now, PERSON_INFO is the alias by which the LOV component and search index the values.
Best Practices for BI Publisher LOVs
Always use simple, clear aliases for any concatenated fields intended for display or user selection.
Test the LOV search for “starts with” and “contains” using both parts of the concatenated string.
If you need to return both the display value and an internal code, include both columns with appropriate aliases (e.g.,
PERSON_INFO,PERSON_ID).
Summary
BI Publisher LOV search in Oracle Fusion Cloud ERP only operates correctly when each display field, especially a concatenated value, is given a SQL alias and referenced explicitly in the Data Model LOV definition.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
