Work relationship table in Oracle Fusion HCM

Hi friends, we are going to discuss about the Work relationship table in Oracle Fusion HCM. We will share the complete information’s about work relationship table. In oracle fusion, We first need to create the Employee work relationship with Legal Entity before creating the employee assignment. In work relationship we do mentions this employee belongs to which LE or Legal Entity. We also define the start of the employee for the Legal Entity in this Work relationship table. We can create multiple work relationships of employee for different LE’s but at a single time only one work relationship will be active, and others will be disabled. It means work relationship helps to store information’s that this employee is working for which LE and from which date. We cannot change the Start date for LE in this table. It will be remaining unchanged. Here below, we will share the name of Work relationship table in Oracle Fusion HCM.

Name of the Work relationship table in Oracle Fusion

1.PER_PERIODS_OF_SERVICE

Work relationship table in Oracle Fusion HCM

Important Columns of Work relationship table

1.       LEGAL_ENTITY_ID

2.       ORIGINAL_DATE_OF_HIRE

3.       PRIMARY_FLAG

4.       PERIOD_OF_SERVICE_ID

5.       ACTUAL_TERMINATION_DATE

LEGAL_ENTITY_ID: - This Column helps to store the Legal Entity Id. Using this We can join the table HR_ORGANIZATION_UNITS_F_TL to find the LE name.

ORIGINAL_DATE_OF_HIRE: - This Column helps to store the Hire date of the Employee for the LE. This data we cannot change for the Employee in LE.

PRIMARY_FLAG: - This column helps to store which Work relationship is Active. Others records which don’t have this flag means these work relationships are disabled.

PERIOD_OF_SERVICE_ID: - This Column helps to store the unique id of Work relationship table in Oracle Fusion. Using this column, we join the PER_ALL_ASSIGNMNET_F table (PERIOD_OF_SERVICE_ID).

ACTUAL_TERMINATION_DATE: - This Column helps to store the Termination Date if the Employee record is terminated in Oracle Fusion.

Important SQL Query Using Work relationship table in Oracle Fusion

SELECT distinct papf.person_number EMPLOYEE_NUMBER,

ppnf2.first_name,

ppnf2.last_name,

ppnf2.middle_names,

papf.person_id,

   pjft.name    jobname,

   pjf.job_code,

PAAM.ASSIGNMENT_NUMBER EMP_ASSIGNMENT_NUMBER,

PAAM.EFFECTIVE_START_DATE ASSG_EFFECTIVE_START_DATE,

PEA.EMAIL_ADDRESS,

   papf2.person_number supervisor_name,

 papf2.person_number sUPERVISOR_NUMBER,

hauft.NAME BusinessUnit,

TO_CHAR(PAPF.START_DATE,'MMDDYYYY') HIRE_DATE,

(select TO_CHAR(min(papf_int.EFFECTIVE_START_DATE),'MMDDYYYY')  from per_all_people_f papf_int where papf_int.person_id =papf.person_id) min_start_date,

(select h.source_system_owner

from fusion.HRC_INTEGRATION_KEY_MAP h

where 1=1

and h.object_name='PeriodOfService'

and h.surrogate_id=paam.PERIOD_OF_SERVICE_ID) WorkRelationship_owner,

(select h.source_system_id

from fusion.HRC_INTEGRATION_KEY_MAP h

where 1=1

and h.object_name='PeriodOfService'

and h.surrogate_id=paam.PERIOD_OF_SERVICE_ID) WorkRelationship_system_id,

(select TO_CHAR(max(ppos1.date_start),'MMDDYYYY') from per_periods_of_service ppos1

where ppos1.person_id=papf.person_id

and ppos1.ACTUAL_TERMINATION_DATE is null) orig_hire_date

FROM   per_all_people_f papf,

 per_all_people_f papf2,

       per_all_assignments_m paam,

       per_jobs_f pjf,

   per_jobs_f_tl pjft,

GL_CODE_COMBINATIONS GCC,

PER_EMAIL_ADDRESSES PEA,

 per_assignment_supervisors_f pasf,

  per_person_names_f ppnf,

    per_person_names_f ppnf2,

HR_ORGANIZATION_UNITS_F_TL hauft,

PER_PEOPLE_LEGISLATIVE_F PPLF,

per_periods_of_service P_PER_SER

WHERE  papf.person_id = paam.person_id

AND PPLF.person_id=papf.person_id

AND papf.person_id = ppnf2.person_id

AND P_PER_SER.ACTUAL_TERMINATION_DATE IS NULL

AND papf.person_id=P_PER_SER.person_id

AND PPLF.LEGISLATION_CODE=P_PER_SER.LEGISLATION_CODE

AND    TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN pplf.effective_start_date AND pplf.effective_end_date

AND    paam.primary_assignment_flag = 'Y'

AND    paam.assignment_type IN ('E','C')

and    paam.effective_latest_change = 'Y'

and    paam.DEFAULT_CODE_COMB_ID=gcc.code_combination_id(+)

AND    TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

AND    paam.job_id = pjf.job_id

AND    TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date

AND    PAPF.PRIMARY_EMAIL_ID=PEA.EMAIL_ADDRESS_ID(+)

AND    papf.person_id = pasf.person_id

AND    pasf.manager_type = 'LINE_MANAGER'

AND    ppnf.person_id = pasf.manager_id

AND  papf2.person_id = pasf.manager_id

AND    TRUNC(SYSDATE) BETWEEN papf2.effective_start_date AND papf2.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN ppnf2.effective_start_date AND ppnf2.effective_end_date

AND    pjf.job_id = pjft.job_id

AND    pjft.language = 'US'

AND    ppnf.name_type = 'GLOBAL'

AND paam.ASSIGNMENT_STATUS_TYPE NOT IN ('INACTIVE')

AND    ppnf2.name_type = 'GLOBAL'

AND    TRUNC(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date

AND hauft.organization_id = paam.business_unit_id

Work relationship table in Oracle Fusion HCM

Work relationship table in Oracle Fusion HCM