Sample HDL for Assignment Data Load

METADATA|WorkTerms|AssignmentNumber|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId
MERGE|WorkTerms|ET5087851|ASG_CHANGE|DATAMIGRATION|2021/10/01|4712/12/31|Y|2|300000619781821

METADATA|Assignment|AssignmentNumber|ActionCode|ReasonCode|WorkTermsAssignmentId|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|FLEX:PER_ASG_DF|pensionPlanCode(PER_ASG_DF=CA)|payrollDivision(PER_ASG_DF=CA)
MERGE|Assignment|E5087851|ASG_CHANGE|DATAMIGRATION|300000619847597|2021/10/01|4712/12/31|Y|2|300000619781821|CA|08|REG

Query for WorkTerms
SELECT   
PAPF.PERSON_NUMBER,PAAF.assignment_type,
paaf.effective_start_date,
'MERGE' METADATA,
'WorkTerms' WorkTerms,
PAAF.assignment_number,
'ASG_CHANGE' ActionCode,
'DATAMIGRATION' ReasonCode,
'2021/10/01' EffectiveStartDate,
'4712/12/31' EffectiveEndDate,
'Y' EffectiveLatestChange,
decode(paaf.effective_start_date,to_date('10/01/2021','mm/dd/rrrr'),PAAF.EFFECTIVE_SEQUENCE+1,1) EffectiveSequence,
PAAF.PERIOD_OF_SERVICE_ID PeriodOfServiceId
FROM     per_all_people_f PAPF
,        per_person_names_f PPNF
,        per_all_assignments_f PAAF
,        PER_PERIODS_OF_SERVICE PPOS_MAIN
WHERE    PAPF.person_id = PPNF.person_id
AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PPNF.effective_start_date AND PPNF.effective_end_date
AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date
AND      PPNF.name_type = 'GLOBAL'
AND      PPOS_MAIN.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS WHERE PPOS.person_id = PAPF.person_id)
AND      PPOS_MAIN.person_id = PAPF.person_id
AND      PAAF.person_id = PAPF.person_id
AND      PAAF.assignment_type ='ET'--IN ('E','C','P','N')
AND      PPOS_MAIN.period_of_service_id = PAAF.period_of_service_id
and paaf.legislation_code='CA'
AND      PAPF.person_number IN ('5087851')    
ORDER BY to_number( PAPF.person_number )

Assignment Line Query

SELECT   

PAPF.PERSON_NUMBER,PAAF.assignment_type,

paaf.effective_start_date,

'MERGE' METADATA,

'Assignment' Assignment,

PAAF.assignment_number,

'ASG_CHANGE' ActionCode,

'DATAMIGRATION' ReasonCode,

PAAF.WORK_TERMS_ASSIGNMENT_ID WorkTermsAssignmentId,

'2021/10/01' EffectiveStartDate,

'4712/12/31' EffectiveEndDate,

'Y' EffectiveLatestChange,

decode(paaf.effective_start_date,to_date('10/01/2021','mm/dd/rrrr'),PAAF.EFFECTIVE_SEQUENCE+1,1) EffectiveSequence,

PAAF.PERIOD_OF_SERVICE_ID PeriodOfServiceId,

'CA' "FLEX:PER_ASG_DF",

'' "pensionPlanCode(PER_ASG_DF=CA)",

'' "payrollDivision(PER_ASG_DF=CA)"

FROM     per_all_people_f PAPF

,        per_person_names_f PPNF

,        per_all_assignments_f PAAF

,        PER_PERIODS_OF_SERVICE PPOS_MAIN

WHERE    PAPF.person_id = PPNF.person_id

AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date

AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PPNF.effective_start_date AND PPNF.effective_end_date

AND      to_date('10/01/2021','mm/dd/rrrr') BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date

AND      PPNF.name_type = 'GLOBAL'

AND      PPOS_MAIN.date_start = (SELECT max(date_start) FROM PER_PERIODS_OF_SERVICE PPOS WHERE PPOS.person_id = PAPF.person_id)

AND      PPOS_MAIN.person_id = PAPF.person_id

AND      PAAF.person_id = PAPF.person_id

AND      PAAF.assignment_type ='E'--IN ('E','C','P','N')

AND      PPOS_MAIN.period_of_service_id = PAAF.period_of_service_id

and paaf.legislation_code='CA'

AND      PAPF.person_number IN ('5087851')    

ORDER BY to_number( PAPF.person_number )