Oracle Cloud Source System IDs for all Business Objects

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

Leave a Reply

Your email address will not be published. Required fields are marked *