There is a common requirement to use value sets in various DFF attributes across Fusion HCM application. Oracle HCM supports different kind of value sets – Independent, Table validated etc.
There is a use case where a user wants to see a list of values containing numbers from 1 to n. There are two approaches to create a value set for this. Either you can define an Independent value set with 1 to n unique values or you can define a table validated value set.
Independent value set approach works fine if the value of n is small. But if the value of n is a large integer for example, n = 60, then it is not pragmatic to go ahead with a independent value set.
See the below example to define a table validated value set which will display values ranging from 1 to 60:
| FROM Clause | (SELECT TO_NUMBER( LEVEL) value FROM DUAL CONNECT BY LEVEL <= 60) a |
| Value Attributes Table Alias | |
| *Value Column Name | a.value |
| Value Column Type | NUMBER |
| Value Column Length | |
| Description Column Name | |
| Description Column Type | |
| Description Column Length | |
| ID Column Name | a.value |
| ID Column Type | NUMBER |
| ID Column Length | |
| Enabled Flag Column Name | |
| Start Date Column Name | |
| End Date Column Name | |
| WHERE Clause | 1=1 |
| ORDER BY Clause | a.value |
This will look like below in application:
