Loading OWE Tables
This section provides an overview of the data warehouse tables of primary concern to workforce analytic applications and discusses how to:
Load competency data.
Load staffing data.
Load job data.
Load employee compensation history data.
Understanding OWE Tables
You use the ETL process to load internal source data from the OWS staging tables into your OWE tables. The following table provides a brief overview of the OWE tables loaded by the ETL process that are of primary concern to workforce analytic applications. Important points about some of the fact (*_F00) tables are presented both in this table and in this section following the table. Important points about some of the dimension (*_D00) and reference (*_R00) tables are discussed in the next section after that.
This table is not a comprehensive list of all of the data warehouse tables in the OWE.
See My Oracle Support for the most complete, up-to-date documentation of the data warehouse tables. Where a discrepancy exists, the report delivered on My Oracle Support takes precedence.
See My Oracle Support for suggested table loading sequences.
After you've completed the ETL process, you must run the Currency Conversion job if, and only if, the data that you imported with ETL contains currency amounts, which require standardization into a base currency equivalent. Currency Conversion must be run as a standalone job within a jobstream.
OWE Table Name |
Source Tables |
Usage within Workforce Analytics |
---|---|---|
ACCOMP_D00 |
ACCOMP_TBL |
Workforce Planning |
BENEF_PLAN_TBL |
BENF_PLAN_TBL |
Workforce Rewards |
COMPANY_D00 |
COMPANY_TBL |
Workforce Planning |
COMPETENCY_D00 |
COMPETENCY_TBL |
Workforce Planning |
DEDUCTION_TBL |
DEDUCTION_TBL |
Workforce Rewards |
DEPARTMENT_TBL |
DEPT_TBL |
Workforce Rewards Workforce Planning |
EARNINGS_TBL |
EARNINGS_TBL |
Workforce Rewards |
EMPL_REVW_F00 |
EP_APPR EP_APPR_ROLE |
Workforce Rewards |
JOB_F00 |
JOB |
Workforce Rewards Workforce Planning |
JOB_INTRADY_F00 |
JOB GVT_JOB PER_ORG_ASGN |
Workforce Rewards |
JOBCD_TRNPR_D00 |
JOBCD_TRN_PROG |
Workforce Rewards |
JOBCODE_D00 |
JOBCODE_TBL |
Workforce Rewards Workforce Planning |
LOCATION_D00 |
LOCATION_TBL |
Workforce Rewards Workforce Planning |
MAJOR_TBL |
MAJOR_TBL |
Workforce Planning |
PERSONAL_D00 |
PERS_DATA_EFFDT NAMES PERSON |
Workforce Planning |
POSITION_DATA |
POSITION_DATA |
Workforce Rewards |
SAL_GRADE_D00 |
SAL_GRADE_TBL |
Workforce Rewards |
SAL_MTRXTBL_D00 |
SAL_MATRIX_TBL |
Workforce Rewards |
SAL_PLAN_R00 |
SAL_PLAN_TBL |
Workforce Rewards |
SAL_RATECD_D00 |
SAL_RATECD_TBL |
Workforce Rewards |
SAL_STEP_D00 |
SAL_STEP_TBL |
Workforce Rewards |
SCHOOL_D00 |
SCHOOL_TBL |
Workforce Planning |
SCHOOL_TYPE_D00 |
SCHOOL_TYPE_TBL |
Workforce Planning |
STAFFING_F00 |
HRS_JOB_OPENING HRS_JO_RQMT |
Workforce Rewards |
VC_PLAN_D00 |
VC_PLAN_TBL |
Workforce Rewards |
WA_ACMPLISH_F00 |
ACCOMPLISHMENTS |
Workforce Planning |
WA_COMP_HST_F00 |
|
Workforce Rewards |
WA_COMPTNCY_F00 |
|
Workforce Planning |
WA_COVG_CD_R00 |
COVRG_CD_TBL |
Workforce Rewards |
WA_REVWRTG_R00 |
REVW_RATING_TBL |
Workforce Rewards Workforce Planning |
WA_ROLE_D00 |
CM_ROLE |
Workforce Planning |
WA_ROLEACMP_D00 |
CM_ROLE CM_ROLE_ACCOMPS |
Workforce Planning |
WA_RTGMDL_R00 |
RATING_MDL_TBL |
Workforce Rewards Workforce Planning |
WA_TASK_D00 |
CM_ROLE |
Workforce Planning |
WA_TASKCMPT_D00 |
CM_ROLE_ACCOMPS |
Workforce Planning |
Note: Only some, not all, of the fields in the HRMS tables are used in the data warehouse tables. If an apparent discrepancy exists between the information on the previous chart and on My Oracle Support, the information in My Oracle Support takes precedence.
Loading Competency Data
The WA_CMPTN_EE_F00 table records employee-level competency data, and it is used in HCM Warehouse and Workforce Scorecard. In this table, the competency source field (WA_CMSOURCE) identifies the source of the competency data for an employee. The field is populated during data loading. The sources are the HRMS modules that use competencies: Plan Careers, Manage Competencies, and Performance Management. Valid values are Career Strength,Career Weakness,Competencies,NVQ Units Progression, andPerformance Management.
The WA_CMPTNCY_F00 table records competency ratings for each employee, applicant, or contractor evaluation, and it is used in Workforce Planning. The sources are the HRMS modules: Manage Competencies and Performance Management (employee appraisals). Only the official, approved rating from the employee review is selected.
This list shows the main competency-related dimension tables and fact data warehouse tables used by Workforce Planning:
WA_CMPTNCY_F00
WA_ACMPLISH_F00
WA_ACMP_MAJ_F00
WA_TASK_D00
WA_TASKCMPT_D00
WA_TASK_ACMP_D00
WA_ROLE_D00
WA_ROLECMPT_D00
WA_ROLEACMP_D00
WA_ROLE_TASK_D00
Loading Staffing Data
During data loading, the system imports data for the Staffing table (STAFFING_F00) from two HRMS tables. When the data comes from the JOB_REQUISITION table, the value for the EPM field REQ_NEW_POS is copied over directly as Y (yes) orN (no). When the data is imported from the POSITION_DATA table, the EPM field REQ_NEW_POS value is derived using the following mapping logic:
If the HRMS table POSITION_DATA.ACTION_REASON field value is NEW, then the value for the EPM field REQ_NEW_POS is set toY. Otherwise, the value for REQ_NEW_POS is set toN.
Loading Job Data
Before you use ETL to load the OWE table JOB_F00, you must have completed setup of online pages for the following tables:
CURRENCY_CD_TBL
FREQUENCY_TBL
SETID_TBL
WA_LOCN_MAP_DFN
WA_ACTN_RSN_DFN
BUS_UNIT_TBL_PF
BUS_UNIT_SRC_PF
During data loading, the system creates an additional row in JOB_F00 for employees who have a more recent row in JOB_F00 with another business unit to indicate that the employee is no longer active for the first business unit. For example, assume that the system imports two rows of data for an employee with the following business units, effective dates, and effective statuses:
CORP1, 1/1999, Active
CORP2, 2/2000, Active
Then the system will insert a third row of data into JOB_F00 to indicate that the employee is no longer active for the first business unit:
CORP1, 2/2000, Inactive
Setting the WA_VOLUNTARY_FLAG Field
The ACTION, ACTION_REASON, and WA_VOLUNTARY_FLAG fields are in the JOB_F00 table. WA_VOLUNTARY_FLAG is a Workforce Analytics field that you map online prior to data loading. The mapping identifies whether each action and reason combination imported from HRMS results in a voluntary action, involuntary action, or no action.
Importing Effective-Sequenced Job Actions
During data loading, the system imports the maximum effective sequence row for each effective date from the HRMS Job Data table and places that row in JOB_F00. In contrast, the system loads all effective-sequenced rows from the HRMS Job Data table into JOB_INTRADY_F00.
Importing Review Rating Data to JOB_F00
Normally during data loading, the system populates the EPM review rating field (JOB_F00.REVIEW_RATING) with HRMS data. When data is imported into the JOB_F00 table from the HRMS Job Data table, the loading process verifies that only valid values are supplied for REVIEW_RATING by comparing them with values in the lookup table WA_REVWRTG_R00. If a corresponding value isn't on the WA_REVWRTG_R00 table, then some rows of data that you might expect to see for that employee may not be imported. You must verify that all values for a given REVIEW_RATING have a corresponding row on WA_REVWRTG_R00.
Streamlining JOB_F00 and Row-Level Security
If your implementation includes row-level security at the employee level, based on JOB_F00 and PERSONAL_D00, then load the JOB_F00 and PERSONAL_D00 tables before setting up security.
Loading Employee Compensation History Data
The EPM compensation code field (WA_COMPCODE) on the Compensation Code table (COMPCODE_D00) categorizes types of compensation. These codes are used to relate compensation to nodes on the COMPCODE tree. Prior to data loading earnings, deductions, benefits plans, and pay item types from HRMS tables are mapped to these compensation codes using online pages. During data loading, employee compensation history is imported into the employee Compensation History table (WA_COMP_HST_F00).