how to find source system id in Oracle Fusion

How to find source system id in Oracle Fusion HDL

In this post , We will be discuss about how to find source system id in Oracle Fusion HDL. 

We will share the sql query which will help to extract the Employee In-formations with the source system id details for each piece of employees information in oracle fusion HDL. We can able to find the source system id in oracle fusion referring this below post .I will share the important HDL table which helps to store the source system id. Please find below detail about how to find source system id in Oracle Fusion.

How to find source system id in Oracle Fusion

Important Tables to find source system id in Oracle Fusion

HRC_INTEGRATION_KEY_MAP is the table which helps to store the source system id in oracle fusion HDL.

HRC_INTEGRATION_KEY_MAP

Detail SQL query to find source system id in 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

How to find source system id in Oracle Fusion