| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19039-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users Release 7.9.6.3 Part Number E19039-01 |
|
|
PDF · Mobi · ePub |
This section describes how to configure Oracle Human Resources Analytics. It contains the following topics:
Section 7.2, "Configuration Required Before A Full Load for Oracle HR Analytics"
Section 7.2.1, "Configuration Steps for Oracle HR Analytics for All Source Systems"
Section 7.2.2, "Configuration Steps for Oracle HR Analytics for Oracle EBS"
Section 7.2.4, "Configuration Steps for Oracle HR Analytics for PeopleSoft"
Section 7.2.5, "Configuration Steps for Oracle HR Analytics for Universal"
Section 7.3, "Configuration Steps for Controlling Your Data Set"
Note:
All references to Oracle 11i also apply to Oracle R12.Oracle Human Resources contains information for workforce profile, employee events, compensation, recruitment, leave and absence, and learning.
The Oracle HR Analytics application has the following functional areas:
Workforce Profile. The Workforce Profile functional area is the information foundation for workforce analysis. It provides the comprehensive core workforce information to support analysis on head count, retention, workforce diversity, employee performance, and contingent labor utilization. Key Workforce Profile information such as employee, organization, supervisor, performance band, and service band are shared with other HR functional areas. Sensitive personal attributes like birth date, age, and marital status are organized in a separate folder to allow for restricted access.
Configurable HR event analysis is another key feature of Workforce Profile functional area. Customers can configure various employee assignment actions to support analysis in voluntary/involuntary termination, hires, transfers, promotions, or layoffs, and so on. In addition, changes in an employee's job, organization, location, supervisor and salary are tracked to support workforce movement analysis.
Specifically, the following analysis is supported by Workforce Profile functional area:
Headcount analysis
Workforce diversity
Employee attrition and retention
Employee performance
Span of control
Internal mobility
Compensation. HR Compensation functional area analyzes employee payroll information that is vital to success in today's economy. It enables you to analyze salaries, benefits, and other rewards that comprise your employee compensation plan. The delivered compensation metrics allow you to correlate employee pay with performance and perform compensation parity analyses at different levels of granularity. It proactively detects over or under-compensated employees, which can have big impact on your company's ability to maintain a competitive edge. The HR Compensation area provides the information your HR Management department needs to manage compensation costs and evaluate the effectiveness of the compensation plan by identifying emerging trends within the organization, or within specific areas of compensation.
Recruitment. Recruitment functional area provides executives, recruiting managers and line managers the intelligence in assessing the efficiency and effectiveness of the recruitment process in sourcing and attracting quality new hires. It delivers over 100 metrics to monitor the entire recruitment life cycle. Specifically, the following recruitment analysis are supported by the Recruitment functional area:
Job vacancy analysis
Recruitment events analysis
Quality of hire
Source of hire
Applicant pool analysis
Referral analysis
Learning. Learning is a key component of Talent Management. The Learning functional area focuses on the analysis of course offerings, delivery methods, course utilization, and learner enrollment and completion. By combining learning and workforce metrics, the Learning functional area provides critical insight into the effectiveness of learning delivery and how learning contributes to workforce development and employee performance.
Leave and Absence. Absenteeism impedes workforce productivity and increase workforce cost. Leave & Absence functional area analyzes workforce leave and absence trends, working days lost, and absenteeism hot spots.
Human Resource Effectiveness. Human Resources Effectiveness allows senior HR executives to monitor key HR effectiveness metrics at the enterprise level. It combines key HR metrics with the organization's financial data. The correlation of workforce and financial metrics provides insight into how workforce trends directly impact the organization's operations and financial strength.
U.S. Statutory Compliance. The U.S. Statutory Compliance functional area delivers monitoring reports to support compliance with US legislative EEO, AAP, and Vets100 reporting.
This section contains configuration steps that you need to perform on Oracle HR Analytics before you do a full data load. It contains the following topics:
Section 7.2.1, "Configuration Steps for Oracle HR Analytics for All Source Systems"
Section 7.2.2, "Configuration Steps for Oracle HR Analytics for Oracle EBS"
Section 7.2.4, "Configuration Steps for Oracle HR Analytics for PeopleSoft"
Section 7.3, "Configuration Steps for Controlling Your Data Set"
Before you run Execution Plans for HR Subject Areas, ensure that you have used DAC to set the appropriate Source System Parameters. To do so, in DAC, go to the Design view, and select the appropriate custom container from the drop-down list. Then, display the Source System Parameters tab.
When you load OLTP data into the HR Workforce Snapshot Fact tables, data is loaded from 01/01/2004 12:00:00 AM out-of-the-box. To load data from an earlier date, use the $$HR_WRKFC_SNAPSHOT_DT parameter on the Source System Parameters tab in DAC to specify a different date. Note that loading more data into the snapshot fact tables can adversely affect ETL performance.
This section contains configuration steps required before you do a full data load that apply to all source systems. It contains the following topics:
Note:
For configuration steps that apply to all analytics modules (for example, Oracle Financial Analytics, Oracle HR Analytics, or Oracle Sales Analytics), see Chapter 3, "Configuring Common Areas and Dimensions."This section contains information about how to configure the band dimensions for all source systems. It contains the following topics:
Section 7.2.1.1.2, "Configuring file_age_band.csv for Age Band Dimension"
Section 7.2.1.1.3, "Configuring the file_req_age_band.csv for Job Requisition Age Band Dimension"
Section 7.2.1.1.4, "Configuring the file_perf_age_band.csv for Performance Band Dimension"
To enable data analysis based on various groups of a given attribute, Oracle BI Applications provides an option to configure your choice of groups, or bands, for these four attribute families:
Person Age
Job Requisition Age
Performance Ratings
Period of Service
The band data that you configure is stored in four corresponding dimension tables. Table 7-1 provides a description of each of these tables.
Table 7-1 Dimension Tables that Store Band Data
| Dimension Table | Description |
|---|---|
|
W_AGE_BAND_D |
Age Band table. This table breaks down the ages of people into different bands to help determine the age ranges the people fall into. The table has two levels:
|
|
W_JOB_RQSTN_AGE_BAND_D |
Job Requisition Age Band table. This table breaks down the age of the job requisition into different bands to help determine the age range the job requisition falls into. The table has two levels:
|
|
W_PERFORMANCE_BAND_D |
Performance Band table. This table breaks down the performance ratings into different bands to help determine the level of quality of a candidate. The table has two levels:
|
|
W_PRD_OF_WRK_BAND_D |
Period of Work Band table. This table breaks down employees and contingent workers into different bands to help determine the time that the employees or the contingent workers have been employed. The table has three levels:
|
Each band dimension uses a CSV file to define the band definitions. The CSV files that you need to configure for the band dimensions are:
file_age_band.csv
file_req_age_band.csv
file_perf_age_band.csv
file_emp_pow_band.csv
file_cwk_pow_band.csv
Note:
If these bands are changed after the initial ETL run, the data in subsequent ETL runs will be inaccurate. Also, any changes to these files will not retroactively change data that has already run through the system.All band dimensions use one common CSV file, file_row_gen_band.csv, to generate the individual elements that fall into each band. No configuration is required of this common file.
All CSV files for the band dimensions, including the common file, are stored in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).
The Age Band dimension uses file_age_band.csv. To configure this file, open the CSV file in a text editor and provide inputs based on the column descriptions in Table 7-2.
Table 7-2 Age Band Dimension Columns
| Parameter Name | Description |
|---|---|
|
AGE_BAND_CODE |
This is the short name of the band and should be unique. |
|
AGE_BAND_DESC |
This is a description of the band that is displayed to end users. It is intended to be easier to read than the AGE_BAND_CODE. |
|
BAND_MIN_MONTHS |
This is the minimum number of months to qualify for the band (inclusive). If this field is blank, min years is required. If both min months and min years are present, min years is ignored. |
|
BAND_MAX_MONTHS |
This is the maximum number of months to qualify for the band (non inclusive). If this field is blank and max years is blank then 1800 is implied, if both max months and max years are present, max years is ignored. |
|
BAND_MIN_YEARS |
This is the minimum number of years to qualify for the band (inclusive). If this field is blank, min months is required, if both min months and min years are present, min years is ignored. |
|
BAND_MAX_YEARS |
This is the maximum number of years to qualify for the band (non inclusive). If this field is blank and max months is blank then 150 is implied, if both max months and max years are present, max years is ignored. |
When defining the data in the CSV file, ensure that:
The first band starts with a value of 0 for BAND_MIN_MONTHS or BAND_MIN_YEARS.
The last band does not have a value for BAND_MAX_MONTHS or BAND_MAX_YEARS.
The bands are contiguous to avoid losing rows in reports, which display by Age Band
Table 7-3 is an example of file_age_band.csv inputs.
The Job Requisition Age Band dimension uses file_req_age_band.csv. To configure this file, open the CSV file in a text editor and provide inputs based on the column descriptions in Table 7-4.
Table 7-4 Job Requisition Age Band Dimension Columns
| Parameter Name | Description |
|---|---|
|
REQ_AGE_BAND_CODE |
This is the short code of the band and should be unique. |
|
REQ_AGE_BAND_DESC |
This is a description of the band that is displayed for end users. It is intended to be easier to read than the REQ_AGE_BAND_CODE. |
|
REQ_BAND_MIN_MONTHS |
This is the minimum number of months to qualify for the band (inclusive). If this field is blank, min years is required. If both min months and min years are present, min years is ignored. |
|
REQ_BAND_MAX _MONTHS |
This is the maximum number of months to qualify for the band (non inclusive). If this field is blank and max years is blank then 1800 is implied. If both max months and max years are present, max years is ignored. |
|
REQ_BAND_MIN_YEARS |
This is the minimum number of years to qualify for the band (inclusive). If this field is blank, min months is required. If both min months and min years are present, min years is ignored. |
|
REQ_BAND_MAX_YEARS |
This is the maximum number of years to qualify for the band (non inclusive). If this field is blank and max months is blank then 150 is implied. If both max months and max years are present, max years is ignored. |
When defining the data in the CSV file, ensure that:
The first band starts with a value of 0 for REQ_BAND_MIN_MONTHS or REQ_BAND_MIN_YEARS
The last band does not have a value for REQ_BAND_MAX_MONTHS or REQ_BAND_MAX_YEARS
The bands should be contiguous to avoid losing rows in reports which display by Requisition Age Band
Table 7-5 Example of file_req_age_band.csv Inputs
| REQ_AGE_BAND_CODE | REQ_AGE_BAND_DESC | REQ_BAND_MIN_MONTHS | REQ_BAND_MAX_MONTHS |
|---|---|---|---|
|
REQ_AGE_BAND_1 |
<1 Month |
0 |
1 |
|
REQ_AGE_BAND_2 |
1 to 3 Months |
1 |
3 |
|
REQ_AGE_BAND_3 |
3 to 6 Months |
3 |
6 |
|
REQ_AGE_BAND_4 |
6 to 12 Months |
6 |
12 |
|
REQ_AGE_BAND_5 |
12 to 24 Months |
12 |
24 |
|
REQ_AGE_BAND_6 |
>= 24 Months |
24 |
The Performance Band dimension uses file_perf_age_band.csv. To configure this file, open the CSV file in a text editor and provide inputs based on the column descriptions in Table 7-6.
Table 7-6 Performance Band Dimension Columns
| Parameter Name | Description |
|---|---|
|
PERF_BAND_CODE |
This is the short code of the band and should be unique. |
|
PERF_BAND_DESC |
This is a description of the band that is displayed to end users. It is intended to be easier to read than the PERF_BAND_CODE. |
|
BAND_MIN_NORM_RATING |
This is the minimum rating to qualify for the band (inclusive). This field is required. |
|
BAND_MAX_NORM_RATING |
This is the maximum rating to qualify for the band (non inclusive). If this field is blank then 100 is implied. |
When defining the data in the CSV file, ensure that:
The first band starts with a value of 0 for BAND_MIN_NORM_RATING.
The last band does not have a value for BAND_MAX_NORM_RATING.
The bands are contiguous to avoid losing rows in reports which display by Performance Band
See the related domain value configuration sections for EBS and PeopleSoft:
For information about configuring the domainValues_perf_nrml_rating_oraVersion.csv, see Section 7.2.2.13, "How to Configure the domainValues_perf_nrml_rating_oraVersion.csv."
For information about configuring the domainValues_perf_nrml_rating_psft.csv, see Section 7.2.4.19, "How to Configure the domainValues_perf_nrml_rating_psft.csv."
The Period of Work Band dimension uses these two files:
file_emp_pow_band.csv. Use this file to configure the employee information.
file_cwk_pow_band.csv. Use this file to configure the contingent worker information.
To configure the file_emp_pow_band.csv, open the file in a text editor and provide inputs based on the column descriptions in Table 7-8.
Table 7-8 Employee Columns in the file_emp_pow_band.csv for the Period of Work Band Dimension
| Column Name | Description |
|---|---|
|
EMP_POW_BAND_CODE |
This is the short code of the band and should be unique. |
|
EMP_POW_BAND_DESC |
This is a description of the band that is displayed to end users. It is intended to be easier to read than the EMP_POW_BAND_CODE. |
|
EMP_BAND_MIN_MONTHS |
This is the minimum number of months to qualify for the band (inclusive). If this field is blank, min years is required. If both min months and min years are present, min years is ignored. |
|
EMP_BAND_MAX_MONTHS |
This is the maximum number of months to qualify for the band (non inclusive). If this field is blank and max years is blank then 1800 is implied, if both max months and max years are present, max years is ignored. |
|
EMP_BAND_MIN_YEARS |
This is the minimum number of years to qualify for the band (inclusive). If this field is blank, min months is required. If both min months and min years are present, min years is ignored. |
|
EMP_BAND_MAX_YEARS |
This is the maximum number of years to qualify for the band (non inclusive). If this field is blank and max months is blank then 150 is implied. If both max months and max years are present, max years is ignored. |
When defining the data in the CSV file, ensure that:
The first band starts with a value of 0 for EMP_BAND_MIN_MONTHS or EMP_BAND_MIN_YEARS
The last band does not have value for EMP_BAND_MAX_MONTHS or EMP_BAND_MAX_YEARS
The bands are contiguous to avoid losing rows in reports which display by Period of Placement band
Table 7-9 Example of file_emp_pow_band.csv Inputs
| EMP_POW_BAND_CODE | EMP_POW_BAND_DESC | EMP_BAND_MIN_MONTHS | EMP_BAND_MAX_MONTHS |
|---|---|---|---|
|
EMP_BAND_1 |
<1 Year |
0 |
12 |
|
EMP_BAND_2 |
1 to 2 Years |
12 |
24 |
|
EMP_BAND_3 |
2 - 5 Years |
24 |
60 |
|
EMP_BAND_4 |
5 to 10 Years |
60 |
120 |
|
EMP_BAND_5 |
10 to 20 Years |
120 |
240 |
|
EMP_BAND_6 |
>= 20 Years |
240 |
For contingent worker data, use the file_cwk_pow_band.csv file. Open the file in a text editor and provide inputs based on the column descriptions in Table 7-10.
Table 7-10 Contingent Worker Columns in the file_cwk_pow_band.csv for the Period of Work Band Dimension
| Column Name | Description |
|---|---|
|
CWK_POW_BAND_CODE |
This is the short code of the band and should be unique. |
|
CWK_POW_BAND_DESC |
This is a description of the band that is displayed to end users. It is intended to be easier to read than the CWK_POW_BAND_CODE. |
|
CWK_BAND_MIN_MONTHS |
This is the minimum number of months to qualify for the band (inclusive). If this field is blank, min years is required. If both min months and min years are present, min years is ignored. |
|
CWK_BAND_MAX_MONTHS |
This is the maximum number of months to qualify for the band (non inclusive). If this field is blank and max years is blank, then 1800 is implied. If both max months and max years are present, max years is ignored. |
|
CWK_BAND_MIN_YEARS |
This is the minimum number of years to qualify for the band (inclusive). If this field is blank, min months is required. If both min months and min years are present, min years is ignored. |
|
CWK_BAND_MAX_YEARS |
This is the maximum number of years to qualify for the band (non inclusive). If this field is blank and max months is blank, then 150 is implied. If both max months and max years are present, max years is ignored. |
When defining the data in the CSV file, ensure that:
The first band starts with a value of 0 for CWK_BAND_MIN_MONTHS or CWK_BAND_MIN_YEARS
The last band does not have a value for CWK_BAND_MAX_MONTHS or CWK_BAND_MAX_YEARS
The bands are contiguous to avoid losing rows in reports which display by Period of Placement band
You configure Oracle HR Analytics by mapping domain values to columns in the CSV files located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
For more information on configuring domain values with CSV worksheet files, see Section 17.13, "About Domain Values" and Section 17.14, "Configuring the Domain Value Set with CSV Worksheet Files."
For more information on domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Note:
Incorrect mappings may result in inaccurate calculations of Oracle Business Intelligence metrics.Some sessions may fail if these procedures are not compiled in the database before running the workflows.
Note:
When editing CSV files, ensure that you:Do not change the case of values in the CSV files for columns with the name format W_<xyz>_CODE; however, if required, carefully change the case of values for the columns with the name format W_xyz_DESC or W_<xyz >_NAME.
For example, do not change 'CONTRACTOR' to 'Contractor'.
Do not add new values, other than the values present in the CSV file, to the columns with the name format W_ columns.
In other words, if you add new rows to the spreadsheet, then the columns with the name format W_ values must map to those in the default spreadsheet. If you add new columns with the name format W_ values, then you must customize the affected mappings.
This section contains configuration steps required before you do a full data load that apply to Oracle EBS. It contains the following topics:
Section 7.2.2.1, "Domain Values and CSV Worksheet Files for Oracle EBS"
Section 7.2.2.2, "How to Configure the domainValues_AbsenceEvent_Status_oraVersion.csv"
Section 7.2.2.3, "How to Configure the domainValues_Employment_Cat_oraVersion.csv"
Section 7.2.2.4, "How to Configure the domainValues_Employee_Ethnic_Group_Code_oraVersion.csv"
Section 7.2.2.5, "How to Configure the domainValues_Employment_Full_Time_Flg_oraVersion.csv"
Section 7.2.2.6, "How to Configure the domainValues_Employee_Sex_MF_oraVersion.csv"
Section 7.2.2.7, "How to Configure the domainValues_Employment_Status_oraVersion.csv"
Section 7.2.2.8, "How to Configure the domainValues_Employee_Veteran_Status_Code_oraVersion.csv"
Section 7.2.2.9, "How to Configure the domainValues_Flsa_oraVersion.csv"
Section 7.2.2.10, "How to Configure the domainValues_HRPosition_Active_Pos_Flg_oraVersion.csv"
Section 7.2.2.11, "How to Configure the domainValues_Pay_Type_Flg_oraVersion.csv"
Section 7.2.2.12, "How to Configure the domainValues_Pay_Type_Grp_Code_oraVersion.csv"
Section 7.2.2.13, "How to Configure the domainValues_perf_nrml_rating_oraVersion.csv"
Section 7.2.2.14, "How to Configure the domainValues_Recruitment_Event_Reason_oraVersion.csv"
Section 7.2.2.15, "How to Configure the domainValues_Recruitment_Source_Type_oraVersion.csv"
Section 7.2.2.16, "How to Configure the domainValues_Requisition_Category_oraVersion.csv"
Section 7.2.2.17, "How to Configure the domainValues_Status_Recruitment_oraVersion.csv"
Section 7.2.2.18, "How to Configure the domainValues_Status_Vacancy_oraVersion.csv"
Section 7.2.2.19, "How to Configure the domainValues_Wrkfc_EventType_oraVersion.csv"
Section 7.2.2.20, "How to Configure the domainValues_LearningEnrollment_Status_oraVersion.csv"
Section 7.2.2.21, "How to Configure the domainValues_Wrkfc_Appraisals_oraVersion.csv"
Section 7.2.2.22, "How to Configure the file_Normal_Hours_Conversion_Factor_oraVersion.csv"
Section 7.2.2.23, "How to Configure Address Types for the Employee Dimension"
Section 7.2.2.24, "How to Configure Phone Types for HR Profile"
Section 7.2.2.25, "How to Configure Education Degree Codes for Employee Dimension"
Section 7.2.2.26, "How to Configure Flags for the Pay Type Dimension"
Section 7.2.2.27, "How to Configure Classification Names for Payroll"
Table 7-12 lists the CSV worksheet files and the domain values for Oracle HR Analytics in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Table 7-12 Domain Values and CSV Worksheet Files for Oracle E-Business Suite
| Worksheet File Name | Domain Value Table - Column | Description | Session |
|---|---|---|---|
|
domainValues_AbsenceEvent_Status_oraVersion.csv |
W_ABSENCE_EVENT_D.APPROVAL_STATUS |
Lists the absence approval status and their corresponding domain value of Approval Status. |
SDE_ORA_AbsenceEvent |
|
domainValues_Employment_Cat_oraVersion.csv |
W_EMPLOYMENT_D.W_EMPLOYEE_CAT_CODE |
Lists the User Person Types and their corresponding domain values of 'Employment Category Code' for the Oracle 11i Application. |
SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full, SDE_ORA_Employment Dimension_Workforce |
|
domainValues_Employee_Ethnic_Group_Code_oraVersion.csv |
W_EMPLOYEE_D.W_ETHNIC_GRP_CODE |
Lists the Ethnic codes and their corresponding domain values of 'Ethnic Group Code' for the Oracle 11i Application. |
SDE_ORA_Employee Dimension, SDE_ORA_Employee Dimension_Full |
|
domainValues_Employment_Full_Time_Flg_oraVersion.csv |
W_EMPLOYMENT_D.W_FULL_TIME_FLG |
Lists the Employment Categories and their corresponding domain values of 'Full Time Flag' for the Oracle 11i Application. |
SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full, SDE_ORA_Employment Dimension_Workforce |
|
domainValues_Employee_Sex_MF_oraVersion.csv |
W_EMPLOYEE_D.W_SEX_MF_CODE |
Lists the Sex codes and their corresponding domain values of 'Sex Code' for the Oracle 11i Application. |
SDE_ORA_EmployeeDimension, SDE_ORA_EmployeeDimension_Full |
|
domainValues_Employment_Status_oraVersion.csv |
W_EMPLOYMENT_D.W_EMPLOYMENT_STAT_CODE |
Lists the Per System Statuses and their corresponding domain values of 'Employment Status' for the Oracle 11i Application. |
SDE_ORA_Employment Dimension, SDE_ORA_Employment Dimension_Full, SDE_ORA_EmploymentDimension_Workforce |
|
domainValues_Employee_Veteran_Status_Code_oraVersion.csv |
W_EMPLOYEE_D.W_VETERAN_STATUS_CODE |
Lists the Veteran codes and their corresponding domain values of 'Veteran Status Code' for the Oracle 11i Application. |
SDE_ORA_EmployeeDimension, SDE_ORA_EmployeeDimension_Full |
|
domainValues_EventTypes_oraVersion.csv Note: This file is not used for Version 7.9.6 or later. |
W_EVENT_TYPE_D.W_EVENT_CLASS, W_EVENT_TYPE_D.W_EVENT_GRP_CODE, W_EVENT_TYPE_D.W_EVENT_REASON_CODE, W_EVENT_SUBG_CODE |
Lists the Event Types, Event Codes and Meanings and their corresponding domain values of 'Event Group', 'Event Sub-Group' and 'Event Reason' for the Oracle 11i Application. |
- |
|
domainValues_Flsa_oraVersion.csv |
W_EMPLOYMENT_D.W_EXEMPT_FLG, W_JOB_D.W_FLSA_STAT_CODE, W_HR_POSITION_D.W_EXEMPT_FLG |
Lists the FLSA Statuses and their corresponding domain values of 'Exempt Flag' for the Oracle R12 Application. |
SDE_ORA_EmploymentDimension_Workforce, SDE_ORA_JobDimension, SDE_ORA_JobDimension_Full, SDE_ORA_HRPositionDimension |
|
domainValues_HRPosition_Active_Pos_Flg_oraVersion.csv |
W_HR_POSITION_D.W_ACTIVE_POSITION_FLG |
Lists the Position Statuses and their corresponding domain values of 'Active Position Flag' for the Oracle 11i Application. |
SDE_ORA_HRPosition Dimension, SDE_ORA_HRPositionDimension |
|
domainValues_Pay_Type_Flg_oraVersion.csv |
W_PAY_TYPE_D.W_PAY_TYPE_FLG |
Lists the Costing Debit or Credit values and their corresponding domain values of 'Pay type Flag' for the Oracle 11i Application. |
SDE_ORA_PayTypeDimension, SDE_ORA_PayTypeDimension_Full |
|
domainValues_Pay_Type_Grp_Code_oraVersion.csv |
W_PAY_TYPE_D.W_PAY_TYPE_GRP_CODE |
Lists the Classification Names, Element Names and their corresponding domain values of 'Pay Type Group Code' for the Oracle 11i Application. |
SDE_ORA_PayTypeDimension, SDE_ORA_PayTypeDimension_Full |
|
domainValues_Perf_nrml_rating_oraVersion.csv |
W_PERFORMANCE_BAND_D.PERF_BAND_CODE, PERF_BAND_NAME |
Lists the performance ratings and their corresponding domain values of Performance Band Code/Name. |
SIL_PerformanceBandDimension |
|
domainValues_Recruitment_Event_Reason_oraVersion.csv |
W_RCRTMNT_EVENT_TYPE_D.W_EVENT_CODE, W_RCRTMNT_EVENT_TYPE_D.W_SUB_STAGE_CODE, W_RCRTMNT_EVENT_TYPE_D.W_STAGE_CODE, W_RCRTMNT_EVENT_TYPE_D.W_REASON_CODE, W_RCRTMNT_EVENT_TYPE_D.W_REASON_TYPE_CODE |
Lists the Job Requisition Statuses and various Recruitment Events and their corresponding domain values of 'Recruitment Event Code', 'Recruitment Sub Stage Code', 'Recruitment Stage Code', 'Recruitment Event Reason Code' and 'Recruitment Event Reason Type Code' for Oracle EBS. |
SDE_ORA_RecruitmentEventTypeDimension_ApplicantEvents, SDE_ORA_RecruitmentEventTypeDimension_ApplicantEvents_Full, SDE_ORA_RecruitmentEventTypeDimension_ApplicantTerminated, SDE_ORA_RecruitmentEventTypeDimension_ApplicantTerminated_Full, SDE_ORA_RecruitmentEventTypeDimension_Hire_Leave, SDE_ORA_RecruitmentEventTypeDimension_Hire_Leave_Full, SDE_ORA_RecruitmentEventTypeDimension_RequisitionStatuses, SDE_ORA_RecruitmentEventTypeDimension_RequisitionStatuses_Full, SDE_ORA_RecruitmentEventTypeDimension_Unspecified_Full |
|
domainValues_Recruitment_Source_Type_oraVersion.csv |
W_RCRTMNT_SOURCE_D.W_SOURCE_TYPE_CODE |
Lists the Recruitment sources (details) and their corresponding domain values of 'Recruitment Source Type Code' for Oracle EBS. |
SDE_ORA_RecruitmentSourceDimension_Detail, SDE_ORA_RecruitmentSourceDimension_Detail_Full, SDE_ORA_RecruitmentSourceDimension_Type, SDE_ORA_RecruitmentSourceDimension_Type_Full |
|
domainValues_Requisition_Category_oraVersion.csv |
W_JOB_RQSTN_D.W_CATEGORY_CODE |
Lists the Job Requisition Categories and their corresponding domain values of 'Job Requisition Category Code' for Oracle EBS. |
SDE_ORA_JobRequisitionDimension, SDE_ORA_JobRequisitionDimension_Full |
|
domainValues_Status_Recruitment_oraVersion.csv |
W_STATUS_D.W_STATUS_CLASS, W_STATUS_D.W_STATUS_CODE |
Lists the Recruitment Statuses and their corresponding domain values of 'Status Class' and 'Status Code' for Oracle EBS. |
SDE_ORA_StatusDimension_RecruitmentStatus, SDE_ORA_StatusDimension_RecruitmentStatus_Full |
|
domainValues_Status_Vacancy_oraVersion.csv |
W_STATUS_D.W_STATUS_CLASS, W_STATUS_D.W_STATUS_CODE |
Lists the Job Requisition Statuses and their corresponding domain values of 'Status Class' and 'Status Code' for Oracle EBS. |
SDE_ORA_StatusDimension_RequisitionStatus, SDE_ORA_StatusDimension_RequisitionStatus_Full |
|
domainValues_Wrkfc_EventType_oraVersion.csv |
W_WRKFC_EVENT_TYPE_D.W_EVENT_GRP_CODE, W_WRKFC_EVENT_TYPE_D.W_EVENT_SUBG_CODE, W_WRKFC_EVENT_TYPE_D.EVENT_CODE, W_WRKFC_EVENT_TYPE_D.PROMOTION_FLG, W_WRKFC_EVENT_TYPE_D.TRANSFER_FLG |
Lists the Event Types, Event Codes and Meanings and their corresponding domain values of 'Event Group', 'Event Sub-Group', and 'Event' for the Oracle R12 Application. |
SDE_ORA_PersistedStage_WorkforceEventDimension_Domain |
|
domainValues_LearningEnrollment_Status_oraVersion.csv |
W_STATUS_D.W_STATUS_CODE |
Maps source statuses onto a warehouse status. Measures/dates/FKs are set accordingly. |
SDE_ORA_Status Dimension_LearningEnrollment Status, SDE_ORA_Status Dimension_LearningEnrollment Status_Full |
|
domainValues_Wrkfc_Appraisals_oraVersion.csv |
W_ORA_WEVT_PERF_PS (Filter) |
Defines which appraisals to include or exclude (for example, include "Annual Performance Reviews," exclude any other type). |
SDE_ORA_PersistedStage_WorkforceEvent_Performance_Full, SDE_ORA_PersistedStage_WorkforceEvent_Performance_Incr |
|
file_Normal_Hours_Conversion_Factor_oraVersion.csv |
W_ORA_WEVT_ASG_PS.NORMAL_HOURS_MONTH |
Used to convert working hours to an annualized figure, which is then divided by 12 to store hours by month. |
SDE_ORA_PersistedStage_WorkforceEvent_Assignment_Full, SDE_ORA_PersistedStage_WorkforceEvent_Assignment_Incr |
This section explains how to configure the domainValues_AbsenceEvent_Status_oraVersion.csv file.
Note: This task is Oracle EBS R12-specific. For Oracle EBS 11.5.10 source systems, this configuration is not required.
Identify Approval Status values in your Oracle source system by using the following SQL:
SELECT DISTINCT APPROVAL_STATUS FROM PER_ABSENCE_ATTENDANCES
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_AbsenceEvent_Status_oraVersion.csv file in a text editor. In the file, verify whether the information provided in the following table is available, starting from the sixth line.
| APPROVAL_STATUS | ABSENCE_STATUS_CODE | ABSENCE_STATUS_NAME |
|---|---|---|
| Y | APPROVED | Approved |
| N | UNAPPROVED | Not Approved |
| - | REQUESTED | Requested or In Progress |
You can update or customize values for the ABSENCE_STATUS_CODE and ABSENCE_STATUS_NAME domain values.
This section explains how to configure the domainValues_Employment_Cat_oraVersion.csv file. A sub category is added to the employment dimension.
Identify the User Person Types in your Oracle source system by using the following SQL:
SELECT DISTINCT SYSTEM_PERSON_TYPE, USER_PERSON_TYPE
FROM PER_PERSON_TYPES
WHERE SYSTEM_PERSON_TYPE IN ('EMP','CWK','OTHER','EMP_APL','EX_EMP','EX_EMP_APL','RETIREE','PRTN')
ORDER BY 1,2
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Employment_Cat_oraVersion.csv file in a text editor.
From the SQL query result, copy the User Person Types and System Person Types data to the domainValues_Employment_Cat_oraVersion.csv file in the USER_PERSON_TYPE and SYS_PERSON_TYPE columns. Copy data starting from the sixth line.
In the domainValues_Employment_Cat_oraVersion.csv file, map each combination of the User Person Type and System Person Type data (results of the SQL query) to one delivered combination of the W_EMPLOYEE_CATEGORY_CODE and W_EMPLOYEE_SUB_CAT_CODE domain value.
You can map more than one combination of the User Person Type and System Person Type data to the same combination of the W_EMPLOYEE_CATEGORY_CODE and W_EMPLOYEE_SUB_CAT_CODE domain value by adding a new row. For example:
| USER_PERSON_TYPE | SYS_PERSON_TYPE | W_EMPLOYEE_CATEGORY_CODE |
|---|---|---|
| Contractor | OTHER | CONTINGENT |
| Contingent Worker | CWK | CONTINGENT |
Save and close the file.
This section explains how to configure the domainValues_Employee_Ethnic_Group_Code_oraVersion.csv file.
Identify the Ethnic Group Codes in your Oracle source system by using the following SQL:
SELECT DISTINCT PER_INFORMATION1 FROM PER_ALL_PEOPLE_F
WHERE PER_INFORMATION1 in
('1','2','3','4','5','6','7','8','9','10','11','12','13','BA','BC','BO','C','I','O','P','W')
ORDER BY 1
Using a text editor, open the domainValues_Employee_Ethnic_Group_Code_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the PER_INFORMATION1 to the ETHNIC_CODE column in the file. The data must be copied starting from the sixth line.
Map each Ethnic Code to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Employment_Full_Time_Flg_oraVersion.csv file. The employment dimension is enhanced to include the Regular and Temporary worker configuration.
Identify the Employment Categories in your Oracle source system by using the following SQL:
SELECT DISTINCT EMPLOYMENT_CATEGORY FROM PER_ALL_ASSIGNMENTS_F ORDER BY 1
From in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Employment_Full_Time_Flg_oraVersion.csv file in a text editor.
From the SQL query result, copy the Employment Category data to the domainValues_Employment_Full_Time_Flg_oraVersion.csv file in the EMPLOYMENT_CATEGORY column. Copy data starting from the sixth line.
Map an Employment Category in the Oracle E-Business Suite source system to two columns: FULL_TIME_FLAG and W_REG_TEMP<_CODE|_DESC> on the Oracle E-Business Suite on the Employment Category.
In the domainValues_Employment_Full_Time_Flg_oraVersion.csv file, map each EMPLOYMENT_CATEGORY value to one domain value. Use the columns to the right of the EMPLOYMENT_CATEGORY value to define whether the EMPLOYMENT_CATEGORY value corresponds to a full time employment category (FULL_TIME_FLAG) or not and if worker status is (REG) Regular Worker or (TEMP) Temporary Worker (W_REG_TEMP_CODE, W_REG_TEMP_DESC).
Save and close the file.
This section explains how to configure the domainValues_Employee_Sex_MF_oraVersion.csv file.
Identify the Sex Codes in your Oracle source system by using the following SQL:
SELECT DISTINCT SEX FROM PER_ALL_PEOPLE_F ORDER BY 1
Using a text editor, open the domainValues_Employee_Sex_MF_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the SEX column to the SEX column in the file. The data must be copied starting from the sixth line.
Map each Sex Code to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Employment_Status_oraVersion.csv file.
Identify the Per System Statuses in your Oracle source system by using the following SQL:
SELECT DISTINCT PER_SYSTEM_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES ORDER BY 1
Using a text editor, open the domainValues_Employment_Status_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the PER_SYSTEM_STATUS to the PER_SYSTEM_STATUS column in the file. The data must be copied starting from the sixth line.
Map each PER_SYSTEM_STATUS to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Employee_Veteran_Status_Code_oraVersion.csv file.
Identify the Veteran Status Codes in your Oracle source system by using the following SQL:
SELECT DISTINCT PER_INFORMATION5 FROM PER_ALL_PEOPLE_F WHERE PER_INFORMATION_CATEGORY = 'US' AND PER_INFORMATION5 IS NOT NULL ORDER BY 1
Using a text editor, open the domainValues_Employee_Veteran_Status_Code_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the PER_INFORMATION5 column to the VETERAN_STATUS_CODE column in the file. The data must be copied starting from the sixth line.
Map each Veteran Status Code to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Flsa_oraVersion.csv file.
Identify the FLSA Statuses in your Oracle source system by using the following SQL:
SELECT
DISTINCT CASE WHEN JOB_INFORMATION_CATEGORY IN ('US','CA')
THEN JOB_INFORMATION3 END FLSA_STAT_CODE
FROM PER_JOBS
ORDER BY 1
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Employment_Exempt_Flg_oraVersion.csv file in a text editor.
From the SQL query result, copy the FLSA_STAT_CODE data to the domainValues_Flsa_oraVersion.csv file in the FLSA_STATUS_CODE column. Copy data starting from the sixth line.
Map each FLSA_STATUS_CODE value to one domain value.
Save and close the file.
This section explains how to configure the domainValues_HRPosition_Active_Pos_Flg_oraVersion.csv file.
Identify the Position Statuses in your Oracle source system by using the following SQL:
SELECT DISTINCT STATUS FROM HR_ALL_POSITIONS_F ORDER BY 1
Using a text editor, open the domainValues_HRPosition_Active_Pos_Flg_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the STATUS to the STATUS column in the file. The data must be copied starting from the sixth line.
Map each position STATUS to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Pay_Type_Flg_oraVersion.csv file.
Identify the Costing (Debit or Credit) in your Oracle source system by using the following SQL:
SELECT DISTINCT COSTING_DEBIT_OR_CREDIT FROM PAY_ELEMENT_CLASSIFICATIONS ORDER BY 1
Using a text editor, open the domainValues_Pay_Type_Flg_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the COSTING_DEBIT_OR_CREDIT to the COSTING_DEBIT_OR_CREDIT column in the file. The data must be copied starting from the sixth line.
Map each _DEBIT_OR_CREDIT to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Pay_Type_Grp_Code_oraVersion.csv file.
Identify the Pay Elements in your Oracle source system by using the following SQL:
SELECT DISTINCT CLASSIFICATION_NAME, ELEMENT_NAME FROM PAY_ELEMENT_TYPES_F, PAY_ELEMENT_CLASSIFICATIONS WHERE PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID = PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID AND CLASSIFICATION_NAME NOT LIKE '%Information%' AND CLASSIFICATION_NAME NOT LIKE '%Employer%' AND CLASSIFICATION_NAME NOT LIKE '%Balance%' ORDER BY 1, 2
Using a text editor, open the domainValues_Pay_Type_Grp_Code_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Classification Name and Element Name to the CLASSIFICATION_NAME and ELEMENT_NAME columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Note:
Do not change lines one to four in the domainValues_Pay_Type_Grp_Code_oraVersion.csv file.Map each Element Name to one domain value. Classification Names are also extracted with Element Names to help you map the domain values. If the element is not related to Payroll Pay Check, you can map the element to 'OTHER'.
Save and close the file.
This section explains how to configure the domainValues_perf_nrml_rating_oraVersion.csv file. Using the Oracle source system's performance functionality determines the configuration options for the domainValues_perf_nrml_rating_oraVersion.csv file.
Identify the Performance Ratings in your Oracle source system by using the following SQL:
SELECT perf_rating,rating_level_id,rating_desc,
CASE WHEN max_rating > 0 THEN
ROUND(100 * rnk_rating / max_rating, 0)
END NRMLIZED_RATING,
NULL PERF_BAND_CODE
FROM
(SELECT to_char(prl.step_value) perf_rating,prl.rating_level_id
rating_level_id,
prl.name rating_desc,
prl.step_value rnk_rating,
MAX(prl.step_value) KEEP (DENSE_RANK LAST ORDER BY prl.step_value) OVER
(PARTITION BY prl.rating_scale_id) max_rating
FROM per_rating_levels prl
WHERE prl.rating_scale_id IN
(
SELECT DISTINCT
lvl.rating_scale_id
FROM
per_rating_levels lvl,
per_appraisals appr
WHERE appr.overall_performance_level_id = lvl.rating_level_id)
UNION ALL
SELECT
lookup_code perf_rating,
to_number(null) rating_level_id,
meaning rating_desc,
RANK() OVER (ORDER BY lookup_code) rnk_rating,
SUM(1) OVER () max_rating
FROM hr_lookups
WHERE lookup_type = 'PERFORMANCE_RATING'
)
ORDER BY 2, 1
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_perf_nrml_rating_oraVersion.csv file in a text editor.
From the SQL query result, copy the PERF_RATING, RATING_LEVEL_ID, and RATING_DESC data into the domainValues_perf_nrml_rating_(ver).csv file. Copy data starting from the sixth line.
In the domainValues_perf_nrml_rating_oraVersion.csv file, map each PERF_RATING, RATING_LEVEL_ID, and RATING_DESC value to one NRMLIZED_RATING or to one PERF_BAND_CODE domain value.
The NRMLIZED_RATING values are from 0 to 100 (percent).
The PERF_BAND_CODE values should correspond to the PERF_BAND_CODE column in the $PMServer\srcfiles\file_perf_band.csv file (for example, in INFA_HOME\server\infa_shared\SrcFiles).
Note:
Executing the SQL statements provided in step 1 will convert rating levels into the normalized rating values and the converted values are written to the NRMLIZED_RATING column; you can change the defaults if required.Save and close the file.
This section explains how to configure the domainValues_Recruitment_Event_Reason_oraVersion.csv file.
Note:
You must configure this csv file for the recruitment Subject Area to function properly.Identify the assignment statuses in your Oracle source system by using the following SQL:
SELECT TO_CHAR(ASSIGNMENT_STATUS_TYPE_ID) STATUS_CODE, USER_STATUS STATUS_NAME
FROM PER_ASSIGNMENT_STATUS_TYPES
WHERE PER_SYSTEM_STATUS in
('ACTIVE_APL','INTERVIEW1','INTERVIEW2','OFFER','ACCEPTED','TERM_APL')
Identify the Job requisition statuses in your Oracle Source system by using the following SQL:
SELECT LOOKUP_CODE, MEANING FROM HR_STANDARD_LOOKUPS WHERE LOOKUP_TYPE = 'VACANCY_STATUS'
Identify the Assignment Change Code and Reasons in your Oracle source system by using the following SQL:
SELECT LOOKUP_CODE, MEANING FROM HR_STANDARD_LOOKUPS WHERE LOOKUP_TYPE = 'APL_ASSIGN_REASON'
Identify the Application Termination Code and Reasons in your Oracle source system by using the following SQL:
SELECT LOOKUP_CODE, MEANING FROM HR_STANDARD_LOOKUPS WHERE LOOKUP_TYPE = 'TERM_APL_REASON'
Using a text editor, open the domainValues_Recruitment_Event_Reason_oraVersion.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the assignment status type and name from the results of using the SQL statement in step 1 to the STATUS_CODE and STATUS_NAME columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Copy the lookup code and meaning from the results of using the SQL statement in step 2 to the STATUS_CODE and STATUS_NAME columns in the file respectively. Append this data to the data copied in step 6. Use commas to separate the entries.
The Lookup Code and Meaning in step 3 and step 4 are used to populate the REASON_CODE and REASON_NAME columns in the file. Map the values of STATUS_CODE and STATUS_NAME to valid values of REASON_CODE and REASON_NAME columns in the file respectively. Use commas to separate the entries.
To see the STATUS_CODE and REASON_CODE combinations currently used in your Oracle source system for active application statuses, use the following SQL:
SELECT DISTINCT STATUS_CODE, STATUS_NAME, REASON_CODE, REASON_NAME FROM ( SELECT ASG.ASSIGNMENT_STATUS_TYPE_ID STATUS_CODE, STS.USER_STATUS STATUS_NAME, ASG.CHANGE_REASON REASON_CODE, LKP.MEANING REASON_NAME FROM PER_ALL_ASSIGNMENTS_F ASG, PER_ASSIGNMENT_STATUS_TYPES STS, HR_STANDARD_LOOKUPS LKP WHERE ASG.ASSIGNMENT_STATUS_TYPE_ID = STS.ASSIGNMENT_STATUS_TYPE_ID AND ASG.CHANGE_REASON = LKP.LOOKUP_CODE(+) AND LKP.LOOKUP_TYPE(+) = 'APL_ASSIGN_REASON' AND ASG.ASSIGNMENT_TYPE = 'A' UNION ALL SELECT ASG.ASSIGNMENT_STATUS_TYPE_ID STATUS_CODE, STS.USER_STATUS STATUS_NAME, ASG.STATUS_CHANGE_REASON REASON_CODE, LKP.MEANING REASON_NAME FROM IRC_ASSIGNMENT_STATUSES ASG, PER_ASSIGNMENT_STATUS_TYPES STS, HR_STANDARD_LOOKUPS LKP WHERE ASG.ASSIGNMENT_STATUS_TYPE_ID = STS.ASSIGNMENT_STATUS_TYPE_ID AND ASG.STATUS_CHANGE_REASON = LKP.LOOKUP_CODE(+) AND LKP.LOOKUP_TYPE(+) = 'APL_ASSIGN_REASON' )
Note:
Use a REASON_CODE, REASON_NAME of 'Any' in case you have not assigned reason code for some assignment changes. The application first searches for events with the assignment status type and reason combination. If the reason is not configured for a particular assignment status type, it uses the event corresponding to REASON_CODE 'Any.'The REASON_CODE and REASON_NAME for all the job requisition STATUS_CODE and STATUS_NAME values obtained from step 2 should be 'Unspecified.'
Map all the SOURCE_CODE, SOURCE_NAME, REASON_CODE, REASON_NAME combinations with the following domain columns:
W_EVENT_CODE - Recruitment Event Code
W_EVENT_DESC - Recruitment Event Description
W_SUB_STAGE_CODE - Recruitment Sub Stage Code
W_SUB_STAGE_DESC - Recruitment Sub Stage Description
W_STAGE_CODE - Recruitment Stage Code
W_STAGE_DESC - Recruitment Stage Description
W_REASON_CODE - Recruitment Reason Code
W_REASON_DESC - Recruitment Reason Description
W_REASON_TYPE_CODE - Recruitment Reason Type Code
W_REASON_TYPE_DESC - Recruitment Reason Type Description
W_APL_EVENT_FLG - Applicant Event Flag. 'Y' for Applicant Events, 'N' for others
W_REQ_EVENT_FLG - Job Requisition Event Flag. 'Y' for job Requisition Events, 'N' for others
Note:
EVENT_SEQ_NUM column orders the events in this file. For example, if there are 6 events called Job Requisition Approved, Application Received, Applicant Interviewed, Applicant Offered, Applicant Offer Accepted and Job Requisition Closed, the event sequence can be 10,20,30,40,50,60 in that order.For a list of published domain values, see Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
Note:
An additional CSV file called file_Recruitment_Event_Reason_Hard_Coded_oraVersion.csv is used to populate the recruitment event dimension. It creates the automatically generated recruitment events. In addition, when there is no reason specified for an event like hire, termination, first performance evaluation and first transfer, this file is used to create a default event for it.You do not need to configure anything for this file. Instead, use the file domainValues_Recruitment_Event_Reason_oraVersion.csv to specify the configurable data in the recruitment event dimension, as described in the preceding procedure.
This section explains how to configure the domainValues_Recruitment_Source_Type_oraVersion.csv file.
Identify the Recruitment Source Types in your Oracle source system by using the following SQL:
SELECT HR_STANDARD_LOOKUPS.LOOKUP_TYPE, HR_STANDARD_LOOKUPS.LOOKUP_CODE, HR_STANDARD_LOOKUPS.MEANING FROM HR_STANDARD_LOOKUPS WHERE LOOKUP_TYPE = 'REC_TYPE'
Using a text editor, open the domainValues_Recruitment_Source_Type_oraVersion.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Lookup Code and Meaning to the SOURCE_TYPE_CODE and SOURCE_TYPE_NAME columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Recruitment Source Type (LOOKUP_CODE) to one domain value for each of the two domain columns - W_SOURCE_TYPE_CODE and W_SOURCE_TYPE_DESC. The Recruitment Source Type Description (MEANING) is extracted with the Source Type to help you map the domain values.
Map each of these additional source types, which are added to W_RCRTMNT_SOURCE_D table by the extract mapping:
Type = IREC; Name = iRecruitment
Type = UNKNOWN; Name = unknown
To review the list of published values, refer to your configuration or the Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Requisition_Category_oraVersion.csv file.
Identify the Requisition Categories in your Oracle source system by using the following SQL:
SELECT HR_STANDARD_LOOKUPS.LOOKUP_TYPE, HR_STANDARD_LOOKUPS.LOOKUP_CODE, HR_STANDARD_LOOKUPS.MEANING FROM HR_STANDARD_LOOKUPS WHERE LOOKUP_TYPE = 'VACANCY_CATEGORY'
Using a text editor, open the domainValues_Requisition_Category_oraVersion.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Lookup Code and Meaning to the CATEGORY_CODE and CATEGORY_NAME columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Requisition Category (LOOKUP_CODE) to one domain value for each of the two domain columns - W_CATEGORY_CODE and W_ CATEGORY_NAME. The Requisition Category Description (MEANING) is extracted with each Category Code to help you map the domain values.
To review the list of published values, refer to your configuration or Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Status_Recruitment_oraVersion.csv file.
Identify the Recruitment Statuses in your Oracle source system by using the following SQL:
SELECT
PER_ASSIGNMENT_STATUS_TYPES.PER_SYSTEM_STATUS AS LOOKUP_CODE,
PER_ASSIGNMENT_STATUS_TYPES.USER_STATUS AS MEANING FROM
PER_ASSIGNMENT_STATUS_TYPES
WHERE
PER_SYSTEM_STATUS in('ACTIVE_APL','INTERVIEW1','INTERVIEW2','OFFER','ACCEPTED','TERM_APL')
Group By PER_ASSIGNMENT_STATUS_TYPES.PER_SYSTEM_STATUS,
PER_ASSIGNMENT_STATUS_TYPES.USER_STATUS
Using a text editor, open the domainValues_Status_Recruitment_oraVersion.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Lookup Code and Meaning to the PER_SYSTEM_STATUS and USER_STATUS columns in the file, respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Recruitment Status (LOOKUP_CODE) to one domain value for each of the two domain columns - W_STATUS_CODE and W_STATUS_NAME. The W_STATUS_CLASS domain value column must be the value RECRUITMENT_STATUS for all Recruitment Status codes you put in the file. The Recruitment Status Description - USER_STATUS (MEANING) is extracted with each Status Code to help you map the domain values.
To review the list of published values, refer to your configuration or Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Status_Vacancy_oraVersion.csv file.
Identify the Job Requisition Statuses in your Oracle source system by using the following SQL:
SELECT HR_STANDARD_LOOKUPS.LOOKUP_TYPE, HR_STANDARD_LOOKUPS.LOOKUP_CODE, HR_STANDARD_LOOKUPS.MEANING FROM HR_STANDARD_LOOKUPS WHERE LOOKUP_TYPE = 'VACANCY_STATUS'
Using a text editor, open the domainValues_Status_Recruitment_oraVersion.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Lookup Code and Meaning to the STATUS_CODE and STATUS_NAME columns in the file, respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Requisition Status (LOOKUP_CODE) to one domain value for each of the two domain columns - W_STATUS_CODE and W_STATUS_NAME. The W_STATUS_CLASS domain value column must be the value VACANCY_STATUS for all Requisition Status codes you put in the file. The Requisition Status Description (MEANING) is extracted with each Status Code to help you map the domain values.
To review the list of published values, refer to your configuration or Oracle Business Analytics Warehouse Data Model Reference.
Save and close the file.
This section explains how to configure the domainValues_Wrkfc_EventType_oraVersion.csv file. The Workforce Event dimension is available in this release. During data loading, data is loaded into the persisted staging table, W_ORA_DMN_WEVT_TYP_PS, which is used to input data to the dimension table, W_WRKFC_EVENT_TYPE_D.
The default domainValues_Wrkfc_EventType_oraVersion.csv file contains event groups, sub groups, and events, which are supported by the Workforce Event dimension. Using the EVENT_TYPE, EVENT_REASON, ORG_CHANGE_FLG, JOB_CHANGE_FLG, POS_CHANGE_FLG, GRD_CHANGE_FLG, LOC_CHANGE_FLG, and SUP_CHANGE_FLG columns, map a combination of source system attribute changes to a conformed warehouse event.
By default, events are defined in the domainValues_Wrkfc_EventType_oraVersion.csv file. These events are listed under the column heading W_EVENT_NAME:
Assignment Change
Transfer
Promotion
Assignment Start
New Hire
Rehire
Voluntary Termination
Involuntary Termination
Assignment End
FTE Change
Headcount Change
Performance Review
Person Type Change
Salary Review
To configure the domainValues_Wrkfc_EventType_oraVersion.csv file:
Identify the potential list of Workforce Event Reasons in your Oracle source system by using the following SQL:
/* Change the language condition to your implementation language */
SELECT 'ASG' EVENT_TYPE
,LOOKUP_CODE EVENT_REASON
,'ANY' ORG_CHANGE
,'ANY' JOB_CHANGE
,'ANY' POS_CHANGE
,'ANY' GRD_CHANGE
,'ANY' LOC_CHANGE
,'ANY' SUP_CHANGE
,'ASG' W_EVENT_CODE
,'ASSIGNMENT EVENT' W_EVENT_DESC
,'ASG~OTHER' W_EVENT_SUBG_CODE
,'ASSIGNMENT CHANGE' W_EVENT_SUBG_DESC
,'ASG~OTHER' W_EVENT_GRP_CODE
,'ASSIGNMENT CHANGE' W_EVENT_GRP_DESC
,'N' PROMOTION_EVENT_FLG
,'N' TRANSFER_EVENT_FLG
FROM HR_STANDARD_LOOKUPS
WHERE LOOKUP_TYPE IN ('EMP_ASSIGN_REASON', 'CWK_ASSIGN_REASON')
UNION ALL
SELECT 'TERM' EVENT_TYPE
,LOOKUP_CODE EVENT_REASON
,'N' ORG_CHANGE
,'N' JOB_CHANGE
,'N' POS_CHANGE
,'N' GRD_CHANGE
,'N' LOC_CHANGE
,'N' SUP_CHANGE
,'TERM' W_EVENT_CODE
,'TERMINATION' W_EVENT_DESC
,'TERM~VOLUNTARY' W_EVENT_SUBG_CODE
,'VOLUNTARY TERMINATION' W_EVENT_SUBG_DESC
,'TERM~VOLUNTARY' W_EVENT_GRP_CODE
,'VOLUNTARY TERMINATION' W_EVENT_GRP_DESC
,'N' PROMOTION_EVENT_FLG
,'N' TRANSFER_EVENT_FLG
FROM HR_STANDARD_LOOKUPS
WHERE LOOKUP_TYPE IN ('LEAV_REAS', 'HR_CWK_TERMINATION_REASONS')
ORDER BY 1, 2
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Wrkfc_EventType_oraVersion.csv file in a text editor.
Map each Event Type (LOOKUP_TYPE) data to one domain value for these three domain columns: W_EVENT_GRP_CODE, W_EVENT_SUBG_CODE, and W_EVENT_CODE. The Event Category (LOOKUP_TYPE) and Event Description (MEANING) data are also extracted with the Event Type data to help you map the domain values.
Note:
When you are editing the CSV files, ensure that you do not remove the following event types:ASG_START
- HIRE
- REHIRE
ASG_END
- FTE
- HDC
- PERF
- PTYP
In cases where (on the source system) there is no enforced event reason (for example, Assignment Budget Value change), HR analytics provides a mechanism to capture the event. Do not alter or remove these settings from the default domainValues_Wrkfc_EventType_oraVersion.csv file.
Do not delete the existing examples; however, modify them if required based on the following rules:
For the ASG event types, you can split out by reason and change flags, provide the event_reason value as 'Unassigned' for events that do not have a reason. However, do not modify rows with default value 'Any'.
For the TERM event types, you can split out by reason, but set the change flag value to 'N', provide the event_reason value as 'Unassigned' for events that do not have a reason. However, do not modify rows with default value 'Any'.
For the ASG_END, FTE, HDC, PERF, PTYP, and SAL event types, set the change flags value to 'N' and the Reason value to 'Not Applicable'.
Configure data options based on your requirements.
Table 7-13 Configuration Options for the domainValues_Wrkfc_EventType_oraVersion.csv file
| Configuration Option or Flags | Values | Description |
|---|---|---|
|
EVENT_REASON |
Any |
If you want to configure the *_FLG columns instead of the EVENT_REASON column as you do not consider the event reason given against the source transaction as important to the actual event; then for the EVENT_REASON column, provide value Any. For examples on this configuration option, see the domainValues_Wrkfc_EventType_oraVersion.csv file. The *_FLG configuration options are provided in this table. |
|
ORG_CHANGE_FLG |
Any Y |
A change in the ORGANIZATION_ID value between the current and previous effective date records (for the same ASSIGNMENT_ID), in the source table PER_ALL_ASSIGNMENTS_F. |
|
JOB_CHANGE_FLG |
Any Y |
A change in the JOB_ID value between the current and previous effective date records (for the same ASSIGNMENT_ID), in the source table PER_ALL_ASSIGNMENTS_F. |
|
POS_CHANGE_FLG |
Any Y |
A change in the POSITION_ID value between the current and previous effective date records (for the same ASSIGNMENT_ID), in the source table PER_ALL_ASSIGNMENTS_F. |
|
GRD_CHANGE_FLG |
Any Y |
A change in the GRADE_ID value between the current and previous effective date records, in the source table PER_ALL_ASSIGNMENTS_F. |
|
LOC_CHANGE_FLG |
Any Y |
A change in the LOCATION_ID value between the current and previous effective date records (for the same ASSIGNMENT_ID), in the source table, PER_ALL_ASSIGNMENTS_F. |
|
SUP_CHANGE_FLG |
Any Y |
A change in the SUPERVISOR_ID value between the current and previous effective date records (for the same ASSIGNMENT_ID), in the source table PER_ALL_ASSIGNMENTS_F. If you do not consider the change in assignment attribute (for example, a Job change) for a source transaction as important or influential to the actual event, then for the *_FLG column, provide the value Any. If you do consider the change in assignment attribute important, then provide the value Y. For examples on these configuration options, see the domainValues_Wrkfc_EventType_oraVersion.csv file. |
|
Unassigned |
Not Available |
In cases where (on the source system) it is optional to provide an event reason (for example, Termination Leaving Reason), HR analytics provides a mechanism to capture this reason as unassigned whilst still allowing the conformed mapping. For examples on this configuration option, see the domainValues_Wrkfc_EventType_oraVersion.csv file, see TERM Unassigned. |
|
Not Applicable |
Not Available |
In cases where (on the source system) there is no enforced event reason (for example, Assignment Budget Value change), HR analytics provides a mechanism to capture the event. Do not alter or remove these settings from the default domainValues_Wrkfc_EventType_oraVersion.csv file. |
|
Identifying a Promotion Event |
Not Available |
To identify a promotion on the source system, often only the transaction reason is not enough, to support this scenario, formally map a promotion assignment event reason, for example, PR_PROM, or use a combination of one or more of the *_FLG columns. Additionally, set the PROMOTION_EVENT_FLG column value to 'Y' to explicitly indicate that this selection is a promotion event. For examples on this configuration option, in the domainValues_Wrkfc_EventType_oraVersion.csv file, see information provided in the ASG PR_PROM column. |
|
Identifying a Transfer Event |
Identifying a Transfer Event |
To identify a transfer on the source system, often only the transaction reason is not enough, to support this scenario, formally map a transfer assignment event reason if you have one, or use a combination of one or more of the *_FLG columns. Additionally, set the TRANSFER_EVENT_FLG column value to 'Y' to explicitly indicate that this selection is a transfer event. For examples on this configuration option, in the domainValues_Wrkfc_EventType_oraVersion.csv file, see information provided in the column where ORG_CHANGE_FLG = Y. |
|
Identifying a Termination Event (Voluntary or Involuntary) |
Not Available |
To define the employees' voluntary and involuntary leaving reason mappings, you must provide rows in the domainValues_Wrkfc_EventType_oraVersion.csv file as employees' voluntary and involuntary leaving reasons is a sub-concept and this information is not captured in the Oracle E-Business Suite source system. For examples on this configuration option, in the domainValues_Wrkfc_EventType_oraVersion.csv file, see TERM. |
Save and close the file.
This section explains how to configure the domainValues_LearningEnrollment_Status_oraVersion.csv file.
Identify the Learning Enrollment Statuses in your Oracle source system by using the following SQL:
SELECT DISTINCT BOOKING_STATUS_TYPE_ID STATUS_CODE, NAME STATUS_NAME FROM OTA_BOOKING_STATUS_TYPES ORDER BY 1,2
Using a text editor, open the domainValues_LearningEnrollment_Status_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Status Code and Status Name to the STATUS_CODE and STATUS_NAME columns in the file, respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Status Code to one domain value. Status Names are also extracted with Status Codes to help you map the domain values.
Save and close the file.
This section explains how to configure the domainValues_Wrkfc_Appraisals_oraVersion.csv file.
Identify the Appraisal Templates in your Oracle source system by using the following SQL:
SELECT APPRAISAL_TEMPLATE_ID APPRAISAL_TEMPLATE_ID, NAME APPRAISAL_TEMPLATE_NAME, 'Y' EXCLUDE_FLAG FROM PER_APPRAISAL_TEMPLATES;
Using a text editor, open the domainValues_Wrkfc_Appraisals_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Change the EXCLUDE_FLAG to 'N' for appraisals that carry performance ratings you want to display (for example, annual appraisals).
Save and close the file.
This section explains how to configure the file_Normal_Hours_Conversion_Factor_oraVersion.csv file.
Identify the Frequency Codes in your Oracle source system by using the following SQL:
SELECT LOOKUP_CODE NORMAL_HOURS_FREQ, MEANING FROM HR_STANDARD_LOOKUPS A WHERE LOOKUP_TYPE = 'FREQUENCY' AND ENABLED_FLAG = 'Y';
Using a text editor, open the file_Normal_Hours_Conversion_Factor_oraVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Add/amend the annual and monthly conversion factors to convert assignment working hours from the entered units in per_all_assignments_f into annual and monthly values.
For example, an assignment working 40 hours / W (Week) might have the following conversion factors:
Annual = 52 (52 weeks per year - so hours per year = 40 * 52 = 2080)
Monthly = 52/12 = 4.3 (4.3 weeks per month)
Save and close the file.
There are three address fields in the Employee dimension table:
Permanent address
Mail address
Work address
For each of these addresses, Oracle Business Intelligence Applications uses only the primary ones. In addition, the following logic is used to determine the various types of addresses:
Permanent: Address Type = 'H' (in other words, this is also the home address).
Mail: Address Type = 'M'. If this is not available, use Permanent address (which can be the best alternate for a mailing address).
Work: Address Type = 'Default'. If this is not available, use Permanent address (which can be the best alternate for a mailing address).
You can modify this logic if required. For example, if your system tracks work address with a special address type 'W', then you need to modify the existing logic. Or, if you do not want to assume that the mail address (M) is same as the home address (H), you may want to remove the null evaluation check there.
To configure Address Type:
In Informatica PowerCenter Designer, open the SDE_ORAVersion_Adaptor directory.
In Mapplet Designer, open the mplt_SA_ORA_EmployeeDimension mapplet.
For each of the Lookup Procedures Lkp_Work_Address, Lkp_Mail_Address, Lkp_Perm_Address, do the following:
Double click the Lookup Procedure to display the Edit Transformations dialog.
Display the Properties tab.
Change the Value of the Lookup Sql Override attribute to the following:
SELECT W_EMPLOYEE_D_ADDRESSES_TMP.STREET_ADDRESS as STREET_ADDRESS, W_EMPLOYEE_D_ADDRESSES_TMP.TOWN_OR_CITY as TOWN_OR_CITY, W_EMPLOYEE_D_ADDRESSES_TMP.STATE as STATE, W_EMPLOYEE_D_ADDRESSES_TMP.REGION as REGION, W_EMPLOYEE_D_ADDRESSES_TMP.COUNTRY as COUNTRY, W_EMPLOYEE_D_ADDRESSES_TMP.POSTAL_CODE as POSTAL_CODE, W_EMPLOYEE_D_ADDRESSES_TMP.DATE_FROM as DATE_FROM, W_EMPLOYEE_D_ADDRESSES_TMP.ADDRESS_ID as ADDRESS_ID, W_EMPLOYEE_D_ADDRESSES_TMP.PERSON_ID as PERSON_ID, W_EMPLOYEE_D_ADDRESSES_TMP.DATASOURCE_NUM_ID as DATASOURCE_NUM_ID, W_EMPLOYEE_D_ADDRESSES_TMP.DATE_FROM as EFFECTIVE_START_DATE FROM W_EMPLOYEE_D_ADDRESSES_TMP WHERE PRIMARY_FLAG = 'Y' AND ADDRESS_TYPE ='W'
The outputs of these 3 address lookups are evaluated in the expression Exp_SA_Employee_Addresses. The ports EXT_MAIL_ST_ADDRESS, EXT_MAIL_CITY, EXT_MAIL_STATE_CODE, EXT_MAIL_STATE_NAME, EXT_MAIL_STATE_REGION, EXT_MAIL_COUNTRY and so on have logic to use the respective home address columns if the mailing address columns are null. When a mail address is not available, if you do not want to assume that the mail address (M) is the same as the home address (H), then you would modify the logic in the outgoing ports EXT_MAIL_ST_ADDRESS, MAIL_CITY, MAIL_STATE_CODE, MAIL_STATE_NAME, MAIL_STATE_REGION, MAIL_COUNTRY and so on as follows:
From:
IIF(ISNULL(LKP_MAIL_ADDRESS_ID),LKP_PERM_STREET_ADDRESS,LKP_MAIL_STREET_ADDRES S)
To:
MAIL_STREET_ADDRESS
Note: The above example for the EXT_MAIL_ST_ADDRESS port only. Repeat the same for EXT_MAIL_CITY, EXT_MAIL_STATE_CODE and so on using the correct port names and the correct ADDRESS_ID column.
Validate and save changes to the repository.
If you are using version control for the Informatica Repository, then check in your changes.
There are four phone related fields in the Employee dimension table, as follows:
Fax
Work phone
Pager
Mobile phone
The following logic is used to determine the various types of addresses:
Fax: Phone Type = 'WF' (Work Fax)
Work Phone: Phone Type = 'W1' (first work phone, if there are more than one)
Pager: Phone Type = 'P' (Pager)
Mobile: Phone Type = 'M' (Mobile)
You can modify this logic if required. For example, if your system tracks the primary work phone with a special phone type 'WP', instead of W1, then you need to modify the existing logic.
To configure Phone Type
In Informatica PowerCenter Designer, open the SDE_ORAVersion_Adaptor directory.
In Mapplet Designer, open the mplt_SA_ORA_EmployeeDimension mapplet.
For each of the Lookup Procedures Lkp_Work_Phone, Lkp_Pager_Phone, Lkp_Mobile_Phone, Lkp_Fax_Phone, do the following:
Double click the Lookup Procedure to display the Edit Transformations dialog.
Display the Properties tab.
Edit the Value of the Lookup Sql Override attribute.
For example, if you have a specific phone type for the primary work phone, 'WP', you would change the expression for the SQL override in lookup Lkp_Work_Phone as follows:
From:
SELECT W_EMPLOYEE_D_PHONES_TMP.PHONE_NUMBER as PHONE_NUMBER, W_EMPLOYEE_D_ PHONES_TMP.PARENT_ID as PARENT_ID, W_EMPLOYEE_D_PHONES_TMP.PHONE_TYPE as PHONE_TYPE, W_EMPLOYEE_D_PHONES_TMP.DATASOURCE_NUM_ID as DATASOURCE_NUM_ID, W_EMPLOYEE_D_PHONES_TMP.DATE_FROM as DATE_FROM FROM W_EMPLOYEE_D_PHONES_TMP WHERE PHONE_TYPE='W1'
To:
SELECT W_EMPLOYEE_D_PHONES_TMP.PHONE_NUMBER as PHONE_NUMBER, W_EMPLOYEE_D_ PHONES_TMP.PARENT_ID as PARENT_ID, W_EMPLOYEE_D_PHONES_TMP.PHONE_TYPE as PHONE_TYPE, W_EMPLOYEE_D_PHONES_TMP.DATASOURCE_NUM_ID as DATASOURCE_NUM_ID, W_EMPLOYEE_D_PHONES_TMP.DATE_FROM as DATE_FROM FROM W_EMPLOYEE_D_PHONES_TMP WHERE PHONE_TYPE='WP'
Validate and save changes to the repository. If you are using the version controlling for the Informatica Repository, then check in your changes.
This section explains how to configure the categories that are used to evaluate the highest education degree code for an employee.
The session SDE_ORA_EmployeeDimension uses the Informatica parameter $$QUALIFICATION_CATEGORY_LIST to get the list of categories for the defined Education Degrees in Oracle E-Business Suite. The mapping SDE_ORA_EmployeeDimension uses this parameter to identify and populate Highest Education Degree attribute for the Employee Dimension. Incorrect setup of the parameter could cause the issues with quality of the data for Highest Education Degree attribute in Employee Dimension.
To configure the categories that are used to evaluate the highest education degree code for an employee:
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab, and select the SDE_ORA_EmployeeDimension task.
Display the Parameters subtab.
Enter an appropriate value for the parameter $$QUALIFICATION_CATEGORY_LIST.
For example, $$QUALIFICATION_CATEGORY_LIST = 'DEGREE', 'DT'.
Save your changes.
To obtain the list of the valid values for the education degree codes, log in to the Oracle E-Business Suite Instance using SQL*Plus and execute the following SQL:
SELECT LOOKUP_CODE, MEANING FROM HR_STANDARD_LOOKUPS WHERE LOOKUP_TYPE = 'PER_CATEGORIES'
Select from the returned lookup_code column values and decide which ones are used to identify Education Degrees.
The Pay Type dimension W_PAY_TYPE_D has three Flag columns, namely COMP_FLG, TAXABLE_FLG, and PENSION_COMP_FLG, apart from others. Each of these three flags can be either 'Y' or 'N'. These flag columns indicate whether the Pay Type specified in the current record is a Taxable Earning type, a Regular Compensation Earning type, or a Pension Compensation Earning type. These Flag columns are chosen to contribute towards slowly changing dimensions of type 2, and hence should be configured correctly.
Each of these three Flag columns are evaluated based on the Incoming Classification Name using a Flat File lookup. This Flat file must be configured to see the accurate values of the Pay Type Flags in the Data Warehouse.
To configure flags for the Pay Type Dimension
Using a text editor, open the file_comp_pension_taxable_flg_oraVersion.csv file in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Add all possible Classification Names that are being used in your business.
Corresponding to the Classification Name, add the relevant values for the Pay Type Flags. Make sure to use either 'Y' or 'N'.
In Oracle HR Analytics, these flags apply to Earnings only. Therefore, you should only need to configure Classification Names related to Earnings. For other areas, the default Informatica mapping values should be acceptable.
This section explains how to configure classification names for Payroll.
The Aggregated items are loaded into the Payroll Fact table with a DETAIL_FLG = 'N', apart from the line items. The Aggregated Line items that are loaded are: TOTAL_GROSS, NET_PAY, TOTAL_DEDUCTIONS and TOTAL_TAXES.
The following parameters need to be configured to get accurate results. Each Aggregated Line item is computed by grouping by elements that belong to a certain Classification name set.
Table 7-14 Classification Name Parameters
| Parameter Name | Description |
|---|---|
|
$$GROSS_PAY_CLASSIFICATION_NAME |
Add all the Classification Names that add up to the Total Gross Pay. |
|
$$TOTAL_DEDUCTION_CLASSIFICATION_NAME |
Add all the Classification Names that correspond to any deduction. |
|
$$TOTAL_TAXES_CLASSIFICATION_NAME |
Add all the Classification Names that correspond to various Taxes. |
|
$$TOTAL_GROSS_FOR_NETPAY_EARNINGS |
Add all the Earnings Classification Names that are realized in Net pay. Note that some Classification Names are considered for Gross pay, but do not feature in the Net Pay (for example, Imputed Income). This is the major difference between this parameter and the $$GROSS_PAY_CLASSIFICATION_NAME parameter. |
|
$$TOTAL_GROSS_FOR_NETPAY_DEDUCTIONS |
This is same as the parameter $$TOTAL_DEDUCTION_CLASSIFICATION_NAME. However, in case some additional Classification Names are required to be added or removed for Net Pay, this parameter should be used for that. |
The parameters are assigned default values when Oracle BI Applications is installed out of the box, but you can modify the values by performing the steps in the following procedure.
To configure the classification name parameters:
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab, and select the task SDE_ORA_PayrollFact_Agg_Items.
Display the Parameters subtab, and specify the following parameters with an appropriate value.
Include the following parameter values in ' ':
$$CLASSIFICATION_NAMES_FOR_GROSS_PAY='PTO Accruals,Earnings,Imputed Earnings'
$$CLASSIFICATION_NAMES_FOR_TOTAL_DEDUCTION='Involuntary Deductions,Pre-Tax Deductions,Voluntary Deductions,Tax Deductions'
$$CLASSIFICATION_NAMES_FOR_TOTAL_TAXES='Tax Deductions'
$$CLASSIFICATION_NAMES_FOR_NET_PAY_EARNINGS='PTO Accruals,Earnings'
$$CLASSIFICATION_NAMES_FOR_NET_PAY_DEDUCTIONS='PTO Accruals,Earnings'
$$HINT=/*+ USE_HASH(A T) */
Save your changes.
To find out the possible Classification Names existing in your system, run the following SQL against the OLTP Database:
SELECT CLASSIFICATION_NAME FROM PAY_ELEMENT_CLASSIFICATIONS WHERE LEGISLATION_CODE LIKE 'US'
The Classification Name of Elements can be obtained using the following SQL:
Note: Enter the Element Type IDs of the elements for which you need the Classification Name within the quotes. If there are multiple elements, separate them using commas:
SELECT E.ELEMENT_TYPE_ID,E.ELEMENT_NAME,C.CLASSIFICATION_NAME FROM PAY_ELEMENT_TYPES_F E, PAY_ELEMENT_CLASSIFICATIONS C WHERE E.CLASSIFICATION_ID = C.CLASSIFICATION_ID AND E.ELEMENT_TYPE_ID IN ()
This section explains how to configure the key flexfields in Oracle EBS applications.
For more information on flexfields, see Oracle Applications Flexfields Guide Release 12.
Flexfield configurations vary from implementation to implementation, in order to accommodate all kinds of flexfield configurations done at the Oracle EBS application source systems, the attributes sourced from the flexfield columns are parameterized and configurable in Oracle Business Intelligence Applications for HR.
To configure Oracle HR Analytics to support flexfields, do the following:
Section 7.2.3.2, "How to Configure the Key Flexfields for the Job Dimension"
Section 7.2.3.3, "How to Configure the Key Flexfields for the HR Position Dimension"
Section 7.2.3.4, "How to Configure the Key Flexfields for the Pay Grade Dimension"
To create a flexfield configuration file:
In DAC, display the Execute view.
On the Execution Plans tab, run the Execution Plan Human Resources – Oracle R12 – Flexfield.
If the Execution Plan Human Resources – Oracle R12 – Flexfield runs successfully, then the hr_file_flex_kff_dff_user_config_map.csv file is generated in the $PMServer\SrcFiles directory (for example, INFA_HOME\server\infa_shared\SrcFiles).
The generated hr_file_flex_kff_dff_user_config_map.csv file contains information on the flexfield configuration data from the Oracle E-Business Suite source environment.
The hr_file_flex_kff_dff_user_config_map.csv file can be used to configure the following columns:
Job Dimension:
JOB_CODE
JOB_NAME
JOB_FAMILY
JOB_FUNCTION
JOB_LEVEL
HR Position Dimension:
POSITION_NUM
Pay Grade Dimension:
PAY_LVL_NAME
Other source-specific flexfields that you want to load into the warehouse might require customizations, depending on your requirements.
The generated file hr_file_flex_kff_dff_user_config_map.csv includes the following columns:
BI_COL_CODE
LOGICAL_CODE
SOURCE_TABLE
CONTEXT_NAME
FLEX_VALUE_SET_NAME
WHERE_CLAUSE_PAR_REVIEW
VALUE_SET_SQL
VALUE_SET_WHERE_CALUSE
SQL_LENGTH
APP_ID
The columns LOGICAL_CODE, SOURCE_TABLE, CONTEXT_NAME , FLEX_VALUE_SET_NAME, VALUE_SET_SQL, VALUE_SET_WHERE_CALUSE, and APP_ID are populated with the data from the source system. You must configure the column BI_COL_CODE based on the Flex configuration in the source system.
The WHERE_CLAUSE_PAR_REVIEW and SQL_LENGTH columns in the CSV file might require user attention in some cases, as explained in the following paragraphs.
The WHERE_CLAUSE_PAR_REVIEW column in the generated hr_file_flex_kff_dff_user_config_map.csv file has possible values of Yes/No.
Some of the columns (VALUE_SET_SQL, VALUE_SET_WHERE_CLAUSE) might have values that require user attention, because they might have variables defined in the source system in the Flex tables.
If you plan to configure flexfields to any rows in the spreadsheet that have the WHERE_CLAUSE_PAR_REVIEW column set to Yes, then you must review the VALUE_SET_SQL and VALUE_SET_WHERE_CLAUSE for those columns and make sure the variables are replaced with the appropriate values.
The SQL_LENGTH column in the generated file hr_file_flex_kff_dff_user_config_map.csv provides an estimate of the length of the SQL that will be generated for a particular Flex configuration. Currently, there is a limit of 2000 characters for the value set SQL generated for any Flex configuration. For example, if you configure 8 rows in the CSV file for JOB_CODE, and the sum of SQL_LENGTH for these 8 rows is greater than 2000 characters, then the value set SQL used to retrieve Flex Data for JOB_CODE will be truncated and will generate errors while running the mapping. Because of this limitation, you must find alternate solutions if the sum of SQL_LENGTH for any Flex configuration is greater than 2000 characters.
This section provides information on configuring the Job Code, Job Name, and Job Family Code columns.
This set up is optional as the flexfield segments may not be present in your source and if present, the flexfield segments may not map to the intended functional meaning of the warehouse target columns.
To configure the key flexfields for the Job dimension:
Open the hr_file_flex_kff_dff_user_config_map.csv file, generated using steps provided in Section 7.2.3.1, "How to Generate a Flexfield Configuration File for HCM hr_file_flex_kff_dff_user_config_map.csv" in a text editor. If you are using Microsoft Excel, then select Data, then select Filter, and then select the Auto Filter option.
Filter the SOURCE_TABLE column for the PER_JOBS and PER_JOB_DEFINITIONS data.
Identify appropriate rows and assign the following flex codes in the BI_COL_CODE column.
| Job Flex Field | BI_COL_CODE |
|---|---|
| Job Code | JOB_CODE |
| Job Name | JOB_NAME |
| Job Family | JOB_FAMILY |
| Job Function | JOB_FUNCTION |
| Job Level | JOB_LEVEL |
Save and close the file.
After making changes to the hr_file_flex_kff_dff_user_config_map.csv file, you must run a full load of your regular ETL.
This section provides information on configuring the HR Position dimension column: Position Number.
This set up is optional as the flexfield segments may not be present in your source and if present, the flexfield segments may not map to the intended functional meaning of the warehouse target columns.
To configure the key flexfields for the HR Position dimension:
Open the hr_file_flex_kff_dff_user_config_map.csv file, generated using steps provided in Section 7.2.3.1, "How to Generate a Flexfield Configuration File for HCM hr_file_flex_kff_dff_user_config_map.csv" in a text editor. If you are using Microsoft Excel, then select Data, then select Filter, and then select the Auto Filter option.
Filter the SOURCE_TABLE column for the PER_POSITION_DEFINITIONS data.
Identify appropriate rows and assign the following flex codes in the BI_COL_CODE column.
| HR Position Flex Field | BI_COL_CODE |
|---|---|
| Position Number | POSITION_NUM |
Save and close the file.
After making changes to the hr_file_flex_kff_dff_user_config_map.csv file, you must run a full load of your regular ETL.
This section provides information on configuring the Pay Grade dimension column: Pay Level Name.
To configure the key flexfields for the Pay Grade dimension.
This set up is optional as the flexfield segments may not be present in your source and if present, the flexfield segments may not map to the intended functional meaning of the warehouse target columns.
To configure the key flexfields for the Pay Grade dimension.
Open the hr_file_flex_kff_dff_user_config_map.csv file, generated using steps provided in Section 7.2.3.1, "How to Generate a Flexfield Configuration File for HCM hr_file_flex_kff_dff_user_config_map.csv" in a text editor. If you are using Microsoft Excel, then select Data, then select Filter, and then select the Auto Filter option.
Filter the SOURCE_TABLE column for the PER_GRADE_DEFINITIONS data.
Identify appropriate rows and assign the following flex codes in the BI_COL_CODE column.
| HR Pay Level Flex Field | BI_COL_CODE |
|---|---|
| Pay Level Name | PAY_LVL_NAME |
Save and close the file.
After making changes to the hr_file_flex_kff_dff_user_config_map.csv file, you must run a full load of your regular ETL.
To configure the Collect FTE, Collect Performance Ratings, and Load User Flex Data configuration tags:
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Configuration Tags tab.
In the Edit subtab, use the Include Tasks check box to include or exclude the following tags:
Human Resources - Collect FTE
Human Resources - Collect Performance Ratings
Human Resources - Generate Flex Data
Human Resources - Load User Flex Data
Note: Display the Description sub-tab to see a detailed description of a configuration tag.
Save the details.
This section contains configuration steps required before you do a full data load that apply to PeopleSoft. It contains the following topics:
Section 7.2.4.1, "Domain Values and CSV Worksheet Files for HR Analytics for PeopleSoft HCM"
Section 7.2.4.2, "How to Configure the domainValues_Employee_Ethnic_Group_Code_psft.csv"
Section 7.2.4.3, "How to Configure the domainValues_Employee_Sex_MF_psft.csv"
Section 7.2.4.4, "How to Configure the domainValues_Employee_Veteran_Status_Code_psft.csv"
Section 7.2.4.5, "How to Configure the domainValues_Employment_Category_psft.csv"
Section 7.2.4.6, "How to Configure the domainValues_Flsa_psft.csv"
Section 7.2.4.7, "How to Configure the domainValues_Employment_Full_Time_Flg_psft.csv"
Section 7.2.4.8, "How to Configure the domainValues_Employment_Status_psft.csv"
Section 7.2.4.9, "How to Configure the domainValues_Wrkfc_EventType_psft.csv"
Section 7.2.4.10, "How to Configure the domainValues_HRPosition_Active_Pos_Flg_psft.csv"
Section 7.2.4.11, "How to Configure the domainValues_Pay_Type_Grp_Code_psft.csv"
Section 7.2.4.12, "How to Configure the domainValues_Emp_EventType_EventReason_Code_psft.csv"
Section 7.2.4.13, "How to Configure the domainValues_Recruitment_Source_Type_psftVersion.csv"
Section 7.2.4.14, "How to Configure the domainValues_Requisition_Category_psftVersion.csv (WIP)"
Section 7.2.4.15, "How to Configure the domainValues_Status_Vacancy_Recruitment_psftVersion.csv"
Section 7.2.4.16, "How to Configure the domainValues_Recruitment_Event_Reason_psftVersion.csv"
Section 7.2.4.17, "How to Configure the file_pension_comp_flg.csv"
Section 7.2.4.19, "How to Configure the domainValues_perf_nrml_rating_psft.csv"
Section 7.2.4.20, "How to Configure the domainValues_Wrkfc_IntlAssign_psft.csv"
Section 7.2.4.21, "How to Configure the domainValues_AbsenceEvent_Status_psft.csv"
Section 7.2.4.22, "How to Configure the domainValues_Learning_Enrollment_Status_psftVersion.csv"
Section 7.2.4.23, "How to Build an Execution Plan for Running HR and GL Subject Areas Together"
The following table lists the CSV worksheet files and the domain values for PeopleSoft HR Analytics in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Note:
Some sessions may fail if these procedures are not compiled in the database before running the workflows.Table 7-15 Domain Values and CSV Worksheet Files for HR Analytics for PeopleSoft HCM
| Domain Value | Table | Column Description | Session |
|---|---|---|---|
|
domainValues_Employee_Ethnic_Group_Code_psft.csv |
W_EMPLOYEE_D.W_ETHNIC_GRP_CODE |
Lists the Ethnic codes and corresponding domain values of 'Ethnic Group Code' for PeopleSoft. |
SDE_PSFT_EmployeeDimension |
|
domainValues_Employee_Sex_MF_psft.csv |
W_EMPLOYEE_D.W_SEX_MF_CODE |
Lists the Sex codes and corresponding domain values of 'Sex Code' for PeopleSoft. |
SDE_PSFT_EmployeeDimension |
|
domainValues_Employee_Veteran_Status_Code_psft.csv |
W_EMPLOYEE_D.W_VETERAN_STATUS_CODE |
Lists the Veteran codes and corresponding domain values of 'Veteran Status Code'. |
SDE_PSFT_EmployeeDimension |
|
domainValues_Employment_Category_psft.csv |
W_EMPLOYMENT_D.W_EMPLOYEE_CAT_CODE |
Lists the User Person Types and corresponding domain values of 'Employment Category Code' for PeopleSoft. |
SDE_PSFT_EmploymentDimension SDE_PSFT_EmploymentDimension_Workforce |
|
domainValues_Flsa_psft.csv |
W_EMPLOYMENT_D.W_EXEMPT_FLG, W_JOB_D.W_FLSA_STAT_CODE, W_HR_POSITION_D.W_EXEMPT_FLG |
Lists the FLSA Statuses and their corresponding domain values of 'Exempt Flag' for the PeopleSoft Application. |
SDE_PSFT_EmploymentDimension_Workforce, SDE_PSFT_JobDimension, SDE_PSFT_JobDimension_Full, SDE_ PSFT_HRPositionDimension |
|
domainValues_Employment_Full_Time_Flg_psft.csv |
W_EMPLOYMENT_D.W_FULL_TIME_FLG |
Lists the Employment Categories and corresponding domain values of 'Full Time Flag'. |
SDE_PSFT_EmploymentDimension |
|
domainValues_Employment_Status_psft.csv |
W_EMPLOYMENT_D.W_EMPLOYMENT_STAT_CODE |
Lists the Per System Statuses and corresponding domain values of 'Employment Status'. |
SDE_PSFT_EmploymentDimension |
|
domainValues_HRPosition_Active_Pos_Flg_psft.csv |
W_HR_POSITION_D.W_ACTIVE_POSITION_FLG |
Lists the Position Statuses and corresponding domain values of 'Active Position Flag' |
SDE_PSFT_HRPositionDimension |
|
domainValues_Pay_Type_Grp_code_psft.csv |
W_PAY_TYPE_D.W_PAY_TYPE_GRP_CODE |
Lists the Classification Names, Element Names and corresponding domain values of 'Pay Type Group Code' |
SDE_PSFT_PayTypeDimension_Total, SDE_PSFT_PayTypeDimension_Earnings, SDE_PSFT_PayTypeDimension_FederalTaxes, SDE_PSFT_PayTypeDimension_Deductions, SDE_PSFT_PayTypeDimension_LocalTaxes1, SDE_PSFT_PayTypeDimension_LocalTaxes2, SDE_PSFT_PayTypeDimension_StateTaxes1, SDE_PSFT_PayTypeDimension_StateTaxes2 |
|
domainValues_Emp_EventType_EventReason_Code_psft.csv |
NA |
Lists the event type reason codes. |
SDE_PSFT_EventTypeDimension_Stage2 |
|
domainValues_Wrkfc_EventType_psft.csv |
W_WRKFC_EVENT_TYPE_D.W_EVENT_GRP_CODE, W_WRKFC_EVENT_TYPE_D.W_EVENT_SUBG_CODE, W_WRKFC_EVENT_TYPE_D.EVENT_CODE, W_WRKFC_EVENT_TYPE_D.PROMOTION_FLG, W_WRKFC_EVENT_TYPE_D.TRANSFER_FLG |
Lists the Event Types, Event Codes and Meanings and their corresponding domain values of 'Event Group', 'Event Sub-Group' and 'Event' for the PeopleSoft Application. |
SDE_PSFT_PersistedStage_WorkforceEventDimension_Domain |
|
domainValues_Recruitment_Source_Type_psftVersion.csv |
W_RCRTMNT_SOURCE_D.W_SOURCE_TYPE_CODE |
Lists the Recruitment sources (details) and corresponding domain values of 'Recruitment Source Type Code' for PeopleSoft. |
SDE_PSFT_RecruitmentSourceDimension |
|
domainValues_Requisition_Category_psftVersion.csv |
W_JOB_RQSTN_D.W_CATEGORY_CODE |
Lists the Job Requisition Categories and corresponding domain values of 'Job Requisition Category Code' for PeopleSoft. |
SDE_PSFT_JobRequisitionDimension, SDE_PSFT_JobRequisitionDimension_Full |
|
domainValues_Status_Vacancy_Recruitment_psftVersion.csv |
W_STATUS_D.W_STATUS_CLASS, W_STATUS_D.W_STATUS_CODE |
Lists the Job Requisition and Recruitment Statuses and corresponding domain values of 'Status Class' and 'Status Code' for PeopleSoft. |
SDE_PSFT_StatusDimension_Vacancy_Recruitment_Status, SDE_PSFT_StatusDimension_Vacancy_Recruitment_Status_Full |
|
domainValues_Recruitment_Event_Reason_psftVersion.csv |
W_RCRTMNT_EVENT_TYPE_D.W_EVENT_CODE, W_RCRTMNT_EVENT_TYPE_D.W_SUB_STAGE_CODE, W_RCRTMNT_EVENT_TYPE_D.W_STAGE_CODE, W_RCRTMNT_EVENT_TYPE_D.W_REASON_CODE, W_RCRTMNT_EVENT_TYPE_D.W_REASON_TYPE_CODE |
Lists the Job Requisition Statuses and various Recruitment Events and corresponding domain values of 'Recruitment Event Code', 'Recruitment Sub Stage Code', 'Recruitment Stage Code', 'Recruitment Event Reason Code' and 'Recruitment Event Reason Type Code' for PeopleSoft. |
SDE_PSFT_RecruitmentEventTypeDimension_Stage1, SDE_PSFT_RecruitmentEventTypeDimension_Stage1_Full, SDE_PSFT_RecruitmentEventTypeDimension_Stage2, SDE_PSFT_RecruitmentEventTypeDimension_Stage2_Full |
|
domainValues_perf_nrml_rating_psft.csv |
W_PERFORMANCE_BAND_D.PERF_BAND_CODE, PERF_BAND_NAME |
Lists the performance ratings and review types and their corresponding domain values of Performance Band Code/Name. |
SIL_PerformanceBandDimension |
|
domainValues_IntlAssign_psft.csv |
W_INTL_ASSGN_D.W_COMP_OWNER_FLG |
Lists the International Assignment Types and Home/Host Classes and their corresponding domain values of W_COMP_OWNER_FLG (the compensation owner). |
SIL_IntlAssignmentDimension |
|
domainValues_AbsenceEvent_Status_psft.csv |
W_ABSENCE_EVENT_D.APPROVAL_STATUS |
Lists the absence approval statuses ('Y'/'N') and their corresponding domain value of Approval Status. |
SDE_PSFT_AbsenceEventDimension |
|
domainValues_Learning_Enrollment_Status_psftVersion.csv |
W_STATUS_D.W_STATUS_CODE |
Maps source statuses onto a warehouse status. Measures/dates/FKs are set accordingly. |
SDE_PSFT_StatusDimension_LearningEnrollmentStatus SDE_PSFT_StatusDimension_LearningEnrollmentStatus_Full |
Identify the Ethnic Group Codes in your PeopleSoft source system by using the following SQL:
SELECT DISTINCT REG_REGION, ETHNIC_GRP_CD FROM PS_DIVERS_ETHNIC WHERE REG_REGION = 'USA' ORDER BY 1
Using a text editor, open the domainValues_Employee_Ethnic_Group_Code_psft.csv file in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the ETHNIC_GRP_CD to the ETHNIC_CODE column in the file. The data must be copied starting from the sixth line.
Map each Ethnic Code to one domain value.
Save and close the file.
Identify the Sex Codes in your PeopleSoft source system by using the following SQL:
SELECT A.FIELDVALUE, A.XLATLONGNAME FROM PSXLATITEM A WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE) AND A.FIELDNAME = 'SEX' ORDER BY 1
Using a text editor, open the domainValues_Employee_Sex_MF_psft.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the FIELDVALUE column to the SEX column in the file. The data must be copied starting from the sixth line.
Map each Sex Code to one domain value.
Save and close the file.
Identify the Veteran Status Codes in your PeopleSoft source system by using the following SQL:
SELECT A.FIELDVALUE, A.XLATLONGNAME FROM PSXLATITEM A WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE) AND A.FIELDNAME = 'MILITARY_STATUS' ORDER BY 1
Using a text editor, open the domainValues_Employee_Veteran_Status_Code_psft.csv file located in $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the FIELDVALUE column to the VETERAN_STATUS_CODE column in the file. The data must be copied starting from the sixth line.
Map each Veteran Status Code to one domain value.
Save and close the file.
A sub category is added to enhance the employment dimension and to map the EMPL_CLASS in the case of multiple SETID.
To configure the domainValues_Employment_Category_psft.csv file:
Identify the User Person Types, EMPL_CLASS and PER_ORG, in the PeopleSoft source system using the following SQL:
SELECT EMPL.EMPL_CLASS AS EMP_CAT_CODE,
EMPL.SETID AS SETID_EMPL_CLASS,
JOB.PER_ORG AS PER_ORG,
empl.descrshort,
COUNT(*) COUNTER
FROM PS_EMPL_CLASS_TBL EMPL,
PS_JOB JOB
WHERE EMPL.EMPL_CLASS = JOB.EMPL_CLASS
GROUP BY EMPL.EMPL_CLASS,
EMPL.SETID,
JOB.PER_ORG,
empl.descrshort
ORDER BY SETID_EMPL_CLASS,COUNTER DESC
Note: You must define PS_JOB.EMPL_CLASS in order to work with metrics derived from employment types. The preceding SQL does not return any data if PS_JOB_EMPL_CLASS is not defined.
On the Informatica Server host computer, from the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Employment_Category_psft.csv file in a text editor.
From the SQL query result, copy the EMP_CAT_CODE, SETID_EMPL_CLASS, and PER_ORG data to the domainValues_Employment_Category_psft.csv file in the EMP_CAT_CODE, SETID_EMPL_CLASS, and PER_ORG columns. Copy data starting from the sixth line.
In the domainValues_Employment_Category_psft.csv file, map each combination of EMP_CAT_CODE, SETID_EMPL_CLASS, and PER_ORG data (results of the SQL query) to one of the delivered combinations of W_EMP_CAT_CODE and W_EMPLOYEE_SUB_CAT_CODE domain value.
You can map more than one combination of the EMP_CAT_CODE, SETID_EMPL_CLASS, and PER_ORG values to the same combination of the W_EMP_CAT_CODE and W_EMPLOYEE_SUB_CAT_CODE domain value by adding new rows. For examples on mapping data, see the domainValues_Employment_Category_psft.csv file.
Note:
If you have the same EMP_CAT_CODE in multiple SETID_EMPL_CLASS and you are certain that they resolve to the same functional meaning, you can avoid duplication of rows into the domainValues_Employment_Category_psft.csv file by adding the word 'Any' to the SETID_EMPL_CLASS value. For example:C, Any, EMP R, STD, EMP
Save and close the file.
To configure the domainValues_Flsa_psft.csv file.
Identify the FLSA Statuses in the PeopleSoft source system using the following SQL:
SELECT A.FIELDVALUE, A.XLATLONGNAME
FROM PSXLATITEM A
WHERE A.EFFDT = (SELECT MAX (C.EFFDT)
FROM PSXLATITEM C
WHERE C.FIELDNAME = A.FIELDNAME
AND C.FIELDVALUE = A.FIELDVALUE
)
AND A.FIELDNAME = 'FLSA_STATUS'
ORDER BY 1
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Flsa_psft.csv file in a text editor.
From the SQL query result, copy the FIELDVALUE data to the domainValues_Flsa_psft.csv file in the FLSA_STATUS_CODE column. Copy data starting from the sixth line.
Map each FLSA_STATUS_CODE value to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Employment_Full_Time_Flg_psft.csv file. The employment dimension is enhanced to include a Regular or Temporary worker configuration.
Identify the Employment Categories in your PeopleSoft source system by using the following SQL:
SELECT CASE WHEN A.FIELDVALUE IN ('F','P') THEN A.FIELDVALUE ELSE NULL END AS FULL_PART_TIME,
A.XLATLONGNAME FULL_PART_DESC,
CASE WHEN A.FIELDVALUE = 'F' THEN 'R'
WHEN A.FIELDVALUE = 'P' THEN 'T'
ELSE NULL END AS REG_TEMP,
CASE WHEN A.FIELDVALUE = 'F' THEN (SELECT B.XLATLONGNAME
FROM PSXLATITEM B
WHERE B.FIELDNAME = 'REG_TEMP'
AND B.FIELDVALUE = 'R'
AND B.EFF_STATUS = 'A' )
WHEN A.FIELDVALUE = 'P' THEN (SELECT C.XLATLONGNAME
FROM PSXLATITEM C
WHERE C.FIELDNAME = 'REG_TEMP'
AND C.FIELDVALUE = 'T'
AND C.EFF_STATUS = 'A' )
ELSE NULL END AS REG_TEMP_DESC,
CASE WHEN A.FIELDVALUE = 'F' THEN 'Y'
WHEN A.FIELDVALUE = 'P' THEN 'N'
ELSE NULL END AS FULL_TIME_FLAG
FROM PSXLATITEM A
WHERE A.FIELDNAME = 'FULL_PART_TIME'
AND A.EFF_STATUS = 'A'
Using a text editor, open the domainValues_Employment_Full_Time_Flg_psft.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
From the SQL query result, copy the FIELDVALUE data to the domainValues_Employment_Full_Time_Flg_psft.csv file in the FULL_PART_TIME column. Copy data starting from the sixth line.
The Informatica lookup against the domainValues_Employment_Full_Time_Flg_psft.csv is based on the FULL_PART_TIME and REG_TEMP values, they are used to determine if the record is for a Full Time or Part-Time employment and if they are a Regular or Temporary employment record.
In the domainValues_Employment_Full_Time_Flg_psft.csv file, map each FULL_PART_TIME value to one domain value. Use the columns to the right of the FULL_PART_TIME to define whether the REG_TEMP value corresponds to a Regular or Temporary worker (REG_TEMP), and the worker is a Regular or Temporary worker (W_REG_TEMP_CODE, W_REG_TEMP_DESC).
Save and close the file.
Identify the Per System Statuses in your PeopleSoft source system by using the following SQL:
SELECT A.FIELDVALUE, A.XLATLONGNAME FROM PSXLATITEM A WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE) AND A.FIELDNAME = 'EMPL_STATUS' ORDER BY 1
Using a text editor, open the domainValues_Employment_Full_Time_Flg_psft.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the FIELDVALUE to the EMPL_STATUS column in the file. The data must be copied starting from the sixth line.
Map each EMPL_STATUS to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Wrkfc_EventType_psft.csv file. The Workforce Event dimension is available. During data loading, data is loaded into a new persisted staging table, W_PSFT_DMN_WEVT_TYP_PS, which is used to input data to the dimension table, W_WRKFC_EVENT_TYPE_D.
The default domainValues_Wrkfc_EventType_psft.csv file contains event groups, sub groups, and events, which are supported by the workforce event dimension. Using the columns EVENT_TYPE, EVENT_REASON, ORG_CHANGE_FLG, JOB_CHANGE_FLG, POS_CHANGE_FLG, GRD_CHANGE_FLG, LOC_CHANGE_FLG, and SUP_CHANGE_FLG, map a combination of source system attribute changes to a conformed warehouse event.
By default, the following events are defined in the domainValues_Wrkfc_EventType_psft.csv file under the column heading, EVENT_NAME:
International Transfer End
Additional Assignment End
Layoff
Assignment Change
Promotion
Renew Contract
Assignment Start
International Transfer
Transfer
New Hire
Rehire
Involuntary Termination
Retirement
Resignation
To configure the domainValues_Wrkfc_EventType_psft.csv file:
Identify the potential list of Workforce Event Reasons in the PeopleSoft source system using the following SQL:
SELECT ACTION, ACTION_REASON, DESCR FROM PS_ACTN_REASON_TBL ORDER BY 1
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_Wrkfc_EventType_psft.csv file in a text editor.
Map the PeopleSoft Action, Reason from the SQL to a single row combination of:
EVENT_TYPE
EVENT_REASON
W_EVENT_GRP_CODE
W_EVENT_GRP_NAME
W_EVENT_SUBG_CODE
W_EVENT_SUBG_NAME
EVENT_CODE
EVENT_NAME
Note that the value in EVENT_CODE should be unique, in combination with the EVENT_TYPE and EVENT_REASON columns. The target table is W_WRKFC_EVENT_TYPE_D, unlike in previous releases, which used W_CODE_D.
When setting W_EVENT_GRP_CODE, W_EVENT_SUBG_CODE, and EVENT_CODE values, it is recommended that you prepend SUBG_CODE values with the GRP_CODE value followed by a tilda (~). This naming convention is not strictly required, but as with all W_ columns, the values in the .csv might have dependencies in the RPD.
Configure the domainValues_Wrkfc_EventType_psft.csv file based on your requirements.
Table 7-16 Configuration Options for the domainValues_Wrkfc_EventType_psft.csv file
| Configuration Options or Flags | Values | Description |
|---|---|---|
|
EVENT_REASON |
Any |
If you want to configure the *_FLG columns instead of the EVENT_REASON column as you do not consider the event reason given against the source transaction as important to the actual event, then for the EVENT_REASON column, provide the value "Any." The *_FLG configuration options are provided in this table. |
|
ORG_CHANGE_FLG |
Any Y |
A change in the DEPTID value between the current and previous records, in the source table PS_JOB. |
|
JOB_CHANGE_FLG |
Any Y |
A change in the JOBCODE value between the current and previous records, in the source table PS_JOB. |
|
POS_CHANGE_FLG |
Any Y |
A change in the POSITION_NBR value between the current and previous records, in the source table PS_JOB. |
|
GRD_CHANGE_FLG |
Any Y |
A change in the GRADE, STEP, or SAL_ADMIN_PLAN value between the current and previous records, in the source table PS_JOB. |
|
LOC_CHANGE_FLG |
Any Y |
A change in the LOCATION value between the current and previous records, in the source table PS_JOB. |
|
SUP_CHANGE_FLG |
Any Y |
A change in the SUPERVISOR_ID or REPORTS_TO value between the current and previous date records, in the source table PS_JOB. If you do not consider the change in job attribute (for example, a Job change) for a source transaction as important or influential to the actual event, then for the *_FLG columns, provide value Any. If you do consider the change in assignment attribute important, then provide the value Y. |
|
Unassigned |
- |
In cases where (on the source system) it is optional to provide an action or action reason, HR analytics provide a mechanism to capture this reason as unassigned whilst still allowing the conformed mapping. |
|
Not Applicable |
- |
In cases where (on the source system) there is no enforced action or action reason, HR analytics provides a mechanism to capture the event. Do not alter or remove these settings from the default domainValues_Wrkfc_EventType_psft.csv file. |
|
Mandatory Events |
- |
To correctly identify hires and terminations:
For the hire and termination events, set all the change flag values to "N". To verify actions that set the hire or termination dates, check the ps_action_stat_tbl values. |
|
PROMOTION_EVENT_FLG |
Y or N |
To identify a promotion on the PeopleSoft source system, sometimes only the action or action reason data is not enough. To support this search, formally map a promotion action if you have one (for example, PRO), or use a combination of one or more *_FLG columns. Additionally, set the PROMOTION_EVENT_FLG column value to 'Y' to explicitly indicate that this selection is a promotion event. For examples on this configuration option, see the domainValues_Wrkfc_EventType_psft.csv file. |
|
TRANSFER_EVENT_FLG |
Y or N |
To identify a transfer on the PeopleSoft source system, sometimes only the Action or Action Reason data is not enough. To support this search, formally map a transfer action or action reason event reason if you have one, or use a combination of one or more * _FLG columns. Additionally, set the TRANSFER_EVENT_FLG column value to 'Y' to explicitly indicate that this selection is a transfer event. For examples on this configuration option, see the domainValues_Wrkfc_EventType_psft.csv file. |
|
Identifying a Termination Event (Voluntary or Involuntary) |
- |
To define employees' voluntary and involuntary leaving reasons, you must provide rows in the domainValues_Wrkfc_EventType_psft.csv file as employees' voluntary and involuntary leaving reasons is a sub-concept and this information is not captured in the source system. For examples on this configuration option, see W_EVENT_GRP_CODE = TERM in the domainValues_Wrkfc_EventType_psft.csv file. |
|
EXCLUDE_FLG |
- |
Exclude Flag can be used to prevent certain PeopleSoft Actions from being picked up as events in the Workforce Event Fact. Excluding events that are not important for analysis helps limit the size of the Workforce Event Fact. |
Save and close the file.
Identify the Position Statuses in your PeopleSoft source system by using the following SQL:
SELECT A.FIELDVALUE, A.XLATLONGNAME FROM PSXLATITEM A WHERE A.EFFDT = (SELECT MAX (C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE) AND A.FIELDNAME = 'EFF_STATUS' ORDER BY 1
Using a text editor, open the domainValues_HRPosition_Active_Pos_Flg_psft.csv file in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the FIELDVALUE to the EFF_STATUS column in the file. The data must be copied starting from the sixth line.
Map each position EFF_STATUS to one domain value.
Save and close the file.
Table 7-17 shows pay type sources and their associated group codes.
Table 7-17 Pay Elements for ETL
| PAYTYPE_SOURCE | W_PAY_TYPE_GRP_CODE |
|---|---|
|
DEDUCTIONS |
DEDUCTIONS |
|
FEDERAL_TAXES |
TAXES |
|
LOCAL_TAXES |
TAXES |
|
STATE_TAXES |
TAXES |
|
EARNINGS |
REGULAR |
|
TOTAL |
TOTAL |
To configure the domainValues_Pay_Type_Grp_Code_psft.csv:
Using a text editor, open the domainValues_Pay_Type_Grp_Code_psft.csv file in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Classification Name and Element Name to the PAYTYPE_SOURCE. The data must be edited starting from the sixth line. Use commas to separate the entries.
Map each Element Name to one domain value. Classification Names are also extracted with Element Names to help you map the domain values. If the element is not related to Payroll Pay Check, you can map the element to 'OTHER'.
Save and close the file.
Identify the Costing (Debit or Credit) in your PeopleSoft source system by using the following SQL:
SELECT ACTION + ':'+ ACTION_REASON EVENT_REASON, DESCR FROM PS_ACTN_REASON_TBL ORDER BY 1
Oracle Syntax:
SELECT ACTION ||':'|| ACTION_REASON EVENT_REASON, DESCR FROM PS_ACTN_REASON_TBL ORDER BY 1
Using a text editor, open the domainValues_Emp_EventType_EventReason_Code_psft.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy EVENT_REASON to the EVENT_REASON column in the file. The data must be copied starting from the sixth line.
Map each EVENT_REASON to one domain value.
Save and close the file.
This section explains how to configure the domainValues_Recruitment_Source_Type_psftVersion.csv file.
Identify the Recruitment Source Type in your PeopleSoft source system by using the following SQL:
SELECT DISTINCT HRS_SOURCE_ID SOURCE_TYPE_CODE, HRS_SOURCE_NAME SOURCE_TYPE_NAME FROM PS_HRS_SOURCE ORDER BY HRS_SOURCE_ID
Using a text editor, open the domainValues_Recruitment_Source_Type_psft Version.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Source Type Code and Source Type Name to the SOURCE_TYPE_CODE and SOURCE_TYPE_NAME columns in the file, respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Source Type Code (SOURCE_TYPE_CODE) to one domain value for each of the domain columns--W_SOURCE_TYPE_CODE and W_SOURCE_TYPE_DESC. Source Type Name (SOURCE_TYPE_NAME) is extracted to help you map the domain values.
Save and close the file.
This section explains how to configure the domainValues_Requisition_Category_psftVersion.csv file.
Identify the Requisition Category in your PeopleSoft source system by using the following SQL:
SELECT A.FIELDVALUE as CATEGORY_CODE, A.XLATLONGNAME AS CATEGORY_NAME
FROM PSXLATITEM A
WHERE A.EFFDT = (SELECT MAX(C.EFFDT) FROM PSXLATITEM C
WHERE C.FIELDNAME = A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE )
AND A.FIELDNAME IN ('HRS_JO_TYPE')
Using a text editor, open the domainValues_Requisition_Category_psft Version.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the category code and category name to the CATEGORY_CODE, CATEGORY_NAME columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each category code (CATEGORY_CODE) to one domain value for each of the domain columns--W_CATEGORY_CODE and W_CATEGORY_NAME. Category name (CATEGORY_NAME) is extracted to help you map the domain values.
Save and close the file.
This section explains how to configure the domainValues_Status_Vacancy_Recruitment_psftVersion.csv file.
Identify the Vacancy/Requisition and Recruitment Status in your PeopleSoft source system by using the following SQL:
SELECT STATUS_CODE, DESCRSHORT AS STATUS_NAME,
CASE WHEN STATUS_AREA = 1 THEN 'VACANCY_STATUS' ELSE 'RECRUITMENT_STATUS' END AS W_STATUS_CLASS
FROM PS_HRS_STS_TBL
WHERE PS_HRS_STS_TBL.STATUS_AREA IN ('1', '3')
Using a text editor, open the domainValues_Status_Vacancy_Recruitment_psftVersion.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Status Code, Status Name, and Status Class to the STATUS_CODE, STATUS_NAME, and W_STATUS_CLASS columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Status Code (STATUS_CODE) to one domain value for each of the domain columns--W_STATUS_CLASS, W_STATUS_CODE, and W_STATUS_NAME. Status Name (STATUS_NAME) is extracted to help you map the domain values.
Save and close the file.
This section explains how to configure the domainValues_Recruitment_Event_Reason_psftVersion.csv file.
Identify the Recruitment Event Reasons in your PeopleSoft source system by using the following SQL:
/* Select all status codes. All of these rows do not have a reason code. */ SELECT STATUS_AREA, STATUS_AREA || '~' || STATUS_CODE STATUS_CODE, DESCR STATUS_DESC, NULL REASON_CODE, NULL REASON_DESC FROM PS_HRS_STS_TBL WHERE (STATUS_AREA = '1' OR STATUS_AREA = '3') UNION ALL /* Select all status reasons. */ SELECT A.STATUS_AREA, A.STATUS_AREA || '~' || A.STATUS_CODE STATUS_CODE, A.DESCR STATUS_DESC, A.STATUS_AREA || '~' || A.STATUS_CODE || '~' || B.STATUS_REASON REASON_CODE, B.DESCR REASON_DESC FROM PS_HRS_STS_TBL A, PS_HRS_STSRSN_TBL B WHERE B.STATUS_AREA = A.STATUS_AREA AND B.STATUS_CODE = A.STATUS_CODE AND (A.STATUS_AREA = '1' OR A.STATUS_AREA = '3')
Using a text editor, open the domainValues_Recruitment_Event_Reason_psftVersion.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Status Area, Status Code, Status Description, Reason Code, and Reason Description to the STATUS_AREA, STATUS_CODE, STATUS_DESC, REASON_CODE, REASON_DESC columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Status Code (STATUS_CODE) and Reason Code (REASON_CODE) to one domain value for each of these domain columns:
W_EVENT_CODE
W_EVENT_DESC
W_SUB_STAGE_CODE
W_SUB_STAGE_DESC
W_STAGE_CODE
W_STAGE_DESC
W_REASON_CODE
W_REASON_DESC
W_REASON_TYPE_CODE
W_REASON_TYPE_DESC
EVENT_SEQ_NUM
Status Description (STATUS_DESC) and Reason Description (REASON_DESC) are extracted to help you map the domain values.
Save and close the file.
Identify the Earnings Codes in your PeopleSoft source system by using the following SQL:
SELECT DISTINCT ERNCD, DESCR FROM PS_EARNINGS_TBL ORDER BY 1
Using a text editor, open the file file_pension_comp_flg_psft.csv, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy over the ERNCD and DESCR column into the file. The data must be copied starting from the sixth line.
Relate a Pension Compensation Flag and a Compensation Flag based on the nature of the Earnings Code.
Save and close the file.
The SQL can be further tuned if all the Earnings Code of your Organization is of a particular type. For example, if all the Earnings Code that you are using starts with 'B' then the SQL can be modified as:
SELECT DISTINCT ERNCD, DESCR FROM PS_EARNINGS_TBL WHERE ERNCD LIKE 'B%' ORDER BY 1
The file 'file_pension_comp_flg_psft.csv' is used to populate the Compensation Flag and the Pension Compensation Flag. The value of the Compensation Flag and the Pension Compensation Flag is chosen based on the Input Earnings Code.
Compensation Flag (COMP_FLG)
Pension Compensation Flag (PENSION_COMP_FLG)
To modify Compensation Flag and Pension Compensation Flag:
Edit the file 'file_pension_comp_flg_psft.csv' in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles) using in a text editor.
Enter the COMP_FLG and PENSION_COMP_FLG value against the correct Earnings Code.
Save the file.
This section explains how to configure the domainValues_perf_nrml_rating_psft.csv file. Using the PeopleSoft source system's performance functionality determines the configuration options for the domainValues_perf_nrml_rating_psft.csv file.
To configure the domainValues_perf_nrml_rating_psft.csv file:
Identify the Performance Ratings and Review Types in the PeopleSoft source system using the following SQL:
SELECT
mdl.RATING_MODEL,
mdl.DESCR RATING_MODEL_DESC,
rtng.REVIEW_RATING,
rtng.DESCR REVIEW_RATING_DESC,
CASE WHEN pct.max_ep_rating > 0
THEN round(100 * rtng.ep_rating / pct.max_ep_rating, 0)
WHEN pct.max_review_points > 0
THEN round(100 *rtng.review_points / pct.max_review_points, 0)
WHEN pct.max_band_points > 0
THEN round(100 * ((rtng.from_points + rtng.to_points) / 2) / pct.max_band_points, 0)
WHEN pct.max_eligibility_points > 0
THEN round(100 * rtng.eligibility_points / pct.max_eligibility_points, 0)
WHEN pct.max_rnk > 0
THEN round(100 * SUM(1) OVER
(PARTITION BY mdl.rating_model
ORDER BY rtng.review_rating RANGE UNBOUNDED PRECEDING) /
pct.max_rnk)
END deflt_rating_pct
,null perf_band_code
FROM
PS_RATING_MDL_TBL mdl
,PS_REVW_RATING_TBL rtng
,(SELECT
rating_model
,effdt
,max(ep_rating) max_ep_rating
,max(review_points) max_review_points
,max(eligibility_points) max_eligibility_points
,max((from_points + to_points) / 2) max_band_points
,count(*) max_rnk
FROM ps_revw_rating_tbl
GROUP BY rating_model,effdt
) pct
WHERE mdl.RATING_MODEL = rtng.RATING_MODEL
AND rtng.EFFDT = mdl.EFFDT
AND mdl.rating_model = pct.rating_model
AND mdl.effdt = pct.effdt
AND mdl.EFFDT =
(SELECT MAX(tmp.EFFDT)
FROM PS_RATING_MDL_TBL tmp
WHERE mdl.RATING_MODEL=tmp.RATING_MODEL)
ORDER BY 1,2,3
From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_perf_nrml_rating_psft.csv file in a text editor.
From the SQL query result, copy the RATING_MODEL, RATING_MODEL_DESC, REVIEW_RATING, and REVIEW_RATING_DESC data into the domainValues_perf_nrml_rating_psft.csv file. Copy data starting from the sixth line.
Map each RATING_MODEL, RATING_MODEL_DESC, REVIEW_RATING and REVIEW_RATING_DESC data to one NORMALIZED_RATING or to one PERF_BAND_CODE domain value.
The NORMALIZED_RATING values are from 0 to 100 (percent).
The PERF_BAND_CODE values should correspond to the PERF_BAND_CODE column in the $PMServer\srcfiles\file_perf_band.csv file (for example, in INFA_HOME\server\infa_shared\SrcFiles).
Save and close the file.
Note:
You must perform this additional DAC parameter configuration.To perform DAC task parameter configurations:
In DAC, query back the task name SDE_PSFT_PersistedStage_WorkforceEvent_Performance.
Set the $$APP_REVIEW_EXCL_STATUS parameter to exclude Review statuses (that is, any transaction that is not completed), the default value, for example, 'CA','TA', which is handled as an IN statement in the mapping SQL.
Set the $$APP_REVIEW_TYPE parameter to the Review type to include (only one type, for example, Annual Review is supported) the default value, for example, 'K0ANNUAL'.
Note:
To prevent Informatica mapping failure, include the DAC variable values in single-quotes.To configure the domainValues_Wrkfc_IntlAssign_psft.csv file.
Identify the International Assignment Types and Home/Host Classes in your PeopleSoft source system using the following SQL (using these SQL statements depends on the configuration that you require, see in this section the note under step 4 before running these SQL statements):
SELECT NULL PAYGROUP, ASSGN_TYPE INTL_ASSGN_TYPE_CODE, FIELDVALUE HOME_HOST_CLASS_CODE, CASE WHEN FIELDVALUE = 'H' THEN 'N' ELSE 'Y' END W_COMP_OWNER_FLG FROM PS_ASSGN_TYPE_TBL ASG, PSXLATITEM FLG WHERE FLG.FIELDNAME LIKE 'HOME_HOST_CLASS' ORDER BY 1,2,3; SELECT PAYGROUP PAYGROUP, NULL INTL_ASSGN_TYPE_CODE, FIELDVALUE HOME_HOST_CLASS_CODE, CASE WHEN FIELDVALUE = 'H' THEN 'N' ELSE 'Y' END W_COMP_OWNER_FLG FROM PS_PAYGROUP_TBL PYG, PSXLATITEM FLG WHERE FLG.FIELDNAME LIKE 'HOME_HOST_CLASS' ORDER BY 1,2,3;
Open the domainValues_Wrkfc_IntlAssign_psft.csv file using a text editor in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
From the SQL query result, copy the INTL_ASSGN_TYPE_CODE and HOME_HOST_CLASS_CODE data into the domainValues_Wrkfc_IntlAssign_psft.csv file. Copy data starting from the sixth line.
Map each INTL_ASSGN_TYPE_CODE and HOME_HOST_CLASS value to one W_COMP_OWNER_FLG domain value (Y/N).
Note:
Optionally, if you use a dummy Payroll in order to identify Employees on International Assignment, then define the PAYGROUP value to one W_COMP_OWNER_FLG domain value (Y/N).To configure the domainValues_AbsenceEvent_Status_psft.csv file.
Approval Status in the PeopleSoft source system is 'Y/N', with the edited field's default value set to 'N'. You can also identify Approval Status values using the following SQL:
SELECT DISTINCT EMPLOYER_APPROVED FROM PS_ABSENCE_HIST
You will also find the (Y/N) values.
Note:
For later use, a null value is added for absence status to include Requested or in progress.From the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles), open the domainValues_AbsenceEvent_Status_psft.csv file in Microsoft Excel; in the file, verify if the information provided in the table is available, starting from the fifth line.
| APPROVAL_STATUS | ABSENCE_STATUS_CODE | ABSENCE_STATUS_NAME |
|---|---|---|
| Y | APPROVED | Approved |
| N | UNAPPROVED | Not Approved |
| REQUESTED | Requested or In Progress |
This section explains how to configure the domainValues_Learning_Enrollment_Status_psftVersion.csv.
Identify the Learning Enrollment Statuses in your PeopleSoft source system by using the following SQL:
SELECT
A.FIELDVALUE STATUS_CODE,
A.XLATLONGNAME STATUS_NAME
FROM
PSXLATITEM A LEFT OUTER JOIN PSXLATITEMLANG B ON
A.FIELDNAME=B.FIELDNAME AND A.FIELDVALUE=B.FIELDVALUE AND B.LANGUAGE_CD='ENG'
WHERE
A.FIELDNAME IN ('LM_STTS') AND
A.EFFDT = (SELECT MAX(C.EFFDT) FROM PSXLATITEM C WHERE C.FIELDNAME =
A.FIELDNAME AND C.FIELDVALUE = A.FIELDVALUE )
Using a text editor, open the domainValues_Learning_Enrollment_Status_psftVersion.csv file, located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Status Code and Status Name to the STATUS_CODE and STATUS_NAME columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each Status Code to one domain value. Status Names are also extracted with Status Codes to help you map the domain values.
Save and close the file.
Human Resources Analytics requires that some financial fact tables from Oracle BI Applications Financial Analytics be populated in order to display default dashboards for "HR - Performance." Although DAC execution plans are shipped by default for Oracle EBS and Universal adaptors, PeopleSoft HR Analytics customers need to build an execution plan to deploy the financial metrics in HR Analytics, because PeopleSoft FSCM transaction tables do not exist in Human Resources Analytics.
The following procedure shows an example of how to build a composite execution plan to deploy financial-related metrics in HR Analytics using PeopleSoft 9.0 as a source. Be sure to replace "9.0" with "8.9" or "9.1," as appropriate for your PeopleSoft OLTP version.
To build an execution plan to deploy financial-related metrics in HR Analytics:
In DAC, select File, and then select New Source System Container. In the New Source System Container dialog, create a custom container called "PeopleSoft 9.0 HCM" from the existing container "PeopleSoft 9.0," and then click OK.
Figure 7-1 shows the New Source System Container dialog for the PeopleSoft 9.0 HCM container.
Figure 7-1 New Source System Container Dialog for PeopleSoft 9.0 HCM Container
Similar to step 1, create another new custom container called "PeopleSoft 9.0 FSCM" from the existing container "PeopleSoft 9.0," and then click OK.
In Design view, select the new container "PeopleSoft 9.0 FSCM" and then select the Tasks tab. Query for the following three tasks and deactivate them:
SDE_PSFT_GEOCountryDimension
SIL_DayDimension_GenerateSeed
SIL_DayDimension_CleanSeed
Figure 7-2 shows these three tasks.
Create a new Execution plan called "Human Resources with GL - PeopleSoft 9.0." Choose subject areas as follows:
Financials - General Ledger
HR - Absence Event
HR - Learning Management
HR - Payroll
HR - Recruitment
HR - Workforce
Figure 7-3 shows the Subject Areas subtab in DAC for the "Human Resources with GL - PeopleSoft 9.0" execution plan.
Figure 7-3 Subject Areas for the "Human Resources with GL - PeopleSoft 9.0" Execution Plan
Note the following:
The "Financials - General Ledger" subject area is the only subject area from the container "PeopleSoft 9.0 FSCM." All the other subject areas are from the container "PeopleSoft 9.0 HCM."
If "HR - Learning Management" is not a subject area of interest, do not select it. Because e-Learning comes in a different physical schema for PeopleSoft OLTP, if you do not have e-Learning implemented at the OLTP level, you should not select the Learning subject area. If you selected Learning, it is expected that you have e-Learning implemented, and DAC will need the physical connection for your e-Learning schema (commonly known as ELM).
Select the Parameters subtab in the bottom pane. Then, generate one copy of the parameters for each container. Set the values of the parameters as shown in Table 7-8.
Table 7-18 Parameter Values for Human Resources with GL - PeopleSoft 9.0 Execution Plan
| Type | Name | Value | Delay | Prune Days | Source System |
|---|---|---|---|---|---|
|
DATASOURCE |
DBConnection_OLAP |
DataWarehouse |
0 |
30 |
PeopleSoft 9.0 FSCM |
|
DATASOURCE |
DBConnection_OLAP |
DataWarehouse |
0 |
30 |
PeopleSoft 9.0 HCM |
|
DATASOURCE |
DBConnection_OLTP |
PSFT_9_0_HCM |
0 |
30 |
PeopleSoft 9.0 HCM |
|
DATASOURCE |
DBConnection_OLTP |
PSFT_9_0_FINSCM |
0 |
30 |
PeopleSoft 9.0 FSCM |
|
DATASOURCE |
DBConnnection_OLTP_ELM |
PSFT_9_0_ELM |
0 |
30 |
PeopleSoft 9.0 HCM |
|
DATASOURCE |
FlatFileConnection |
PSFT_9_0_HCM_FlatFile |
0 |
30 |
PeopleSoft 9.0 HCM |
|
DATASOURCE |
FlatFileConnection |
PSFT_9_0_FlatFile |
0 |
30 |
PeopleSoft 9.0 FSCM |
|
FOLDER |
PLP |
PLP |
0 |
- |
PeopleSoft 9.0 FSCM |
|
FOLDER |
PLP |
PLP |
0 |
- |
PeopleSoft 9.0 HCM |
|
FOLDER |
SDE_PSFT_90_Adaptor |
SDE_PSFT_90_Adaptor |
0 |
- |
PeopleSoft 9.0 FSCM |
|
FOLDER |
SDE_PSFT_90_Adaptor |
SDE_PSFT_90_Adaptor |
0 |
- |
PeopleSoft 9.0 HCM |
|
FOLDER |
SILOS |
SILOS |
0 |
- |
PeopleSoft 9.0 FSCM |
|
FOLDER |
SILOS |
SILOS |
0 |
- |
PeopleSoft 9.0 HCM |
Note the following:
Be sure to choose the physical data source PSFT_9_0_FINSCM for the logical data source DBConnection_OLTP when the container is "PeopleSoft 9.0 FSCM," and choose PSFT_9_0_HCM for the same logical data source when the container is "PeopleSoft 9.0 HCM."
The value of "Prune Days" is set by default and can be changed if needed.
The physical Informatica folders are set to the appropriate corresponding logical folder, for both containers.
You should only provide the physical data source PSDT_9_0_ELM against the logical data source DBConnection_OLTP_ELM if you selected the Learning subject area in the previous step.
Figure 7-4 shows the Parameters subtab in DAC.
Figure 7-4 Parameters Subtab for the "Human Resources with GL - PeopleSoft 9.0" Execution Plan
Build this execution plan.
Select Set up, then select the Physical Data Sources tab. Configure the connections for the following physical data sources:
DataWarehouse
PSFT_9_0_HCM
PSFT_9_0_ELM (if you use e-Learning)
PSFT_9_0_FINSCM
Execute the "Human Resources with GL - PeopleSoft 9.0" execution plan.
See technical note 1269240.1 on My Oracle Support for detailed information about using Oracle HR Analytics with the Universal adapter.
This section contains additional configuration steps for Oracle HR Analytics.
Section 7.3.1, "Configuration Steps for Oracle HR Analytics for All Source Systems"
Section 7.3.2, "Configuration Steps for Oracle HR Analytics for Oracle EBS"
Section 7.3.3, "Configuration Steps for Oracle HR Analytics for PeopleSoft"
This section contains configuration steps that apply to all source systems. It contains the following topics:
Section 7.3.1.1, "How to Aggregate the Payroll Table for Oracle HR Analytics"
Section 7.3.1.2, "Resolving Dimensions for Every Snapshot Date"
Section 7.3.1.4, "About Future-Dated Security for Oracle HR Analytics"
You can aggregate the Payroll table to a different time levels, and aggregate levels of Employees, Jobs, and Payment Types dimensions. There are two time grain parameters to configure for this aggregate table and these parameters need to have the same value.
The GRAIN parameter has a preconfigured value of Month. The possible values for the GRAIN parameter are:
DAY
WEEK
MONTH
QUARTER
YEAR
The Payroll aggregate table is fully loaded from the base table in the initial ETL run by the mapping 'PLP_PayrollAggregate_Load_Full'. The table can grow to millions of records. The Payroll aggregate table is not fully reloaded from the base table after an incremental ETL run. Oracle HR Analytics minimizes the incremental aggregation effort, by modifying the aggregate table incrementally as the base table is updated. Oracle BI Applications looks for new records in the base table during the incremental ETL. This process is done in two steps:
There are new records in the W_PAYROLL_A table, which are inserted after the last ETL run. These new records are inserted into the W_PAYROLL_A_TMP table. This step is part of the post load-processing workflow, and the mapping is called 'PLP_PayrollAggregate_Extract'.
Oracle HR Analytics aggregates the W_PAYROLL_A_TMP table and joins it with the W_PAYROLL_A aggregate table to insert new or update existing buckets to the aggregate table. This step is part of the post load-processing workflow, and the mapping is called 'PLP_PayrollAggregate_Load'.
To load the Payroll aggregate table (W_PAYROLL_A), you need to configure the post-load processing parameters in DAC.
To load the Payroll aggregate table (W_PAYROLL_A):
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab, and select the Task PLP_PayrollAggregate_Load.
Display the Parameters subtab, and add a parameter with the name $$GRAIN.
Depending upon the aggregation requirement, define the value as one of the following: 'DAY', 'WEEK', 'MONTH', 'QUARTER' or 'YEAR'.
Select Static as the parameter type.
Save the task.
For dimensions that keep history, it is important to look up the dimension values for each snapshot date in the fact. To achieve this, set the Dimension Type2 Flag parameters in DAC for the task PLP_WorkforceEventFact_Month. Setting these parameters enables the workforce snapshot fact table to correctly resolve the type 2 slowly-changing dimensional foreign key.
By default, the Type2 Flag parameters are set to N, which means that dimensions with history may not be perfectly in sync with the fact on every snapshot date. Change this setting to Y for each dimension where the WIDs need to be resolved as of the snapshot date. There might be a small performance impact, particularly with a full load, if all the flags are set to Y, because the ETL will be performing many additional lookups for every snapshot.
The following list shows the parameters defined in the task PLP_WorkforceEventFact_Month in DAC. These parameters have a default value of N. Set these parameters to Y only for dimensions that require WIDs to be resolved as of the snapshot date.
$$EMPLOYEE_TYPE2_FLG
$$ORGANIZATION_TYPE2_FLG
$$JOB_TYPE2_FLG
$$HR_POSITION_TYPE2_FLG
$$LOCATION_TYPE2_FLG
The DAC parameters described in this section are specific to Oracle HR Analytics. The values you set for these parameters largely affect the data in your Data Warehouse and will ultimately drive your reporting experiences.
$$HR_WRKFC_EXTRACT_DATE
Set this date parameter to a date from which you need to extract the workforce analytics data.
$$HR_ABSNC_EXTRACT_DATE
Set this date parameter to a date from which you need to extract the absence data for the workforce. This date parameter value should be greater than or equal to the value of $$HR_WRKFC_EXTRACT_DATE.
$$HR_WRKFC_SNAPSHOT_DT
Set this date parameter to the date from which monthly snapshots need to be aggregated from the workforce event fact. Setting a very early date causes a large number of snapshots. Use discretion when choosing a very early date, because doing so is likely to affect ETL performance. Because the snapshots are typically taken on a monthly basis, it is required that you enter a date against this parameter that is at least the "first day" of a month, or even better, the "first day" of a year (Gregorian). Note that this date parameter value should be greater than or equal to the value of $$HR_WRKFC_EXTRACT_DATE.
HR Analytics loads future-dated transactions into the data warehouse. Future-dated transactions can be excluded from user querying. The following list describes where and how future-dated security is implemented in the HR subject areas:
Fact - HR - Event Information (Workforce) and Fact - HR - Operation (Workforce) are future-data secured by the session level variable, HR_MAX_EFFECTIVE_DT, which defaults to the value CURRENT_DATE in the default configuration.
Fact - HR - Recruitment Event Information has future-dated security by CURRENT_DATE in the logical table sources.
The following fact tables are not restricted by future-dated security, and users can query future-dated data:
Fact - HR - Absence Event
Fact - HR - Payroll
Fact - HR - Learning Enrollment Events
Fact - HR - Learning Enrollment and Completion
This section contains configuration steps that apply to Oracle EBS. It contains the following topics:
Section 7.3.2.1, "About Incremental Processing for Oracle EBS"
Section 7.3.2.2, "Configuring Original Job Requisition Status"
Section 7.3.2.3, "About Job Requisition Metrics in Oracle EBS"
Section 7.3.2.4, "Configuring Employee Assignment and Applicant Assignment Correlation"
Section 7.3.2.5, "About Oracle HR Analytics DAC Parameters and Configuration Tags for Oracle EBS"
Incremental processing for Oracle EBS adaptors supports refreshing of all new or changed data, except where the data has been purged. This includes new records, corrections, date-tracked updates, and date-tracked deletes.
There are separate mappings to detect and process purges.
Note:
This section is provided for informational purposes only. No configuration steps are required for this topic.In Oracle EBS, the Job Requisition status is not preserved as historical information in the OLTP. Therefore, as a Job Requisition status changes, for example from Drafted to Approved to Open to Closed, the OLTP saves only the last status.
The Job Requisition Open event is a significant event because several metrics depend on it. Therefore, you must track this event by configuring the original Job Requisition status event, which occurs on the Job Requisition start date. For example, if the latest status is "Closed," then you can deduce that at one point it had a status of "Open." Therefore, you should map the original status to "Open." However, if the latest status is "Approval Denied," then the requisition was never opened. Therefore, you should map the original status of the Job Requisition to another value, such as "Requested."
Use the file_job_requisition_status_original_ora12.csv file to map the original status of the Job Requisition to the event. An example of how to map this event is provided in the following configuration steps.
To configure the original Job Requisition Status:
Identify the Job Requisition statuses in your Oracle EBS source system by using the following SQL:
SELECT HR_STANDARD_LOOKUPS.LOOKUP_CODE, HR_STANDARD_LOOKUPS.MEANING FROM HR_STANDARD_LOOKUPS WHERE HR_STANDARD_LOOKUPS.LOOKUP_TYPE = 'VACANCY_STATUS'
Using a text editor, open the file_job_requisition_status_original_ora12.csv file located in the $PMServer\LkpFiles directory (for example, INFA_HOME\server\infa_shared\LkpFiles).
Copy the Lookup Code and Meaning to the REQUISITION_CURRENT_STATUS_CODE and REQUISITION_CURRENT_STATUS_NAME columns in the file respectively. The data must be copied starting from the sixth line. Use commas to separate the entries.
Map each REQUISITION_CURRENT_STATUS_CODE to one REQUISITION_ORIGINAL_STATUS_CODE.
REQUISITION_ORIGINAL_STATUS_CODE is used primarily to determine if a job requisition had a status of OPEN at an earlier date. For example, if the REQUISITION_CURRENT_STATUS_CODE of a job requisition is CLOSED, it may mean that the job requisition was OPEN on an earlier date. In this case, the original requisition status can be classified as APPROVED. The APPROVED status can be mapped to RQSTN_OPEN as W_EVENT_CODE, W_SUB_STAGE_CODE, and W_STAGE_CODE in the domainValues_Recruitment_Event_Reason_oraVersion.csv.
If the current Job Requisition status is REJECTED, it may mean that this job requisition previously had a status of PENDING on an earlier date and was never in OPEN status. In this case, the original requisition status can be classified as PENDING instead of OPEN. The PENDING status can be mapped to RQSTN_APPROVAL_PENDING as W_EVENT_CODE, W_SUB_STAGE_CODE and RQSTN_PENDING as stage code.
All values of REQUISITION_ORIGINAL_STATUS_CODE should exist as a value in REQUISITION_CURRENT_STATUS_CODE.
Save and close the file.
Job Requisition in EBS includes the following metrics:
Number of Openings
Budgeted Headcount
Budgeted FTE
In iRecruitment, users enter Job Requisition details, which include Number of Openings. In the ETL run, Number of Openings is mapped to the BUDGET_MEASUREMENT_VALUE field. By default, the NUMBER_OF_OPENINGS field is populated by what is configured at the organization level. The Number of Openings field is not visible in the application by default; however, the application can be customized so that the field is visible.
In iRecruitment, the field with label Number of Openings is mapped to the BUDGET_MEASUREMENT_VALUE column of the PER_ALL_VACANCIES table. By default, the NUMBER_OF_OPENINGS column of the PER_ALL_VACANCIES table is populated by what is configured at the organization level. The Number of Openings field is not visible in the application by default; however, the application can be customized so that the field is visible.
Three parameters have been provided to allow flexibility when setting up these metrics. The following table describes the parameters and their default values, which you can modify to suit your implementation requirements.
| Task Name | Parameter Name | Default Values |
|---|---|---|
| SDE_ORA_JobRequisitionDimension | $$BUDGETED_FTE_SOURCE_COL | CASE WHEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_TYPE = 'FTE' THEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_VALUE ELSE NULL END |
| SDE_ORA_JobRequisitionDimension | $$BUDGETED_HEADCOUNT_SOURCE_COL | CASE WHEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_TYPE = 'HEAD' THEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_VALUE ELSE PER_ALL_VACANCIES.NUMBER_OF_OPENINGS END |
| SDE_ORA_JobRequisitionDimension | $$NUMBER_OF_OPENINGS_SOURCE_COL | PER_ALL_VACANCIES.NUMBER_OF_OPENINGS |
| SDE_ORA_JobRequisitionEventFact | $$BUDGETED_FTE_SOURCE_COL | CASE WHEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_TYPE = 'FTE' THEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_VALUE ELSE NULL END |
| SDE_ORA_JobRequisitionEventFact | $$BUDGETED_HEADCOUNT_SOURCE_COL | CASE WHEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_TYPE = 'HEAD' THEN PER_ALL_VACANCIES.BUDGET_MEASUREMENT_VALUE ELSE PER_ALL_VACANCIES.NUMBER_OF_OPENINGS END |
| SDE_ORA_JobRequisitionEventFact | $$NUMBER_OF_OPENINGS_SOURCE_COL | PER_ALL_VACANCIES.NUMBER_OF_OPENINGS |
Recruitment analytics is designed to correlate the applicant with that of the employee getting hired in the job. This is done in the mapping 'SDE_ORA_ApplicantEventFact_EmpAplAssignments.' By default, the correlation is based only on the PERSON_ID column. However, the logic for this correlation can vary based on customer implementations and might require checks on additional columns to make it more reliable.
The following two parameters have been provided to allow flexibility when setting up the correlation between the employee assignment and the applicant assignment:
$$EMP_APL_CORR_VACANCY_ID_MATCH_REQD
If the customer implementation is such that the VACANCY_ID forms the basis of the correlation between employee assignment and the applicant assignment, then this parameter must be set to 'Y.' By default, the parameter value is set to 'N.'
$$EMP_APL_CORR_APPLICATION_ID_MATCH_REQD
If the customer implementation is such that the APPLICATION_ID forms the basis of the correlation between employee assignment and the applicant assignment, then this parameter must be set to 'Y.' By default, the parameter value is set to 'N.'
If required, customers can set both parameter values to 'Y,' so that both conditions are considered for the correlation.
Note that both of these conditions for correlating employee and applicant assignments are in addition to the existing condition on PERSON_ID.
To configure the Employee and Applicant Assignment Correlation:
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Tasks tab, and query for task 'SDE_ORA_ApplicantEventFact_EmpAplAssignments.'
Go to the Parameters subtab and set the appropriate values for the following parameters:
$$EMP_APL_CORR_VACANCY_ID_MATCH_REQD
$$EMP_APL_CORR_APPLICATION_ID_MATCH_REQD
Choose the task and push the changes to all the referenced containers.
Be aware of the following DAC parameter for Oracle HR Analytics with Oracle EBS source systems:
$$HR_WRKFC_ADJ_SERVICE_DATE
This parameter is used to determine the period of service for workers. Set this to 'Y' to start calculating period of service/placement from rehire date, and 'N' to start calculating period of service/placement from original hire date.
In addition, be aware of the following Oracle EBS-specific configuration tags in the DAC repository. Include these tags as needed, depending on the Execution plan you are running.
Human Resources - Collect FTE
This configuration tag enables you to collect the Full Time Equivalent (FTE) values for workers in HR-Workforce analytics. If the tag is inactivated (disabled), FTE Values are not collected.
Human Resources - Collect Performance Ratings
This configuration tag enables to collect the Performance Ratings for workers in HR-Workforce analytics. If the tag is inactivated (disabled), Performance Ratings are not collected.
Human Resources - Load User Flex Data
This configuration tag enables loading of user customized Oracle E-Business Suite Flexfield data to the warehouse dimension tables (such as JOB_CODE in W_JOB_D). For more information, see Section 7.2.3, "Configuration Steps for Flexflelds." You must include this tag to see Flexfield columns.
The file_duration_units_conversion_to_hours.csv file affects the calculation and conversion of assignment working frequency/amount from hourly, daily, weekly, monthly, quarterly or yearly amounts to hourly, daily, weekly, monthly, quarterly or yearly amounts. The conversation affects the table column W_WRKFC_EVT_MONTH_F.STD_HOURS_MONTH.
This file sets the conversion factor from minutes to hours as 1/60 = 0.0167, hours to hours as 1, days to hours as 8, and so on. In most cases, you can leave the values unchanged. However, if your system uses different conversions (for example, days to hours as 8.5 or 9), you must change the values.
This section contains configuration steps that apply to PeopleSoft. It contains the following topic:
Section 7.3.3.1, "About Incremental Processing for PeopleSoft"
Section 7.3.3.2, "How to Tune Performance for the Payroll Table for PeopleSoft HCM HR Analytics"
Section 7.3.3.3, "About Oracle HR Analytics DAC Parameters for PeopleSoft"
This section describes the default behavior for People Soft incremental processing. Although most data is refreshed, there are some restrictions due to no last update timestamps on some base tables. These restrictions are as follows:
Job data - no restrictions
Appraisals - only new records or changes to the most recent record are processed
International Assignments - only changes to the most recent record are processed
There are separate mappings to detect and process purges.
The Payroll mappings are designed to take care of the Payroll Adjustment and the Payroll Reversal process. These are complicated processes and normally take a reasonable amount of time to complete. Depending on your requirements, there are two ways you can achieve better overall performance in the Payroll Analytics. These techniques are as follows:
Deactivate Adjustments and Reversals, if not required, for a given pay period.
Defer calculating Adjustments and Reversals for some time, until needed.
To deactivate Adjustment and Reversal calculations for Payroll:
If you decide that there will be no Payroll Adjustment and Reversal for a pay period, then you can choose to deactivate those tasks in DAC.
In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Select the Tasks tab.
Select the Inactive check the box for the following tasks:
SDE_PSFT_PayrollFact_Deductions_Adjustments_Reversals
SDE_PSFT_PayrollFact_Earnings_Adjustments_Reversals
SDE_PSFT_PayrollFact_EarningsOther_Adjustments_Reversals
SDE_PSFT_PayrollFact_Total_Adjustments_Reversals
SDE_PSFT_PayrollFact_Taxes_Adjustments_Reversals
Save the tasks.
Assemble the Subject Area HR- Payroll and build the Execution Plans that contain this Subject Area.
To defer Adjustment and Reversal calculations for Payroll:
Deactivate Adjustment and Reversal calculations for Payroll and reassemble the Subject Area Payroll and rebuild the Execution Plans that contain this Subject Area.
When you choose to run these again, reactivate the tasks, and reassemble Subject Area (Payroll) and Execution Plan.
Adjust the parameter $$LAST_EXTRACT_DATE so that it goes back to the date (preferably one day earlier) since when you decided not to run these tasks.
You can use the Oracle HR Analytics DAC parameters for PeopleSoft to customize International Assignment and Appraisal extractions. To force changes to any appraisal or international assignment to be picked up, you can customize the incremental mappings. The filter restricting records to the most recent one can be removed. Doing so increases the processing time for incremental load.
The Oracle HR Analytics DAC parameters for PeopleSoft are as follows:
$$APP_REVIEW_EXCL_STATUS
Set this parameter to exclude statuses (such as, anything that is not completed). The default example is 'CA','TA', which is handled as an IN statement in the mapping SQL.
$$APP_REVIEW_TYPE
Set this parameter to the Review type to include. Only one type is supported, such as Annual Review. The default example is 'K0ANNUAL.'
|
 Copyright © 2009, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
