Resolving Assignment Name and Job Name not in sync issue
Resolving Assignment Name and Job Name not in sync issue

 796 total views

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.

Want to learn Fusion Technical tools? Checkout this article

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.

Assignment Name and Job Name are not in sync
Assignment Name and Job Name are 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
Output from SQL query
Output from SQL Query
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

Data Load is successful
Job and Assignment Names are in sync now
Job and Assignment Names are in sync now

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.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 5 Comments

  1. Gopi

    Superb Bro… keep it up 🙂

  2. Sricharan

    Thanks for your compliment. Keep following for latest updates.

  3. Farhan

    Nice work Sri

  4. Sricharan

    Thanks for your compliment Farhan. Keep following for latest updates.

  5. Abhishek

    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.

Leave a Reply