Resolving Assignment Name and Job Name not in sync issue

• 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.