The HRMSi strategic reports rely on collected data, stored in summary tables, in order to run efficiently. You need to collect data from your transaction tables regularly so that the information in your reports is up to date. You collect the data by running the following pre-defined concurrent programs:
For information on concurrent programs, see: Overview of Concurrent Processing, Oracle Applications Developer's Guide
All Analytics business areas and workbooks that use the competence level hierarchy gather information from the Competence Level Summary table. Use the HRI Load All Competence Level Hierarchy concurrent program to populate this summary table with your competence level hierarchy data.
The program collects pre-calculated data and stores it in the Competence Level Summary (HRI_CS_CMPTNC_LVL_CT) table ready for use by the workbooks.
The HRI Load All Competence Level Hierarchy concurrent program uses the following parameters:
Collect From Date
The earliest date required for competence level hierarchy information. The default is five years previous the first time you run this program, thereafter the value defaults to the previous Collect To Date.
Collect To Date
The latest date required for competence level hierarchy information. The default is the current date.
Full Refresh
If you set Full Refresh to Yes, the concurrent program deletes all information previously held in the summary table, and recalculates the competence level hierarchy for the entire collection date range specified (see the Collect From Date and Collect To Date parameters).
If you set Full Refresh to No, the concurrent program deletes none of the existing table information. The concurrent program incrementally updates the summary table with any competence level hierarchy changes that occurred within the collection date range specified (see the Collect From Date and Collect To Date parameters).
Run this concurrent program infrequently in full refresh mode, and frequently in incremental mode (Full Refresh = No) to capture and report on competence level hierarchy changes in your enterprise.
If a system failure occurs, you must restart the process.
All Analytics business areas and workbooks that use the job competence requirements gather information from the Competence Requirement Fact Summary table. Use the HRI Load All Competence Requirement Fact concurrent program to populate this summary table with your job competence requirement data.
The program collects pre-calculated data and stores it in the Competence Requirement Fact Summary (HRI_MB_CMPTNC_RQRMNT_CT) table ready for use by the workbooks.
The HRI Load All Competence Requirement Fact concurrent program uses the following parameters:
Collect From Date
The earliest date required for competence requirement information. The default is five years previous the first time you run this program, thereafter the value defaults to the previous Collect To Date.
Collect To Date
The latest date required for competence requirement information. The default is the current date.
Full Refresh
If you set Full Refresh to Yes, the concurrent program deletes all information previously held in the summary table, and collects competence fact information for the entire collection date range specified (see the Collect From Date and Collect To Date parameters).
If you set Full Refresh to No, the concurrent program deletes none of the existing table information. The concurrent program incrementally updates the summary table with any competence fact changes that occurred within the collection date range specified (see the Collect From Date and Collect To Date parameters).
Run this concurrent program infrequently in full refresh mode, and frequently in incremental mode (Full Refresh = No) to capture and report on competence level hierarchy changes in your enterprise.
If a system failure occurs, you must restart the process.
This program populates the time dimension folders in the Developer HRMSi – Administration – Analytic Dimensions business area with time information. So, for example, it populates the Time Year – (Dimension Level) folder with a list of years, and the Time Month – (Dimension Level) folder with a list of months.
Update Gregorian Hierarchy in the Time Dimension concurrent program has the following parameters:
Date From
Set this to the earliest date you need to see in your reports.
Date To
Set this to the latest date you need to see in your reports.
Do not use an unnecessarily wide date range, as this will impede performance.
Run this program when you need to collect future dates.
Run the HRI Load All Geography Category Structures concurrent program in order to populate the geography dimension with values. This concurrent program populates the location information stored in the HR_LOCATIONS_ALL table into a separate summary table.
The HRI Load All Geography Category Structures concurrent program has the following parameters:
Collect From Date
The earliest date required for geography information. The default is one month previous the first time you run this program, thereafter the value defaults to the previous Collect To Date.
Collect To Date
The latest date required for geography information. The default is the current date.
Full Refresh
If you set Full Refresh to Yes, the collection program purges previously inserted records and populates the dimension again for the entire collection date range specified (see the Collect From Date and Collect To Date parameters).
If you set Full Refresh to No, the program collects data in the incremental mode and that means it inserts new records without purging the table.
Oracle recommends that you run the HRI Load All Geography Category Structures concurrent program infrequently in full refresh mode, and frequently in incremental mode to reflect changes in the geography hierarchy in your enterprise.
If a system failure occurs, you must restart the process.
This concurrent program collects formula definitions for voluntary and involuntary separation reasons into a table to improve reporting performance.
This concurrent program has no parameters.
At implementation you need to run the HRI Load Separation Reasons Table concurrent program in order to initially populate the Separation Reasons table.
Thereafter, Oracle recommends that you run the HRI Load Separation Reasons Table concurrent program only after changes to the HR_MOVE_TYPE formula.
Note: If you have EDW implemented then it is recommended that you never schedule the HRI Load Separation Reasons Table process to run regularly. It should only be run on an ad-hoc basis when the full implications on the Workforce Separation fact re-collection are understood and accepted.
Use the procedures in this topic in conjunction with the implementation steps to successfully set up the Discoverer Analytic workbooks.
Additional Information: See also My Oracle Support Knowledge Document 2277369.1, Oracle E-Business Suite Support Implications for Discoverer 11gR1.
The geography dimension reflects a hierarchical location on Earth. A geography dimension may define an area as large as a world region or as small as a grid location on a building floor, or a stock location.
You need to define three levels of geography dimension:
Area (parent of country)
Country (parent of region)
Region
You first need to define your areas, countries, and regions for the geography dimension.
Once you have defined your areas, countries, and regions, you need to assign the regions to countries, and the countries to areas.
Create a Region segment on the Location Descriptive Flexfield.
Enter a region in the Region segment.
Map the region flexfield segment to the geography dimension.
See: Mapping the Region flexfield segment to the geography dimension
Finally, populate the geography dimension with values.
Areas are unique groups with countries assigned. You can define areas as you wish. Areas are not predefined—you must create the areas that you need.
Country is an existing dimension and is already defined in Oracle Applications.
Regions are subsets of countries. You can define as many regions as you wish within a country. Regions are not predefined—you must create the regions that you need.
Use the Application Object Library Lookups window.
To define areas
Enter Query Mode and search for Area in the Type field.
Add desired areas (for example, North America, EMEA, or Asiapac)
Save your changes.
To define regions
Enter Query Mode and search for Region in the Type field.
Add desired areas (for example, North, South, East, West, or My Region)
Save your changes.
Once you have defined your areas and regions, you need to assign regions to countries, and countries to areas.
Use the Define Territory Hierarchies window.
To assign regions to countries
In the Parent Territory Type field, select Country.
In the Code field, select a country that you have created.
In the Child Territory region, select the regions you want to associate with the selected country.
Repeat for other countries as necessary.
Save your changes.
To assign countries to areas
In the Parent Territory Type field, select Area.
In the Code field, select an area that you have created.
In the Child Territory region, select the countries you want to associate with the selected area.
Repeat for other areas as necessary.
Save your changes.
Set up a Region segment on the Location descriptive flexfield.
Use the Descriptive Flexfield Segments window.
To set up a Region segment
In the Title field, query for Additional Location Details.
Unfreeze the flexfield.
In the Context Field Values region, select Global Data Elements.
Click the Segments button.
Enter a new region segment name.
Select an available attribute.
In the Value Set field, enter BIS Region Value Set (predefined).
Freeze the flexfield.
Compile the flexfield.
Once you have defined regions and set up the region segment on the Location flexfield, you need to associate regions with locations.
Use the Location window.
To assign a region to a location
In the Name field, query for the desired location.
Click on the flexfield.
In the Region field of the Additional Location Details window, enter the name of a region you defined earlier.
Exit the flexfield.
Save your changes.
Repeat for other locations as necessary.
You need to map your Region flexfield segment (see: Setting up a Region segment) to the Geography dimension.
Use the Flexfield Mappings window.
To map the region flexfield segment to the geography dimension
In the Flexfield Name field, query Additional Location Details.
In the Dimension region, enter a dimension of Geography with a level of Region.
Save your changes
Click the Segment Mapping button.
In the Context Code field, enter Global Data Elements.
In the End User Column Name field, select the name of your region segment.
Close the Segment Mappings window.
Save your changes and close the Flexfield Mappings window.
Once you have performed this procedure, you can populate the Geography dimension. See: Populating the Geography Dimension
The number of training hours for your employees is displayed in the Training Success report and in the following workbooks:
Student Training Success (External Students) Analysis Workbook
Student Training Success (Internal Students) Analysis Workbook
Training Cost and Revenue Analysis Workbook
Employee Training Attendance Success (Organization Hierarchy) Status Analytics Workbook
Employee Training Attendance Success (Organization Hierarchy) Trend Analytics Workbook
The number of training hours is also displayed in the following KPI reports:
Training Attendance Status
Training Attendance with Job Category Status
Within Oracle Learning Management you can record the duration of a training event using a time period of your choice. For example, rather than recording an event in hours you might record it in weeks or months.
To enable the reports to display the number of hours of an event, a predefined formula,, TEMPLATE_BIS_TRAINING_CONVERT_DURATION, converts your time periods into hours.
OTA is installed with four predefined time periods. If you record the duration of events using these predefined time periods the formula automatically converts them into the following hours:
D (Day) = 8 Hours
W (Week) = 40 Hours
M (Month) = 169 Hours
Y (Year) = 2028 Hours
Note: You set up time periods in Oracle Training Administration using the Lookup type FREQUENCY.
However, you should copy and amend the formula if:
The number of hours per time period does not match those of your enterprise.
You have set up different time periods using the Lookup type FREQUENCY.
Additional frequencies you have set up are not automatically converted to hours using the predefined formula.
To ensure that the report and workbooks calculate the correct hours for your training events you can configure the predefined formula.
If you have defined your own training time periods using the FREQUENCY Lookup you must copy and amend the predefined formula. If you do not add your additional time periods to the formula the hours for these events will not be included in the report and workbooks.
To ensure that the report and workbooks calculate the correct hours for your training events you can configure the predefined TEMPLATE_BIS_TRAINING_CONVERT_DURATION formula.
For example, if you record the duration of a training event in days, and within your training organization a day is 10 hours, use the Formula window to:
Copy the predefined template formula TEMPLATE_BIS_TRAINING_CONVERT_DURATION.
You must name the new formula BIS_ TRAINING_CONVERT_DURATION, otherwise the report and workbooks cannot use it.
Replace the number of hours a training event runs in a day.
In the predefined formula the number of hours are set as follows:
hours_per_day = 8
Replace the number of hours with 10:
hours_per_day = 10
Compile the new formula.
For information on editing a formula, see: Writing or Editing a Formula, Oracle HRMS FastFormula User Guide
If you have defined your own training time periods using the FREQUENCY Lookup you must copy and amend the predefined TEMPLATE_BIS_TRAINING_CONVERT_DURATION formula. If you do not add your additional time periods to the formula the hours for these events will not be included in the report and workbooks.
For example, if you set up a time period called Fortnight using the Lookup code of F (Fortnight), use the Formula window to amend the formula as follows:
Copy the predefined template formula TEMPLATE_BIS_TRAINING_CONVERT_DURATION.
You must name the new formula BIS_ TRAINING_CONVERT_DURATION, otherwise the report and workbooks cannot use it.
Add the additional text shown in bold to set up the formula so that it converts fortnight into hours:
/* Main Body of Formula */
hours_per_day = 8
hours_per_week = hours_per_day * 5
hours_per_fortnight = hours_per_week * 2
hours_per_month = hours_per_week * 4.225
hours_per_year = hours_per_month * 12
/* Calculate Duration in Hours */
IF (from_duration_units = 'Y') THEN
hours = from_duration * hours_per_year
ELSE IF (from_duration_units = 'M') THEN
hours = from_duration * hours_per_month
ELSE IF (from_duration_units = 'F') THEN
hours = from_duration * hours_per_fortnight
ELSE IF (from_duration_units = 'W') THEN
hours = from_duration * hours_per_week
ELSE IF (from_duration_units = 'D') THEN
hours = from_duration * hours_per_day
ELSE IF (from_duration_units = 'H') THEN
hours = from_duration
ELSE
hours = 0
/* Calculate Duration in desired units */
IF (to_duration_units = 'H') THEN
to_duration = hours
ELSE IF (to_duration_units = 'D') THEN
to_duration = hours / hours_per_day
ELSE IF (to_duration_units = 'W') THEN
to_duration = hours / hours_per_week
ELSE IF (to_duration_units = 'F') THEN
to_duration = hours / hours_per_fortnight
ELSE IF (to_duration_units = 'M') THEN
to_duration = hours / hours_per_month
ELSE IF (to_duration_units = 'Y') THEN
to_duration = hours / hours_per_year
ELSE
to_duration = 0
RETURN to_duration
Compile the formula.
For more information on editing a formula, see: Writing or Editing a Formula, Oracle HRMS FastFormula User Guide
The Hours Worked report, and the Employee Hours Worked Trend Analytics Workbook, enable you to investigate the number of regular hours and overtime hours worked by employees in your enterprise.
Within Oracle Human Resources you use elements to record the number of regular and overtime hours worked. You can set up different elements to represent overtime bands and regular hours. This gives you the freedom to record and pay employees exactly how you want.
To use the Hours Worked report or workbook, you need to set up information about how you are recording regular and overtime hours. To do this you use Oracle FastFormula. You first need to create database items for the input values of the elements that record regular and overtime hours.
Create a database items for the input values of the elements that record regular hours and overtime hours.
You first need to create a formula to calculate the regular hours recorded for your employees.
You must name the regular hours formula BIS_REG_HOURS. This is the formula that the Hours Worked reports look for when calculating the regular hours.
The formula type must be QuickPaint.
Set up your formula as follows to display regular hours on the Hours Worked reports:
default for <element name>_<input name>_entry_value is 0.00 hours_worked = <element name>_<input name>_entry_value Return hours_worked
In the above code <element name>_<input name>_entry_value represents the database item you created for the input value of the element that records regular hours. Replace the following:
<element name>: Replace with the name of the element you are using to record regular hours worked.
<input name>: Replace with the name of the input value on the element you are using to record regular hours worked.
See: Writing or Editing a Formula, Oracle HRMS FastFormula User Guide
Most enterprises have more than one overtime band set up. For example, you may have an overtime band for double time, time and a half, triple time and so on.
You need to create a formula for each overtime band you use. You must name your formulas:
BIS_OT_BAND1
BIS_OT_BAND2
BIS_OT_BAND3
... and so on.
The number at the end of the formula name should change with each overtime band.
Set up the formula type as QuickPaint.
You can set up the elements that record overtime in different ways:
A single element with input for each overtime band
Multiple elements, one for each overtime band
Whichever method you use, you need to set up one formula for each overtime band. Base your formulas on the following code:
default for <element name>_<input name>_entry_value is 0.00 hours_worked = to_number(<element name>_<input name>_entry_value) return hours_worked
In the above code <element name>_<input name>_entry_value represents the database items you created for the input value of each element that record overtime hours. Replace the following:
<element name>: Replace with the name of the element you are using to record overtime hours worked.
<input name>: Replace with the name of the input value on the element you are using to record overtime hours worked.
The Hours Worked report shows up to 10 overtime bands, therefore, for this report, you can write up to 10 formulas. The Employee Hours Worked Trend Analytics Workbook displays one overtime band as default, but you can customize this workbook to display however many overtime bands you use in your enterprise.
The Hours Worked report uses the description you enter in the Description field of the Oracle Human Resources Formula window to label each overtime bar on the chart. It only uses the first 20 characters of the description.
See: Writing or Editing a Formula, Oracle HRMS FastFormula User Guide
If you are using Oracle Human Resources in the United States and you use the predefined Overtime element to record information about overtime, writing the formula is simple. Oracle Human Resources provides a predefined example of the formula you need to write. The example formula is called EXAMPLE_BIS_OT_BAND1 and you can view it using the Formula window.
The sample formula is shown below:
/******************************************************
FORMULA NAME: EXAMPLE_BIS_OT_BAND1
FORMULA TYPE: Quickpaint
DESCRIPTION: This is an example of the syntax required for the
formula you need to set up for use with
the Hours Worked Report. The function
get_hours_worked calculates total hours worked from
the Overtime element predefined with US Payroll.
INPUTS: None
DBI Required: None
Change History
10 Sep 98 jmay Created
*******************************************************/
/* Defaults Section */
/* Inputs Section */
/* Main Body of Formula */
hours_worked = get_hours_worked(1.5)
RETURN hours_worked
Note: This example only works if you use the predefined US Overtime element.
For each overtime band your enterprise uses, set up a formula based on the example. You must name your formulas:
BIS_OT_BAND1
BIS_OT_BAND2
BIS_OT_BAND3
... and so on.
The number at the end of the formula name should change with each overtime band.
Prior to amending the formula for configuring working hours, you need to create database items for the input values on the elements you use to record regular and overtime hours.
Use the Element window.
To create database items for working hours input values
In the Name field, search for the name of the element used to record regular or overtime hours.
Click Input Values to go to the Input Values window.
Select Database Item to create a database item for the input value.
Repeat the above steps for each element you use to record hours.
The Employee Hours Worked Trend Analytics Workbook contains a calculation named Example Overtime Band 1. To display all the overtime bands used by your enterprise, you need to create new calculations for each overtime band. Use the predefined Example Overtime Band 1 calculation as your template.
The calculation text in Example Overtime Band 1 is:
SUM(HRI_UTL_GET_HOURS_WORKED('EXAMPLE_BIS_OT_BAND1',"Assignment Id (for Workbook Calculation)",NULL,Period End Date))
Use Oracle Discoverer User Edition.
Additional Information: See also My Oracle Support Knowledge Document 2277369.1, Oracle E-Business Suite Support Implications for Discoverer 11gR1.
To adapt the Hours Worked Workbook
In Oracle Discoverer User Edition, open the HRMSi - Employee Hours Worked (Organization Hierarchy) Trend Analytics workbook.
Copy and rename the example calculation Example Overtime Band 1 for each overtime band you want to display in the report.
In the text of each new calculation, change EXAMPLE_BIS_OT_BAND1 to the name of each of one of your overtime band formulas.
Note: If you have not created Overtime Band formulas, see: Setting Up and Configuring Working Hours
Hide the example calculation.
Reports that use the competence level hierarchy gather information from the Competence Level Summary table.
Populate this summary table with your competence level hierarchy data. Use the HRI Load All Competence Level Hierarchy concurrent program. The program will collect pre-calculated data and store it in the Competence Level Summary table ready for use by the workbooks.
Use the Submit Requests window.
To populate the Competence Level Summary table
Select Single Request in the Submit a New Request window.
In the Name field, select HRI Load All Competence Level Hierarchy.
At implementation, use the following parameter values:
| Parameter | Value at Implementation |
|---|---|
| Collect From Date | The earliest date required for competence level hierarchy information (default is five years previous) |
| Collect To Date | The current date (default) |
| Full Refresh | Yes (default) |
Submit your request.
Define when you want to run this process using the schedule options.
For full information on this concurrent program, see HRI Load All Competence Level Hierarchy
Reports that use the job competence requirements gather information from the Competence Requirement Fact Summary table.
You must populate this summary table with the job competence requirements used by your reports. Use the HRI Load All Competence Requirement Fact concurrent program. The program will collect pre-calculated data and store it in the Competence Requirement Fact Summary table ready for use by the workbooks.
Use the Submit Requests window.
To populate the Competence Requirement Fact Summary table
Select Single Request in the Submit a New Request window.
In the Name field, select HRI Load All Competence Requirement Fact.
At implementation, use the following parameter values:
| Parameter | Value at Implementation |
|---|---|
| Collect From Date | The earliest date required for competence requirement information (default is five years previous) |
| Collect To Date | The current date (default) |
| Full Refresh | Yes (default) |
Submit your request.
Define when you want to run this process using the schedule options.
For full information on this concurrent program, see HRI Load All Competence Requirement Fact
Analytics workbooks use time elements such as Year, Month, and Week. The workbooks will not return any data unless you populate the Discoverer End User Layer time dimension folders with time elements by using the Update Gregorian Hierarchy in the Time Dimension concurrent program.
The Update Gregorian Hierarchy in the Time Dimension concurrent program populates the Time dimension folders in the Developer HRMSi – Administration – Analytic Dimensions business area with time information. So, for example, it populates the Time Year – (Dimension Level) folder with a list of years, and the Time Month – (Dimension Level) folder with a list of months.
Use the Submit Requests window.
To populate the Time Dimension folders
Select Single Request in the Submit a New Request window.
In the Name field, select Update Gregorian Hierarchy in the Time Dimension.
At implementation, use the following parameter values:
| Parameter | Value at Implementation |
|---|---|
| Date From | The earliest date you need to see in your reports |
| Date To | The latest date you need to see in your reports |
Submit your request.
Run this program when required to collect dates further in the future. For full information on this concurrent program, see: Update Gregorian Hierarchy in the Time Dimension
Do not use an unnecessarily wide date range, as this will impede performance.
Prior to populating the geography dimension, you must set up the geography structures. See: Setting Up Geography
Run the HRI Load All Geography Category Structures concurrent program to populate the geography dimension with values. This concurrent program populates the location information stored in the HR_LOCATIONS_ALL table into a separate summary table.
Use the Submit Requests window.
To populate the geography dimension
Select Single Request in the Submit a New Request window.
In the Name field, select HRI Load All Geography Category Structures.
At implementation, use the following parameter values:
| Parameter | Value at Implementation |
|---|---|
| Collect From Date | The earliest date required for geography information (default is one month previous) |
| Collect To Date | The current date (default) |
| Full Refresh | Yes (default is No) |
Submit your request.
Run this program when required to collect dates further in the future. For full information on this concurrent program, see: HRI Load All Geography Category Structures