SQL query to find the Employees Data source system id from oracle fusion
In this post, We will be discuss about SQL query to find the Employees Data source system id from oracle fusion. This query will help to extract the Employee In formations with the source system id details for each piece of employees information in oracle fusion. We can able to get the source system id for each type of employee information. This is one of the important sql query to find source system id details related to employees in oracle fusion. Please find below the detail SQL query to find the Employees Data source system id from oracle fusion.
12 Important Tables to find the Employees Data source system id from oracle fusion
1.HRC_INTEGRATION_KEY_MAP
2.per_all_people_f
4.per_all_assignments_m
5.per_jobs_f
6.per_jobs_f_tl
7.GL_CODE_COMBINATIONS
8.PER_EMAIL_ADDRESSES
9.per_assignment_supervisors_f
10.per_person_names_f
11.HR_ORGANIZATION_UNITS_F_TL
12.PER_PEOPLE_LEGISLATIVE_F
Detail SQL query to find the Employees Data source system id from oracle fusion
SELECT papf.person_number EMPLOYEE_NUMBER,
papf.person_id,
pjft.name jobname,
PAAM.ASSIGNMENT_NUMBER EMP_ASSIGNMENT_NUMBER,
gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6 EXP_ACCOUNT,
PAAM.EFFECTIVE_START_DATE ASSG_EFFECTIVE_START_DATE,
PEA.EMAIL_ADDRESS,
ppnf.full_name supervisor_name,
papf2.person_number sUPERVISOR_NUMBER,
hauft.NAME BusinessUnit,
TO_CHAR(PAPF.START_DATE,'MMDDYYYY') HIRE_DATE,
(select min(papf_int.EFFECTIVE_START_DATE) 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='Person'
and h.surrogate_id=papf.person_id) person_owner,
(select h.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='Person'
and h.surrogate_id=papf.person_id) person_system_id,
(select h.source_system_owner
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='PersonName'
and h.surrogate_id=ppnf2.person_name_id) PersonName_owner,
(select h.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='PersonName'
and h.surrogate_id=ppnf2.person_name_id) PersonName_system_id,
(select h.source_system_owner
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='PersonLegislativeInfo'
and h.surrogate_id=pplf.PERSON_LEGISLATIVE_ID) PersonLegislative_owner,
(select h.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='PersonLegislativeInfo'
and h.surrogate_id=pplf.PERSON_LEGISLATIVE_ID) PersonLegislative_system_id,
PEA.EMAIL_ADDRESS_ID,
(select h.source_system_owner
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='EmailAddress'
and h.surrogate_id=PEA.EMAIL_ADDRESS_ID) PersonEmail__owner,
(select h.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='EmailAddress'
and h.surrogate_id=PEA.EMAIL_ADDRESS_ID) PersonEmail_system_id,
(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 h.source_system_owner
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='Assignment'
and h.surrogate_id=paam.work_terms_assignment_id) WorkTerms_owner,
(select h.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='Assignment'
and h.surrogate_id=paam.work_terms_assignment_id) WorkTerms_system_id,
(select h.source_system_owner
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='Assignment'
and h.surrogate_id=paam.ASSIGNMENT_ID) Assignment_owner,
(select h.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='Assignment'
and h.surrogate_id=paam.ASSIGNMENT_ID) Assignment_system_id,
(select h.source_system_owner
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='AssignmentSupervisor'
and h.surrogate_id=pasf.assignment_supervisor_id) Supervisor_owner,
(select h.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
where 1=1
and h.object_name='AssignmentSupervisor'
and h.surrogate_id=pasf.assignment_supervisor_id) Supervisor_system_id
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
WHERE papf.person_id = paam.person_id
AND PPLF.person_id=papf.person_id
AND papf.person_id = ppnf2.person_id
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 = 'E'
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 PEA.EMAIL_ADDRESS_ID=PAPF.PRIMARY_EMAIL_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 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
