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 )

Triggering HCM Extract Using OIC

We need to use the HCM Cloud Adapter

Under Services, select FlowActionsService and select submitFlow Operation

In the Mapper, pass the parameters as below. Make sure Recurring Flag is also passed as it is mandatory.

Save the integration and execute the same.