Introduction

As a part of the capability to extract data from HCM using read-optimized data store, HCM Cloud exposes Object Change extract.

Object Change is a generic business view that represents a data change in the system. Such a data change can pertain to various business objects that can be manipulated in HCM in various ways, such as user UI transactions, API calls or backend processes.

You can define and run Object Change extracts in order to detect changes you are interested in, according to your requirements.

Objectives

Upon completing this tutorial, you will understand:

  • What are Object Change extracts and what data changes they allow to detect
  • How to use Object Change extracts to efficiently detect data changes

Prerequisites

Before you begin, familiarize yourself with extracting data from HCM using read-optimized data store.

Refer to the Extract Data from HCM Read-optimized Data Store tutorial.

Use Cases

This functionality allows you to address use cases such as:

  1. Detecting data changes of specific business objects
  2. Filtering by operation type - tracking creations, updates and, in particular, deletions
  3. Tracking changed attributes of business objects and their old and new values

Incremental extracts:

This feature doesn't replace incremental extracts, it does however complement them. For example, incremental extracts don't allow to track deletions, neither provide insight into changed attributes and their old and new values.

Atom feeds:

This feature is a more generic alternative to Atom feeds.

Task 1: Choose Business Objects

Below is the list of fully-qualified names of supported business objects.

Source Object Name
oracle.apps.hcm.addresses.publicModel.entity.AddressDEO
oracle.apps.hcm.compensation.salary.core.protectedModel.entity.SalaryEO
oracle.apps.hcm.documentsOfRecord.core.protectedModel.entity.DocumentDeliveryPreferenceEO
oracle.apps.hcm.employment.core.publicModel.entity.AssignmentExtraInfoEFFDEO
oracle.apps.hcm.employment.core.publicModel.entity.AssignmentSupervisorDEO
oracle.apps.hcm.employment.core.publicModel.entity.AssignmentWorkMeasureDEO
oracle.apps.hcm.employment.core.publicModel.entity.CWKAssignmentDEO
oracle.apps.hcm.employment.core.publicModel.entity.ContractDEO
oracle.apps.hcm.employment.core.publicModel.entity.EmployeeAssignmentDEO
oracle.apps.hcm.employment.core.publicModel.entity.PWAssignmentDEO
oracle.apps.hcm.employment.core.publicModel.entity.PeriodOfServiceEO
oracle.apps.hcm.locations.model.entity.LocationDetailDEO
oracle.apps.hcm.locations.model.entity.LocationDetailTranslationDEO
oracle.apps.hcm.locations.model.entity.LocationEO
oracle.apps.hcm.locations.model.entity.LocationExtraInfoEFFDEO
oracle.apps.hcm.locations.model.entity.LocationLegislativeEFFDEO
oracle.apps.hcm.organizations.protectedModel.entity.OrgInformationEFFDEO
oracle.apps.hcm.organizations.protectedModel.entity.OrganizationUnitDEO
oracle.apps.hcm.organizations.protectedModel.entity.OrganizationUnitTranslationDEO
oracle.apps.hcm.people.core.protectedModel.entity.CitizenshipEO
oracle.apps.hcm.people.core.protectedModel.entity.DriversLicenceEO
oracle.apps.hcm.people.core.protectedModel.entity.EmailAddressEO
oracle.apps.hcm.people.core.protectedModel.entity.EthnicityEO
oracle.apps.hcm.people.core.protectedModel.entity.NationalIdentifierEO
oracle.apps.hcm.people.core.protectedModel.entity.PassportEO
oracle.apps.hcm.people.core.protectedModel.entity.PersonDEO
oracle.apps.hcm.people.core.protectedModel.entity.PersonEO
oracle.apps.hcm.people.core.protectedModel.entity.PersonExtraInfoDEO
oracle.apps.hcm.people.core.protectedModel.entity.PersonLegislativeInfoDEO
oracle.apps.hcm.people.core.protectedModel.entity.PersonNameDEO
oracle.apps.hcm.people.core.protectedModel.entity.PhoneEO
oracle.apps.hcm.people.core.protectedModel.entity.ReligionEO
oracle.apps.hcm.people.core.protectedModel.entity.VisaPermitDEO
oracle.apps.hcm.people.healthAndSafety.protectedModel.entity.PersonDisabilityDEO
oracle.apps.hcm.users.publicModel.entity.UserEO
oracle.apps.hcm.workStructures.grades.model.entity.GradeDEO
oracle.apps.hcm.workStructures.grades.model.entity.GradeLegislativeEFFDEO
oracle.apps.hcm.workStructures.grades.model.entity.GradeTranslationEO
oracle.apps.hcm.workStructures.jobs.model.entity.JobDEO
oracle.apps.hcm.workStructures.jobs.model.entity.JobExtraInfoDEO
oracle.apps.hcm.workStructures.jobs.model.entity.JobLegislativeDEO
oracle.apps.hcm.workStructures.jobs.model.entity.JobTranslationEO
oracle.apps.hcm.workStructures.positions.model.entity.PositionDEO
oracle.apps.hcm.workStructures.positions.model.entity.PositionExtraInformationDEO
oracle.apps.hcm.workStructures.positions.model.entity.PositionLegislativeDEO
oracle.apps.hcm.workStructures.positions.model.entity.PositionTranslationEO

Objects ending with DEO are date effective objects, otherwise they are non-date effective objects.

Task 2: Define an Extract

Define an extract query using objectChangeExtracts business view.

Review Task 2: Preparing and Testing an Extraction Query of the Extract Data from HCM Read-optimized Data Store tutorial.

Following attributes may be useful to define filters of your Object Change extract:

Attribute Description
sourceObjectName Fully-qualified business object name. See the list from step #1 for the applicable values
groupSequence If present, indicates the sequence number of the change within the business transaction
operationType

Applicable to non-date effective objects only.

Indicates the DML type of the operation. One of:

  • DELETE
  • INSERT
  • UPDATE
dateEffectiveMode

Applicable to date effective objects only.

Indicates the type of the date effective operation. One of:

  • CORRECTION
  • DELETE THIS CHANGE
  • END DATE
  • END DATE FUTURE CHANGES REMOVED
  • INSERT
  • LOGICAL EED CHANGE
  • LOGICAL ESD CHANGE
  • PHYSICAL ESD CHANGE
  • UPDATE
  • UPDATE CHANGE INSERT
  • ZAP

Refer to Manage Date Effective Objects in the HCM REST API documentation for more details.
effectiveDate

Applicable to date effective business objects only.

Indicates the effective date of the change, which can be a past, present or future date

keyAttribute1Name

keyAttribute1DataType

keyAttribute1StringValue

keyAttribute1NumberValue

keyAttribute1DateValue


keyAttribute2Name

keyAttribute2DataType

keyAttribute2StringValue

keyAttribute2NumberValue

keyAttribute2DateValue


keyAttribute3Name

keyAttribute3DataType

keyAttribute3StringValue

keyAttribute3NumberValue

keyAttribute3DateValue

Set of attributes allowing to identify the specific object instance, corresponding to the business object's SQL table definition.

Up to 3 composite key elements are supported.

Depending on the key element's data type (VARCHAR2 / NUMBER / DATE), either number, string or date attribute will be populated for the respective key element.

In case of date effective business objects, the key information represents the surrogate key of the logical object.
purgeDate

Change records are getting continuously purged. This attribute is the planned purge date for the change record.

Purge policy may vary between objects and events. 99% of events are retained for 90 days or longer.
timeCreated Creation timestamp of the change record
changeComponents

An object change record can have one or more change components.

While a change record represents a logical change of an object, change components provide additional details, including those of changes at the physical database records-level. This is relevant especially for date effective objects.

Depending on what information you need, you can examine change components to identify changes of individual physical records.

For example, a date effective update will typically involve two physical records operations:

  • Update of an existing record (setting effective end date)
  • Insert of a new record with the new value

Change components will also contain logical record changes.

Refer to Manage Date Effective Objects in the HCM REST API documentation for more details on date effectivity.

changeComponents.oldEffectiveStartDate

changeComponents.newEffectiveStartDate

changeComponents.oldEffectiveEndDate

changeComponents.newEffectiveEndDate

changeComponents.oldEffectiveSequence

changeComponents.newEffectiveSequence

Applicable to date effective business objects only.

Set of attributes providing date effective details of the underlying physical and logical records changes
changeComponents.physicalType

Applicable to date effective business objects only.

Indicates whether the change component is considered a physical record change (Y / N).

In order to determine the DML type of the operation at the individual physical record change level, check existence of the old and new effective start dates.

  • Insert - oldEffectiveStartDate is null, newEffectiveStartDate is not null
  • Update - oldEffectiveStartDate is not null, newEffectiveStartDate is not null
  • Delete - oldEffectiveStartDate is not null, newEffectiveStartDate is null
changeComponents.logicalType

Applicable to date effective business objects only.

Indicates whether the change component is considered a logical record change (Y / N).
changeComponents.changedAttributes.changedAttributesXml

Details of the changed attributes, including their new and old values.

Below is the sample changed attributes XML payload.

<ChgAttrs>
    <EmailType type="V">
        <attrName>EmailType</attrName>
        <oldVal>
            <![CDATA[W1]]>
        </oldVal>
        <newVal>
            <![CDATA[W1]]>
        </newVal>
    </EmailType>
    <PersonId type="N">
        <attrName>PersonId</attrName>
        <oldVal>300100210126395</oldVal>
        <newVal>300100210126395</newVal>
    </PersonId>
    <DateFrom type="D">
        <attrName>DateFrom</attrName>
        <oldVal>2012-01-01</oldVal>
        <newVal>2012-01-01</newVal>
    </DateFrom>
    <EmailAddress type="V">
        <attrName>EmailAddress</attrName>
        <oldVal>
            <![CDATA[jdoe@example.com]]>
        </oldVal>
        <newVal>
            <![CDATA[john.doe@example.com]]>
        </newVal>
    </EmailAddress>
    <EmailAddressId type="N">
        <attrName>EmailAddressId</attrName>
        <oldVal>300100210126404</oldVal>
        <newVal>300100210126404</newVal>
    </EmailAddressId>
</ChgAttrs>

For each changed attribute available:

  • An XML element is present with the attribute name
  • Type of the attribute is present, one of:
    • V - for text values
    • N - for numeric values
    • D - for date and timestamp values
  • oldVal element is present with the old value
  • newVal element is present with the new value

The set of attributes captured and their specific rules (always present, present only if changed etc.) may vary between business objects.

Task 3: Run the Extract

Follow the steps described in the Extract Data from HCM Read-optimized Data Store tutorial to run the defined extract.

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.