SQL Queries to Generate HDL Data for WorkTerms and Assignments

SELECT  
  (SELECT papf.person_number 
   FROM per_all_people_f papf 
   WHERE papf.person_id = paam.person_id AND ROWNUM = 1) AS person_number,
  
  'MERGE|WorkTerms|'||paam.assignment_id||'|'||
  TO_CHAR(paam.effective_start_date,'YYYY/MM/DD')||'|'||
  TO_CHAR(paam.effective_end_date,'YYYY/MM/DD')||'|'||
  paam.effective_sequence||'|'||
  paam.effective_latest_change||'|'||
  paam.assignment_type||'|'||
  paam.assignment_status_type_id||'|'||
  paam.business_unit_id||'|'||
  paam.period_of_service_id||'|'||
  paam.person_id||'|'||
  paam.primary_flag||'|'||
  paam.action_code||'|'||
  paam.reason_code||'|'||
  paam.work_terms_assignment_id||'|||'
  
FROM per_all_assignments_m paam
WHERE TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND paam.assignment_type IN ('ET', 'CT')
  AND paam.person_id IN (
    SELECT person_id 
    FROM per_all_people_f 
    WHERE person_number IN ('21792','10471','9088','22041','14489','12249','13140','12322',
                            '18127','14996','12584','4970','16028','12904','14290','14386',
                            '9211','48688','19888','16408','20963','6733','5584','16716',
                            '9424','15873','16282','57442')
  )

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.