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
Nov 5 2023
Configuration – Using UNION in a value set

For requirements to add List of values to a DFF segments, one can create independent value set or a table based value set. For example, if you want LOV as your existing departments, then you can use a table based value set.

But sometimes, you need to display one hard coded value “All” along with list of departments. This is in order to specify that all departments are applicable for a DFF segement value.

In, this case you need to use UNION.

Below is how the query will look like:

SELECT substr(pd.name,1,125) dep_name
  FROM per_departments pd
UNION
SELECT 'All Departments' dep_name
  FROM DUAL

Value Set setup:

From Clause:

(SELECT substr(pd.name,1,125) dep_name  FROM per_departments pd UNION SELECT ‘All Departments’ dep_name  FROM DUAL) a

Value Column Name:

a.dep_name

ID Column Name:

a.dep_name

Mandeep Gupta 0 Comments
Aug 25 2022
Configuration – Value Set to show numbers from 1 to 60

There is a common requirement to use value sets in various DFF attributes across Fusion HCM application. Oracle HCM supports different kind of value sets – Independent, Table validated etc.

There is a use case where a user wants to see a list of values containing numbers from 1 to n. There are two approaches to create a value set for this. Either you can define an Independent value set with 1 to n unique values or you can define a table validated value set.

Independent value set approach works fine if the value of n is small. But if the value of n is a large integer for example, n = 60, then it is not pragmatic to go ahead with a independent value set.

See the below example to define a table validated value set which will display values ranging from 1 to 60:

FROM Clause(SELECT TO_NUMBER( LEVEL) value FROM DUAL CONNECT BY LEVEL <= 60) a
Value Attributes Table Alias 
*Value Column Namea.value
Value Column TypeNUMBER
Value Column Length
Description Column Name
Description Column Type 
Description Column Length
ID Column Namea.value
ID Column TypeNUMBER
ID Column Length
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clause1=1
ORDER BY Clausea.value

This will look like below in application:

Mandeep Gupta 0 Comments
Oct 5 2021
BIP – Query to extract GL Cost Details from Department

GL Cost center Information can be maintained at Department Level.

This information can be extracted using below SQL:

SELECT DISTINCT houft.name, hoif.ORG_INFORMATION2	"Value Set Id"
       ,hoif.ORG_INFORMATION3	"Cost Center 1"
	   ,hoif.ORG_INFORMATION7	"Record Identifier"
  FROM HR_ORGANIZATION_INFORMATION_F hoif,
       HR_ORGANIZATION_UNITS_F_TL houft
 WHERE trunc(sysdate) BETWEEN hoif.EFFECTIVE_START_DATE AND hoif.EFFECTIVE_END_DATE
   AND houft.organization_id = hoif.organization_id
   AND hoif.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO'
   AND houft.language = 'US'
   AND TRUNC(sysdate) BETWEEN houft.EFFECTIVE_START_DATE AND houft.EFFECTIVE_END_DATE
ORDER by 1

To bulk upload GL Cost Centers Organization.dat HDL can be used. Please check the below link for more details:

https://fusionhcmconsulting.com/2021/01/hdl-sample-hdl-to-load-gl-information-at-department-level/

Mandeep Gupta 0 Comments
Jul 10 2021
Configuration – Value set to get Grade Steps based on Worker Grade

Assignment DFF supports a number of parameters which can be used to get dynamic values based on Worker assignment.

List of supported parameters for a DFF can be found using below link:

Configuration – How to get list of supported parameters for a DFF?

In this example, we will create a value set to display list of Grade steps based on Worker’s assigned grade. Create a Table validated value set using below:

FROM ClausePER_GRADE_STEPS_F_TL pgftl, PER_GRADE_STEPS_F pgsf,PER_GRADES_F pgf
Value Attributes Table Alias 
*Value Column NameSUBSTR(pgftl.NAME,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column NameSUBSTR(pgftl.NAME,1,150)
Description Column Type VARCHAR2
Description Column Length 150
ID Column NameSUBSTR(pgftl.NAME,1,150)
ID Column TypeVARCHAR2
ID Column Length150
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausepgftl.GRADE_STEP_ID=pgsf.GRADE_STEP_ID
     AND pgftl.language=’US’
AND trunc(sysdate) between trunc(pgftl.effective_start_date) and trunc(pgftl.effective_end_date)
AND pgsf.GRADE_ID=pgf.GRADE_ID
     AND trunc(sysdate) between trunc(pgsf.effective_start_date) and trunc(pgsf.effective_end_date)
     AND trunc(sysdate) between trunc(pgf.effective_start_date) and trunc(pgf.effective_end_date)
AND pgf.grade_id = :{PARAMETER.GRADE_ID}
ORDER BY ClauseSUBSTR(pgftl.NAME,1,150)
Mandeep Gupta 1 Comments
May 25 2021
Value Set – Table Value set to get list of employees

While extending the DFF attributes for additional functionality, one of the common requirements is to get the list of workers in the system.

For this purpose a table based value set can be defined and attached to the DFF attribute.

FROM Clauseper_all_people_f papf, per_person_names_f ppnf
Value Attributes Table Alias 
*Value Column Namepapf.person_number
Value Column TypeVARCHAR2
Value Column Length30
Description Column Name ppnf.full_name
Description Column Type 
Description Column Length 
ID Column Namepapf.person_number
ID Column TypeVARCHAR2
ID Column Length30
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausetrunc(SYSDATE) between papf.effective_start_date AND papf.effective_end_date
AND trunc(SYSDATE) between ppnf.effective_start_date AND ppnf.effective_end_date
and ppnf.name_type = ‘GLOBAL’
and ppnf.person_id=papf.person_id
and papf.person_id IN (select distinct person_id from per_periods_of_Service ppos where NVL(ppos.actual_termination_date, trunc(sysdate)) >= trunc(sysdate))
ORDER BY Clause 

The above value set will show a list of only active employees. If you want to include inactive employees as well, please modify the where clause as below:

trunc(SYSDATE) between papf.effective_start_date AND papf.effective_end_date
AND trunc(SYSDATE) between ppnf.effective_start_date AND ppnf.effective_end_date
and ppnf.name_type = 'GLOBAL'
and ppnf.person_id=papf.person_id
and papf.person_id IN (select distinct person_id from per_periods_of_Service ppos)
Mandeep Gupta 0 Comments
Apr 3 2021
Reports (BIP) – Query to find module type and key

While defining Common Lookups or value sets, you need to provide module value. Each module has an associated module type, module key and product code associated with it. For example:

These details are stored in backed in a table – FND_APPL_TAXONOMY.

Use the below query to find module type, module key etc for a module:

select fat.MODULE_NAME
,fat.MODULE_TYPE
,fat.MODULE_KEY
,fat.PRODUCT_CODE
from FND_APPL_TAXONOMY fat
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of courses
*FROM Clausewlf_learning_items_f wlif, wlf_learning_items_f_tl wlift
Value Attributes Table Alias 
*Value Column NameSUBSTR(wlift.name,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(wlif.LEARNING_ITEM_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE Clausewlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID AND wlift.language = ‘US’ AND wlif.LEARNING_ITEM_TYPE = ‘ORA_COURSE’ AND TRUNC(SYSDATE) BETWEEN wlif.effective_start_date and wlif.effective_end_date AND wlif.effective_start_date BETWEEN wlift.effective_start_date and wlift.effective_end_date
ORDER BY Clause 
Mandeep Gupta 0 Comments
Feb 5 2021
Value Set – Table Value set to get list of departments

Use below setup to create a table based value set which will show the list of all departments:

*FROM ClauseHR_ORG_UNIT_CLASSIFICATIONS_F HOCF, HR_ORGANIZATION_UNITS_F_TL HOUT
Value Attributes Table Alias 
*Value Column NameSUBSTR(HOUT.NAME,1,150)
Value Column TypeVARCHAR2
Value Column Length150
Description Column Name 
Description Column Type 
Description Column Length 
ID Column NameTO_CHAR(HOUT.ORGANIZATION_ID)
ID Column TypeVARCHAR2
ID Column Length40
Enabled Flag Column Name 
Start Date Column Name 
End Date Column Name 
WHERE ClauseHOCF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID AND TRUNC(SYSDATE) BETWEEN HOCF.EFFECTIVE_START_DATE AND HOCF.EFFECTIVE_END_DATE AND HOCF.EFFECTIVE_START_DATE BETWEEN HOUT.EFFECTIVE_START_DATE AND HOUT.EFFECTIVE_END_DATE AND HOUT.LANGUAGE = USERENV(‘LANG’) AND HOCF.CATEGORY_CODE = ‘DEPARTMENT’
ORDER BY Clause 
Mandeep Gupta 0 Comments