HDL Assignment and Work Terms Update Query for Oracle HCM

select 'METADATA|WorkTerms|AssignmentId|AssignmentNumber|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|PeriodOfServiceId|PersonId|PersonNumber|LegalEmployerName|AssignmentStatusTypeCode|BusinessUnitShortCode|AssignmentCategory|GradeCode|HourlySalariedCode|JobCode|LocationCode|ManagerFlag|NormalHours|Frequency|NoticePeriod|NoticePeriodUOM|DepartmentName|PositionCode|PositionOverrideFlag|ProbationPeriod|ProbationUnit|ReasonCode|RetirementAge|RetirementDate|WorkAtHomeFlag|ActionCode|GUID|SourceSystemOwner|SourceSystemId'

from dual

UNION ALL

SELECT DISTINCT

       'MERGE|WorkTerms|' ||

       paam.work_terms_assignment_id  || '|' ||

                 paam.Assignment_Number || '|' ||

                 TO_CHAR(paam.effective_end_date,'yyyy/mm/dd') || '|' ||

       paam.Effective_Latest_Change || '|' ||

                 paam.Effective_Sequence || '|' ||

                 TO_CHAR(paam.effective_start_date,'yyyy/mm/dd') || '|' ||

                 paam.period_of_service_id || '|' ||

                 paam.person_id || '|' ||

                 papf.person_number || '|' ||

                 haou_le.name || '|' ||

                 past.assignment_status_code || '|' ||

                 haou_bu.name || '|'||

                 paam.employment_category || '|'||

                 pg.grade_code || '|'||

                 paam.HOURLY_SALARIED_CODE || '|'||

                 pj.job_code || '|'||

                 hl.internal_location_code || '|' ||

                 paam.manager_flag || '|' ||

                 paam.normal_hours || '|' ||

                 paam.frequency || '|' ||

                 paam.notice_period || '|'||

                 paam.Notice_Period_UOM || '|'||

                 haou_cc.name || '|'||

                 hapf.POSITION_CODE || '|'||

                 paam.Position_Override_Flag || '|' ||

                 paam.Probation_Period || '|' ||

                 paam.Probation_Unit || '|' ||

                 paam.reason_code || '|' ||

                 paam.Retirement_Age || '|' ||

                 paam.retirement_date || '|' ||

                 paam.work_at_home || '|' ||

                 paam.action_code || '|' ||

                 '|' ||

                 '|'

FROM

          fusion.per_all_people_f               papf

         ,fusion.per_all_assignments_m          paam

         ,fusion.hr_all_organization_units      haou_bu

         ,fusion.hr_all_organization_units      haou_le

         ,fusion.per_jobs                       pj

         ,fusion.per_jobs_f                     pjf

         ,fusion.hr_locations                   hl

         ,fusion.hr_all_organization_units      haou_cc

       ,fusion.per_periods_of_service         ppos

       ,fusion.per_grades                     pg

       ,fusion.per_person_types_tl            pptt

                 ,PER_ACTION_OCCURRENCES pao

                 ,fusion.per_actions_tl          patl

       ,fusion.per_action_reasons_tl   partl

                 ,fusion.HR_ALL_POSITIONS_F hapf

                 , per_assignment_status_types     past

     WHERE 1 = 1

               AND paam.person_id = papf.person_id

               AND hapf.position_id(+) = paam.position_id

     AND paam.effective_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date

     and paam.assignment_type = 'ET'

     and haou_bu.organization_id = paam.business_unit_id

     AND haou_le.organization_id = paam.legal_entity_id

     AND pj.job_id (+) = paam.job_id

     AND paam.effective_start_date BETWEEN pj.effective_start_date(+) AND pj.effective_end_date(+)

     AND pjf.job_id(+) = paam.job_id

     AND paam.effective_start_date BETWEEN pjf.effective_start_date(+) AND pjf.effective_end_date(+)

     AND hl.location_id (+) = paam.location_id

     AND haou_cc.organization_id (+) = paam.organization_id

    AND ppos.person_id              = paam.person_id

     AND ppos.period_of_service_id = paam.period_of_service_id

     AND pg.grade_id (+) = paam.grade_id

    AND pptt.person_type_id = paam.person_type_id

    and pptt.LANGUAGE = 'US'     

    AND pptt.SOURCE_LANG ='US'

              AND pao.action_id=patl.action_id(+)

  and patl.SOURCE_LANG(+) = 'US'

  and patl.LANGUAGE(+) = 'US'

  and pao.ACTION_OCCURRENCE_ID(+) = paam.ACTION_OCCURRENCE_ID

  AND pao.action_Reason_id=partl.action_Reason_id(+)

  and partl.SOURCE_LANG(+) = 'US'

   and partl.LANGUAGE(+) = 'US'

    AND paam.assignment_status_type_id  =   past.assignment_status_type_id

   AND PAPF.PERSON_NUMBER IN (:Person_Number)         

UNION ALL

select 'METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentType|AssignmentNumber|AssignmentStatusTypeCode|BusinessUnitShortCode|AssignmentCategory|GradeCode|HourlySalariedCode|JobCode|LocationCode|ManagerFlag|NormalHours|Frequency|NoticePeriod|NoticePeriodUOM|DepartmentName|PeriodOfServiceId|PersonId|PersonNumber|LegalEmployerName|PositionCode|PositionOverrideFlag|ProbationPeriod|ProbationUnit|ReasonCode|RetirementAge|RetirementDate|WorkAtHomeFlag|GUID|SourceSystemOwner|SourceSystemId'

from dual

UNION ALL

SELECT DISTINCT

       'MERGE|Assignment|' ||

       paam.Assignment_Id || '|' ||

                 paam.work_terms_assignment_id  || '|' ||

                 paam.action_code || '|' ||

                 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_Number || '|' ||

                 past.assignment_status_code || '|' ||

                 haou_bu.name || '|'||

                 paam.employment_category || '|'||

       pg.grade_code || '|'||

                 paam.HOURLY_SALARIED_CODE || '|'||

                 pj.job_code || '|'||

                 hl.internal_location_code || '|' ||

                 paam.manager_flag || '|' ||

                 paam.normal_hours || '|' ||

                 paam.frequency || '|' ||

                 paam.notice_period || '|'||

                 paam.Notice_Period_UOM || '|'||

                 haou_cc.name || '|'||

                  paam.period_of_service_id || '|' ||

                 paam.person_id || '|' ||

                 papf.person_number || '|' ||   

                 haou_le.name || '|' ||               

                 hapf.POSITION_CODE || '|'||

                 paam.Position_Override_Flag || '|' ||

                 paam.Probation_Period || '|' ||

                 paam.Probation_Unit || '|' ||

                 paam.reason_code || '|' ||

                 paam.Retirement_Age || '|' ||

                 paam.retirement_date || '|' ||

                 paam.work_at_home || '|' ||

                 '|' ||

                 '|'

                 FROM

          fusion.per_all_people_f               papf

         ,fusion.per_all_assignments_m          paam

         ,fusion.hr_all_organization_units      haou_bu

         ,fusion.hr_all_organization_units      haou_le

         ,fusion.per_jobs                       pj

         ,fusion.per_jobs_f                     pjf

         ,fusion.hr_locations                   hl

         ,fusion.hr_all_organization_units      haou_cc

       ,fusion.per_periods_of_service         ppos

       ,fusion.per_grades                     pg

       ,fusion.per_person_types_tl            pptt

                 ,PER_ACTION_OCCURRENCES pao

                ,fusion.HR_ALL_POSITIONS_F hapf

                , per_assignment_status_types     past

     WHERE 1 = 1

               AND paam.person_id = papf.person_id

               AND hapf.position_id(+) = paam.position_id

     AND paam.effective_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date

     and paam.assignment_type = 'E'

     and haou_bu.organization_id = paam.business_unit_id

     AND haou_le.organization_id = paam.legal_entity_id

     AND pj.job_id (+) = paam.job_id

     AND paam.effective_start_date BETWEEN pj.effective_start_date(+) AND pj.effective_end_date(+)

     AND pjf.job_id(+) = paam.job_id

     AND paam.effective_start_date BETWEEN pjf.effective_start_date(+) AND pjf.effective_end_date(+)

     AND hl.location_id (+) = paam.location_id

     AND haou_cc.organization_id (+) = paam.organization_id

    AND ppos.person_id              = paam.person_id

     AND ppos.period_of_service_id = paam.period_of_service_id

     AND pg.grade_id (+) = paam.grade_id

    AND pptt.person_type_id = paam.person_type_id

    and pptt.LANGUAGE = 'US'     

    AND pptt.SOURCE_LANG ='US'

               AND paam.assignment_status_type_id  =   past.assignment_status_type_id

  and pao.ACTION_OCCURRENCE_ID(+) = paam.ACTION_OCCURRENCE_ID

       AND PAPF.PERSON_NUMBER IN (:Person_Number)

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