Oracle has provided a new feature where working hours for each day can be stored against assignment working hours.
The data is stored in PER_WORKING_HOUR_PATTERNS_F table.
Use below query to extract the data:
SELECT papf.person_number, pwhpf.*
FROM PER_WORKING_HOUR_PATTERNS_F pwhpf
,PER_ALL_ASSIGNMENTS_M paam
,PER_ALL_PEOPLE_F papf
WHERE pwhpf.OBJECT_ID = paam.ASSIGNMENT_ID
AND paam.PERSON_ID = papf.PERSON_ID
AND papf.person_number = '10011'
AND paam.assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.effective_start_date BETWEEN pwhpf.effective_start_date AND pwhpf.effective_end_date
Query to extract data in HDL format:
SELECT
'METADATA|WorkingHourPattern|WorkingHourPatternId|EffectiveStartDate|EffectiveEndDate|Object|ObjectId|AssignmentNumber|PersonId|ReplaceFirstEffectiveStartDate' as DATA_ROW, 1 ORDERBY From dual
UNION
SELECT 'MERGE|WorkingHourPattern|'
|| pwhpf.WORKING_HOUR_PATTERN_ID
|| '|'
|| TO_CHAR(pwhpf.EFFECTIVE_START_DATE,'YYYY/MM/DD')
|| '|'
|| TO_CHAR(pwhpf.EFFECTIVE_END_DATE,'YYYY/MM/DD')
|| '|ASSIGNMENT|'
|| pwhpf.object_id
|| '|'
|| paam.assignment_number
|| '|'
|| paam.person_id
|| '|'
|| 'Y'
as DATA_ROW, 2 ORDERBY
FROM PER_WORKING_HOUR_PATTERNS_F pwhpf
,PER_ALL_ASSIGNMENTS_M paam
WHERE paam.assignment_id = pwhpf.object_id
AND paam.assignment_type = 'E'
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.effective_start_date BETWEEN pwhpf.effective_start_date AND pwhpf.effective_end_date;