In this article we will look into how to bypass the FROM_CLAUSE limit restrictions of 240 chars and use a workaround which could be used to achieve complex sql queries in table based Valueset to be used as LOV for flex fields or to retrieve values in HCM Extract integrations.
In our previous articles, we have covered the basics of valusets, how to get the Valueset details in SQL Query and how to achieve aggregate functions in valusets. If you have missed any of them.
Basics of Valuesets in Fusion HCM
SQL Query to get the Valueset and Lookup Values
Using Table based Valueset when DBI is unavailable
Using Aggregate functions in Table based Valueset
There is a limitation in the table Valueset that it can only accept maximum of 240 characters. If you have multiple tables or if you want to use some sub queries to make up a table then you can’t use them.
In this article I will come up with a work around for this problem by splitting the query into two parts and creating two valuesets and using one table Valueset in another. Looks interesting right..
Let’s look into the details and sample use case for it.
Consider we are creating a DFF and the LOV of that field should display all User’s names in Fusion (which includes both Employees and Non-Employees like parties). Getting the Names from PER_PEOPLE_NAMES_F is easy and we can write query easily. However if the person is not an employee, we need to get the details from PER_LDAP_USERS where the party users information will be stored.
So, this would the SQL that will fetch the required info for me. However when I try to put this into table based Valueset, it failed as the FROM clause length is more than 240.
SELECT nvl(ppfv.display_name, sq1.display_name) AS display_name
FROM per_users pu,
per_person_names_f_v ppfv,
(SELECT plu.username,
plu.first_name || ' ' || plu.last_name AS display_name,
row_number() OVER (PARTITION BY plu.username ORDER BY last_update_date DESC) plu_row_number
FROM per_ldap_users plu
WHERE plu.request_type IN ('CREATE','UPDATE')
AND plu.request_status = 'COMPLETE') sq1
WHERE pu.person_id = ppfv.person_id(+)
AND nvl(pu.HR_TERMINATED, 'N') <> 'Y'
AND nvl(pu.SUSPENDED, 'N') <> 'Y'
AND trunc(sysdate) BETWEEN trunc(ppfv.effective_start_date(+)) AND trunc(ppfv.effective_end_date(+))
AND pu.username = sq1.username(+)
AND sq1.plu_row_number(+) = 1
I tried to make this query compact by removing some conditions but i was getting incorrect results.
So, I thought of splitting this query into two parts and using the PAY_FF_FUNCTIONS.GVS function which is used to test the table based valuesets in SQL Query and here are the two SQLs that were created.
Table of Contents
Child Valueset XX_CHILD_VS:
select plu.first_name || ' ' || plu.last_name as display_name from per_ldap_users plu where plu.request_type in ( 'CREATE', 'UPDATE' ) and plu.request_status = 'COMPLETE' AND plu.username = :{PARAMETER.USERNAME} order by last_update_date desc
This Valueset derives the ldap name from per_ldap_users for the input username.
Parent Valueset XX_PARENT_VS:
select nvl(ppfv.display_name,(SELECT PAY_FF_FUNCTIONS.GVS('XX_CHILD_VS','|=USER_NAME='''||PU.USERNAME||'''') VALUE FROM DUAL)) as display_name from per_users pu, per_person_names_f_v ppfv where pu.person_id = ppfv.person_id(+) and nvl( pu.HR_TERMINATED, 'N') <> 'Y' and nvl( pu.SUSPENDED, 'N') <> 'Y' and trunc(sysdate) BETWEEN trunc( ppfv.effective_start_date(+)) AND trunc(ppfv.effective_end_date(+))
This parent valuset gets the employee names from PPNF table for all users, if at all the name doesn’t exist, then it calls the child Valueset to get the ldap name for that user which solves our purpose of displaying both employee names and ldap names in the LOV.
I have tested this approach and it has worked fine. In most of the cases where the complete query can’t fit in the from clause, this approach can be used. This is a wonderful workaround and is never explored by anyone that I know of. If you have any other workarounds on getting this done, please post them in comments section.
Note: The PAY_FF_FUNCTIONS.GVS function will return only one row of data for the input parameters passed.. Even if the query returns multiple rows, the function will return the first row retrieved. So make sure order by clause is used in the child Valueset to pick up the right row that needs to be fetched.
Tip: PAY_FF_FUNCTIONS.GVS function can also be used in HCM Extract Advanced Filter criteria if you are using a complex criteria and the length exceeds the 4000 character limit set by Oracle. By using this function, you can push the validation code to table based value set and save characters in advanced filter.
Excellent…
Yes, we can use GVS function in extract’s advanced filter criteria also, it also has some character limit of around 2000. I have used it and worked fine.
Yes. Raghavendra, We can use this GVS function in HCM Extract Advanced Filter condition as well.. The limit is 4000 characters for advanced filter.