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)