2Tables

Overview

This chapter provides information about tables within Oracle Fusion HCM Analytics warehouse and their columns, primary keys denoted as key columns, datatypes, and the referred table and column names.

Global Human Resources

DW_ACTION_D

Action Dimension

Details

Module: Global Human Resources

Key Columns

ACTION_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ACTION_ID NUMBER 38 0 True Action Identifier DW_ACTION_D_TL ACTION_ID
SOURCE_RECORD_ID VARCHAR2 64 unique key
ACTION_CODE VARCHAR2 32 Action Code
ACTION_TYPE_ID NUMBER 38 0 Action Type Identifier
ACTION_TYPE_CODE VARCHAR2 32 Action Type Code
TERMINATION_TYPE VARCHAR2 256 Termination Category
REORGANIZATION_FLAG VARCHAR2 256 Flag to indicate if action is reorganization related

DW_ACTION_D_TL

Stores Action Name

Details

Module: Global Human Resources

Key Columns

ACTION_ID, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ACTION_ID NUMBER 38 0 True Action Identifier
LANGUAGE VARCHAR2 16 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
ACTION_NAME VARCHAR2 128 Name of the Action
ACTION_DESCRIPTION VARCHAR2 2048 Description of the Action

DW_ACTION_REASON_D

Action Reason Dimension

Details

Module: Global Human Resources

Key Columns

ACTION_REASON_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ACTION_REASON_ID NUMBER 38 0 True Action reason identifier DW_ACTION_REASON_D_TL ACTION_REASON_ID
SOURCE_RECORD_ID VARCHAR2 64 unique key
ACTION_REASON_CODE VARCHAR2 32 Action reason code

DW_ACTION_REASON_D_TL

Stores Action Reason Name

Details

Module: Global Human Resources

Key Columns

ACTION_REASON_ID, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ACTION_REASON_ID NUMBER 38 0 True Action Reason Identifier
LANGUAGE VARCHAR2 16 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
ACTION_REASON_NAME VARCHAR2 128 Name of the Action Reason

DW_ACTION_TYPE_D_TL

Stores Action Type Name

Details

Module: Global Human Resources

Key Columns

LANGUAGE, ACTION_TYPE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ACTION_TYPE_ID NUMBER 38 0 True Action Type Identifier
LANGUAGE VARCHAR2 16 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
ACTION_TYPE_NAME VARCHAR2 64 Name of the Action Type

DW_ASSIGNMENT_CATEGORY_D_TL

Stores Assignment Category Name

Details

Module: Global Human Resources

Key Columns

ASSIGNMENT_CATEGORY, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_CATEGORY VARCHAR2 32 True Assignment Category
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
ASSIGNMENT_CATEGORY_NAME VARCHAR2 128 Name of the Assignment Category
ASSIGNMENT_CATEGORY_DESCRIPTION VARCHAR2 256 Description of the Assignment Category

DW_ASSIGNMENT_STATUS_D_TL

Stores Assignment Status Name

Details

Module: Global Human Resources

Key Columns

ASSIGNMENT_STATUS_TYPE_ID, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_STATUS_TYPE_ID NUMBER 38 0 True Assignment Status Identifier
LANGUAGE VARCHAR2 16 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
ASSIGNMENT_STATUS_NAME VARCHAR2 128 Name of the Assignment Status

DW_CITIZENSHIP_STATUS_D_TL

This dimension captures the Citizenship Status translation information

Details

Module: Global Human Resources

Key Columns

LANGUAGE, CITIZENSHIP_STATUS

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
CITIZENSHIP_STATUS VARCHAR2 32 True Citizenship Status
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
CITIZENSHIP_STATUS_NAME VARCHAR2 128 Name of the Citizenship Status
CITIZENSHIP_STATUS_DESCRIPTION VARCHAR2 256 Description of the Citizenship Status

DW_CMP_ASG_SALARY_F

Salary holds details of salary proposals for Employee or Contingent worker assignments.

Details

Module: Global Human Resources

Key Columns

SALARY_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
SALARY_ID NUMBER 38 0 True Primary Key sequence for Salary in Fusion Source.
SOURCE_RECORD_ID VARCHAR2 64 Unique Source Record Identifier This column contains SALARY_ID.
ASSIGNMENT_ID NUMBER 38 0 Person's Work term assignment ID
SALARY_BASIS_ID NUMBER 38 0 Salary Basis ID, it is the entity that establishes the period of time for which an employee's salary is quoted, typically either Monthly, Hourly or Annually. DW_SALARY_BASIS_D SALARY_BASIS_ID
EFFECTIVE_START_DATE DATE Salary Start Date.
EFFECTIVE_END_DATE DATE Salary End Date.
SALARY_APPROVED VARCHAR2 32 Yes or No flag if the Salary is approved or not
ACTION_OCCURRENCE_ID NUMBER 38 0 It defines the action occurrences for an action
ACTION_ID NUMBER 38 0 Actions Identifier DW_ACTION_D ACTION_ID
ACTION_REASON_ID NUMBER 38 0 Actions reason identifier DW_ACTION_REASON_D ACTION_REASON_ID
SALARY_AMOUNT NUMBER Salary Amount entered based on the Salary Basis
CURRENCY_CODE VARCHAR2 16 Currency of salary amount
DW_DATE_FROM DATE Warehouse Effective From Date for the Salary Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Salary Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the salary to be brought into the warehouse based on initial extract date set by user.

DW_DISABILITY_CATEGORY_D_TL

This dimension captures the Disability Category translation information

Details

Module: Global Human Resources

Key Columns

DISABILITY_CATEGORY, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
DISABILITY_CATEGORY VARCHAR2 32 True Disability Category
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
DISABILITY_CATEGORY_NAME VARCHAR2 128 Name of the Disability Category
DISABILITY_CATEGORY_DESCRIPTION VARCHAR2 256 Description of the Disability Category

DW_DISABILITY_REASON_D_TL

This dimension captures the Disability Reason translation information

Details

Module: Global Human Resources

Key Columns

DISABILITY_REASON, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
DISABILITY_REASON VARCHAR2 32 True Disability Reason
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
DISABILITY_REASON_NAME VARCHAR2 128 Name of the Disability Reason
DISABILITY_REASON_DESCRIPTION VARCHAR2 256 Description of the Disability Reason

DW_DISABILITY_STATUS_D_TL

This dimension captures the Disability Status translation information

Details

Module: Global Human Resources

Key Columns

LANGUAGE, DISABILITY_STATUS

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
DISABILITY_STATUS VARCHAR2 32 True Disability Status
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
DISABILITY_STATUS_NAME VARCHAR2 128 Name of the Disability Status
DISABILITY_STATUS_DESCRIPTION VARCHAR2 256 Description of the Disability Status

DW_ETHNICITY_D_TL

This dimension captures the Ethnicity translation information

Details

Module: Global Human Resources

Key Columns

ETHNICITY, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ETHNICITY VARCHAR2 32 True Ethnicity
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
ETHNICITY_NAME VARCHAR2 128 Name of the Ethnicity
ETHNICITY_DESCRIPTION VARCHAR2 256 Description of the Ethnicity

DW_GRADE_RATE_CURRENT_D

It stores current records for Rate definitions.

Details

Module: Global Human Resources

Key Columns

RATE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RATE_ID NUMBER 38 0 True System generated primary key column
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains RATE_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
RATE_TYPE VARCHAR2 64 Stores rate type, whether it's Salary/Bonus
RATE_OBJECT_TYPE VARCHAR2 64 Stores type "GRADE"
RATE_UOM VARCHAR2 64 Unit of Measure for the rate. Defaulting with M(Money)
RATE_FREQUENCY VARCHAR2 64 Frequency of the rate : Annual/Monthly/Hourly
CURRENCY_CODE VARCHAR2 16 Currency Code for the rate
LEGISLATION_CODE VARCHAR2 64 Legislation code
ACTIVE_STATUS VARCHAR2 64 Status of the rate : Active/InActive
ANNUALIZATION_FACTOR NUMBER Annualization Factor value Eg : 1 for Annual, 12 for monthly, 2080 for hourly, manually provided value in the UI for Payroll period

DW_GRADE_RATE_D

It stores the Rate definitions for a specific Grade.

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, EFFECTIVE_START_DATE, RATE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RATE_ID NUMBER 38 0 True System generated primary key column
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains RATE_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
RATE_TYPE VARCHAR2 64 Stores rate type, whether it's Salary/Bonus
RATE_OBJECT_TYPE VARCHAR2 64 Stores type "GRADE"
RATE_UOM VARCHAR2 64 Unit of Measure for the rate. Defaulting with M(Money)
RATE_FREQUENCY VARCHAR2 64 Frequency of the rate : Annual/Monthly/Hourly
CURRENCY_CODE VARCHAR2 16 Currency Code for the rate
LEGISLATION_CODE VARCHAR2 64 Legislation code
ACTIVE_STATUS VARCHAR2 64 Status of the rate : Active/InActive
ANNUALIZATION_FACTOR NUMBER Annualization Factor value Eg : 1 for Annual, 12 for monthly, 2080 for hourly, manually provided value in the UI for Payroll period

DW_GRADE_RATE_VALUE_CURRENT_D

It stores current records for Grade Rate values.

Details

Module: Global Human Resources

Key Columns

RATE_VALUE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RATE_VALUE_ID NUMBER 38 0 True System generated primary key column
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains RATE_VALUE_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
RATE_ID NUMBER 38 0 Rate definition identifier
RATE_OBJECT_TYPE VARCHAR2 32 Stores type "GRADE"
RATE_OBJECT_ID NUMBER 38 0 Stores grade_id for grade rate value. DW_PAY_GRADE_CURRENT_D GRADE_ID
MINIMUM VARCHAR2 32 Stores minimum value for a grade rate
MAXIMUM VARCHAR2 32 Stores maximum value for a grade rate
MID_VALUE VARCHAR2 32 Stores mid value(a value between minimum and maximum) for a grade rate
LEGISLATION_CODE VARCHAR2 64 Legislation code

DW_GRADE_RATE_VALUE_D

It stores the Grade Rate values for a specific Grade.

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, EFFECTIVE_START_DATE, RATE_VALUE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RATE_VALUE_ID NUMBER 38 0 True System generated primary key column
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains RATE_VALUE_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
RATE_ID NUMBER 38 0 Rate definition identifier
RATE_OBJECT_TYPE VARCHAR2 32 Stores type "GRADE"
RATE_OBJECT_ID NUMBER 38 0 Stores grade_id for grade rate value.#1 DW_PAY_GRADE_D GRADE_ID
MINIMUM VARCHAR2 32 Stores minimum value for a grade rate
MAXIMUM VARCHAR2 32 Stores maximum value for a grade rate
MID_VALUE VARCHAR2 32 Stores mid value(a value between minimum and maximum) for a grade rate
LEGISLATION_CODE VARCHAR2 64 Legislation code
Notes

#1 DW_PAY_GRADE_D is date effective. Along with GRADE_ID join on dates.

DW_HIGHEST_EDUCATION_LEVEL_D_TL

This dimension captures the Highest Education Level translation information

Details

Module: Global Human Resources

Key Columns

HIGHEST_EDUCATION_LEVEL, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
HIGHEST_EDUCATION_LEVEL VARCHAR2 32 True Highest Education Level
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
HIGHEST_EDUCATION_LEVEL_NAME VARCHAR2 128 Name of the Highest Education Level
HIGHEST_EDUCATION_LEVEL_DESCRIPTION VARCHAR2 256 Description of the Highest Education Level

DW_JOB_CURRENT_D

This stores jobs current record.

Details

Module: Global Human Resources

Key Columns

JOB_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_ID NUMBER 38 0 True Transaction system generated primary key column.For translation refer table DW_JOB_D_CURRENT_TL and join with this field. DW_JOB_D_CURRENT_TL JOB_ID
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE from base table.
JOB_CODE VARCHAR2 32 Code of the job
JOB_FAMILY_ID NUMBER 38 0 Identifier of the job family. #2 DW_JOB_FAMILY_CURRENT_D JOB_FAMILY_ID
ACTIVE_STATUS VARCHAR2 32 Indicates if a job is active or inactive.
MANAGER_LEVEL VARCHAR2 32 Indicates the management level of the job.For translation refer table DW_JOB_MANAGER_LEVEL_D_TL and join with this field. DW_JOB_MANAGER_LEVEL_D_TL MANAGER_LEVEL
MED_CHECKUP_REQ VARCHAR2 32 Indicates if the job requires medical checkup.
JOB_FUNCTION_CODE VARCHAR2 32 Code of the job function. For translation refer table DW_JOB_FUNCTION_D_TL and join with this field. DW_JOB_FUNCTION_D_TL JOB_FUNCTION_CODE
PROGRESSION_JOB_ID NUMBER 38 0 Next possible job during promotion

DW_JOB_D

DW_JOB_D stores jobs that have been defined for an Enterprise defining role an employee can perform in the enterprise.

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, EFFECTIVE_START_DATE, JOB_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_ID NUMBER 38 0 True Transaction system generated primary key column.For translation refer table DW_JOB_D_TL and join with this field.#1 DW_JOB_D_TL JOB_ID
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
JOB_CODE VARCHAR2 32 Code of the job
JOB_FAMILY_ID NUMBER 38 0 Identifier of the job family. #2 DW_JOB_FAMILY_D JOB_FAMILY_ID
ACTIVE_STATUS VARCHAR2 32 Indicates if a job is active or inactive.
MANAGER_LEVEL VARCHAR2 32 Indicates the management level of the job.For translation refer table DW_JOB_MANAGER_LEVEL_D_TL and join with this field. DW_JOB_MANAGER_LEVEL_D_TL MANAGER_LEVEL
MED_CHECKUP_REQ VARCHAR2 32 Indicates if the job requires medical checkup.
JOB_FUNCTION_CODE VARCHAR2 32 Code of the job function. For translation refer table DW_JOB_FUNCTION_D_TL and join with this field. DW_JOB_FUNCTION_D_TL JOB_FUNCTION_CODE
PROGRESSION_JOB_ID NUMBER 38 0 Next possible job during promotion
Notes

#1 DW_JOB_D_TL is date effective. Along with JOB_ID join on dates.

#2 DW_JOB_FAMILY_D is date effective. Along with JOB_FAMILY_ID join on dates.

DW_JOB_D_CURRENT_TL

It contains translation of JOB ID of DW_JOB_CURRENT_D

Details

Module: Global Human Resources

Key Columns

JOB_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_ID NUMBER 38 0 True Job ID of Job generated in transaction system.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
JOB_NAME VARCHAR2 256 Name for the Job

DW_JOB_D_TL

DW_JOB_D_TL contains translation of JOB ID of DW_JOB_D

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, LANGUAGE, EFFECTIVE_START_DATE, JOB_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_ID NUMBER 38 0 True Job ID of Job generated in transaction system.
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
LANGUAGE VARCHAR2 16 True Indicates the code of the language into which the contents of the translatable columns are translated.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
JOB_NAME VARCHAR2 256 Name for the Job

DW_JOB_FAMILY_CURRENT_D

It holds Job Families. Job Families enable identification of related jobs that may not reflect the same attributes as other jobs in the same grouping. It holds only current records.

Details

Module: Global Human Resources

Key Columns

JOB_FAMILY_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_FAMILY_ID NUMBER 38 0 True Transaction system generated primary key column.For translation refer table DW_JOB_FAMILY_D_CURRENT_TL joining this ID. DW_JOB_FAMILY_D_CURRENT_TL JOB_FAMILY_ID
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_FAMILY_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
BUSINESS_GROUP_ID NUMBER 38 0 Identifier of Enterprise, used for multi-tenancy partitioning.
ACTIVE_STATUS VARCHAR2 32 Indicates if a job family is active or inactive.
JOB_FAMILY_CODE VARCHAR2 256 Code for Job Family

DW_JOB_FAMILY_D

DW_JOB_FAMILY_D holds Job Families. Job Families enable identification of related jobs that may not reflect the same attributes as other jobs in the same grouping

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, EFFECTIVE_START_DATE, JOB_FAMILY_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_FAMILY_ID NUMBER 38 0 True Transaction system generated primary key column.For translation refer table DW_JOB_FAMILY_D_TL joining this ID.#1 DW_JOB_FAMILY_D_TL JOB_FAMILY_ID
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_FAMILY_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
BUSINESS_GROUP_ID NUMBER 38 0 Identifier of Enterprise, used for multi-tenancy partitioning.
ACTIVE_STATUS VARCHAR2 32 Indicates if a job family is active or inactive.
JOB_FAMILY_CODE VARCHAR2 256 Code for Job Family
Notes

#1 DW_JOB_FAMILY_D_TL is date effective. Along with JOB_FAMILY_ID join on dates.

DW_JOB_FAMILY_D_CURRENT_TL

It contains current records of translations for JOB FAMILY ID of DW_JOB_FAMILY_CURRENT_D

Details

Module: Global Human Resources

Key Columns

JOB_FAMILY_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_FAMILY_ID NUMBER 38 0 True Job Family ID of Job generated in transaction system.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_FAMILY_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
JOB_FAMILY_NAME VARCHAR2 256 Name for the Job family

DW_JOB_FAMILY_D_TL

DW_JOB_FAMILY_D_TL contains translation of JOB FAMILY ID of DW_JOB_FAMILY_D

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, EFFECTIVE_START_DATE, LANGUAGE, JOB_FAMILY_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_FAMILY_ID NUMBER 38 0 True Job Family ID of Job generated in transaction system.
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
LANGUAGE VARCHAR2 16 True Indicates the code of the language into which the contents of the translatable columns are translated.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_FAMILY_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
JOB_FAMILY_NAME VARCHAR2 256 Name for the Job family

DW_JOB_FUNCTION_D_TL

Stores Job Function Name

Details

Module: Global Human Resources

Key Columns

JOB_FUNCTION_CODE, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
JOB_FUNCTION_CODE VARCHAR2 32 True Job Function Identifier
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
JOB_FUNCTION_NAME VARCHAR2 128 Name of the Job Function
JOB_FUNCTION_DESCRIPTION VARCHAR2 256 Description of the Job Function

DW_JOB_MANAGER_LEVEL_D_TL

It stores Job Manager Level Name

Details

Module: Global Human Resources

Key Columns

MANAGER_LEVEL, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
MANAGER_LEVEL VARCHAR2 32 True Manager Level Identifier
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
MANAGER_LEVEL_NAME VARCHAR2 128 Name of the Manager Level
MANAGER_LEVEL_DESCRIPTION VARCHAR2 256 Description of the Manager Level

DW_LEGISLATION_D_TL

This dimension captures the legislation translation information

Details

Module: Global Human Resources

Key Columns

LEGISLATION_CODE, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
LEGISLATION_CODE VARCHAR2 16 True legislation Code
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
LEGISLATION_NAME VARCHAR2 128 Name of the legislation Code
LEGISLATION_DESCRIPTION VARCHAR2 4000 Description of the legislation Code

DW_MANAGER_CF_TD_CURRENT_DH

Manager Top Down Column Flattened Hierarchy

Details

Module: Global Human Resources

Key Columns

MANAGER_TYPE , PERSON_ID , ENTERPRISE_ID , ASSIGNMENT_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier DW_PERSON_NAME_D PERSON_ID
ASSIGNMENT_ID NUMBER 38 0 True Assignment Identifier
MANAGER_TYPE VARCHAR2 32 True Type of Manager
ENTERPRISE_ID NUMBER 38 0 True Manager Person's Enterprise
SOURCE_RECORD_ID VARCHAR2 256 Unique Identifier
LEVEL1_MANAGER_ID NUMBER 38 0 Level1 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL1_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level1 Manager Assignment Identifier
LEVEL2_MANAGER_ID NUMBER 38 0 Level2 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL2_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level2 Manager Assignment Identifier
LEVEL3_MANAGER_ID NUMBER 38 0 Level3 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL3_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level3 Manager Assignment Identifier
LEVEL4_MANAGER_ID NUMBER 38 0 Level4 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL4_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level4 Manager Assignment Identifier
LEVEL5_MANAGER_ID NUMBER 38 0 Level5 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL5_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level5 Manager Assignment Identifier
LEVEL6_MANAGER_ID NUMBER 38 0 Level6 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL6_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level6 Manager Assignment Identifier
LEVEL7_MANAGER_ID NUMBER 38 0 Level7 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL7_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level7 Manager Assignment Identifier
LEVEL8_MANAGER_ID NUMBER 38 0 Level8 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL8_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level8 Manager Assignment Identifier
LEVEL9_MANAGER_ID NUMBER 38 0 Level9 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL9_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level9 Manager Assignment Identifier
LEVEL10_MANAGER_ID NUMBER 38 0 Level10 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL10_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level10 Manager Assignment Identifier
LEVEL11_MANAGER_ID NUMBER 38 0 Level11 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL11_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level11 Manager Assignment Identifier
LEVEL12_MANAGER_ID NUMBER 38 0 Level12 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL12_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level12 Manager Assignment Identifier
LEVEL13_MANAGER_ID NUMBER 38 0 Level13 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL13_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level13 Manager Assignment Identifier
LEVEL14_MANAGER_ID NUMBER 38 0 Level14 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL14_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level14 Manager Assignment Identifier
LEVEL15_MANAGER_ID NUMBER 38 0 Level15 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL15_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level15 Manager Assignment Identifier
LEVEL16_MANAGER_ID NUMBER 38 0 Level16 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL16_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level16 Manager Assignment Identifier
LEVEL17_MANAGER_ID NUMBER 38 0 Level17 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL17_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level17 Manager Assignment Identifier
LEVEL18_MANAGER_ID NUMBER 38 0 Level18 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL18_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level18 Manager Assignment Identifier
LEVEL19_MANAGER_ID NUMBER 38 0 Level19 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL19_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level19 Manager Assignment Identifier
LEVEL20_MANAGER_ID NUMBER 38 0 Level20 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL20_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level20 Manager Assignment Identifier

DW_MANAGER_CF_TD_DH

Manager Top Down Column Flattened Hierarchy

Details

Module: Global Human Resources

Key Columns

DATE_TO , PERSON_ID , DATE_FROM , ENTERPRISE_ID , MANAGER_TYPE , ASSIGNMENT_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier DW_PERSON_NAME_D PERSON_ID
ASSIGNMENT_ID NUMBER 38 0 True Assignment Identifier
DATE_FROM DATE True Hierarchy Start Date
DATE_TO DATE True Hierarchy End Date
MANAGER_TYPE VARCHAR2 32 True Type of Manager
ENTERPRISE_ID NUMBER 38 0 True Manager Person's Enterprise
SOURCE_RECORD_ID VARCHAR2 256 Unique Identifier
LEVEL1_MANAGER_ID NUMBER 38 0 Level1 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL1_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level1 Manager Assignment Identifier
LEVEL2_MANAGER_ID NUMBER 38 0 Level2 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL2_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level2 Manager Assignment Identifier
LEVEL3_MANAGER_ID NUMBER 38 0 Level3 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL3_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level3 Manager Assignment Identifier
LEVEL4_MANAGER_ID NUMBER 38 0 Level4 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL4_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level4 Manager Assignment Identifier
LEVEL5_MANAGER_ID NUMBER 38 0 Level5 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL5_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level5 Manager Assignment Identifier
LEVEL6_MANAGER_ID NUMBER 38 0 Level6 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL6_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level6 Manager Assignment Identifier
LEVEL7_MANAGER_ID NUMBER 38 0 Level7 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL7_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level7 Manager Assignment Identifier
LEVEL8_MANAGER_ID NUMBER 38 0 Level8 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL8_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level8 Manager Assignment Identifier
LEVEL9_MANAGER_ID NUMBER 38 0 Level9 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL9_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level9 Manager Assignment Identifier
LEVEL10_MANAGER_ID NUMBER 38 0 Level10 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL10_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level10 Manager Assignment Identifier
LEVEL11_MANAGER_ID NUMBER 38 0 Level11 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL11_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level11 Manager Assignment Identifier
LEVEL12_MANAGER_ID NUMBER 38 0 Level12 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL12_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level12 Manager Assignment Identifier
LEVEL13_MANAGER_ID NUMBER 38 0 Level13 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL13_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level13 Manager Assignment Identifier
LEVEL14_MANAGER_ID NUMBER 38 0 Level14 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL14_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level14 Manager Assignment Identifier
LEVEL15_MANAGER_ID NUMBER 38 0 Level15 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL15_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level15 Manager Assignment Identifier
LEVEL16_MANAGER_ID NUMBER 38 0 Level16 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL16_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level16 Manager Assignment Identifier
LEVEL17_MANAGER_ID NUMBER 38 0 Level17 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL17_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level17 Manager Assignment Identifier
LEVEL18_MANAGER_ID NUMBER 38 0 Level18 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL18_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level18 Manager Assignment Identifier
LEVEL19_MANAGER_ID NUMBER 38 0 Level19 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL19_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level19 Manager Assignment Identifier
LEVEL20_MANAGER_ID NUMBER 38 0 Level20 Manager Person Identifier DW_PERSON_NAME_D PERSON_ID
LEVEL20_MANAGER_ASSIGNMENT_ID NUMBER 38 0 Level20 Manager Assignment Identifier
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_MANAGER_DN_CURRENT_DH

Manager Hierarchy Flattened denormalized Dimension

Details

Module: Global Human Resources

Key Columns

MANAGER_DISTANCE, MANAGER_ID, BUSINESS_GROUP_ID, MANAGER_ASSIGNMENT_ID, PERSON_ID, MANAGER_TYPE, ASSIGNMENT_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier#1 DW_PERSON_NAME_D PERSON_ID
ASSIGNMENT_ID NUMBER 38 0 True Assignment identifier
MANAGER_TYPE VARCHAR2 32 True Determines the type of manager
MANAGER_ID NUMBER 38 0 True Identifies Manager's Person record#1 DW_PERSON_NAME_D PERSON_ID
MANAGER_ASSIGNMENT_ID NUMBER 38 0 True Identifies Manager's Assignment record
MANAGER_DISTANCE NUMBER 38 0 True Manager Hierarchy level
BUSINESS_GROUP_ID NUMBER 38 0 True Identifier of Enterprise
SOURCE_RECORD_ID VARCHAR2 512 Functional Unique key
PRIMARY_ASSIGNMENT_FLAG VARCHAR2 32 Identifies whether the assignment is the primary
PRIMARY_MANAGER_FLAG VARCHAR2 32 identifies LINE_MANAGER manager type
Notes

#1 DW_PERSON_NAME_D may contain multiple records for a person. Join should also consider a date to get the dimension values.

DW_MANAGER_DN_DH

Manager Hierarchy Flattened denormalized Dimension

Details

Module: Global Human Resources

Key Columns

MANAGER_DISTANCE, MANAGER_ID, BUSINESS_GROUP_ID, MANAGER_ASSIGNMENT_ID, EFFECTIVE_START_DATE, MANAGER_TYPE, PERSON_ID, EFFECTIVE_END_DATE, ASSIGNMENT_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier#1 DW_PERSON_NAME_D PERSON_ID
ASSIGNMENT_ID NUMBER 38 0 True Assignment identifier
EFFECTIVE_START_DATE DATE True Start date
EFFECTIVE_END_DATE DATE True End date
MANAGER_TYPE VARCHAR2 32 True Determines the type of manager
MANAGER_ID NUMBER 38 0 True Identifies Manager's Person record#1 DW_PERSON_NAME_D PERSON_ID
MANAGER_ASSIGNMENT_ID NUMBER 38 0 True Identifies Manager's Assignment record
MANAGER_DISTANCE NUMBER 38 0 True Manager Hierarchy level
BUSINESS_GROUP_ID NUMBER 38 0 True Identifier of Enterprise
SOURCE_RECORD_ID VARCHAR2 512 Functional Unique key
PRIMARY_ASSIGNMENT_FLAG VARCHAR2 32 Identifies whether the assignment is the primary
PRIMARY_MANAGER_FLAG VARCHAR2 32 identifies LINE_MANAGER manager type
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.
Notes

#1 DW_PERSON_NAME_D may contain multiple records for a person. Join should also consider a date to get the dimension values.

DW_MANAGER_REPORTEES_CF_DN_CURRENT_DH

Current Manager Reportees Hierarchy Column Flattened Denormalized Dimension

Details

Module: Global Human Resources

Key Columns

MANAGER_ASSIGNMENT_ID, LEVEL20_REPORTEE_ASSIGNMENT_ID, MANAGER_TYPE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
MANAGER_ASSIGNMENT_ID NUMBER 38 0 True Manager Assignment identifier
LEVEL20_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 True Identifies Level 20 Reportee's Assignment record
MANAGER_TYPE VARCHAR2 32 True Type of Manager
SOURCE_RECORD_ID VARCHAR2 256 Functional key of records
MANAGER_ID NUMBER 38 0 Manager person identifier DW_PERSON_D PERSON_ID
LEVEL20_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 20 Reportee's Person record DW_PERSON_D PERSON_ID
MANAGER_DISTANCE NUMBER 38 0 Distance of manager from reportee
LEVEL1_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 1 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL1_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 1 Reportee's Assignment record
LEVEL2_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 2 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL2_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 2 Reportee's Assignment record
LEVEL3_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 3 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL3_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 3 Reportee's Assignment record
LEVEL4_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 4 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL4_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 4 Reportee's Assignment record
LEVEL5_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 5 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL5_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 5 Reportee's Assignment record
LEVEL6_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 6 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL6_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 6 Reportee's Assignment record
LEVEL7_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 7 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL7_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 7 Reportee's Assignment record
LEVEL8_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 8 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL8_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 8 Reportee's Assignment record
LEVEL9_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 9 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL9_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 9 Reportee's Assignment record
LEVEL10_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 10 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL10_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 10 Reportee's Assignment record
LEVEL11_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 11 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL11_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 11 Reportee's Assignment record
LEVEL12_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 12 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL12_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 12 Reportee's Assignment record
LEVEL13_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 13 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL13_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 13 Reportee's Assignment record
LEVEL14_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 14 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL14_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 14 Reportee's Assignment record
LEVEL15_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 15 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL15_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 15 Reportee's Assignment record
LEVEL16_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 16 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL16_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 16 Reportee's Assignment record
LEVEL17_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 17 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL17_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 17 Reportee's Assignment record
LEVEL18_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 18 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL18_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 18 Reportee's Assignment record
LEVEL19_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 19 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL19_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 19 Reportee's Assignment record

DW_MANAGER_REPORTEES_CF_DN_DH

Manager Reportees Hierarchy Column Flattened Denormalized Dimension

Details

Module: Global Human Resources

Key Columns

LEVEL20_REPORTEE_ASSIGNMENT_ID, MANAGER_ASSIGNMENT_ID, MANAGER_TYPE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
MANAGER_ASSIGNMENT_ID NUMBER 38 0 True Manager Assignment identifier
LEVEL20_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 True Identifies Level 20 Reportee's Assignment record
EFFECTIVE_START_DATE DATE True Start date
EFFECTIVE_END_DATE DATE True End date
MANAGER_TYPE VARCHAR2 32 True Type of Manager
SOURCE_RECORD_ID VARCHAR2 256 Functional key of records
MANAGER_ID NUMBER 38 0 Manager person identifier DW_PERSON_D PERSON_ID
LEVEL20_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 20 Reportee's Person record DW_PERSON_D PERSON_ID
MANAGER_DISTANCE NUMBER 38 0 Distance of manager from reportee
LEVEL1_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 1 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL1_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 1 Reportee's Assignment record
LEVEL2_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 2 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL2_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 2 Reportee's Assignment record
LEVEL3_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 3 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL3_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 3 Reportee's Assignment record
LEVEL4_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 4 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL4_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 4 Reportee's Assignment record
LEVEL5_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 5 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL5_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 5 Reportee's Assignment record
LEVEL6_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 6 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL6_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 6 Reportee's Assignment record
LEVEL7_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 7 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL7_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 7 Reportee's Assignment record
LEVEL8_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 8 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL8_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 8 Reportee's Assignment record
LEVEL9_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 9 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL9_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 9 Reportee's Assignment record
LEVEL10_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 10 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL10_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 10 Reportee's Assignment record
LEVEL11_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 11 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL11_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 11 Reportee's Assignment record
LEVEL12_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 12 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL12_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 12 Reportee's Assignment record
LEVEL13_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 13 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL13_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 13 Reportee's Assignment record
LEVEL14_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 14 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL14_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 14 Reportee's Assignment record
LEVEL15_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 15 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL15_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 15 Reportee's Assignment record
LEVEL16_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 16 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL16_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 16 Reportee's Assignment record
LEVEL17_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 17 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL17_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 17 Reportee's Assignment record
LEVEL18_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 18 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL18_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 18 Reportee's Assignment record
LEVEL19_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 19 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL19_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 19 Reportee's Assignment record
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_MANAGER_REPORTEES_CF_DN_NSR_DH

Manager Reportees Hierarchy Column Flattened Denormalized Dimension

Details

Module: Global Human Resources

Key Columns

LEVEL20_REPORTEE_ASSIGNMENT_ID, MANAGER_ASSIGNMENT_ID, MANAGER_TYPE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
MANAGER_ASSIGNMENT_ID NUMBER 38 0 True Manager Assignment identifier
LEVEL20_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 True Identifies Level 20 Reportee's Assignment record
EFFECTIVE_START_DATE DATE True Start date
EFFECTIVE_END_DATE DATE True End date
MANAGER_TYPE VARCHAR2 32 True Type of Manager
SOURCE_RECORD_ID VARCHAR2 256 Functional key of records
MANAGER_ID NUMBER 38 0 Manager person identifier DW_PERSON_D PERSON_ID
LEVEL20_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 20 Reportee's Person record DW_PERSON_D PERSON_ID
MANAGER_DISTANCE NUMBER 38 0 Distance of manager from reportee
LEVEL1_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 1 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL1_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 1 Reportee's Assignment record
LEVEL2_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 2 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL2_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 2 Reportee's Assignment record
LEVEL3_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 3 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL3_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 3 Reportee's Assignment record
LEVEL4_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 4 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL4_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 4 Reportee's Assignment record
LEVEL5_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 5 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL5_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 5 Reportee's Assignment record
LEVEL6_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 6 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL6_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 6 Reportee's Assignment record
LEVEL7_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 7 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL7_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 7 Reportee's Assignment record
LEVEL8_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 8 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL8_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 8 Reportee's Assignment record
LEVEL9_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 9 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL9_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 9 Reportee's Assignment record
LEVEL10_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 10 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL10_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 10 Reportee's Assignment record
LEVEL11_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 11 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL11_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 11 Reportee's Assignment record
LEVEL12_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 12 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL12_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 12 Reportee's Assignment record
LEVEL13_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 13 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL13_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 13 Reportee's Assignment record
LEVEL14_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 14 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL14_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 14 Reportee's Assignment record
LEVEL15_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 15 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL15_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 15 Reportee's Assignment record
LEVEL16_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 16 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL16_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 16 Reportee's Assignment record
LEVEL17_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 17 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL17_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 17 Reportee's Assignment record
LEVEL18_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 18 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL18_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 18 Reportee's Assignment record
LEVEL19_REPORTEE_PERSON_ID NUMBER 38 0 Identifies Level 19 Reportee's Person record DW_PERSON_D PERSON_ID
LEVEL19_REPORTEE_ASSIGNMENT_ID NUMBER 38 0 Identifies Level 19 Reportee's Assignment record
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_MARITAL_STATUS_D_TL

This dimension captures the Marital Status translation information

Details

Module: Global Human Resources

Key Columns

MARITAL_STATUS, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
MARITAL_STATUS VARCHAR2 32 True Marital Status
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
MARITAL_STATUS_NAME VARCHAR2 128 Name of the Marital Status
MARITAL_STATUS_DESCRIPTION VARCHAR2 256 Description of the Marital Status

DW_PAY_GRADE_CURRENT_D

It stores current records of pay grade dimension.

Details

Module: Global Human Resources

Key Columns

GRADE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
GRADE_ID NUMBER 38 0 True System generated primary key column, For translation refer DW_PAY_GRADE_D_CURRENT_TL and join with this ID. DW_PAY_GRADE_D_CURRENT_TL GRADE_ID
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains GRADE_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
ACTIVE_STATUS VARCHAR2 32 Indicates if a grade is active or inactive
GRADE_CODE VARCHAR2 32 Code of grade
GRADE_TYPE VARCHAR2 32 Type of the grade.

DW_PAY_GRADE_D

Pay Grade dimension stores the Grade definitions to identify the level or rank of an employee in an organization.

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_START_DATE , GRADE_ID , EFFECTIVE_END_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
GRADE_ID NUMBER 38 0 True System generated primary key column, For translation refer DW_PAY_GRADE_D_TL and join with this ID. #1 DW_PAY_GRADE_D_TL GRADE_ID
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains GRADE_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
ACTIVE_STATUS VARCHAR2 32 Indicates if a grade is active or inactive
GRADE_CODE VARCHAR2 32 Code of grade
GRADE_TYPE VARCHAR2 32 Type of the grade.
Notes

#1 DW_PAY_GRADE_D_TL is date effective. Along with GRADE_ID, join on dates.

DW_PAY_GRADE_D_CURRENT_TL

It contains current row translations for Grade ID of DW_PAY_GRADE_CURRENT_D

Details

Module: Global Human Resources

Key Columns

GRADE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
GRADE_ID NUMBER 38 0 True Grade ID generated in transaction system.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
PAY_GRADE_NAME VARCHAR2 256 Name for the Grade

DW_PAY_GRADE_D_TL

DW_PAY_GRADE_D_TL contains translation of Grade ID of DW_PAY_GRADE_D

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, LANGUAGE, EFFECTIVE_START_DATE, GRADE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
GRADE_ID NUMBER 38 0 True Grade ID generated in transaction system.
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
LANGUAGE VARCHAR2 16 True Indicates the code of the language into which the contents of the translatable columns are translated.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
PAY_GRADE_NAME VARCHAR2 256 Name for the Grade

DW_PERSON_CITIZENSHIP_D

This dimension captures the Person citizenship Information

Details

Module: Global Human Resources

Key Columns

CITIZENSHIP_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
CITIZENSHIP_ID NUMBER 38 0 True Person Citizenship Identifier
SOURCE_RECORD_ID NUMBER 38 0 Source Record Identifier Uniquely identifies the person citizenships and used as the primary key
DATE_FROM DATE Start date of citizenship
DATE_TO DATE End date of citizenship
PERSON_ID NUMBER 38 0 Person Identifier
LEGISLATION_CODE VARCHAR2 32 Legislation code of the citizenship DW_LEGISLATION_D_TL LEGISLATION_CODE
CITIZENSHIP_STATUS VARCHAR2 32 Status of citizenship DW_CITIZENSHIP_STATUS_D_TL CITIZENSHIP_STATUS

DW_PERSON_D

This dimension captures the Person Information

Details

Module: Global Human Resources

Key Columns

PERSON_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier
SOURCE_RECORD_ID NUMBER 38 0 Source Record Identifier Uniquely identifies the person and used as the primary key
PERSON_NUMBER VARCHAR2 32 Peson Number
START_DATE DATE Start date of person record on system
CORRESPONDENCE_LANGUAGE VARCHAR2 32 language to use for this communication
DATE_OF_BIRTH DATE Date of birth of the person
DATE_OF_DEATH DATE Date of death of the person
COUNTRY_OF_BIRTH VARCHAR2 256 Identifies the country where the person was born
REGION_OF_BIRTH VARCHAR2 256 Region of birth, within the country of birth
TOWN_OF_BIRTH VARCHAR2 256 Town of birth, within the region of birth
BLOOD_TYPE VARCHAR2 32 Identified the blood group of the person

DW_PERSON_DISABILITY_D

This dimension captures the Person Disability Information

Details

Module: Global Human Resources

Key Columns

PERSON_DISABILITY_ID, EFFECTIVE_END_DATE, EFFECTIVE_START_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_DISABILITY_ID NUMBER 38 0 True Person Legistlation Identifier
EFFECTIVE_START_DATE DATE True Date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 256 Source Record Identifier Uniquely identifies the Person Disablity and used as the primary key
PERSON_ID NUMBER 38 0 Identifier of the Person DW_PERSON_D PERSON_ID
LEGISLATION_CODE VARCHAR2 16 Legislation code derived from Legal Entity. DW_LEGISLATION_D_TL LEGISLATION_CODE
DISABILITY_ORGANIZATION_ID NUMBER 38 0 Disability Organization
REGISTRATION_ID VARCHAR2 32 ID given to person following registration with disability organization
REGISTRATION_DATE DATE Date of registration with organization for the disabled
REGISTRATION_EXP_DATE DATE Expiry date of registration with organization for the disabled.
DISABILITY_CATEGORY VARCHAR2 32 Person Disability Category Name DW_DISABILITY_CATEGORY_D_TL DISABILITY_CATEGORY
DISABILITY_DESCRIPTION VARCHAR2 2048 Person Disability description text
DISABILITY_DEGREE NUMBER 38 0 Person Disability degree
DISABILITY_REASON VARCHAR2 32 Person Disability Reason name DW_DISABILITY_REASON_D_TL DISABILITY_REASON
WORK_RESTRICTION VARCHAR2 2048 Person Disability work restriction details
DISABILITY_STATUS VARCHAR2 32 Denotes the status for the person's disability like Temporary DW_DISABILITY_STATUS_D_TL DISABILITY_STATUS
SELF_DISCLOSED_TYPE VARCHAR2 32 Self Disclosed Disability DW_SELF_DISCLOSED_TYPE_D_TL SELF_DISCLOSED_TYPE
DISCLOSURE_DATE DATE Disability Disclosure Date
ACCOMMODATION_REQUEST VARCHAR2 2048 Accommodation Request
DW_DATE_FROM DATE Warehouse Effective From Date for the Person Disability record. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Person Disability record. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_PERSON_ETHNICITY_D

This dimension captures the Person Ethnicity Information

Details

Module: Global Human Resources

Key Columns

ETHNICITY_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ETHNICITY_ID NUMBER 38 0 True Person Enthnicity Identifier
SOURCE_RECORD_ID NUMBER 38 0 Source Record Identifier Uniquely identifies the Person Ethnicity and used as the primary key
PERSON_ID NUMBER 38 0 Person Identifier DW_PERSON_D PERSON_ID
LEGISLATION_CODE VARCHAR2 32 Legislation code derived from Legal Entity. DW_LEGISLATION_D_TL LEGISLATION_CODE
ETHNICITY VARCHAR2 32 Ethnicity.Valid for the legislation code DW_ETHNICITY_D_TL ETHNICITY

DW_PERSON_LEGISLATION_ALL_CURRENT_D

This dimension captures the Person latest information

Details

Module: Global Human Resources

Key Columns

LEGISLATION_CODE, PERSON_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier
LEGISLATION_CODE VARCHAR2 16 True Legislation code derived from Legal Entity. DW_LEGISLATION_D_TL LEGISLATION_CODE
SOURCE_RECORD_ID VARCHAR2 256 Source Record Identifier Uniquely identifies the Person and uses as the primary key
PERSON_NUMBER VARCHAR2 32 Person Number
START_DATE DATE Start date of person record on system
CORRESPONDENCE_LANGUAGE VARCHAR2 32 language to use for this communication
DATE_OF_BIRTH DATE Date of birth of the person
DATE_OF_DEATH DATE Date of death of the person
COUNTRY_OF_BIRTH VARCHAR2 256 Identifies the country where the person was born
REGION_OF_BIRTH VARCHAR2 256 Region of birth, within the country of birth
TOWN_OF_BIRTH VARCHAR2 256 Town of birth, within the region of birth
BLOOD_TYPE VARCHAR2 32 Identified the blood group of the person
SEX VARCHAR2 32 Sex and valid for the legislation code DW_SEX_D_TL SEX
MARITAL_STATUS VARCHAR2 32 Marital Status and valid for the legislation code DW_MARITAL_STATUS_D_TL MARITAL_STATUS
MARITAL_STATUS_DATE DATE Date the Marital Status came into effect
HIGHEST_EDUCATION_LEVEL VARCHAR2 32 Legislation-specific value that indicates the highest level of education of the person DW_HIGHEST_DEGREE_D_TL HIGHEST_DEGREE
DISABILITY_FLAG VARCHAR2 16 Flag to indicate if the person is disabled or not
ETHNICITY VARCHAR2 32 Ethnicity.Valid for the legislation code DW_ETHNICITY_D_TL ETHNICITY
RELIGION VARCHAR2 32 Religion.Valid for the legislation code DW_RELIGION_D_TL RELIGION

DW_PERSON_LEGISLATION_ALL_D

This dimension captures the Person history information

Details

Module: Global Human Resources

Key Columns

LEGISLATION_CODE, PERSON_ID, DATE_FROM

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier
LEGISLATION_CODE VARCHAR2 16 True Legislation code derived from Legal Entity. DW_LEGISLATION_D_TL LEGISLATION_CODE
DATE_FROM DATE True Date at the beginning of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 256 Source Record Identifier Uniquely identifies the Person and uses as the primary key
DATE_TO DATE Date at the end of the date range within which the row is effective.
PERSON_NUMBER VARCHAR2 32 Peson Number
START_DATE DATE Start date of person record on system
CORRESPONDENCE_LANGUAGE VARCHAR2 32 language to use for this communication
DATE_OF_BIRTH DATE Date of birth of the person
DATE_OF_DEATH DATE Date of death of the person
COUNTRY_OF_BIRTH VARCHAR2 256 Identifies the country where the person was born
REGION_OF_BIRTH VARCHAR2 256 Region of birth, within the country of birth
TOWN_OF_BIRTH VARCHAR2 256 Town of birth, within the region of birth
BLOOD_TYPE VARCHAR2 32 Identified the blood group of the person
SEX VARCHAR2 32 Sex and valid for the legislation code DW_SEX_D_TL SEX
MARITAL_STATUS VARCHAR2 32 Marital Status and valid for the legislation code DW_MARITAL_STATUS_D_TL MARITAL_STATUS
MARITAL_STATUS_DATE DATE Date the Marital Status came into effect
HIGHEST_EDUCATION_LEVEL VARCHAR2 32 Legislation-specific value that indicates the highest level of education of the person DW_HIGHEST_DEGREE_D_TL HIGHEST_DEGREE
DISABILITY_FLAG VARCHAR2 16 Flag to indicate if the person is disabled or not
ETHNICITY VARCHAR2 32 Ethnicity.Valid for the legislation code DW_ETHNICITY_D_TL ETHNICITY
RELIGION VARCHAR2 32 Religion.Valid for the legislation code DW_RELIGION_D_TL RELIGION

DW_PERSON_LEGISLATION_DAILY_EVENT_F

This fact captures the Person daily event information

Details

Module: Global Human Resources

Key Columns

LEGISLATION_CODE, PERSON_ID, EVENT_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_ID NUMBER 38 0 True Person Identifier DW_PERSON_D PERSON_ID
LEGISLATION_CODE VARCHAR2 16 True Legislation code derived from Legal Entity.
EVENT_DATE DATE True Date at the beginning of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 256 Source Record Identifier Uniquely identifies the Person and uses as the primary key
SEX VARCHAR2 32 Sex and valid for the legislation code DW_SEX_D_TL SEX
MARITAL_STATUS VARCHAR2 32 Marital Status and valid for the legislation code DW_MARITAL_STATUS_D_TL MARITAL_STATUS
HIGHEST_EDUCATION_LEVEL VARCHAR2 32 Legislation-specific value that indicates the highest level of education of the person DW_HIGHEST_DEGREE_D_TL HIGHEST_DEGREE
DISABILITY_FLAG VARCHAR2 16 Flag to indicate if the person is disabled or not
ETHNICITY VARCHAR2 32 Ethnicity.Valid for the legislation code DW_ETHNICITY_D_TL ETHNICITY
RELIGION VARCHAR2 32 Religion.Valid for the legislation code DW_RELIGION_D_TL RELIGION
DATE_OF_BIRTH DATE Date of birth of the person
AGE_IN_YEARS NUMBER 38 0 Person Age as of Event Date
SEX_CHANGE_IND NUMBER 38 0 Change Indicator for sex
MARITAL_STATUS_CHANGE_IND NUMBER 38 0 Change Indicator for marital status
HIGHEST_EDUCATION_CHANGE_IND NUMBER 38 0 Change Indicator for Highest education
DISABILITY_FLAG_CHANGE_IND NUMBER 38 0 Change Indicator for Disability flag

DW_PERSON_LEGISLATIVE_D

This dimension captures the Person Legislative Information

Details

Module: Global Human Resources

Key Columns

PERSON_LEGISLATIVE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_LEGISLATIVE_ID NUMBER 38 0 True Person Legistlation Identifier
EFFECTIVE_START_DATE DATE True Date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 256 Source Record Identifier Uniquely identifies the person legislation and used as the primary key
PERSON_ID NUMBER 38 0 Identifier of the Person DW_PERSON_D PERSON_ID
LEGISLATION_CODE VARCHAR2 16 Legislation code derived from Legal Entity. DW_LEGISLATION_D_TL LEGISLATION_CODE
SEX VARCHAR2 32 Sex and valid for the legislation code DW_SEX_D_TL SEX
MARITAL_STATUS VARCHAR2 32 Marital Status and valid for the legislation code DW_MARITAL_STATUS_D_TL MARITAL_STATUS
MARITAL_STATUS_DATE DATE Date the Marital Status came into effect
HIGHEST_EDUCATION_LEVEL VARCHAR2 32 Legislation-specific value that indicates the highest level of education of the person DW_HIGHEST_DEGREE_D_TL HIGHEST_DEGREE
DW_DATE_FROM DATE Warehouse Effective From Date for the Person Disability record. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Person Disability record. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_PERSON_NAME_CURRENT_D

This dimension captures the Person latest names

Details

Module: Global Human Resources

Key Columns

PERSON_NAME_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_NAME_ID NUMBER 38 0 True Person Name Identifier
SOURCE_RECORD_ID NUMBER 38 0 Source Record Identifier Uniquely identifies the person Name and used as the primary key
PERSON_ID NUMBER 38 0 Identifier of the Person to whom this name belongs DW_PERSON_D PERSON_ID
LEGISLATION_CODE VARCHAR2 16 Country context for the Name data DW_LEGISLATION_D_TL LEGISLATION_CODE
NAME_TYPE VARCHAR2 32 This column tells us whether the name is Global or Local.
DISPLAY_NAME VARCHAR2 256 Derived denormalized name formatted for on-screen display.Formatting depends on the Legislation Code
FIRST_NAME VARCHAR2 256 Person's First name
LAST_NAME VARCHAR2 256 Person's Last Name
MIDDLE_NAMES VARCHAR2 128 Person's middle name
TITLE VARCHAR2 32 Person's title, means of addressing person.
PRE_NAME_ADJUNCT VARCHAR2 256 Prefix to a person's last name
SUFFIX VARCHAR2 128 Part of name appended to the last name
KNOWN_AS VARCHAR2 128 Preferred first name for the user.
PREVIOUS_LAST_NAME VARCHAR2 256 Previous Last Name of the person if any
FULL_NAME VARCHAR2 2048 System constructed version of name, used in more formal display purposes
LIST_NAME VARCHAR2 256 Constructed version of a person's name, intended for use in an alphabetically-ordered list. The value may naturally differ depending on the Legislation Code.Expect the Last Name as the first component in this format in most cases.
ORDER_NAME VARCHAR2 256 System-constructed version of name used for sorting

DW_PERSON_NAME_D

This dimension captures the Global Person Name Information

Details

Module: Global Human Resources

Key Columns

PERSON_NAME_ID, EFFECTIVE_END_DATE, EFFECTIVE_START_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_NAME_ID NUMBER 38 0 True Person Name Identifier
EFFECTIVE_START_DATE DATE True Date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 256 Source Record Identifier Uniquely identifies the person Name and used as the primary key
PERSON_ID NUMBER 38 0 Identifier of the Person to whom this name belongs DW_PERSON_D PERSON_ID
LEGISLATION_CODE VARCHAR2 16 Country context for the Name data DW_LEGISLATION_D_TL LEGISLATION_CODE
NAME_TYPE VARCHAR2 32 This column tells us whether the name is Global or Local.
DISPLAY_NAME VARCHAR2 256 Derived denormalized name formatted for on-screen display.Formatting depends on the Legislation Code
FIRST_NAME VARCHAR2 256 Person's First name
LAST_NAME VARCHAR2 256 Person's Last Name
MIDDLE_NAMES VARCHAR2 128 Person's middle name
TITLE VARCHAR2 32 Person's title, means of addressing person.
PRE_NAME_ADJUNCT VARCHAR2 256 Prefix to a person's last name
SUFFIX VARCHAR2 128 Part of name appended to the last name
KNOWN_AS VARCHAR2 128 Preferred first name for the user.
PREVIOUS_LAST_NAME VARCHAR2 256 Previous Last Name of the person if any
FULL_NAME VARCHAR2 2048 System constructed version of name, used in more formal display purposes
LIST_NAME VARCHAR2 256 Constructed version of a person's name, intended for use in an alphabetically-ordered list. The value may naturally differ depending on the Legislation Code.Expect the Last Name as the first component in this format in most cases.
ORDER_NAME VARCHAR2 256 System-constructed version of name used for sorting
DW_DATE_FROM DATE Warehouse Effective From Date for the Person Name record. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Person Name record. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_PERSON_PERFORMANCE_F

This table contains profile items that make up a Profile with Performance rating level.

Details

Module: Global Human Resources

Key Columns

PROFILE_ITEM_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PROFILE_ITEM_ID NUMBER 38 0 True Primary Key sequence for Salary in Fusion Source.
SOURCE_RECORD_ID VARCHAR2 64 Unique Source Record Identifier with profile item id This column contains SALARY_ID.
PERSON_ID NUMBER 38 0 Persons ID DW_PERSON_D PERSON_ID
RATING_LEVEL_ID NUMBER 38 0 The rating value. The values depend on the rating model selected DW_RATING_LEVEL_D RATING_LEVEL_ID
DATE_FROM DATE Date From
DATE_TO DATE Date To
DW_DATE_FROM DATE Warehouse Effective From Date for the Performance. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for Performance. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for performance to be brought into the warehouse based on initial extract date set by user.

DW_PERSON_POTENTIAL_F

This table contains profile items that make up a Profile with Potential rating level.

Details

Module: Global Human Resources

Key Columns

PROFILE_ITEM_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PROFILE_ITEM_ID NUMBER 38 0 True Primary Key sequence for Salary in Fusion Source.
SOURCE_RECORD_ID VARCHAR2 64 Unique Source Record Identifier with profile item id This column contains SALARY_ID.
PERSON_ID NUMBER 38 0 Persons ID DW_PERSON_D PERSON_ID
RATING_LEVEL_ID NUMBER 38 0 The rating value. The values depend on the rating model selected DW_RATING_LEVEL_D RATING_LEVEL_ID
DATE_FROM DATE Date From
DATE_TO DATE Date To
DW_DATE_FROM DATE Warehouse Effective From Date for the Potential. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for Potential. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for Potential to be brought into the warehouse based on initial extract date set by user.

DW_PERSON_RELIGION_D

This dimension captures the Person Religion Information

Details

Module: Global Human Resources

Key Columns

RELIGION_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RELIGION_ID NUMBER 38 0 True Person Enthnicity Identifier
SOURCE_RECORD_ID NUMBER 38 0 Source Record Identifier Uniquely identifies the person religions and used as the primary key
PERSON_ID NUMBER 38 0 Person Identifier DW_PERSON_D PERSON_ID
LEGISLATION_CODE VARCHAR2 32 Legislation code derived from Legal Entity. DW_LEGISLATION_D_TL LEGISLATION_CODE
RELIGION VARCHAR2 32 Religion.Valid for the legislation code DW_RELIGION_D_TL RELIGION

DW_PERSON_TYPE_D_TL

Stores Person Type Name

Details

Module: Global Human Resources

Key Columns

PERSON_TYPE_ID, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERSON_TYPE_ID NUMBER 38 0 True Person Type Identifier
LANGUAGE VARCHAR2 16 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
PERSON_TYPE_NAME VARCHAR2 128 Name of the Person Type

DW_POSITION_CURRENT_D

It stores records for all the current positions.

Details

Module: Global Human Resources

Key Columns

POSITION_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
POSITION_ID NUMBER 38 0 True Transaction system generated primary key column.For translation refer table DW_POSITION_D_CURRENT_TL joining this ID. DW_POSITION_D_CURRENT_TL POSITION_ID
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
POSITION_CODE VARCHAR2 32 Code of the position
ACTIVE_STATUS VARCHAR2 32 Indicates if a position is active or inactive.
POSITION_TYPE VARCHAR2 32 Indicates the type of the position for example pooled or single.
MAX_PERSONS NUMBER 38 0 Position headcount
SECURITY_CLEARANCE VARCHAR2 32 Indicates the security clearance of a position.
OVERLAP_ALLOWED VARCHAR2 32 Indicates if overlap is allowed.
SEASONAL_FLAG VARCHAR2 32 Indicates if a position is seasonal.
SEASONAL_START_DATE DATE Seasonal start date.
SEASONAL_END_DATE DATE Seasonal end date.

DW_POSITION_D

DW_POSITION_D stores all the positions with their attributes.

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, POSITION_ID, EFFECTIVE_START_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
POSITION_ID NUMBER 38 0 True Transaction system generated primary key column.For translation refer table DW_POSITION_D_TL joining this ID.#1 DW_POSITION_D_TL POSITION_ID
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains JOB_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
POSITION_CODE VARCHAR2 32 Code of the position
ACTIVE_STATUS VARCHAR2 32 Indicates if a position is active or inactive.
POSITION_TYPE VARCHAR2 32 Indicates the type of the position for example pooled or single.
MAX_PERSONS NUMBER 38 0 Position headcount
SECURITY_CLEARANCE VARCHAR2 32 Indicates the security clearance of a position.
OVERLAP_ALLOWED VARCHAR2 32 Indicates if overlap is allowed.
SEASONAL_FLAG VARCHAR2 32 Indicates if a position is seasonal.
SEASONAL_START_DATE DATE Seasonal start date.
SEASONAL_END_DATE DATE Seasonal end date.
Notes

#1 DW_POSITION_D_TL is date effective. Along with POSITION_ID join on dates.

DW_POSITION_D_CURRENT_TL

It stores current records for translation of DW_POSITION_CURRENT_D

Details

Module: Global Human Resources

Key Columns

POSITION_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
POSITION_ID NUMBER 38 0 True Position ID generated in transaction system.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains POSITION_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
POSITION_NAME VARCHAR2 256 Denotes the translated name of the position

DW_POSITION_D_TL

DW_POSITION_D_TL contains translation of DW_POSITION_D

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, POSITION_ID, EFFECTIVE_START_DATE, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
POSITION_ID NUMBER 38 0 True Position ID generated in transaction system.
EFFECTIVE_START_DATE DATE True Date Effective Entity: indicates the date at the beginning of the date range within which the row is effective.
EFFECTIVE_END_DATE DATE True Date Effective Entity: indicates the date at the end of the date range within which the row is effective.
LANGUAGE VARCHAR2 16 True Indicates the code of the language into which the contents of the translatable columns are translated.
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier This column contains POSITION_ID,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
POSITION_NAME VARCHAR2 256 Denotes the translated name of the position

DW_RATING_LEVEL_D

It holds Rating scale, Rating categories and boundaries grouped under models for person.

Details

Module: Global Human Resources

Key Columns

RATING_LEVEL_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RATING_LEVEL_ID NUMBER 38 0 True Transaction system generated primary key column.For translation refer table DW_RATING_LEVEL_D_TL for Level name and description and join with RATING_LEVEL_ID. DW_RATING_LEVEL_D_TL RATING_LEVEL_ID
SOURCE_RECORD_ID VARCHAR2 128 Unique Source Record Identifier.This column contains RATING_LEVEL_ID, RATING_MODEL_ID and CATEGORY_ID .
RATING_MODEL_ID NUMBER 38 0 Transaction system generated primary key column.For translation refer table DW_RATING_LEVEL_D_TL for Model name and description and join with RATING_LEVEL_ID. DW_RATING_LEVEL_D_TL RATING_LEVEL_ID
CATEGORY_ID NUMBER 38 0 Transaction system generated primary key column.For translation refer table DW_RATING_LEVEL_D_TL for Category name and description and join with RATING_LEVEL_ID. DW_RATING_LEVEL_D_TL RATING_LEVEL_ID
RATING_LEVEL_CODE VARCHAR2 32 Code of the job
RATING_MODEL_CODE VARCHAR2 32 Identifier of the job family. #2 DW_JOB_FAMILY_D JOB_FAMILY_ID
NUMERIC_RATING NUMBER Indicates if a job is active or inactive.

DW_RATING_LEVEL_D_TL

It stores translation of rating level, model and its categories.

Details

Module: Global Human Resources

Key Columns

CATEGORY_ID, RATING_MODEL_ID, RATING_LEVEL_ID, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RATING_LEVEL_ID NUMBER 38 0 True ID of rating level generated in transaction system.
RATING_MODEL_ID NUMBER 38 0 True ID of Model generated in transaction system.
CATEGORY_ID NUMBER 38 0 True ID of category generated in transaction system.
LANGUAGE VARCHAR2 16 True Indicates the code of the language into which the contents of the translatable columns are translated.
SOURCE_RECORD_ID VARCHAR2 256 Unique Source Record Identifier.This column contains RATING_LEVEL_ID,RATING_MODEL_ID and CATEGORY_ID.
RATING_LEVEL_NAME VARCHAR2 2048 Name for Rating level
RATING_LEVEL_DESCRIPTION VARCHAR2 4000 Description of Rating level
RATING_LEVEL_SHORT_DESCRIPTION VARCHAR2 256 Short description of Rating level
MODEL_NAME VARCHAR2 256 Name for Rating Model
MODEL_DESCRIPTION VARCHAR2 4000 Description of Rating Model
CATEGORY_NAME VARCHAR2 256 Name for Rating Category
CATEGORY_DESCRIPTION VARCHAR2 2048 Description of Rating Category

DW_RELIGION_D_TL

This dimension captures the Religion translation information

Details

Module: Global Human Resources

Key Columns

RELIGION, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
RELIGION VARCHAR2 32 True Religion
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
RELIGION_NAME VARCHAR2 128 Name of the Religion
RELIGION_DESCRIPTION VARCHAR2 256 Description of the Religion

DW_SALARY_BASIS_D

It establishes the period of time for which an employee's salary is quoted.

Details

Module: Global Human Resources

Key Columns

SALARY_BASIS_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
SALARY_BASIS_ID NUMBER 38 0 True Primary Key sequence for Salary basis in Fusion Source.For translation refer table DW_SALARY_BASIS_D_TL joining with this id. DW_SALARY_BASIS_D_TL SALARY_BASIS_ID
SOURCE_RECORD_ID VARCHAR2 64 Unique Source Record Identifier This column contains SALARY_BASIS_ID.
ANNUALIZED_HOURS NUMBER Annualized Hours
ELEMENT_TYPE_ID NUMBER 38 0 Element Type
GRADE_RATE_ID NUMBER 38 0 Grade Rate Identifier. #1 DW_GRADE_RATE_VALUE_D RATE_ID
SALARY_BASIS_CODE VARCHAR2 32 Salary Basis Code
SALARY_ANNUALIZATION_FACTOR NUMBER Salary Annualization factor
Notes

#1 DW_GRADE_RATE_VALUE_D is date effective.

DW_SALARY_BASIS_D_TL

Stores Salary Bases translated Name

Details

Module: Global Human Resources

Key Columns

SALARY_BASIS_ID, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
SALARY_BASIS_ID NUMBER 38 0 True Salary Basis Identifier
LANGUAGE VARCHAR2 16 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
SALARY_BASIS_NAME VARCHAR2 128 Name of Salary Basis
SALARY_BASIS_DESCRIPTION VARCHAR2 2048 Description of Salary Basis

DW_SELF_DISCLOSED_TYPE_D_TL

This dimension captures the Self Disclosed Type translation information

Details

Module: Global Human Resources

Key Columns

LANGUAGE, SELF_DISCLOSED_TYPE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
SELF_DISCLOSED_TYPE VARCHAR2 32 True Self Disclosed Type
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
SELF_DISCLOSED_TYPE_NAME VARCHAR2 128 Name of the Self Disclosed Type
SELF_DISCLOSED_TYPE_DESCRIPTION VARCHAR2 256 Description of the Self Disclosed Type

DW_SERVICE_PERIOD_D

This table stores the work relation ship details between person and their legal employer for the period of service

Details

Module: Global Human Resources

Key Columns

PERIOD_OF_SERVICE_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
PERIOD_OF_SERVICE_ID NUMBER 38 0 True Perod of Service identifier
SOURCE_RECORD_ID VARCHAR2 32 Source Record Identifier Consists of Period of Service Identifier
PERSON_ID NUMBER 38 0 Person Identifier
LEGISLATION_CODE VARCHAR2 256 Legislation Code
DATE_START DATE Start date of the period of employment
ACTUAL_TERMINATION_DATE DATE Actual date of termination of employment.
NOTIFIED_TERMINATION_DATE DATE Date when the termination of employment was noted.
PROJECTED_TERMINATION_DATE DATE Projected employment termination date.
LAST_WORKING_DATE DATE Last Date Worked (for terminated workers).
PRIMARY_FLAG VARCHAR2 32 If Yes, this employment period is considered the primary for the Legal Entity.

DW_SEX_D_TL

This dimension captures the Sex translation information

Details

Module: Global Human Resources

Key Columns

SEX, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
SEX VARCHAR2 32 True Sex
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
SEX_NAME VARCHAR2 128 Name of the Sex
SEX_DESCRIPTION VARCHAR2 256 Description of the Sex

DW_USER_PERSON_D

It stores one record per fusion system user.

Details

Module: Global Human Resources

Key Columns

USER_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
USER_ID NUMBER 38 0 True System generated primary key column
SOURCE_RECORD_ID VARCHAR2 64 Unique Source Record Identifier This column contains USER_ID.
USER_GUID VARCHAR2 128 The latest user Guid of the user.
USERNAME VARCHAR2 128 The latest principal username of the user
PERSON_ID NUMBER 38 0 Person ID in HCM for this user (if available)

DW_WORKER_CATEGORY_D_TL

Stores Worker Category Name

Details

Module: Global Human Resources

Key Columns

LANGUAGE, WORKER_CATEGORY

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
WORKER_CATEGORY VARCHAR2 32 True Worker Category
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
WORKER_CATEGORY_NAME VARCHAR2 128 Name of the Worker Category
WORKER_CATEGORY_DESCRIPTION VARCHAR2 256 Description of the Worker Category

DW_WORKER_TYPE_D_TL

Stores Worker Type Name

Details

Module: Global Human Resources

Key Columns

WORKER_TYPE, LANGUAGE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
WORKER_TYPE VARCHAR2 32 True Worker Type
LANGUAGE VARCHAR2 32 True Translation language
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of CODE_TYPE, CODE
WORKER_TYPE_NAME VARCHAR2 128 Name of the Worker Type
WORKER_TYPE_DESCRIPTION VARCHAR2 256 Description of the Worker Type

DW_WRKFRC_ASG_DAILY_EVENT_F

Workforce Assignment Event Fact

Details

Module: Global Human Resources

Key Columns

EVENT_DATE, ASSIGNMENT_ID

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_ID NUMBER 38 0 True Assignment Identifier
EVENT_DATE DATE True Assignment Event Date
SOURCE_RECORD_ID VARCHAR2 64 Primary Key consisting of Assignment Identifier and Event Date
DEPARTMENT_ID NUMBER 38 0 Department Identifier#4 DW_DEPARTMENT_D DEPARTMENT_ID
REPORTING_ESTABLISHMENT_ID NUMBER 38 0 Reporting Establishment Identifier#5 DW_REPORTING_EST_D REPORTING_EST_ID
JOB_ID NUMBER 38 0 Job Identifier DW_JOB_D JOB_ID
JOB_FAMILY_ID NUMBER 38 0 Job Family Identifier DW_JOB_FAMILY_D JOB_FAMILY_ID
LOCATION_ID NUMBER 38 0 Location Identifier DW_WORKER_LOCATION_D LOCATION_ID
POSITION_ID NUMBER 38 0 Position Identifier DW_POSITION_D POSITION_ID
GRADE_ID NUMBER 38 0 Grade Identifier DW_PAY_GRADE_D GRADE_ID
RATE_VALUE_ID NUMBER 38 0 Grade Rate Value Identifier DW_GRADE_RATE_VALUE_D RATE_VALUE_ID
RATE_ID NUMBER 38 0 Grade Rate Identifier DW_GRADE_RATE_D RATE_ID
SALARY_BASIS_ID NUMBER 38 0 Salary Basis Identifier DW_SALARY_BASIS_D SALARY_BASIS_ID
LEGAL_EMPLOYER_ID NUMBER 38 0 Legal Employer Identifier#6 DW_LEGAL_EMPLOYER_D LEGAL_EMPLOYER_ID
BUSINESS_UNIT_ID NUMBER 38 0 Business Unit Identifier#7 DW_BUSINESS_UNIT_D BUSINESS_UNIT_ID
PERIOD_OF_SERVICE_ID NUMBER 38 0 Period of Service Identifier DW_SERVICE_PERIOD_D PERIOD_OF_SERVICE_ID
PERSON_ID NUMBER 38 0 Person Identifier DW_PERSON_D PERSON_ID
PERSON_TYPE_ID NUMBER 38 0 Person Type Identifier DW_PERSON_TYPE_D_TL PERSON_TYPE_ID
ASSIGNMENT_STATUS_TYPE_ID NUMBER 38 0 Assignment Status Type Identifier DW_ASSIGNMENT_STATUS_D_TL ASSIGNMENT_STATUS_TYPE_ID
PERFORMANCE_RATING_LEVEL_ID NUMBER 38 0 Assignment Status Type Identifier DW_RATING_LEVEL_D RATING_LEVEL_ID
POTENTIAL_RATING_LEVEL_ID NUMBER 38 0 Assignment Status Type Identifier DW_RATING_LEVEL_D RATING_LEVEL_ID
MANAGER_ASSIGNMENT_ID NUMBER 38 0 Manager Assignment Identifier DW_PERSON_D PERSON_ID
MANAGER_ID NUMBER 38 0 Manager Person Identifier DW_PERSON_D PERSON_ID
ASSIGNMENT_NUMBER VARCHAR2 32 Assignment Number
ASSIGNMENT_NAME VARCHAR2 128 Assignment Name
WORKER_TYPE VARCHAR2 32 Indicates type of records- assignment , employment terms DW_WORKER_TYPE_D_TL WORKER_TYPE
WORKER_CATEGORY VARCHAR2 32 Employee category example: Blue collar, Civil Servant, etc DW_WORKER_CATEGORY_D_TL WORKER_CATEGORY
ASSIGNMENT_CATEGORY VARCHAR2 32 User defined category. For example Full-Time Permanent or Part-Time Permanent DW_ASSIGNMENT_CATEGORY_D_TL ASSIGNMENT_CATEGORY
LEGISLATION_CODE VARCHAR2 256 Legislation of Assignment
COUNTRY VARCHAR2 256 Person Country
SEX VARCHAR2 32 Person Sex
MARITAL_STATUS VARCHAR2 32 Person Marital Status
HIGHEST_EDUCATION_LEVEL VARCHAR2 32 Person Highest Educational Level
ETHNICITY VARCHAR2 32 Person Ethnicity
RELIGION VARCHAR2 32 Person Religion
DISABILITY_FLAG VARCHAR2 32 Person Disability Indicator Flag
PRIMARY_FLAG VARCHAR2 32 Represents overall Primary assignment
PRIMARY_ASSIGNMENT_FLAG VARCHAR2 32 Primary assignment Flag
SYSTEM_PERSON_TYPE VARCHAR2 32 Person Type in System
PAY_SYSTEM_STATUS VARCHAR2 32 Payroll status indicating whether the assignment is processed in payroll runs.
PER_SYSTEM_STATUS VARCHAR2 32 HR status used to determine how the assignment is processed
DATE_OF_BIRTH DATE Person Date of Birth
DATE_START DATE Start date for Assignment
ACTUAL_TERMINATION_DATE DATE Termination Date for Assignment
MANAGER_IND NUMBER 38 0 Manager Indicator
AGE_IN_YEARS NUMBER 38 0 Person Age as of Event Date in Years
AGE_IN_MONTHS NUMBER 38 0 Person Age as of Event Date in months
TENURE_IN_YEARS NUMBER 38 0 Person Tenure as of Event Date in years
TENURE_IN_MONTHS NUMBER 38 0 Person Tenure as of Event Date in Months
DAYS_SINCE_LATEST_PROMOTION NUMBER 38 0 Days since last promotion as of Event Date
COMPUTED_COMPA_RATIO NUMBER Warehouse Computed Compa Ratio. Formula used is (Salary/Grade Rate Mid Value)
ANNUALIZED_SALARY NUMBER For future use
COMPUTED_ANNUALIZED_SALARY NUMBER Calculated Annual Salary based in the formula Salary*Annualization factor
CURRENCY_CODE VARCHAR2 16 Currency Code for Salary
HEADCOUNT NUMBER Headcount Value
FTE NUMBER FTE Value
HIRE_EVENT_IND NUMBER 38 0 Hire Event Indicator
REHIRE_EVENT_IND NUMBER 38 0 Rehire Event Indicator
PROMOTION_EVENT_IND NUMBER 38 0 Promotion Event Indicator
TRANSFER_EVENT_IND NUMBER 38 0 Transfer Event Indicator
TERM_EVENT_IND NUMBER 38 0 Termination Event Indicator
VOL_TERM_EVENT_IND NUMBER 38 0 Voluntary Termination Event Indicator
INVOL_TERM_EVENT_IND NUMBER 38 0 Involuntary Termination Event Indicator
ASG_CATEGORY_CHANGE_IND NUMBER 38 0 Assignment Category Change Indicator
PER_SYSTEM_STATUS_CHANGE_IND NUMBER 38 0 Assignment Status Change Indicator
ASG_STATUS_TYPE_CHANGE_IND NUMBER 38 0 Assignment Status Type Change Indicator
JOB_CHANGE_IND NUMBER 38 0 Job Change Indicator
GRADE_CHANGE_IND NUMBER 38 0 Grade Change Indicator
LOCATION_CHANGE_IND NUMBER 38 0 Location Change Indicator
POSITION_CHANGE_IND NUMBER 38 0 Position Change Indicator
DEPARTMENT_CHANGE_IND NUMBER 38 0 Department Change Indicator
HEADCOUNT_CHANGE_IND NUMBER 38 0 Headcount Change Indicator
FTE_CHANGE_IND NUMBER 38 0 FTE Change Indicator
MANAGER_CHANGE_IND NUMBER 38 0 Manager Change Indicator
SALARY_CHANGE_IND NUMBER 38 0 Salary Change Indicator
FULL_PART_TIME_FLAG VARCHAR2 32 Full Part Time Flag
PERMANENT_TEMPORARY_FLAG VARCHAR2 32 Permanent Temporary Flag
Notes

#1 DW_JOB_D is date effective. Join should also consider fact EFFECTIVE_START_DATE to get the correct dimension values.

#2 DW_POSITION_D is date effective. Join should also consider fact EFFECTIVE_START_DATE to get the correct dimension values.

#3DW_PAY_GRADE_D is date effective. Join should also consider fact EFFECTIVE_START_DATE to get the correct dimension values.

#4 For getting Organization hierarchy, DEPARTMENT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#5 For getting Organization hierarchy, REPORTING_ESTABLISHMENT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#6 For getting Organization hierarchy, LEGAL_EMPLOYER_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#7 For getting Organization hierarchy, BUSINESS_UNIT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

DW_WRKFRC_ASG_F

Workforce Assignment Fact stores the assignment event along with effective dates

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_LATEST_CHANGE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ASSIGNMENT_ID, EFFECTIVE_SEQUENCE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_ID NUMBER 38 0 True Assignment identifier
EFFECTIVE_START_DATE DATE True Start date of event
EFFECTIVE_END_DATE DATE True End date of event
EFFECTIVE_SEQUENCE NUMBER 38 0 True Sequence to track multiple changes on same effective start date
EFFECTIVE_LATEST_CHANGE VARCHAR2 32 True Flag to indicate last transaction of a day
SOURCE_RECORD_ID VARCHAR2 256 Functional key of records.Combination of Assignment Id , Effective Start Date and effective Sequence
DEPARTMENT_ID NUMBER 38 0 Department Identifier#5 DW_DEPARTMENT_D DEPARTMENT_ID
REPORTING_ESTABLISHMENT_ID NUMBER 38 0 Reporting Establishment Identifier#6 DW_REPORTING_EST_D REPORTING_EST_ID
JOB_ID NUMBER 38 0 Job Identifier #1 DW_JOB_D JOB_ID
LOCATION_ID NUMBER 38 0 Location Identifier DW_WORKER_LOCATION_D LOCATION_ID
POSITION_ID NUMBER 38 0 Position Identifier#2 DW_POSITION_D POSITION_ID
GRADE_ID NUMBER 38 0 Grade Identifier#3 DW_PAY_GRADE_D GRADE_ID
LEGAL_EMPLOYER_ID NUMBER 38 0 Legal Employer#7 DW_LEGAL_EMPLOYER_D LEGAL_EMPLOYER_ID
BUSINESS_UNIT_ID NUMBER 38 0 Business Unit Identifier#8 DW_BUSINESS_UNIT_D BUSINESS_UNIT_ID
PERIOD_OF_SERVICE_ID NUMBER 38 0 Period of Service Identifier DW_SERVICE_PERIOD_D PERIOD_OF_SERVICE_ID
PERSON_ID NUMBER 38 0 Person Identifier#4 DW_PERSON_LEGISLATION_D PERSON_ID
ACTION_OCCURRENCE_ID NUMBER 38 0 Event Action Occurance Identifier
ACTION_ID NUMBER 38 0 Event Action Identifier DW_ACTION_D ACTION_ID
ACTION_REASON_ID NUMBER 38 0 Event Action reason Identifier DW_ACTION_REASON_D ACTION_REASON_ID
PERSON_TYPE_ID NUMBER 38 0 Person Type DW_PERSON_TYPE_D_TL PERSON_TYPE_ID
ASSIGNMENT_STATUS_TYPE_ID NUMBER 38 0 Assignment status type identifier DW_ASSIGNMENT_STATUS_D_TL ASSIGNMENT_STATUS_TYPE_ID
BUSINESS_GROUP_ID NUMBER 38 0 Business Group identifier
SYSTEM_PERSON_TYPE VARCHAR2 32 Person Type in System
PRIMARY_FLAG VARCHAR2 32 Represents overall Primary assignment
PRIMARY_ASSIGNMENT_FLAG VARCHAR2 32 Primary assignment Flag
MANAGER_FLAG VARCHAR2 32 Manager Flag, indicates if employee in this assignment is designated a manager.
ASSIGNMENT_NUMBER VARCHAR2 32 Assignment Number
ASSIGNMENT_NAME VARCHAR2 128 Assignment Name
LABOUR_UNION_MEMBER_FLAG VARCHAR2 32 Labour Union flag
WORKER_TYPE VARCHAR2 32 Identifies the type of record: either assignment (employee, CWK, applicant, non-workers) or a set of Terms DW_WORKER_TYPE_D_TL WORKER_TYPE
WORKER_CATEGORY VARCHAR2 32 Employee category example: Blue collar, Civil Servant, etc DW_WORKER_CATEGORY_D_TL WORKER_CATEGORY
ASSIGNMENT_CATEGORY VARCHAR2 32 User defined category. For example Full-Time Permanent or Part-Time Permanent DW_ASSIGNMENT_CATEGORY_D_TL ASSIGNMENT_CATEGORY
LEGISLATION_CODE VARCHAR2 256 Legislation of Assignment DW_LEGISLATION_D_TL LEGISLATION_CODE
HOURLY_SALARIED_CODE VARCHAR2 32 Identifies if the assignment is paid by the hour or by a salary
PAY_SYSTEM_STATUS VARCHAR2 32 Payroll status indicating whether the assignment is processed in payroll runs.
PER_SYSTEM_STATUS VARCHAR2 32 HR status used to determine how the assignment is processed
NORMAL_HOURS NUMBER Normal working hours
RETIREMENT_AGE NUMBER Retirement Age
RETIREMENT_DATE DATE Retirement Date
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.
FULL_PART_TIME_FLAG VARCHAR2 32 Full Part Time Flag
PERMANENT_TEMPORARY_FLAG VARCHAR2 32 Permanent Temporary Flag
Notes

#1 DW_JOB_D may contain multiple records for a job. Join should also consider fact calendar date to get the dimension values.

#2 DW_POSITION_D may contain multiple records for a position. Join should also consider fact calendar date to get the dimension values.

#3 DW_PAY_GRADE_D may contain multiple records for a grade. Join should also consider fact calendar date to get the dimension values.

#4 DW_PERSON_LEGISLATION_D may contain multiple records for a person. The person information is also legislation specific. Join should also consider fact calendar date and legislation to get the dimension values.

#5 For getting Organization hierarchy, DEPARTMENT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#6 For getting Organization hierarchy, REPORTING_ESTABLISHMENT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#7 For getting Organization hierarchy, LEGAL_EMPLOYER_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#8 For getting Organization hierarchy, BUSINESS_UNIT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

DW_WRKFRC_ASG_FTE_F

Workforce Assignment FTE Fact

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, ASSIGN_WORK_MEASURE_ID, EFFECTIVE_START_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGN_WORK_MEASURE_ID NUMBER 38 0 True Assignment Work Measure Identifier
EFFECTIVE_START_DATE DATE True Start date for Assignment Work measure
EFFECTIVE_END_DATE DATE True End date for Assignment Work measure
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of Assignment Supervisor ID Start Date and End Date
ASSIGNMENT_ID NUMBER 38 0 Assignment Identifier
ACTION_OCCURRENCE_ID NUMBER 38 0 HR Assignment Occurence Identifier
ACTION_ID NUMBER 38 0 HR Assignment Action Identifier DW_ACTION_D ACTION_ID
ACTION_REASON_ID NUMBER 38 0 HR Assignment Action Reason Identifier DW_ACTION_REASON_D ACTION_REASON_ID
FTE NUMBER Assignment Work measure value for FTE (Full Time Equivalent)
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_WRKFRC_ASG_HEADCOUNT_F

Workforce Assignment Headcount Fact

Details

Module: Global Human Resources

Key Columns

EFFECTIVE_END_DATE, ASSIGN_WORK_MEASURE_ID, EFFECTIVE_START_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGN_WORK_MEASURE_ID NUMBER 38 0 True Assignment Work Measure Identifier
EFFECTIVE_START_DATE DATE True Start date for Assignment Work measure
EFFECTIVE_END_DATE DATE True End date for Assignment Work measure
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of Assignment Supervisor ID, Start Date and End Date
ASSIGNMENT_ID NUMBER 38 0 Assignment Identifier
ACTION_OCCURRENCE_ID NUMBER 38 0 HR Assignment Occurence Identifier
ACTION_ID NUMBER 38 0 HR Assignment Action Identifier DW_ACTION_D ACTION_ID
ACTION_REASON_ID NUMBER 38 0 HR Assignment Action Reason Identifier DW_ACTION_REASON_D ACTION_REASON_ID
HEADCOUNT NUMBER Assignment Work measure value for Headcount
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_WRKFRC_ASG_NO_SUP_D

Workforce Assignments having no Supervisor

Details

Module: Global Human Resources

Key Columns

ASSIGNMENT_ID, EFFECTIVE_START_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_ID NUMBER 38 0 True Assignment Identifier
EFFECTIVE_START_DATE DATE True Start date for Assignment Work measure
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of Assignment Assignment Identifier, Start Date
EFFECTIVE_END_DATE DATE End date for Assignment Work measure
WORKER_TYPE VARCHAR2 32 Worker type identifier

DW_WRKFRC_ASG_SNAPSHOT_F

Workforce Assignment Snapshot Fact

Details

Module: Global Human Resources

Key Columns

ASSIGNMENT_ID, CALENDAR_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_ID NUMBER 38 0 True Assignment Identifier
CALENDAR_DATE DATE True Calendar Date DW_DAY_D CALENDAR_DATE
SOURCE_RECORD_ID VARCHAR2 256 Primary Key consisting of Assignment Identifier and Calendar Date
DEPARTMENT_ID NUMBER 38 0 Department Identifier#8 DW_DEPARTMENT_D DEPARTMENT_ID
REPORTING_ESTABLISHMENT_ID NUMBER 38 0 Reporting Establishment Identifier#9 DW_REPORTING_EST_D REPORTING_EST_ID
JOB_ID NUMBER 38 0 Job Identifier#1 DW_JOB_D JOB_ID
JOB_FAMILY_ID NUMBER 38 0 Job Family Identifier#2 DW_JOB_FAMILY_D JOB_FAMILY_ID
LOCATION_ID NUMBER 38 0 Location Identifier DW_WORKER_LOCATION_D LOCATION_ID
POSITION_ID NUMBER 38 0 Position Identifier#3 DW_POSITION_D POSITION_ID
GRADE_ID NUMBER 38 0 Grade Identifier#4 DW_PAY_GRADE_D GRADE_ID
RATE_VALUE_ID NUMBER 38 0 Rate Value Identifier#5 DW_GRADE_RATE_VALUE_D RATE_VALUE_ID
RATE_ID NUMBER 38 0 Rate Identifier#6 DW_GRADE_RATE_D RATE_ID
SALARY_BASIS_ID NUMBER 38 0 Salary Basis Identifier DW_SALARY_BASIS_D SALARY_BASIS_ID
LEGAL_EMPLOYER_ID NUMBER 38 0 Legal Employer Identifier#10 DW_LEGAL_EMPLOYER_D LEGAL_EMPLOYER_ID
BUSINESS_UNIT_ID NUMBER 38 0 Business Unit Identifier#11 DW_BUSINESS_UNIT_D BUSINESS_UNIT_ID
PERIOD_OF_SERVICE_ID NUMBER 38 0 Period of Service Identifier DW_SERVICE_PERIOD_D PERIOD_OF_SERVICE_ID
PERSON_ID NUMBER 38 0 Person Identifier#7 DW_PERSON_LEGISLATION_ALL_D PERSON_ID
PERSON_TYPE_ID NUMBER 38 0 Person Type Identifier DW_PERSON_TYPE_D_TL PERSON_TYPE_ID
ASSIGNMENT_STATUS_TYPE_ID NUMBER 38 0 Assignment Status Type Identifier DW_ASSIGNMENT_STATUS_D_TL ASSIGNMENT_STATUS_TYPE_ID
MANAGER_ASSIGNMENT_ID NUMBER 38 0 Manager Assignment Identifier
MANAGER_ID NUMBER 38 0 Manager Person Identifier DW_PERSON_D PERSON_ID
PERFORMANCE_RATING_LEVEL_ID NUMBER 38 0 Performance rating level id DW_RATING_LEVEL_D RATING_LEVEL_ID
POTENTIAL_RATING_LEVEL_ID NUMBER 38 0 Potential rating level id DW_RATING_LEVEL_D RATING_LEVEL_ID
WORKER_TYPE VARCHAR2 32 Identifies the type of record: either assignment (employee, CWK, applicant, non-workers) or a set of Terms. DW_WORKER_TYPE_D_TL WORKER_TYPE
WORKER_CATEGORY VARCHAR2 32 Employee category example: Blue collar, Civil Servant, etc DW_WORKER_CATEGORY_D_TL WORKER_CATEGORY
ASSIGNMENT_CATEGORY VARCHAR2 32 User defined category. For example Full-Time Permanent or Part-Time Permanent DW_ASSIGNMENT_CATEGORY_D_TL ASSIGNMENT_CATEGORY
LEGISLATION_CODE VARCHAR2 256 Legislation of Assignment DW_LEGISLATION_D_TL LEGISLATION_CODE
COUNTRY VARCHAR2 16 Identifies the Country of the Worker associated with the Assignment DW_COUNTRY_D_TL COUNTRY
SEX VARCHAR2 32 Identifies the Sex of Worker associated with the Assignment DW_SEX_D_TL SEX
MARITAL_STATUS VARCHAR2 32 Identifies the Marital Status of the Worker associated with the Assignment DW_MARITAL_STATUS_D_TL MARITAL_STATUS
HIGHEST_EDUCATION_LEVEL VARCHAR2 32 Identifies the Highest Education Level of the Worker associated with the Assignment DW_HIGHEST_DEGREE_D_TL HIGHEST_DEGREE
ETHNICITY VARCHAR2 32 Identifies the Ethnicity of the Worker associated with the Assignment DW_ETHNICITY_D_TL ETHNICITY
RELIGION VARCHAR2 32 Identifies the Religion of the Worker associated with the Assignment DW_RELIGION_D_TL RELIGION
DISABILITY_FLAG VARCHAR2 16 Identifies if the Worker associated with the assignment has disability
PRIMARY_FLAG VARCHAR2 32 Represents overall Primary assignment
PRIMARY_ASSIGNMENT_FLAG VARCHAR2 32 Primary assignment Flag
ASSIGNMENT_NUMBER VARCHAR2 32 Assignment Number
ASSIGNMENT_NAME VARCHAR2 128 Assignment Name
SYSTEM_PERSON_TYPE VARCHAR2 32 Person Type in System
PAY_SYSTEM_STATUS VARCHAR2 32 Payroll status indicating whether the assignment is processed in payroll runs.
PER_SYSTEM_STATUS VARCHAR2 32 HR status used to determine how the assignment is processed
DATE_OF_BIRTH Date Date of Birth of the Worker associated with the Assignment
DATE_START Date Start Date of the Worker
ACTUAL_TERMINATION_DATE Date Termination Date of the Worker
MANAGER_IND NUMBER 38 0 Indicates if the Worker in the Assignment is a Manager
AGE_IN_YEARS NUMBER 38 0 Age in Years of the Worker associated with the Assignment
AGE_IN_MONTHS NUMBER 38 0 Age in Months of the Worker associated with the Assignment
TENURE_IN_YEARS NUMBER 38 0 Service Period of the Worker
TENURE_IN_MONTHS NUMBER 38 0 Person Tenure in Months
DAYS_SINCE_LATEST_PROMOTION NUMBER 38 0 Number of Days since the last Promotion for the Worker in the Assignment
COMPUTED_COMPA_RATIO NUMBER Computed Compa-Ratio of the Worker in the Assignment
ANNUALIZED_SALARY NUMBER For future use
COMPUTED_ANNUALIZED_SALARY NUMBER Calculated Annual Salary based in the formula Salary*Annualization factor
CURRENCY_CODE VARCHAR2 16 Currency of Annualized Salary Amount
FTE NUMBER FTE Value
HEADCOUNT NUMBER Headcount Value
FULL_PART_TIME_FLAG VARCHAR2 32 Full Part Time Flag
PERMANENT_TEMPORARY_FLAG VARCHAR2 32 Permanent Temporary Flag
Notes

#1 DW_JOB_D may contain multiple records for a job. Join should also consider fact calendar date to get the dimension values.

#2 DW_JOB_FAMILY_D may contain multiple records for a job family.

#3 DW_POSITION_D may contain multiple records for a position. Join should also consider fact calendar date to get the dimension values.

#4 DW_PAY_GRADE_D may contain multiple records for a grade. Join should also consider fact calendar date to get the dimension values.

#5 DW_GRADE_RATE_VALUE_D may contain multiple records for a grade rate value. Join should also consider fact calendar date to get the dimension values.

#6 DW_GRADE_RATE_D may contain multiple records for a grade rate. Join should also consider fact calendar date to get the dimension values.

#7 DW_PERSON_LEGISLATION_D may contain multiple records for a person. The person information is also legislation specific. Join should also consider fact calendar date and legislation to get the dimension values.

#8 For getting Organization hierarchy, DEPARTMENT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#9 For getting Organization hierarchy, REPORTING_ESTABLISHMENT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#10 For getting Organization hierarchy, LEGAL_EMPLOYER_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

#11 For getting Organization hierarchy, BUSINESS_UNIT_ID represents DW_ORGANIZATION_CF_DH.ORGANIZATION_ID

DW_WRKFRC_ASG_SUPERVISOR_F

Workforce Assignment Supervisor Fact

Details

Module: Global Human Resources

Key Columns

ASSIGNMENT_SUPERVISOR_ID, EFFECTIVE_END_DATE, EFFECTIVE_START_DATE

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_SUPERVISOR_ID NUMBER 38 0 True Supervisor identifier for the Assignment
EFFECTIVE_START_DATE DATE True Start date of supervisor for the assignment
EFFECTIVE_END_DATE DATE True End date of supervisor for the assignment
SOURCE_RECORD_ID VARCHAR2 64 Source Record Identifier It is a unique combination of Assignment Supervisor ID, Start Date and End Date
ASSIGNMENT_ID NUMBER 38 0 Worker Assignment Identifier
PERSON_ID NUMBER 38 0 Worker Person Identifier DW_PERSON_D PERSON_ID
PRIMARY_FLAG VARCHAR2 32 Flag to indicate if this is the main supervisor. Y indicates main supervisor.
MANAGER_TYPE VARCHAR2 32 Identifies the role this supervisor has with regards to the overall organization structure.
MANAGER_ASSIGNMENT_ID NUMBER 38 0 Supervisors Assignment Identifier
MANAGER_ID NUMBER 38 0 Supervisors Person Identifier DW_PERSON_D PERSON_ID
ACTION_OCCURRENCE_ID NUMBER 38 0 HR Assignment Occurence Identifier
ACTION_ID NUMBER 38 0 HR Assignment Action Identifier DW_ACTION_D ACTION_ID
ACTION_REASON_ID NUMBER 38 0 HR Assignment Action Reason Identifier DW_ACTION_REASON_D ACTION_REASON_ID
DW_DATE_FROM DATE Warehouse Effective From Date for the Assignment Event. If Initial extract date is greater than EFFECTIVE_FROM_DATE then Initial extract date is used.
DW_DATE_TO DATE Warehouse Effective To Date for the Assignment Event. This is equal to EFFECTIVE_END_DATE
DW_INITIAL_RECORD_IND NUMBER 38 0 This indicates whether the record is the first event for the assignment to be brought into the warehouse based on initial extract date set by user.

DW_WRKFRC_GAIN_LOSS_ASG_F

Stores information about the Gain-Loss Events as happened in transaction system. The information is stored at the assignment level.

Details

Module: Global Human Resources

Key Columns

EVENT_ID, EVENT_DATE, ASSIGNMENT_ID, NET_GAIN_LOSS_IND

Columns

Name Datatype Length Precision Not Null Comments Referred Table Referred Column
ASSIGNMENT_ID NUMBER 38 0 True Assignment Identifier
EVENT_DATE DATE True Event Date
EVENT_ID NUMBER 38 0 True Event Identifier
NET_GAIN_LOSS_IND NUMBER 38 0 True 1 for Gain, -1 for Loss
SOURCE_RECORD_ID VARCHAR2 256 Primary Key consisting of Assignment Identifier,Event Date,Event ID and net gain loss indicator
GAIN_IND NUMBER 38 0 1 for Gain Event else 0
LOSS_IND NUMBER 38 0 1 for Loss Event else 0
GAIN_LOSS_DATE DATE Event Date for Gain Events, Event Date - 1 for Loss Events
ACTION_TYPE_CODE VARCHAR2 32 Action type code
ACTION_ID NUMBER 38 0 Event Action Identifier DW_ACTION_D ACTION_ID
ACTION_REASON_ID NUMBER 38 0 Event Action reason Identifier DW_ACTION_REASON_D ACTION_REASON_ID
ACTION_META_REASON_CODE VARCHAR2 128 Action Meta Reason Code
JOB_ID NUMBER 38 0 Job Identifier DW_JOB_D JOB_ID
DEPARTMENT_ID NUMBER 38 0 Department Identifier#4 DW_DEPARTMENT_D DEPARTMENT_ID
PERSON_ID NUMBER 38 0 Person Identifier DW_PERSON_D PERSON_ID
LOCATION_ID NUMBER 38 0 Location Identifier DW_WORKER_LOCATION_D LOCATION_ID
POSITION_ID NUMBER 38 0 Position Identifier DW_POSITION_D POSITION_ID
GRADE_ID NUMBER 38 0 Grade Identifier DW_PAY_GRADE_D GRADE_ID
SUPERVISOR_ID NUMBER 38 0 Person ID of the Immediate Line Manager DW_PERSON_D PERSON_ID
SUPERVISOR_ASSIGNMENT_ID NUMBER 38 0 Assignment ID of the Immediate Line Manager
LEGAL_EMPLOYER_ID NUMBER 38 0 Legal Employer Identifier#6 DW_LEGAL_EMPLOYER_D LEGAL_EMPLOYER_ID
BUSINESS_UNIT_ID NUMBER 38 0 Business Unit Identifier#7 DW_BUSINESS_UNIT_D BUSINESS_UNIT_ID
REPORTING_ESTABLISHMENT_ID NUMBER 38 0 Reporting Establishment Identifier#5 DW_REPORTING_EST_D REPORTING_EST_ID
PERIOD_OF_SERVICE_ID NUMBER 38 0 Period of Service Identifier DW_SERVICE_PERIOD_D PERIOD_OF_SERVICE_ID
PERSON_TYPE_ID NUMBER 38 0 Person Type Identifier DW_PERSON_TYPE_D_TL PERSON_TYPE_ID
SYSTEM_PERSON_TYPE VARCHAR2 32 Person Type in System
WORKER_TYPE VARCHAR2 32 Indicates type of records- assignment , employment terms DW_WORKER_TYPE_D_TL WORKER_TYPE
LEGISLATION_CODE VARCHAR2 256 Legislation of Assignment
ASSIGNMENT_CATEGORY VARCHAR2 32 User defined category. For example Full-Time Permanent or Part-Time Permanent DW_ASSIGNMENT_CATEGORY_D_TL ASSIGNMENT_CATEGORY
ASSIGNMENT_STATUS_TYPE_ID NUMBER 38 0 Assignment Status Type Identifier DW_ASSIGNMENT_STATUS_D_TL ASSIGNMENT_STATUS_TYPE_ID
PAY_SYSTEM_STATUS VARCHAR2 32 Payroll status indicating whether the assignment is processed in payroll runs.
PER_SYSTEM_STATUS VARCHAR2 32 HR status used to determine how the assignment is processed
WORKER_CATEGORY VARCHAR2 32 Employee category example: Blue collar, Civil Servant, etc DW_WORKER_CATEGORY_D_TL WORKER_CATEGORY
FULL_PART_TIME_FLAG VARCHAR2 32 Full/Part Time Flag associated with the Assignment
AGE_IN_YEARS NUMBER 38 0 Person Age as of Event Date in Years
TENURE_IN_YEARS NUMBER 38 0 Person Tenure as of Event Date in years
AGE_IN_MONTHS NUMBER 38 0 Person Age as of Event Date in months
TENURE_IN_MONTHS NUMBER 38 0 Person Tenure as of Event Date in Months
ASSIGNMENT_NUMBER VARCHAR2 32 Assignment Number
ASSIGNMENT_NAME VARCHAR2 128 Assignment Name
PRIMARY_FLAG VARCHAR2 32 Represents overall Primary assignment
PRIMARY_ASSIGNMENT_FLAG VARCHAR2 32 Primary assignment flag
HEADCOUNT NUMBER Headcount at the time of Event
FTE NUMBER FTE at the time of Event
EVENT_ASG_COUNT NUMBER 38 0 1
JOB_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the job else 0
FROM_JOB_ID NUMBER 38 0 Identifier of the Job where there is a loss DW_JOB_D JOB_ID
TO_JOB_ID NUMBER 38 0 Identifier of the Job where there is a gain DW_JOB_D JOB_ID
GRADE_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the grade else 0
FROM_GRADE_ID NUMBER 38 0 Identifier of the grade where there is a loss DW_PAY_GRADE_D GRADE_ID
TO_GRADE_ID NUMBER 38 0 Identifier of the grade where there is a gain DW_PAY_GRADE_D GRADE_ID
LOCATION_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the location else 0
FROM_LOCATION_ID NUMBER 38 0 Identifier of the location where there is a loss DW_WORKER_LOCATION_D LOCATION_ID
TO_LOCATION_ID NUMBER 38 0 Identifier of the location where there is a gain DW_WORKER_LOCATION_D LOCATION_ID
POSITION_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the position else 0
FROM_POSITION_ID NUMBER 38 0 Identifier of the position where there is a loss DW_POSITION_D POSITION_ID
TO_POSITION_ID NUMBER 38 0 Identifier of the position where there is a gain DW_POSITION_D POSITION_ID
DEPARTMENT_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the department else 0
FROM_DEPARTMENT_ID NUMBER 38 0 Identifier of the department where there is a loss DW_DEPARTMENT_D DEPARTMENT_ID
TO_DEPARTMENT_ID NUMBER 38 0 Identifier of the department where there is a gain DW_DEPARTMENT_D DEPARTMENT_ID
SUPERVISOR_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the immediate manager else 0
FROM_SUPERVISOR_ASG_ID NUMBER 38 0 Assignment Identifier of the Manager (Immediate) who has a loss
TO_SUPERVISOR_ASG_ID NUMBER 38 0 Assignment Identifier of the Manager(Immediate) who has a gain
FROM_SUPERVISOR_PERSON_ID NUMBER 38 0 Person Identifier of the Manager(Immediate) who has a loss DW_PERSON_D PERSON_ID
TO_SUPERVISOR_PERSON_ID NUMBER 38 0 Person Identifier of the Manager(Immediate) who has a gain DW_PERSON_D PERSON_ID
BUSINESS_UNIT_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the business unit else 0
FROM_BUSINESS_UNIT_ID NUMBER 38 0 Identifier of the business unit where there is a loss DW_BUSINESS_UNIT_D BUSINESS_UNIT_ID
TO_BUSINESS_UNIT_ID NUMBER 38 0 Identifier of the business unit where there is a gain DW_BUSINESS_UNIT_D BUSINESS_UNIT_ID
FULL_PART_TIME_GAIN_LOSS_IND NUMBER 38 0 1 when there is a gain or loss to the full or part time status else 0
FROM_FULL_PART_TIME_FLAG VARCHAR2 32 Full/Part time flag where there is a loss
TO_FULL_PART_TIME_FLAG VARCHAR2 32 Full/Part time flag where there is a gain
HIRE_EVENT_IND NUMBER 38 0 1 for hire event else 0 (based on action type)
REHIRE_EVENT_IND NUMBER 38 0 1 for rehire event else 0 (based on action type)
TERM_EVENT_IND NUMBER 38 0 1 for termination event else 0 (based on action type)
GLB_TRANSFER_IN_IND NUMBER 38 0 1 when there is a gain due to global transfer event else 0 (based on action type and active/inactive status)
GLB_TRANSFER_OUT_IND NUMBER 38 0 1 when there is a loss due to global transfer event else 0 (based on action type and active/inactive status)
GLB_TEMP_TRANSFER_IN_IND NUMBER 38 0 1 when there is a gain due to global temporary transfer event else 0 (based on action type)
GLB_TEMP_TRANSFER_OUT_IND NUMBER 38 0