SQL Query to Generate HDL Data Lines for Assignment Business Object

SELECT 
  'METADATA|Assignment|AssignmentId|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentType|AssignmentStatusTypeId|BusinessUnitId|PeriodOfServiceId|PersonId|PrimaryFlag|ActionCode|ReasonCode|WorkTermsAssignmentId|JobCode|DepartmentName|DefaultExpenseAccount|InternalFloor'
FROM dual

UNION ALL

SELECT  
  'MERGE|Assignment|'||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||'|'||
  (SELECT pjft.job_code FROM PER_JOBS_F_VL pjft WHERE paam.job_id = pjft.job_id AND ROWNUM=1)||'|'||
  '|'|| -- DepartmentName placeholder
  '|'|| -- DefaultExpenseAccount placeholder
  paam.internal_floor
FROM per_all_assignments_m paam 
WHERE TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND paam.assignment_type IN ('E','C')
  AND paam.assignment_status_type = 'ACTIVE'
  AND paam.person_id IN (
      SELECT person_id 
      FROM per_all_people_f 
      WHERE person_number IN ('1234')
  )

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