SQL Query to get the Work Assignment in Metadata format

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  
-- ( select papf.person_number from per_all_people_f papf where papf.person_id = paam.person_id and rownum = 1) person_number, 
'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)||'|'||'|'||'|'||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.