SQL Query to get Source System IDs for all Business Objects
SQL Query to get Source System IDs for all Business Objects

 2,663 total views

In one of our previous articles, we have seen how to configure Source System Owner in Fusion.
Defining Source System Owner for HDL in Fusion

Source System Owner and Source System ID are the Source Keys which we use to load/update data using HCM Data Loader (HDL).

HRC_INTEGRATION_KEY_MAP is the underlying base table which stores the Source Keys for all business objects. The link to the core tables and Key map table is the surrogate id which is the main key field of the core table. Ex: PER_AL_PEOPLE_F has the surrogate id as PERSON_ID and we can use this PERSON_ID to get the Source System ID information from HRC_INTEGRATION_KEY_MAP table.

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:

Object NameTableSurrogate ID
AbsenceCertificationANC_ABS_CERTIFICATIONS_FABSENCE_CERTIFICATION_ID
AbsenceReasonANC_ABSENCE_REASONS_FABSENCE_REASON_ID
ActionReasonUsagesPER_ACTION_REASON_USAGESACTION_REASON_USAGE_ID
ActionReasonsPER_ACTION_REASONS_BACTION_REASON_ID
ActionsPER_ACTIONS_BACTION_ID
AddressPER_ADDRESSES_FADDRESS_ID
AllocateChecklistPER_ALLOCATED_CHECKLISTSALLOCATED_CHECKLIST_ID
AllocateChecklistContacts
AllocateChecklistTask
AllocateChecklistTaskNotifications
AssignedPayrollPAY_ASSIGNED_PAYROLLS_FASSIGNED_PAYROLL_ID
AssignmentPER_ALL_ASSIGNMENTS_MASSIGNMENT_ID
AssignmentGradeStepPER_ASSIGN_GRADE_STEPS_FASSIGN_GRADE_STEP_ID
AssignmentResponsibilityPER_ASG_RESPONSIBILITIESASG_RESPONSIBILITY_ID
AssignmentStatusTypePER_ASSIGNMENT_STATUS_TYPESASSIGNMENT_STATUS_TYPE_ID
AssignmentSupervisorPER_ASSIGNMENT_SUPERVISORS_FASSIGNMENT_SUPERVISOR_ID
AssignmentWorkMeasurePER_ASSIGN_WORK_MEASURES_FASSIGN_WORK_MEASURE_ID
AutoIndirect
BalanceAttribute
BalanceAttributeDefault
BalanceAttributeDefinition
BalanceFeed
BalanceGroup
BalanceGroupInclusion
BalanceGroupUsage
BalanceGroupUsageItem
BalanceType
BrandSkill
CalculationCard
CalculationCardAssociationDetails
CalculationCardAssociations
CalculationCardComponents
CalculationComponentDetails
Checklist
ChecklistContacts
ChecklistTask
ChecklistTaskNotifications
ContactRelationship
ContentItem
ContentItemRatingDescription
ContextUsage
CostAllocation
CostAllocationAccount
CountrySecurityProfile
DatabaseItem
DatabaseItemGroup
DatabaseItemUsage
DateTrackedEvent
DefinedBalance
DeliveryMethod
DocumentDeliveryPreference
DocumentType
DocumentTypeSecurityProfile
DocumentTypeSecurityProfileDocumentType
DocumentsOfRecord
ElementClassification
ElementEntryPAY_ELEMENT_ENTRIES_FELEMENT_ENTRY_ID
ElementEntryValuePAY_ELEMENT_ENTRY_VALUES_FELEMENT_ENTRY_VALUE_ID
ElementLinkPAY_ELEMENT_LINKS_FELEMENT_LINK_ID
ElementPayrollFrequencyRule
ElementRunTypeUsage
ElementSpanUsage
ElementType
EmailAddressPER_EMAIL_ADDRESSESEMAIL_ADDRESS_ID
Establishment
EthinicityPER_ETHNICITIESETHNICITY_ID
EventGroup
ExtendedLookupCode
ExtractAction
ExtractBlockLink
ExtractDataElement
ExtractDefinition
ExtractDeliveryOptionDetail
ExtractDeliveryOptions
ExtractParameter
ExtractReplacement
Flow
FlowParameter
FlowTask
FlowTaskInteraction
FlowTaskParameter
Formula
FormulaResultRule
GeneratedDataRole
GeneratedDataRoleProfile
Global
Goal
GradePER_GRADES_FGRADE_ID
GradeStepPER_GRADE_STEPS_FGRADE_STEP_ID
Image
InputValue
JobPER_JOBS_FJOB_ID
JobFamilyPER_JOB_FAMILY_FJOB_FAMILY_ID
JobLegislativePER_JOB_LEG_FJOB_LEG_ID
LearningHierarchy
LearningItem
LegislativeDataGroupSecurityProfile
LegislativeDataGroups
LinkedInputValue
LocationPER_LOCATIONSLOCATION_ID
LocationAddressUsagePER_LOC_ADDRESS_USAGES_FLOC_ADDRESS_USAGE_ID
LocationDetailPER_LOCATION_DETAILS_FLOCATION_DETAILS_ID
LocationLegislativeEFFPER_LOCATION_EXTRA_INFO_FLOCATION_EXTRA_INFO_ID
NameFormatHR_NAME_FORMATSNAME_FORMAT_ID
NameSetup
NameStyle
NationalIdentifier
OrgInformationEFF
OrganizationHR_ALL_ORGANIZATION_UNITS_FORGANIZATION_ID
OrganizationClassification
OrganizationPaymentMethod
OrganizationPaymentMethodUsage
OrganizationSecurityProfile
PayDates
PayTaskUsage
PayrollAssignmentDetails
PayrollBalanceClassification
PayrollConsolidationGroup
PayrollDefinitionPAY_ALL_PAYROLLS_FPAYROLL_ID
PayrollInterfaceInboundRecordHRY_PI_INBD_RECORDSINBD_RECORD_ID
PayrollInterfaceInboundRecordInformationHRY_PI_INBD_RECORD_INFORMATIONINBD_RECORD_INFO_ID
PayrollRelationshipDetailsPAY_PAY_RELATIONSHIPS_FPAYROLL_RELATIONSHIP_ID
PayrollRelationshipType
PayrollRelationshipTypeMap
PayrollTimeDefinitionPAY_TIME_DEFINITIONSTIME_DEFINITION_ID
PayrollTimePeriodPAY_TIME_PERIODSTIME_PERIOD_ID
PeriodOfServicePER_PERIODS_OF_SERVICEPERIOD_OF_SERVICE_ID
PersonPER_ALL_PEOPLE_FPERSON_ID
PersonAddressUsagePER_PERSON_ADDR_USAGES_FPERSON_ADDR_USAGE_ID
PersonHabitsBEN_PER_LE_HABITS_COV_FLE_HABITS_COV_ID
PersonLegislativeInfoPER_PEOPLE_LEGISLATIVE_FPERSON_LEGISLATIVE_ID
PersonNamePER_PERSON_NAMES_FPERSON_NAME_ID
PersonSecurityProfilePER_PERSON_SECURITY_PROFILESPERSON_SECURITY_PROFILE_ID
PersonSecurityProfileAorScope
PersonSecurityProfilePersonType
PersonTypePER_PERSON_TYPESPERSON_TYPE_ID
PersonTypeUsagePER_PERSON_TYPE_USAGES_MPERSON_TYPE_USAGE_ID
PersonalPaymentMethodPAY_PERSON_PAY_METHODS_FPERSONAL_PAYMENT_METHOD_ID
PhonePER_PHONESPHONE_ID
PortraitContentPER_PORTRAIT_CONTENT_BPORTRAIT_CONTENT_ID
PositionHR_ALL_POSITIONS_FPOSITION_ID
PositionExtraInformationPER_POSITION_EXTRA_INFO_FPOSITION_EXTRA_INFO_ID
PositionHierarchyPER_POSITION_HIERARCHY_FPOSITION_HIERARCHY_ID
PositionLegislativePER_POSITION_LEG_FPOSITION_LEG_ID
PositionSecurityProfile
PricingRule
ProfileHRT_PROFILES_BPROFILE_ID
ProfileItemHRT_PROFILE_ITEMSPROFILE_ITEM_ID
ProfileItemCatalog
ProfileRelation
ProgressionRule
RangeItem
Rate
RateContributor
RateDefinition
RateValue
RatingLevel
RatingModel
ReportBlock
ReportCriteria
ReportRecord
ReportRecordRule
ReportSortItem
ReportSortOrder
ReportSortType
RequisitionSecurityProfile
Reservation
Resource
RetroComponent
RetroComponentUsage
RoleMappingPER_ROLE_MAPPINGSROLE_MAPPING_ID
RoleMappingRole
RouteFF_ROUTES_BROUTE_ID
RouteParameterValue
SalaryCMP_SALARYSALARY_ID
SalaryBasisCMP_SALARY_BASESSALARY_BASIS_ID
SeniorityDatePER_SENIORITY_DATESSENIORITY_DATE_ID
SeniorityDateRule
StatusProcessingRule
SubclassificationRule
Task
TaskAction
TaskLibrary
TaskParameter
TaskProperty
TimeDefinitionUsage
UserPER_USERSUSER_ID
UserColumnFF_USER_COLUMNSUSER_COLUMN_ID
UserColumnInstanceFF_USER_COLUMN_INSTANCES_FUSER_COLUMN_INSTANCE_ID
UserEntityFF_USER_ENTITIES_BUSER_ENTITY_ID
UserRolePER_ROLES_DNROLE_ID
UserRowFF_USER_ROWS_FUSER_ROW_ID
UserTableFF_USER_TABLESUSER_TABLE_ID
ValidGradePER_VALID_GRADES_FVALID_GRADE_ID
ValueDefinitionPAY_VALUE_DEFINITIONS_FVALUE_DEFN_ID
WorkingHourPatternPER_WORKING_HOUR_PATTERNS_FWORKING_HOUR_PATTERN_ID

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.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply