In this article we will look into details on why the assignment name and job names wouldn’t be in sync and we will also look into how to resolve this issue manually or using HDL file.
Table of Contents
Background of Assignment Name and Job Name:
When an Assignment has a Job assigned to it, the “Assignment Name” is by default set to either the name of that Job, if the assignment sequence is 1 (eg: Doctor 123), or a combination of the name of that Job and the Assignment Sequence, if the assignment sequence is greater than 1 (eg: Doctor 123-2).
- From then on if the Job assigned to the Assignment is ever changed, the “Assignment Name” changes automatically to match the new “Job Name”.
- However, if for some reason, the “Assignment Name” becomes out of sync with the “Job Name”, then the “Assignment Name” will no longer change automatically if the Job is ever changed.
- If user has manually updated the Assignment Name to something other than Job Name, then any change to the Job will not reflect in the Assignment Name for that employee as the Assignment Name is manually overridden.
To fix this Assignment Name and Job Name not in sync issue, Oracle suggests to run the “Assignment Name and Job Name Mismatch – Report” with blank input, so it identifies all employees with such issue and reports them on the output file. However the diagnostic test will not automatically fix the sync issues. We have to either manually fix the Assignment Name if there are very less employees with this issue or prepare the HDL file and load it to fix the issue. You can refer the Self-Service Data Integrity Framework for Employment Flows – Part 1 (Doc ID 2548287.1)
If you want to fix the Assignment Names manually, then refer Fusion Global HR: Why Assignment Title In Manage Employment page Is Not Correctly Displaying The Job As Expected. (Doc ID 2373918.1)
It’s better to create a BI Report that finds out all discrepancies and then do the HDL load instead of the Diagnostic report which takes up many hours to run for all employees.
We will check sample employee 275 who has an overridden Assignment Name and the job and assignment name is not in sync.

Now, we will run the below SQL Query for this employee and it will produce the HDL output to update the Top of Stack row of the employee. We need to copy rename it Worker.dat and load it.
SELECT 'SET PURGE_FUTURE_CHANGES N' Worker from dual UNION ALL SELECT 'METADATA|WorkTerms|AssignmentId|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|PrimaryWorkTermsFlag|AssignmentStatusTypeId|AssignmentNumber' Worker FROM dual UNION ALL SELECT 'MERGE|WorkTerms|' || assignment_id || '|' || action_code || '|' || reason_code || '|' || to_char(effective_start_date, 'YYYY/MM/DD') || '|' || to_char(effective_end_date, 'YYYY/MM/DD') || '|' || effective_latest_change || '|' || effective_sequence || '|' || period_of_service_id || '|' || primary_work_terms_flag || '|' || assignment_status_type_id || '|' || assignment_number FROM per_all_assignments_m WHERE 1 = 1 and person_id in (select person_id from per_all_people_f where person_number in ('275')) AND assignment_id IN ( SELECT paam.WORK_TERMS_ASSIGNMENT_ID FROM per_all_assignments_m paam ,per_jobs_f_vl job WHERE paam.assignment_type IN ('E','C','P') AND paam.primary_flag = 'Y' AND paam.job_id = job.job_id AND paam.ASSIGNMENT_NAME <> job.name AND sysdate BETWEEN paam.effective_Start_date AND paam.effective_end_date AND sysdate BETWEEN job.effective_Start_date AND job.effective_end_date) AND assignment_type IN ('ET','CT','PT') AND EFFECTIVE_LATEST_CHANGE = 'Y' AND trunc(sysdate) BETWEEN effective_Start_date AND effective_end_date UNION ALL SELECT 'METADATA|Assignment|AssignmentId|ActionCode|ReasonCode|WorkTermsAssignmentId|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|PrimaryFlag|PrimaryAssignmentFlag|AssignmentStatusTypeId|AssignmentName|AssignmentNumber' FROM dual UNION ALL SELECT 'MERGE|Assignment|' || paam.assignment_id || '|' || paam.action_code || '|' || paam.reason_code || '|' || paam.work_terms_assignment_id || '|' || to_char(paam.effective_start_date, 'YYYY/MM/DD') || '|' || to_char(paam.effective_end_date, 'YYYY/MM/DD') || '|' || paam.effective_latest_change || '|' || paam.effective_sequence || '|' || paam.period_of_service_id || '|' || paam.primary_flag || '|' || paam.primary_assignment_flag || '|' || paam.assignment_status_type_id || '|' || job.name || '|' || paam.assignment_number FROM per_all_assignments_m paam ,per_jobs_f_vl job WHERE assignment_type IN ('E','C','P') AND paam.primary_flag = 'Y' AND paam.effective_latest_change = 'Y' AND paam.job_id = job.job_id AND paam.ASSIGNMENT_NAME <> job.name and person_id in (select person_id from per_all_people_f where person_number in ('275')) AND sysdate BETWEEN paam.effective_Start_date AND paam.effective_end_date AND sysdate BETWEEN job.effective_Start_date AND job.effective_end_date

Worker.dat
SET PURGE_FUTURE_CHANGES N
METADATA|WorkTerms|AssignmentId|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|PrimaryWorkTermsFlag|AssignmentStatusTypeId|AssignmentNumber
MERGE|WorkTerms|300000048159209|PROMOTION|CMP_PERF|2013/03/01|4712/12/31|Y|1|300000048159208|N|1|ET275
METADATA|Assignment|AssignmentId|ActionCode|ReasonCode|WorkTermsAssignmentId|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|PrimaryFlag|PrimaryAssignmentFlag|AssignmentStatusTypeId|AssignmentName|AssignmentNumber
MERGE|Assignment|300000048159214|PROMOTION|CMP_PERF|300000048159209|2013/03/01|4712/12/31|Y|1|300000048159208|Y|Y|1|Vice President of HR|E275
Now save this file as Worker.dat and zip it and import this HDL file.
Data Exchange > Import and Load Data


We can see that the Job and Assignment names are in sync now after the HDL update.
You can run the below query to find out all employees who have a change in the Assignment Name and Job Name and generate the HDL file.
Disclaimer: Do your due diligence before proceeding with this load by checking if all employees returned by the query really have the issue or not. Even if some employee has an overridden assignment name, it will get updated to Job Name to make them in sync.
SQL Query to get the HDL file for all employees whose Job and Assignment Names are not in sync on the Top Of Stack row:
SELECT 'SET PURGE_FUTURE_CHANGES N' Worker from dual UNION ALL SELECT 'METADATA|WorkTerms|AssignmentId|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|PrimaryWorkTermsFlag|AssignmentStatusTypeId|AssignmentNumber' Worker FROM dual UNION ALL SELECT 'MERGE|WorkTerms|' || assignment_id || '|' || action_code || '|' || reason_code || '|' || to_char(effective_start_date, 'YYYY/MM/DD') || '|' || to_char(effective_end_date, 'YYYY/MM/DD') || '|' || effective_latest_change || '|' || effective_sequence || '|' || period_of_service_id || '|' || primary_work_terms_flag || '|' || assignment_status_type_id || '|' || assignment_number FROM per_all_assignments_m WHERE 1 = 1 --and person_id in (select person_id from per_all_people_f where person_number in ('Person Numbers')) AND assignment_id IN ( SELECT paam.WORK_TERMS_ASSIGNMENT_ID FROM per_all_assignments_m paam ,per_jobs_f_vl job WHERE paam.assignment_type IN ('E','C','P') AND paam.primary_flag = 'Y' AND paam.job_id = job.job_id AND paam.ASSIGNMENT_NAME <> job.name AND sysdate BETWEEN paam.effective_Start_date AND paam.effective_end_date AND sysdate BETWEEN job.effective_Start_date AND job.effective_end_date) AND assignment_type IN ('ET','CT','PT') AND EFFECTIVE_LATEST_CHANGE = 'Y' AND trunc(sysdate) BETWEEN effective_Start_date AND effective_end_date UNION ALL SELECT 'METADATA|Assignment|AssignmentId|ActionCode|ReasonCode|WorkTermsAssignmentId|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId|PrimaryFlag|PrimaryAssignmentFlag|AssignmentStatusTypeId|AssignmentName|AssignmentNumber' FROM dual UNION ALL SELECT 'MERGE|Assignment|' || paam.assignment_id || '|' || paam.action_code || '|' || paam.reason_code || '|' || paam.work_terms_assignment_id || '|' || to_char(paam.effective_start_date, 'YYYY/MM/DD') || '|' || to_char(paam.effective_end_date, 'YYYY/MM/DD') || '|' || paam.effective_latest_change || '|' || paam.effective_sequence || '|' || paam.period_of_service_id || '|' || paam.primary_flag || '|' || paam.primary_assignment_flag || '|' || paam.assignment_status_type_id || '|' || job.name || '|' || paam.assignment_number FROM per_all_assignments_m paam ,per_jobs_f_vl job WHERE assignment_type IN ('E','C','P') AND paam.primary_flag = 'Y' AND paam.effective_latest_change = 'Y' AND paam.job_id = job.job_id AND paam.ASSIGNMENT_NAME <> job.name --and person_id in (select person_id from per_all_people_f where person_number in ('Person Numbers')) AND sysdate BETWEEN paam.effective_Start_date AND paam.effective_end_date AND sysdate BETWEEN job.effective_Start_date AND job.effective_end_date
Feel free to modify any criteria as per your need. Like hardcoding person numbers or adding assignment status condition or if you want to update the history records too.
Note: The above query will compare the data on the Top Of Stack row only as of the current date. History and future dated records are not going to be impacted with this change.
Superb Bro… keep it up 🙂
Thanks for your compliment. Keep following for latest updates.
Nice work Sri
Thanks for your compliment Farhan. Keep following for latest updates.
I could validate our program logic with this thank you!
We took it a step forward and built an ETL HCM Extract to retrieve and load corrected data into HCM. That way we can manage this ongoing issue.