Search for:
Dec 30 2023
HDL – Sample HDL files load Content Items

To bulk upload items catalogs in HCM profiles, you can use ContentItem.dat. Each of the template have certain mandatory attributes like Context Name, Value Set Name or Value Set Id:

S0, before you start preparing the file, you need to have below information handy:

  1. Context Name – This is a mandatory attribute. If you don’t pass the value in your HDL file, you will get below error:

The values 3000122xxxxx aren’t valid for ContentItemValueSetId.

You can get the context name from HRT_CONTENT_TYPES_B table:

2. Content Item Value Set Name/Id: This is again a mandatory attribute. You can get the Content Value Set Name/ Id from HRT_CONTENT_TP_VALUESETS_TL table.

Once you have the details, you can prepare ContentItem.dat.

Below are the sample files for different item catalog templates:

For Establishments:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|Indian Institute of Technology, Bombay|EDUCATIONAL_ESTABLISHMENT|Establishment||IIT_B|1951/01/01|||IIT_B|HRC_SQLLOADER 
MERGE|ContentItem|Indian Institute of Management, Ahemdabad|EDUCATIONAL_ESTABLISHMENT|Establishment||IIM_A|1951/01/01|||IIT_B|HRC_SQLLOADER

For Licenses and Certifications: 

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner  

MERGE|ContentItem|Oracle Global Human Resources 2023|CERTIFICATION|Licenses and Certifications||O_GHR_2023|1951/01/01|||O_GHR_2023|HRC_SQLLOADER MERGE|ContentItem|Oracle Benefits 2023|CERTIFICATION|Licenses and Certifications||O_BEN_2023|1951/01/01|||O_BEN_2023|HRC_SQLLOADER

For Degrees:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|PhD|DEGREE|Degrees||XX_PhD|1951/01/01|||CI_XX_PhD|HRC_SQLLOADER 
MERGE|ContentItem|Higher National Certificate|DEGREE|Degrees||XX_Higher National Certificate|1951/01/01|||CI_XX_Higher National Certificate|HRC_SQLLOADER

For Competencies:

METADATA|ContentItem|ContextName|ContentItemValueSetName|Name|ContentItemId|ContentItemCode|DateFrom|DateTo|ItemDescription|RatingModelId|RatingModelCode|CountryGeographyCode|CountryCountryCode|SourceSystemId|SourceSystemOwner

MERGE|ContentItem|COMPETENCY|Competencies|Accounting Standards and Principles||XX_ASAP|1951/01/01||To check knowledge on Accounting Standards and Principles.|5|PROFICIENCY|||XX_ASAP|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assessing Talent||XX_AT|1951/01/01||To check knowledge on Assessing Talent.|5|PROFICIENCY|||XX_AT|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assurance and Reporting||XX_AAR|1951/01/01||To check knowledge on Assurance and Reporting.|5|PROFICIENCY|||XX_AAR|HRC_SQLLOADER

For Languages:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|Hindi|LANGUAGE|Languages||XX_Hindi|1951/01/01|||CI_Hindi|HRC_SQLLOADER
MERGE|ContentItem|Punjabi|LANGUAGE|Languages||XX_Punjabi|1951/01/01|||CI_Punjabi|HRC_SQLLOADER

Please note that Rating Mode Id is mandatory for loading competencies. You can find the rating model id and rating model code from hrt_rating_models_b table.

Once the data load is successful, you can run below queries to extract loaded data:

Reports (BIP) – Query to get Content Item Details
Mandeep Gupta 0 Comments
Dec 30 2023
HDL – SET PURGE_FUTURE_CHANGES Command

In this article, I will talk about affect of using SET PURGE_FUTURE_CHANGES in HCM Data Loader file. HCM use date tracked functionality for most of the objects in HCM, be it work structures, worker or payroll related objects.

I have seen many scenarios, where I had to go in past and make an update on the existing past date without touching the current existing record. There are numerous scenarios like these where HCM technical consultants are expected to make use of HDL to update the past dated records. Oracle has designed PURGE_FUTURE_CHANGES SET command specifically for these scenarios.

But before adding this command in your HDL (.dat) file, it is very important to understand the working of this command, otherwise it may unwantedly purge/ change data which is beyond recovery.

Let us start with an example. Below is existing employment data for a worker with Person Number – 120:

Later on, it has been found that Grade should have been changed Grade 6 from 16-Jun-2022 onwards but there is no data for this in the database currently..

So, in this case a new row with Grade update to Grade 6 should be inserted starting on 16-Jun-2022.

So, the technical consultant has created an an HDL with below format:

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|4712/12/31|300000066966135|FUSION|ASG_CHANGE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|4712/12/31|300000066966140|FUSION|ASG_CHANGE|GRADE6

As you can see, EffectiveStartDate = 2022/06/16. So, the intention here is to insert a new row starting 2022/06/16.

But unfortuantely, running above HDL will replace all the existing future rows, i.e. the row with effective start date of 2023/01/01 will be purged from database. Below is how the new data will look once the above HDL load is done:

This happens because the default system setting for update mode is set to REPLACE. This can be verified using “Configure HCM Data Loader” task from Setup and Maintenance:

So, in order to preserve the future dated rows, you have to use SET command:

SET PURGE_FUTURE_CHANGES N

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|4712/12/31|300000066966135|FUSION|ASG_CHANGE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|4712/12/31|300000066966140|FUSION|ASG_CHANGE|GRADE6

Executing this will preserve the future dated rows. But it will change the data in future rows as per current row data.

Now if the requirement is to keep the future dated rows and their data intact, you should pass #RETAIN in EffectiveEndDate attribute as shown in below example:

SET PURGE_FUTURE_CHANGES N

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|#RETAIN|300000066966135|FUSION|ASG_CHANGE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|#RETAIN|300000066966140|FUSION|ASG_CHANGE|GRADE6

With #RETAIN value of Asg Attribute 1 is retained on 01-Jan-2023 row.

Mandeep Gupta 0 Comments
Dec 24 2023
HDL – Sample file to load absence reason

Absence reasons can be loaded in BULK using AbsenceReason.dat.

BaseName is a mandatory attribute in the file. If BaseName is not passed, user get below error:

An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test. Message details: {MESSAGE}.

An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test. Message details: Please check the stack trace for more details.

BaseName has to be provided in UPPER CASE concatenated with an underscore (_) and legislation code.

Example: TEST ABSENCE REASON_US

If the above format is not followed, HDL throws below error:

		There was a problem uploading your data.

Clicking on the error Message, shows below:

Below is the successfully loaded file:

METADATA|AbsenceReason|EffectiveStartDate|EffectiveEndDate|LegislationCode|Status|Name|BaseName|Description|SourceSystemOwner|SourceSystemId
MERGE|AbsenceReason|2024/01/15|2024/12/31|IN|A|Test Absence Reason 1|TEST ABSENCE REASON 1_US|Test Absence Reason 1|HRC_SQLLOADER|TEST ABSENCE REASON 1_US
MERGE|AbsenceReason|2024/01/16|2024/12/31|IN|A|Test Absence Reason 2|TEST ABSENCE REASON 2_US|Test Absence Reason 2|HRC_SQLLOADER|TEST ABSENCE REASON 2_US
Mandeep Gupta 0 Comments
Dec 19 2023
BIP – Query to extract adjustment reasons attached on accrual Plan level

You can attach multiple adjustment reasons on accrual plan level. You can create any new custom adjustment reasons and add them in a lookup – ANC_ABS_PLAN_OTHER_REASONS.

Once the reason is added in the lookup, you can navigate to Absence Plans.

You can choose the newly added lookup code under “Enteries and Balances” tab.

Once the reasons are added, you can run below SQL query to extract the reasons added to the plan:

SELECT aapft.name
      ,aapf.legislation_code
      ,aapf.other_reasons
  FROM ANC_ABSENCE_PLANS_F aapf
      ,ANC_ABSENCE_PLANS_F_TL aapft
 WHERE aapf.absence_plan_id = aapft.absence_plan_id
   AND aapft.language = 'US'
   AND TRUNC(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
   AND aapf.other_reasons IS NOT NULL
   AND aapf.other_reasons LIKE '%XX_MLA%'
ORDER BY 2,1
Mandeep Gupta 0 Comments