I am written a query which pulls work schedule data on person level in Oracle Fusion HCM. What is the filter or the join condition which I need to add to pull the latest date column. I have used the tables
PER_SCHEDULE_ASSIGNMENTS PSA , ZMM_SR_SCHEDULES_TL ZSST , PER_ALL_ASSIGNMENTS_M PAAM , PER_ALL_PEOPLE_F PAPF
The query returns 4 rows with different dates. How to return only 1 row which has the latest date column in it?
Use SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE in all the tables where the date start date is there.
This will get you the current row as of today from those tables. You won't have any duplicates anymore.