Oracle^® Business Intelligence Applications Installation and Configuration Guide > Configuring Oracle Workforce Analytics >

Configuring Domain Values and CSV Worksheet Files for Oracle Workforce Analytics

You configure the CSV files in Oracle Workforce Analytics by mapping domain values to columns. The CSV worksheet files for Oracle Workforce Analytics are in the $pmserver\LkpFiles folder.

For more information on configuring domain values with CSV worksheet files, see About Domain Values and Configuring the Domain Value Set with CSV Worksheet Files.

This section contains the following tasks:

• To configure the domainValues_Employee_Ethnic_Group_Code_ora11i.csv
• To configure the domainValues_Employee_Sex_MF_ora11i.csv
• To configure the domainValues_Employee_Veteran_Status_Code_ora11i.csv
• To configure the domainValues_Employment_Cat_ora11i.csv
• To configure the domainValues_Employment_Exempt_Flg_ora11i.csv
• To configure the domainValues_Employment_Full_Time_Flg_ora11i.csv
• To configure the domainValues_Employment_Status_ora11i.csv
• To configure the domainValues_EventTypes_ora11i.csv
• To configure the domainValues_HRPosition_Active_Pos_Flg_ora11i.csv
• To configure the domainValues_HRPosition_Exempt_Flg_ora11i.csv
• To configure the domainValues_Job_Eeo_Cat_Code_ora11i.csv
• To configure the domainValues_Job_Flsa_Stat_Code_ora11i.csv
• To configure the domainValues_Pay_Type_Grp_Code_ora11i.csv
• To configure the domainValues_Pay_Type_Flg_ora11i.csv

To configure the domainValues_Employee_Ethnic_Group_Code_ora11i.csv

1. Identify the Ethnic Group Codes in your Oracle 11i 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. Open the domainValues_Employee_Ethnic_Group_Code_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
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.

   For more information on Ethnic Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Employee_Sex_MF_ora11i.csv

1. 1) Identify the Sex Codes in your Oracle 11i source system by using the following SQL:

   SELECT DISTINCT SEX FROM PER_ALL_PEOPLE_F ORDER BY 1

2. Open the domainValues_Employee_Sex_MF_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
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.

   For more information on Sex Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Employee_Veteran_Status_Code_ora11i.csv

1. Identify the Veteran Status Codes in your Oracle 11i source system by using the following SQL:

   SELECT DISTINCT PER_INFORMATION5 FROM PER_ALL_PEOPLE_F

   WHERE PER_INFORMATION5 in ('NOTVET', 'OTEDV', 'VET', 'VETDIS', 'VIETVET', 'VIETVETDIS')

   ORDER BY 1

2. Open the domainValues_Employee_Veteran_Status_Code_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
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.

   For more information on Veteran Status Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Employment_Cat_ora11i.csv

1. Identify the User Person Types in your Orace11i 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','OTHER','EMP_APL','EX_EMP','EX_EMP_APL','RETIREE','PRTN')

   ORDER BY 1, 2

   NOTE:  If you have modified the Payroll filter, you need to also modify the SQL. For more information on modifying Payroll filters, see Modifying the Workforce Payroll Filters.

2. Open the domainValues_Employment_Cat_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
3. Copy the User Person Types to the USER_PERSON_TYPE column in the file. The data must be copied starting from the 6th line.
4. Map each User Person Type to one domain value. System Person Types are also extracted with User Person Type to help you map the domain values. Do not copy the System Person types in the CSV file.

   For more information on Employment Category domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Employment_Exempt_Flg_ora11i.csv

1. Identify the FLSA Statuses in your Orace11i source system by using the following SQL:

   SELECT DISTINCT JOB_INFORMATION3 FROM PER_JOBS

   ORDER BY 1

2. Open the domainValues_Employment_Exempt_Flg_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
3. Copy the JOB_INFORMATION3 to the FLSA_STATUS_CODE column in the file. The data must be copied starting from the 6th line.
4. Map each FLSA_STATUS_CODE to one domain value.

   For more information on Exempt Flag domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Employment_Full_Time_Flg_ora11i.csv

1. Identify the Employment Categories in your Orace11i source system by using the following SQL:

   SELECT DISTINCT EMPLOYMENT_CATEGORY FROM PER_ALL_ASSIGNMENTS_F ORDER BY 1

2. Open the domainValues_Employment_Full_Time_Flg_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
3. Copy the Employment Category to the EMPLOYMENT_CATEGORY column in the file. The data must be copied starting from the 6th line.
4. Map each EMPLOYMENT_CATEGORY to one domain value.

   For more information on Full Time Flag domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Employment_Status_ora11i.csv

1. Identify the Per System Statuses in your Orace11i source system by using the following SQL:

   SELECT DISTINCT PER_SYSTEM_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES

   ORDER BY 1

2. Open the domainValues_Employment_Status_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
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.

   For more information on Employment Status domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_EventTypes_ora11i.csv

1. Identify the Event Types in your Orace11i source system by using the following SQL:

   SELECT DISTINCT LOOKUP_TYPE, LOOKUP_CODE, MEANING

   FROM FND_LOOKUP_VALUES

   WHERE LOOKUP_TYPE IN

   ('EMP_ASSIGN_REASON',

   'LEAV_REAS',

   'PROPOSAL_REASON')

   ORDER BY 1, 2, 3

2. Open the domainValues_EventTypes_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
3. Copy the Lookup Type, Lookup Code and Meaning to the LOOKUP_TYPE, LOOKUP_CODE, and MEANING columns in the file respectively. 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 of the 3 domain columns — W_EVENT_GRP_CODE, W_EVENT_SUBG_CODE, and W_EVENT_REASON_CODE. Event Category (LOOKUP_TYPE) and Event Description (MEANING) are also extracted with Event Type to help you map the domain values.

   For more information on Event Type domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_HRPosition_Active_Pos_Flg_ora11i.csv

1. Identify the Position Statuses in your Orace11i source system by using the following SQL:

   SELECT DISTINCT STATUS FROM HR_ALL_POSITIONS_F

   ORDER BY 1

2. Open the domainValues_HRPosition_Active_Pos_Flg_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
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.

   For more information on Active Position Flag domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_HRPosition_Exempt_Flg_ora11i.csv

1. Identify the FLSA Statuses in your Orace11i source system by using the following SQL:

   SELECT DISTINCT JOB_INFORMATION3 FROM PER_JOBS

   ORDER BY 1

2. Open the domainValues_HRPosition_Exempt_Flg_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
3. Copy the JOB_INFORMATION3 to the FLSA_STATUS_CODE column in the file. The data must be copied starting from the 6th line.
4. Map each FLSA_STATUS_CODE to one domain value.

   For more information on Exempt Flag domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Job_Eeo_Cat_Code_ora11i.csv

1. Identify the EEO Job Categories in your Orace11i source system by using the following SQL:

   SELECT DISTINCT JOB_INFORMATION1 FROM PER_JOBS

   ORDER BY 1

2. Open the domainValues_Job_Eeo_Cat_Code_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
3. Copy the JOB_INFORMATION1 to the EEO_JOB_CAT_CODE column in the file. The data must be copied starting from the 6th line.
4. Map each EEO_JOB_CAT_CODE to one domain value.

   For more information on Equal Employment Opportunity (EEO) domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Job_Flsa_Stat_Code_ora11i.csv

1. Identify the FLSA Statuses in your Orace11i source system by using the following SQL:

   SELECT DISTINCT JOB_INFORMATION3 FROM PER_JOBS

   ORDER BY 1

2. Open the domainValues_Job_Flsa_Stat_Code_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
3. Copy the JOB_INFORMATION3 to the FLSA_STAT_CODE column in the file. The data must be copied starting from the 6th line.
4. Map each FLSA_STAT_CODE to one domain value.

   For more information on Fair Labor Standards Act (FLSA) Status Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Pay_Type_Grp_Code_ora11i.csv

1. Identify the Pay Elements in your Orace11i 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. Open the domainValues_Pay_Type_Grp_Code_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
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.
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".

   For more information on Pay Type Group Code domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.

To configure the domainValues_Pay_Type_Flg_ora11i.csv

1. Identify the Costing (Debit or Credit) in your Orace11i source system by using the following SQL:

   SELECT DISTINCT COSTING_DEBIT_OR_CREDIT FROM PAY_ELEMENT_CLASSIFICATIONS

   ORDER BY 1

2. Open the domainValues_Pay_Type_Flg_ora11i.csv file using Microsoft WordPad or Notepad in the $pmserver\lkpfiles folder.
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.

   For more information on Pay Type Flag domain values, see Oracle Business Analytics Warehouse Data Model Reference.

   NOTE:  Incorrect mappings result in inaccurate calculations of Oracle Business Intelligence metrics.

5. Save and close the file.