Value Set – Table Value set to get list of employees

While extending the DFF attributes for additional functionality, one of the common requirements is to get the list of workers in the system.

For this purpose a table based value set can be defined and attached to the DFF attribute.

FROM Clause	per_all_people_f papf, per_person_names_f ppnf
Value Attributes Table Alias
*Value Column Name	papf.person_number
Value Column Type	VARCHAR2
Value Column Length	30
Description Column Name	ppnf.full_name
Description Column Type
Description Column Length
ID Column Name	papf.person_number
ID Column Type	VARCHAR2
ID Column Length	30
Enabled Flag Column Name
Start Date Column Name
End Date Column Name
WHERE Clause	trunc(SYSDATE) between papf.effective_start_date AND papf.effective_end_date AND trunc(SYSDATE) between ppnf.effective_start_date AND ppnf.effective_end_date and ppnf.name_type = ‘GLOBAL’ and ppnf.person_id=papf.person_id and papf.person_id IN (select distinct person_id from per_periods_of_Service ppos where NVL(ppos.actual_termination_date, trunc(sysdate)) >= trunc(sysdate))
ORDER BY Clause

The above value set will show a list of only active employees. If you want to include inactive employees as well, please modify the where clause as below:

trunc(SYSDATE) between papf.effective_start_date AND papf.effective_end_date
AND trunc(SYSDATE) between ppnf.effective_start_date AND ppnf.effective_end_date
and ppnf.name_type = 'GLOBAL'
and ppnf.person_id=papf.person_id
and papf.person_id IN (select distinct person_id from per_periods_of_Service ppos)