HDL – Assignment Working Hour Pattern

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;