7 Configuring Oracle Human Resources Analytics

This section describes how to configure Oracle Human Resources Analytics. It contains the following topics:

• Section 7.1, "Overview of Oracle Human Resources Analytics"

• 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.3, "Configuration Steps for Flexflelds"

  • 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.2.6, "Configuration Steps for Controlling Your Data Set"

Note:

All references to Oracle 11i also apply to Oracle R12.

7.1 Overview of Oracle Human Resources Analytics

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 allows 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.

7.2 Configuration Required Before A Full Load for Oracle HR Analytics

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.3, "Configuration Steps for Flexflelds"

• 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.2.6, "Configuration Steps for Controlling Your Data Set"

Notes

• Before you run Execution Plans for HR Subject Areas, make sure that you have used DAC to set the appropriate Source System Parameters. To do so, in DAC, display the Design view, select the source system type, and 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.

7.2.1 Configuration Steps for Oracle HR Analytics for All Source Systems

This section contains configuration steps required before you do a full data load that apply to all source systems. It contains the following topics:

• Section 7.2.1.1, "Configuring Band Dimensions"

• Section 7.2.1.2, "About Domain Values and CSV Worksheet Files for Oracle HR Analytics"

Note:

For configuration steps that apply to all analytics modules (for example, Oracle Financial Analytics, Oracle HR Analytics, Oracle Sales Analytics, see Chapter 3, "Configuring Common Areas and Dimensions").

7.2.1.1 Configuring Band 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.1, "Overview of Band Dimensions"

• 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"

• Section 7.2.1.1.5, "Configuring file_emp_pow_band.csv and file_cwk_pow_band.csv for Period of Work Band Dimension"

7.2.1.1.1 Overview of Band Dimensions

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: LEVEL_ID = AGE_BAND. This level defines the age bands. LEVEL_ID = AGE. This level defines the age (in months) for a person.
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: LEVEL_ID = RQSTN_AGE_BAND. This level defines the job requisition age bands. LEVEL_ID = RQSTN_AGE. This level defines the job requisition age (in months).
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: LEVEL_ID = PERF_BAND. This level defines the performance rating bands. LEVEL_ID = PERF_RTNG. This level defines each performance ratings (in integers up to 100) for a person.
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: Two levels define the bands: LEVEL_ID = POW_BAND_EMP defines the employees' period of work band; LEVEL_ID = POW_BAND_CWK defines the contingent workers' period of work band. LEVEL_ID = POW. This level defines the period of work (in months) for a person.

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, \PowerCenter8.6.x\server\infa_shared\SrcFiles).

7.2.1.1.2 Configuring file_age_band.csv for Age Band Dimension

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, make sure 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.

Table 7-3 Example of file_age_band.csv Inputs

AGE_BAND_CODE	AGE_BAND_DESC	BAND_MIN_MONTHS	BAND_MAX_MONTHS
AGE_BAND_1	<20 Years	0	240
AGE_BAND_2	20-30 Years	240	360
AGE_BAND_3	30-40 Years	360	480
AGE_BAND_4	40-50 Years	480	600
AGE_BAND_5	50-60 Years	600	720
AGE_BAND_6	>=60 Years	720

7.2.1.1.3 Configuring the file_req_age_band.csv for Job Requisition Age Band Dimension

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, make sure 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

7.2.1.1.4 Configuring the file_perf_age_band.csv for Performance Band Dimension

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, make sure 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_ora<ver>.csv, see Section 7.2.2.13, "How to configure the domainValues_perf_nrml_rating_ora<ver>.csv."

• For information about configuring the domainValues_perf_nrml_rating_psft.csv, see Section 7.2.4.20, "How to configure the domainValues_perf_nrml_rating_psft.csv."

Table 7-7 Example of file_perf_age_band.csv Inputs

PERF_BAND_CODE	PERF_BAND_DESC	BAND_MIN_NORM_RATING	BAND_MAX_NORM_RATING
PERF_BAND_1	<50 Percent	0	50
PERF_BAND_2	50 to 60 Percent	50	60
PERF_BAND_3	60 to 70 Percent	60	70
PERF_BAND_4	70 to 80 Percent	70	80
PERF_BAND_5	80 Percent and Above	80

7.2.1.1.5 Configuring file_emp_pow_band.csv and file_cwk_pow_band.csv for Period of Work Band Dimension

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, make sure 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, make sure 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

Table 7-11 Example of file_cwk_pow_band.csv Inputs

CWK_POW_BAND_CODE	CWK_POW_BAND_DESC	CWK_BAND_MIN_MONTHS	CWK_BAND_MAX_MONTHS
CWK_BAND_1	<1 Month	0	1
CWK_BAND_2	1 Month	0	1
CWK_BAND_3	1 to 3 Months	1	3
CWK_BAND_4	3 to 6 Months	3	6
CWK_BAND_5	12 to 24 Months	12	24
CWK_BAND_6	>= 24 Months	24

7.2.1.2 About Domain Values and CSV Worksheet Files for Oracle HR Analytics

You configure Oracle HR Analytics by mapping domain values to columns in the CSV files located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\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, make sure that you:

• Do no 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.

7.2.2 Configuration Steps for Oracle HR Analytics for Oracle EBS

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_ora<ver>.csv"

• Section 7.2.2.3, "How to Configure the domainValues_Employment_Cat_ora<ver>.csv"

• Section 7.2.2.4, "How to Configure the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv"

• Section 7.2.2.5, "How to Configure the domainValues_Employment_Full_Time_Flg_ora<ver>.csv"

• Section 7.2.2.6, "How to Configure the domainValues_Employee_Sex_MF_ora<ver>.csv"

• Section 7.2.2.7, "How to Configure the domainValues_Employment_Status_ora<ver>.csv"

• Section 7.2.2.8, "How to Configure the domainValues_Employee_Veteran_Status_Code_ora<ver>.csv"

• Section 7.2.2.9, "How to configure the domainValues_Flsa_ora<ver>.csv"

• Section 7.2.2.10, "How to Configure the domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv"

• Section 7.2.2.11, "How to Configure the domainValues_Pay_Type_Flg_ora<ver>.csv"

• Section 7.2.2.12, "How to Configure the domainValues_Pay_Type_Grp_Code_ora<ver>.csv"

• Section 7.2.2.13, "How to configure the domainValues_perf_nrml_rating_ora<ver>.csv"

• Section 7.2.2.14, "How to Configure the domainValues_Recruitment_Event_Reason_ora<ver>.csv"

• Section 7.2.2.15, "How to Configure the domainValues_Recruitment_Source_Type_ora<ver>.csv"

• Section 7.2.2.16, "How to Configure the domainValues_Requisition_Category_ora<ver>.csv"

• Section 7.2.2.17, "How to Configure the domainValues_Status_Recruitment_ora<ver>.csv"

• Section 7.2.2.18, "How to Configure the domainValues_Status_Vacancy_ora<ver>.csv"

• Section 7.2.2.19, "How to Configure the domainValues_Wrkfc_EventType_ora<ver>.csv"

• Section 7.2.2.20, "How to Configure Address Types for HR Profile"

• Section 7.2.2.21, "How to Configure Phone Types for HR Profile"

• Section 7.2.2.22, "How to Configure Education Degree Codes for Employee Dimension"

• Section 7.2.2.23, "How to Configure Flags for the Pay Type Dimension"

• Section 7.2.2.24, "How to Configure Classification Names for Payroll"

7.2.2.1 Domain Values and CSV Worksheet Files for Oracle EBS

Table 7-12 lists the CSV worksheet files and the domain values for Oracle HR Analytics in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\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_ora<ver>.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_ora<ver>.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_EmploymentDimension_Workforce
domainValues_Employee_Ethnic_Group_Code_ora<ver>.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_EmployeeDimension, SDE_ORA_EmployeeDimension_Full
domainValues_Employment_Full_Time_Flg_ora<ver>.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_EmploymentDimension_Workforce
domainValues_Employee_Sex_MF_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.csv Note: The domainValues_EventTypes_ora<ver>.csv file is not used for the 7.9.6 release.	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_ora<ver>.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_ora<ver>.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_HRPositionDimension, SDE_ORA_HRPositionDimension
domainValues_Pay_Type_Flg_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.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

7.2.2.2 How to configure the domainValues_AbsenceEvent_Status_ora<ver>.csv

This section explains how to configure the domainValues_AbsenceEvent_Status_ora<ver>.csv file.

Note: This task is Oracle EBS R12 specific. For Oracle EBS 11.5.10 source systems, this configuration is not required.

To configure the domainValues_AbsenceEvent_Status_ora<ver>.csv file.

1. You can identify Approval Status values using the following SQL:

   SELECT DISTINCT APPROVAL_STATUS 
   FROM PER_ABSENCE_ATTENDANCES 
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_AbsenceEvent_Status_ora<ver>.csv file in a text editor. In the file, verify if 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.

7.2.2.3 How to Configure the domainValues_Employment_Cat_ora<ver>.csv

This section explains how to configure the domainValues_Employment_Cat_ora<ver>.csv file. A sub category is added to the employment dimension.

This section explains how to configure the domainValues_Employment_Cat_ora<ver>.csv.

1. 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
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_Employment_Cat_ora<ver>.csv file in a text editor.

3. From the SQL query result, copy the User Person Types and System Person Types data to the domainValues_Employment_Cat_ora<ver>.csv file in the USER_PERSON_TYPE and SYS_PERSON_TYPE columns. Copy data starting from the sixth line.

4. In the domainValues_Employment_Cat_ora<ver>.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

   
   

5. Save and close the file.

7.2.2.4 How to Configure the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv

This section explains how to configure the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv.

1. 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','BA','BC','BO','C','I','O','P','W') 
    ORDER BY 1 
   

2. Using a text editor, open the domainValues_Employee_Ethnic_Group_Code_ora<ver>.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the PER_INFORMATION1 to the ETHNIC_CODE column in the file. The data must be copied starting from the 6th line.

4. Map each Ethnic Code to one domain value.

5. Save and close the file.

7.2.2.5 How to Configure the domainValues_Employment_Full_Time_Flg_ora<ver>.csv

This section explains how to configure the domainValues_Employment_Full_Time_Flg_ora<ver>.csv file. The employment dimension is enhanced to include the Regular and Temporary worker configuration.

1. 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
   

2. From in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_Employment_Full_Time_Flg_ora<ver>.csv file in a text editor.

3. From the SQL query result, copy the Employment Category data to the domainValues_Employment_Full_Time_Flg_ora<ver>.csv file in the EMPLOYMENT_CATEGORY column. Copy data starting from the sixth line.

4. 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_ora<ver>.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).

5. Save and close the file.

7.2.2.6 How to Configure the domainValues_Employee_Sex_MF_ora<ver>.csv

This section explains how to configure the domainValues_Employee_Sex_MF_ora<ver>.csv.

1. 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 
   

2. Using a text editor, open the domainValues_Employee_Sex_MF_ora<ver>.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the SEX column to the SEX column in the file. The data must be copied starting from the 6th line.

4. Map each Sex Code to one domain value.

5. Save and close the file.

7.2.2.7 How to Configure the domainValues_Employment_Status_ora<ver>.csv

This section explains how to configure the domainValues_Employment_Status_ora<ver>.csv

1. 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 
   

2. Using a text editor, open the domainValues_Employment_Status_ora<ver>.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the PER_SYSTEM_STATUS to the PER_SYSTEM_STATUS column in the file. The data must be copied starting from the 6th line.

4. Map each PER_SYSTEM_STATUS to one domain value.

5. Save and close the file.

7.2.2.8 How to Configure the domainValues_Employee_Veteran_Status_Code_ora<ver>.csv

This section explains how to configure the domainValues_Employee_Veteran_Status_Code_ora<ver>.csv

1. 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 
   

2. Using a text editor, open the domainValues_Employee_Veteran_Status_Code_ora<ver>.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the PER_INFORMATION5 column to the VETERAN_STATUS_CODE column in the file. The data must be copied starting from the 6th line.

4. Map each Veteran Status Code to one domain value.

5. Save and close the file.

7.2.2.9 How to configure the domainValues_Flsa_ora<ver>.csv

This section explains how to configure the domainValues_Flsa_ora<ver>.csv file.

1. Identify the FLSA Statuses in the Oracle source system 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
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_Employment_Exempt_Flg_ora<ver>.csv file in a text editor.

3. From the SQL query result, copy the FLSA_STAT_CODE data to the domainValues_Flsa_ora<ver>.csv file in the FLSA_STATUS_CODE column. Copy data starting from the sixth line.

4. Map each FLSA_STATUS_CODE value to one domain value.

5. Save and close the file.

7.2.2.10 How to Configure the domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv

This section explains how to configure the domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv.

1. 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 
   

2. Using a text editor, open the domainValues_HRPosition_Active_Pos_Flg_ora<ver>.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the STATUS to the STATUS column in the file. The data must be copied starting from the 6th line.

4. Map each position STATUS to one domain value.

5. Save and close the file.

7.2.2.11 How to Configure the domainValues_Pay_Type_Flg_ora<ver>.csv

This section explains how to configure the domainValues_Pay_Type_Flg_ora<ver>.csv.

1. 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 
   

2. Using a text editor, open the domainValues_Pay_Type_Flg_ora<ver>.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the COSTING_DEBIT_OR_CREDIT to the COSTING_DEBIT_OR_CREDIT column in the file. The data must be copied starting from the 6th line.

4. Map each _DEBIT_OR_CREDIT to one domain value.

5. Save and close the file.

7.2.2.12 How to Configure the domainValues_Pay_Type_Grp_Code_ora<ver>.csv

This section explains how to configure the domainValues_Pay_Type_Grp_Code_ora<ver>.csv.

1. 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
   

2. Using a text editor, open the domainValues_Pay_Type_Grp_Code_ora<ver>.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

   Note:

   Do not change lines one to four in the domainValues_Pay_Type_Grp_Code_ora<ver>.csv file.

4. 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'.

5. Save and close the file.

7.2.2.13 How to configure the domainValues_perf_nrml_rating_ora<ver>.csv

This section explains how to configure the domainValues_perf_nrml_rating_ora<ver>.csv file. Utilizing the Oracle source system's performance functionality determines the configuration options for the domainValues_perf_nrml_rating_ora<ver>.csv file.

To configure the domainValues_perf_nrml_rating_ora<ver>.csv file.

1. Identify the Performance Ratings in your Oracle source system 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 
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_perf_nrml_rating_ora<ver>.csv file in a text editor.

3. 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.

4. In the domainValues_perf_nrml_rating_ora<ver>.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 \PowerCenter8.6.x\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.

5. Save and close the file.

7.2.2.14 How to Configure the domainValues_Recruitment_Event_Reason_ora<ver>.csv

This section explains how to configure the domainValues_Recruitment_Event_Reason_ora<ver>.csv.

Note:

You must configure this csv file for the recruitment Subject Area to function properly.

1. 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') 
   

2. 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' 
   

3. 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' 
   

4. 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' 
   

5. Using a text editor, open the domainValues_Recruitment_Event_Reason_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

6. 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 6th line. Use commas to separate the entries.

7. 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.

8. 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.

9. 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.'

10. The REASON_CODE and REASON_NAME for all the job requisition STATUS_CODE and STATUS_NAME values obtained from step 2 should be 'Unspecified.'

11. 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.

12. Save and close the file.

7.2.2.15 How to Configure the domainValues_Recruitment_Source_Type_ora<ver>.csv

This section explains how to configure the domainValues_Recruitment_Source_Type_ora<ver>.csv.

1. 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'
   

2. Using a text editor, open the domainValues_Recruitment_Source_Type_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

4. 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.

5. 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.

6. Save and close the file.

7.2.2.16 How to Configure the domainValues_Requisition_Category_ora<ver>.csv

This section explains how to configure the domainValues_Requisition_Category_ora<ver>.csv.

1. 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'
   

2. Using a text editor, open the domainValues_Requisition_Category_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

4. 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 the Oracle Business Analytics Warehouse Data Model Reference.

5. Save and close the file.

7.2.2.17 How to Configure the domainValues_Status_Recruitment_ora<ver>.csv

This section explains how to configure the domainValues_Status_Recruitment_ora<ver>.csv.

1. 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
   

2. Using a text editor, open the domainValues_Status_Recruitment_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

4. 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 the Oracle Business Analytics Warehouse Data Model Reference.

5. Save and close the file.

7.2.2.18 How to Configure the domainValues_Status_Vacancy_ora<ver>.csv

This section explains how to configure the domainValues_Status_Vacancy_ora<ver>.csv file.

1. 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'
   

2. Using a text editor, open the domainValues_Status_Recruitment_ora<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

4. 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.

5. Save and close the file.

7.2.2.19 How to Configure the domainValues_Wrkfc_EventType_ora<ver>.csv

This section explains how to configure the domainValues_Wrkfc_EventType_ora<ver>.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_ora<ver>.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_ora<ver>.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

Note:

If dashboard end users are using a dashboard containing HR Event Type data, they might see 'Unspecified' values in the columns, Event Group Code, Event Sub Group Code, Source Event Type and Source Event Reason. To avoid seeing see 'Unspecified' values in these columns, they should filter out Unspecified records. For example, to filter the Source Event Reason column they might specify the filter, HR Event Type.Source Event Reason <> 'Unspecified'.

A power user with Ad-hoc (Oracle BI EE Answers) access could change or apply the filter in a given existing report used on a dashboard. An end user could apply the filter in a dashboard, if the dashboard had a prompt for the HR Event Type.Event Group Code (or Description).

To configure the domainValues_Wrkfc_EventType_ora<ver>.csv file.

1. Identify the potential list of Workforce Event Reasons in the Oracle source system 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 
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_Wrkfc_EventType_ora<ver>.csv file in a text editor.

3. 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, make sure 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_ora<ver>.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'.

4. Configure data options based on your requirements.

   Table 7-13 Configuration Options for the domainValues_Wrkfc_EventType_ora<ver>.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 value Any. For examples on this configuration option, see the domainValues_Wrkfc_EventType_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.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_ora<ver>.csv file, see information provided in the ASG PR_PROM column.
   Identifying a Transfer Event	Not Available	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_ora<ver>.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_ora<ver>.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_ora<ver>.csv file, see TERM.

   
   

5. Save and close the file.

7.2.2.20 How to Configure Address Types for HR Profile

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

1. In Informatica PowerCenter Designer, open the SDE_ORA<Version>_Adaptor directory.

2. In Mapplet Designer, open the mplt_SA_ORA_EmployeeDimension mapplet.

3. For each of the Lookup Procedures Lkp_Work_Address, Lkp_Mail_Address, Lkp_Perm_Address, do the following:

   1. Double click on the Lookup Procedure to display the Edit Transformations dialog.

   2. Display the Properties tab.

   3. 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.

4. Validate and save changes to the repository.

   If you are using version control for the Informatica Repository, then check in your changes.

7.2.2.21 How to Configure Phone Types for HR Profile

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

1. In Informatica PowerCenter Designer, open the SDE_ORA<Version>_Adaptor directory.

2. In Mapplet Designer, open the mplt_SA_ORA_EmployeeDimension mapplet.

3. For each of the Lookup Procedures Lkp_Work_Phone, Lkp_Pager_Phone, Lkp_Mobile_Phone, Lkp_Fax_Phone, do the following:

   1. Double click on the Lookup Procedure to display the Edit Transformations dialog.

   2. Display the Properties tab.

   3. 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'
      

4. Validate and save changes to the repository. If you are using the version controlling for the Informatica Repository, then check in your changes.

7.2.2.22 How to Configure Education Degree Codes for Employee Dimension

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:

1. In DAC, go to the Design view, and select your custom container from the drop-down list.

2. Display the Tasks tab, and select the SDE_ORA_EmployeeDimension task.

3. Display the Parameters subtab.

4. Enter an appropriate value for the parameter $$QUALIFICATION_CATEGORY_LIST

   For example, $$QUALIFICATION_CATEGORY_LIST = 'DEGREE', 'DT'.

5. Save your changes.

To obtain the list of the valid values for the education degree codes please login to 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.

7.2.2.23 How to Configure Flags for the Pay Type Dimension

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, or Regular Compensation Earning type or a Pension Compensation Earning type. These Flag columns are chosen to contribute towards slowly changing dimension 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 needs to be configured to see the accurate values of the Pay Type Flags in the Data Warehouse.

To Configure Flags for the Pay Type Dimension

1. Using a text editor, open the file_comp_pension_taxable_flg_ora<ver>.csv file in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

2. Add all possible Classification Names that are being used in your business.

3. 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.

7.2.2.24 How to Configure Classification Names for Payroll

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 following the steps below.

To configure the classification name parameters:

1. In DAC, go to the Design view, and select your custom container from the drop-down list.

2. Display the Tasks tab, and select the task SDE_ORA_PayrollFact_Agg_Items.

3. 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) */

4. Save your changes.

To find out the possible Classification Names existing in your system, please 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 () 

7.2.3 Configuration Steps for Flexflelds

This section explains how to configure the key flexfields in Oracle EBS applications.

For more information on flexfields, see the 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.1, "How to generate Flexfield Configuration file for HCM hr_file_flex_kff_dff_user_config_map.csv"

• 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"

• Section 7.2.3.5, "How to Configure Collect FTE, Collect Performance Ratings, and Load User Flex Data"

7.2.3.1 How to generate Flexfield Configuration file for HCM hr_file_flex_kff_dff_user_config_map.csv

To Create a flexfield configuration file.

1. In DAC, display the Execute view.

2. 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, \PowerCenter8.6.x\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.

7.2.3.2 How to Configure the Key Flexfields for the Job Dimension

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.

1. 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 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.

2. Filter the SOURCE_TABLE column for the PER_JOBS and PER_JOB_DEFINITIONS data.

3. 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

   
   

4. Save and close the file.

7.2.3.3 How to Configure the Key Flexfields for the HR Position Dimension

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.

1. 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 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.

2. Filter the SOURCE_TABLE column for the PER_POSITION_DEFINITIONS data.

3. 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

   
   

4. Save and close the file.

7.2.3.4 How to Configure the Key Flexfields for the Pay Grade Dimension

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.

1. 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 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.

2. Filter the SOURCE_TABLE column for the PER_GRADE_DEFINITIONS data.

3. 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

   
   

4. Save and close the file.

7.2.3.5 How to Configure Collect FTE, Collect Performance Ratings, and Load User Flex Data

To configure the Collect FTE, Collect Performance Ratings, and Load User Flex Data configuration tags:

1. In DAC, click on Design.

2. Display the Configuration Tags tab.

3. In the Edit sub-tab, 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.

   
   

4. Save the details.

7.2.4 Configuration Steps for Oracle HR Analytics for PeopleSoft

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_Emp_EventType_Grp_Code_psft.csv"

• Section 7.2.4.11, "How to Configure the domainValues_HRPosition_Active_Pos_Flg_psft.csv"

• Section 7.2.4.12, "How to Configure the domainValues_Pay_Type_Grp_Code_psft.csv"

• Section 7.2.4.13, "How to Configure the domainValues_Emp_EventType_EventReason_Code_psft.csv"

• Section 7.2.4.14, "How to Configure the domainValues_Recruitment_Source_Type_psft<ver>.csv"

• Section 7.2.4.15, "How to Configure the domainValues_Requisition_Category_psft<ver>.csv (WIP)"

• Section 7.2.4.16, "How to Configure the domainValues_Status_Vacancy_Recruitment_psft<ver>.csv"

• Section 7.2.4.17, "How to Configure the domainValues_Recruitment_Event_Reason_psft<ver>.csv"

• Section 7.2.4.18, "How to Configure the file_pension_comp_flg.csv"

• Section 7.2.4.19, "How to Configure Compensation Flag and Pension Compensation Flag for Pay Type Dimension"

• Section 7.2.4.20, "How to configure the domainValues_perf_nrml_rating_psft.csv"

• Section 7.2.4.21, "How to configure the domainValues_Wrkfc_IntlAssign_psft.csv"

• Section 7.2.4.22, "How to configure the domainValues_AbsenceEvent_Status_psft.csv"

7.2.4.1 Domain Values and CSV Worksheet Files for HR Analytics for PeopleSoft HCM

The following table lists the CSV worksheet files and the domain values for PeopleSoft HR Analytics in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\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_psft<ver>.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_psft<ver>.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_psft<ver>.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_psft<ver>.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

7.2.4.2 How to Configure the domainValues_Employee_Ethnic_Group_Code_psft.csv

1. 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 
   

2. Using a text editor, open the domainValues_Employee_Ethnic_Group_Code_psft.csv file in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the ETHNIC_GRP_CD to the ETHNIC_CODE column in the file. The data must be copied starting from the 6th line.

4. Map each Ethnic Code to one domain value.

5. Save and close the file.

7.2.4.3 How to Configure the domainValues_Employee_Sex_MF_psft.csv

1. 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 
   

2. Using a text editor, open the domainValues_Employee_Sex_MF_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the FIELDVALUE column to the SEX column in the file. The data must be copied starting from the 6th line.

4. Map each Sex Code to one domain value.

5. Save and close the file.

7.2.4.4 How to Configure the domainValues_Employee_Veteran_Status_Code_psft.csv

1. 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 
   

2. Using a text editor, open the domainValues_Employee_Veteran_Status_Code_psft.csv file located in $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the FIELDVALUE column to the VETERAN_STATUS_CODE column in the file. The data must be copied starting from the 6th line.

4. Map each Veteran Status Code to one domain value.

5. Save and close the file.

7.2.4.5 How to Configure the domainValues_Employment_Category_psft.csv

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.

1. 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
   

2. On the Informatica Server host computer, from the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_Employment_Category_psft.csv file in a text editor.

3. 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.

4. 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:

   For example:
   C, Any, EMP
   R, STD, EMP
   
   

5. Save and close the file.

7.2.4.6 How to Configure the domainValues_Flsa_psft.csv

To configure the domainValues_Flsa_psft.csv file.

1. 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
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_Flsa_psft.csv file in a text editor.

3. 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.

4. Map each FLSA_STATUS_CODE value to one domain value.

5. Save and close the file.

7.2.4.7 How to Configure the domainValues_Employment_Full_Time_Flg_psft.csv

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.

1. 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'
   

2. Using a text editor, open the domainValues_Employment_Full_Time_Flg_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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.

4. 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).

5. Save and close the file.

7.2.4.8 How to Configure the domainValues_Employment_Status_psft.csv

1. 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 
   

2. Using a text editor, open the domainValues_Employment_Full_Time_Flg_psft.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the FIELDVALUE to the EMPL_STATUS column in the file. The data must be copied starting from the 6th line.

4. Map each EMPL_STATUS to one domain value.

5. Save and close the file.

7.2.4.9 How to Configure the domainValues_Wrkfc_EventType_psft.csv

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 EndAdditional Assignment EndLayoffAssignment ChangePromotionRenew ContractAssignment StartInternational TransferTransferNew HireRehireInvoluntary TerminationRetirementResignation

Note:

If dashboard end users are using a dashboard containing HR Event Type data, they might see 'Unspecified' values in the columns, Event Group Code, Event Sub Group Code, Source Event Type and Source Event Reason. To avoid seeing see 'Unspecified' values in these columns, they should filter out Unspecified records. For example, to filter the Source Event Reason column they might specify the filter, HR Event Type.Source Event Reason <> 'Unspecified'.

A power user with Ad-hoc (Oracle BI EE Answers) access could change or apply the filter in a given existing report used on a dashboard. An end user could apply the filter in a dashboard, if the dashboard had a prompt for the HR Event Type.Event Group Code (or Description).

To configure the domainValues_Wrkfc_EventType_psft.csv file.

1. 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
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_Wrkfc_EventType_psft.csv file in a text editor.

3. Map each Event Type (ACTION/ACTION_REASON) 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 (ACTION / ACTION REASON) and Action Reason Description (DESCR) data are also extracted with the Event Type data to help you map the domain values.

4. 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 value Any. For examples on this configuration option, see the domainValues_Wrkfc_EventType_psft.csv file. 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. For examples on these configuration options, see the domainValues_Wrkfc_EventType_psft.csv file. 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_ora<ver>.csv file.
   Unassigned	Not Available	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. For examples on this configuration option, see TERM Unassigned in the domainValues_Wrkfc_EventType_psft.csv file.
   Not Applicable	Not Available	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	Not Available	To correctly identify hires and terminations: All actions that set the ps_job.hire_dt should be mapped to event group "HIRE" All actions that set the ps_job.termination_dt should be mapped to event group "TERM" 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.
   Identifying a Promotion Event	Not Available	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 example on this configuration option, see the domainValues_Wrkfc_EventType_psft.csv file.
   Identifying a Transfer Event	Not Available	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)	Not Available	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 TERM in the domainValues_Wrkfc_EventType_psft.csv file.

   
   

5. Save and close the file.

7.2.4.10 How to Configure the domainValues_Emp_EventType_Grp_Code_psft.csv

1. Identify the Event Types Group 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 = 'ACTION' 
    ORDER BY 1 
   

2. Using a text editor, open the domainValues_EMP_EventType_Grp_Code_psft.csv file in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the FIELDVALUE to the LOOKUP_TYPE_CODE. The data must be copied starting from the 6th line. Use commas to separate the entries.

4. Map each Event Type (LOOKUP_CODE) to one domain value for each domain column.

5. Save and close the file.

7.2.4.11 How to Configure the domainValues_HRPosition_Active_Pos_Flg_psft.csv

1. 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 
   

2. Using a text editor, open the domainValues_HRPosition_Active_Pos_Flg_psft.csv file in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the FIELDVALUE to the EFF_STATUS column in the file. The data must be copied starting from the 6th line.

4. Map each position EFF_STATUS to one domain value.

5. Save and close the file.

7.2.4.12 How to Configure the domainValues_Pay_Type_Grp_Code_psft.csv

The table below 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

1. To configure the domainValues_Pay_Type_Grp_Code_psft.csv

2. Using a text editor, open the domainValues_Pay_Type_Grp_Code_psft.csv file in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy the Classification Name and Element Name to the PAYTYPE_SOURCE. The data must be edited starting from the 6th line. Use commas to separate the entries.

4. 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'.

5. Save and close the file.

7.2.4.13 How to Configure the domainValues_Emp_EventType_EventReason_Code_psft.csv

1. 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 
   

2. Using a text editor, open the domainValues_Emp_EventType_EventReason_Code_psft.csv file, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy EVENT_REASON the to the EVENT_REASON column in the file. The data must be copied starting from the 6th line.

4. Map each EVENT_REASON to one domain value.

5. Save and close the file.

7.2.4.14 How to Configure the domainValues_Recruitment_Source_Type_psft<ver>.csv

This section explains how to configure the domainValues_Recruitment_Source_Type_psft<ver>.csv file.

1. 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 
   

2. Using a text editor, open the domainValues_Recruitment_Source_Type_psft <ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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.

4. 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.

5. Save and close the file.

7.2.4.15 How to Configure the domainValues_Requisition_Category_psft<ver>.csv (WIP)

This section explains how to configure the domainValues_Requisition_Category_psft<ver>.csv file.

1. 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') 
   

2. Using a text editor, open the domainValues_Requisition_Category_psft <ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

4. 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.

5. Save and close the file.

7.2.4.16 How to Configure the domainValues_Status_Vacancy_Recruitment_psft<ver>.csv

This section explains how to configure the domainValues_Status_Vacancy_Recruitment_psft<ver>.csv file.

1. 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') 
   

2. Using a text editor, open the domainValues_Status_Vacancy_Recruitment_psft<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

4. 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.

5. Save and close the file.

7.2.4.17 How to Configure the domainValues_Recruitment_Event_Reason_psft<ver>.csv

This section explains how to configure the domainValues_Recruitment_Event_Reason_psft<ver>.csv file.

1. 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') 
   

2. Using a text editor, open the domainValues_Recruitment_Event_Reason_psft<ver>.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line. Use commas to separate the entries.

4. 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.

5. Save and close the file.

7.2.4.18 How to Configure the file_pension_comp_flg.csv

1. 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 
   

2. Using a text editor, open the file file_pension_comp_flg_psft.csv, located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. Copy over the ERNCD and DESCR column into the file. The data must be copied starting from the sixth line.

4. Relate a Pension Compensation Flag and a Compensation Flag based on the nature of the Earnings Code.

5. 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

7.2.4.19 How to Configure Compensation Flag and Pension Compensation Flag for Pay Type Dimension

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

1. Edit the file 'file_pension_comp_flg_psft.csv' in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles) using in a text editor.

2. Enter the COMP_FLG and PENSION_COMP_FLG value against the correct Earnings Code.

3. Save the file.

7.2.4.20 How to configure the domainValues_perf_nrml_rating_psft.csv

This section explains how to configure the domainValues_perf_nrml_rating_psft.csv file. Utilizing 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:

1. 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
   

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles), open the domainValues_perf_nrml_rating_psft.csv file in a text editor.

3. 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.

4. 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 \PowerCenter8.6.x\server\infa_shared\SrcFiles).

5. Save and close the file.

Note:

You must perform this additional DAC parameter configuration.

To perform DAC task parameter configurations:

1. In DAC, query back the task name SDE_PSFT_PersistedStage_WorkforceEvent_Performance.

2. 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.

3. 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.

7.2.4.21 How to configure the domainValues_Wrkfc_IntlAssign_psft.csv

To configure the domainValues_Wrkfc_IntlAssign_psft.csv file.

1. 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;
   

2. Open the domainValues_Wrkfc_IntlAssign_psft.csv file using a text editor in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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 6th line.

4. 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).

7.2.4.22 How to configure the domainValues_AbsenceEvent_Status_psft.csv

To configure the domainValues_AbsenceEvent_Status_psft.csv file.

1. 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.

2. From the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\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

   
   

7.2.5 Configuration Steps for Oracle HR Analytics for Universal

Not applicable to Oracle BI Applications Version 7.9.6.1.

7.2.6 Configuration Steps for Controlling Your Data Set

This section contains additional configuration steps for Oracle HR Analytics.

• Section 7.2.6.1, "Configuration Steps for Oracle HR Analytics for All Source Systems"

• Section 7.2.6.2, "Configuration Steps for Oracle HR Analytics for Oracle EBS"

• Section 7.2.6.3, "Configuration Steps for Oracle HR Analytics for PeopleSoft"

• Section 7.2.6.4, "Configuration Steps for Oracle HR Analytics for Universal"

7.2.6.1 Configuration Steps for Oracle HR Analytics for All Source Systems

This section contains configuration steps that apply to all source systems. It contains the following topics:

• Section 7.2.6.1.1, "How to Aggregate the Payroll Table for Oracle HR Analytics"

7.2.6.1.1 How to Aggregate the Payroll Table 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:

1. 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'.

2. 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)

1. In DAC, go to the Design view.

2. Display the Tasks tab, and select the Task PLP_PayrollAggregate_Load.

3. Display the Parameters subtab, and add a parameter with the name $$GRAIN.

4. Depending upon the aggregation requirement, define the value as one of the following: 'DAY', 'WEEK', 'MONTH', 'QUARTER' or 'YEAR'.

5. Select Static as the parameter type.

6. Save the task.

7.2.6.2 Configuration Steps for Oracle HR Analytics for Oracle EBS

This section contains configuration steps that apply to Oracle EBS. It contains the following topics:

• Section 7.2.6.2.1, "Configuring Original Job Requisition Status"

• Section 7.2.6.2.2, "About Job Requisition Metrics in Oracle EBS"

7.2.6.2.1 Configuring Original Job Requisition Status

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:

1. 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'
   

2. Using a text editor, open the file_job_requisition_status_original_ora12.csv file located in the $PMServer\LkpFiles directory (for example, \PowerCenter8.6.x\server\infa_shared\LkpFiles).

3. 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.

4. 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_ora<ver>.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.

5. Save and close the file.

7.2.6.2.2 About Job Requisition Metrics in Oracle EBS

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 table below 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

7.2.6.3 Configuration Steps for Oracle HR Analytics for PeopleSoft

This section contains configuration steps that apply to PeopleSoft. It contains the following topics:

• Section 7.2.6.3.1, "How to Tune Performance for the Payroll Table for PeopleSoft HCM HR Analytics"

7.2.6.3.1 How to Tune Performance for the Payroll Table for PeopleSoft HCM HR Analytics

The Payroll mappings designed 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 requirement, there are two ways you can achieve better overall performance in the Payroll Analytics. These techniques are as follows:

• Inactivate Adjustments and Reversals, if not required, for a given pay period

• Defer calculating Adjustments and Reversals for some time, until needed.

To inactivate 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 inactivate those tasks in DAC.

1. In DAC, go to the Design view, and select the appropriate custom container.

2. Select the Tasks tab.

3. Deselect 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

4. Save the tasks.

5. Assemble the Subject Area HR- Payroll and build the Execution Plans that contain this Subject Area.

To defer Adjustment and Reversal calculations for Payroll

1. Inactivate Adjustment and Reversal calculations for Payroll and reassemble the Subject Area Payroll and rebuild the Execution Plans that contain this Subject Area.

2. When you choose to run these again, reactivate the tasks, and reassemble Subject Area (Payroll) and Execution Plan.

3. 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.

7.2.6.4 Configuration Steps for Oracle HR Analytics for Universal

Not applicable to Oracle BI Applications Version 7.9.6.1.