Skip to Main Content
Return to Navigation

Loading OWE Tables

This section provides an overview of the data warehouse tables of primary concern to workforce analytic applications and discusses how to:

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

  • GP_RSLT_ERN_DED

  • GP_PYE_PRC_STAT

  • PAY_EARNINGS

  • PAY_OTH_EARNS

  • PAY_CHECK

  • PAY_DEDUCTION

  • TOTAL_DED

  • HEALTH_BENEFIT

  • LIFE_ADD_BEN

  • DISABILITY_BEN

  • FSA_BENEFIT

  • SAVINGS_PLAN

  • VACATION_BEN

  • LEAVE_PLAN

  • CAR_PLAN

  • RTRMNT_PLAN

  • PENSION_PLAN

Workforce Rewards

WA_COMPTNCY_F00

  • CM_EVALUATIONS

  • COMPETENCIES

  • EP_APPR

  • EP_APPR_ITEM

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