Search for:
Aug 25 2022
Configuration – Value Set to show numbers from 1 to 60

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 Namea.value
Value Column TypeNUMBER
Value Column Length
Description Column Name
Description Column Type 
Description Column Length
ID Column Namea.value
ID Column TypeNUMBER
ID Column Length
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clause1=1
ORDER BY Clausea.value

This will look like below in application:

Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of courses
*FROM Clausewlf_learning_items_f wlif, wlf_learning_items_f_tl wlift
Value Attributes Table Alias 
*Value Column NameSUBSTR(wlift.name,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(wlif.LEARNING_ITEM_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausewlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID AND wlift.language = ‘US’ AND wlif.LEARNING_ITEM_TYPE = ‘ORA_COURSE’ AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date and wlif.effective_end_date AND wlif.effective_start_date BETWEEN wlift.effective_start_date and wlift.effective_end_date
ORDER BY Clause 
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of departments

Use below setup to create a table based value set which will show the list of all departments:

*FROM ClauseHR_ORG_UNIT_CLASSIFICATIONS_F HOCF, HR_ORGANIZATION_UNITS_F_TL HOUT
Value Attributes Table Alias 
*Value Column NameSUBSTR(HOUT.NAME,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(HOUT.ORGANIZATION_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE ClauseHOCF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID AND TRUNC(SYSDATE) BETWEEN HOCF.EFFECTIVE_START_DATE AND HOCF.EFFECTIVE_END_DATE AND HOCF.EFFECTIVE_START_DATE BETWEEN HOUT.EFFECTIVE_START_DATE AND HOUT.EFFECTIVE_END_DATE AND HOUT.LANGUAGE = USERENV(‘LANG’) AND HOCF.CATEGORY_CODE = ‘DEPARTMENT’
ORDER BY Clause 
Mandeep Gupta 0 Comments