SQL Query to get Source System IDs for all Business Objects

SQL Query:

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

List of Business Objects which have Integration enabled:

I have filled in the table and surrogate id for the most frequently used tables. This is the complete exhaustive list of all the objects which are integration enabled. You can do a quick search in the tables and views link for the object name for the remaining objects and you would find the table name and surrogate id.

Sample Queries:

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

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
	,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

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.