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 Clause | HR_ORG_UNIT_CLASSIFICATIONS_F HOCF, HR_ORGANIZATION_UNITS_F_TL HOUT |
| Value Attributes Table Alias | |
| *Value Column Name | SUBSTR(HOUT.NAME,1,150) |
| Value Column Type | VARCHAR2 |
| Value Column Length | 150 |
| Description Column Name | |
| Description Column Type | |
| Description Column Length | |
| ID Column Name | TO_CHAR(HOUT.ORGANIZATION_ID) |
| ID Column Type | VARCHAR2 |
| ID Column Length | 40 |
| Enabled Flag Column Name | |
| Start Date Column Name | |
| End Date Column Name | |
| WHERE Clause | HOCF.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 |