Below listed are some of the queries to get Source System Ids for business objects Person, Periods of Service, National Identified, and Person Legislative Info from Oracle HCM Cloud application.
SELECT papf.person_number ,h.source_system_owner ,h.source_system_id ,h.surrogate_id FROM HRC_INTEGRATION_KEY_MAP h ,PER_ALL_PEOPLE_F Papf WHERE h.object_name = 'Person' AND h.surrogate_id = papf.PERSON_ID AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
SELECT papf.person_number ,H.source_system_owner ,H.source_system_id ,H.surrogate_id ,'Period_Of_Service_Id' ,to_char(ppos.DATE_START) ,to_char(ppos.ACTUAL_TERMINATION_DATE) FROM HRC_INTEGRATION_KEY_MAP H ,PER_PERIODS_OF_SERVICE ppos ,per_all_people_f papf WHERE H.object_name = 'PeriodOfService' AND H.surrogate_id = ppos.PERIOD_OF_SERVICE_ID AND ppos.PERSON_ID = papf.person_id AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
SELECT papf.person_number ,source_system_owner ,source_system_id ,surrogate_id ,'National_Identifier_ID' ,PER_NATIONAL_IDENTIFIERS_V.NATIONAL_IDENTIFIER_NUMBER ,PER_NATIONAL_IDENTIFIERS_V.NATIONAL_IDENTIFIER_TYPE FROM HRC_INTEGRATION_KEY_MAP H ,PER_NATIONAL_IDENTIFIERS_V PER_NATIONAL_IDENTIFIERS_V ,per_all_people_f papf WHERE h.object_name = 'NationalIdentifier' AND h.surrogate_id = PER_NATIONAL_IDENTIFIERS_V.NATIONAL_IDENTIFIER_ID AND PER_NATIONAL_IDENTIFIERS_V.person_id = papf.person_id AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
SELECT papf.person_number ,source_system_owner ,source_system_id ,surrogate_id ,PER_PEOPLE_LEGISLATIVE_F.SEX ,PER_PEOPLE_LEGISLATIVE_F.MARITAL_STATUS FROM HRC_INTEGRATION_KEY_MAP H ,PER_PEOPLE_LEGISLATIVE_F PER_PEOPLE_LEGISLATIVE_F ,per_all_people_f papf WHERE h.object_name = 'PersonLegislativeInfo' AND h.surrogate_id = PER_PEOPLE_LEGISLATIVE_F.PERSON_LEGISLATIVE_ID AND PER_PEOPLE_LEGISLATIVE_F.person_id = papf.person_id AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
