DW_CMP_ASG_SALARY_MONTHLY_SNAPSHOT_F (Preview)

This table requires activation of Salary Basis functional area. This table holds employee's Salary record As of every Month end along with demographics data

Details

Module: Compensation

Key Columns

CALENDAR_DATE, ASSIGNMENT_ID

Columns

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 This column is for Oracle Internal use only. Primary Key consisting of Assignment Identifier and Calendar Date
SALARY_ID NUMBER 38 0 Salary Identifier DW_CMP_ASG_SALARY_DETAIL_D SALARY_ID
SALARY_BASIS_ID NUMBER 38 0 Salary Basis Identifier DW_SALARY_BASIS_D SALARY_BASIS_ID
CAL_MONTH_ID NUMBER 38 0 Month Identifier DW_MONTH_D CAL_MONTH_ID
CAL_QUARTER_ID NUMBER 38 0 Quarter Identifier DW_QUARTER_D CAL_QUARTER_ID
CAL_YEAR_ID NUMBER 38 0 Year Identifier DW_YEAR_D CAL_YEAR_ID
SALARY_AMOUNT NUMBER Salary Amount
SALARY_CHANGE_AMT NUMBER Salary increase amount
PREV_AMT NUMBER Previous Salary amount
ANNUALIZED_SALARY NUMBER Annualized Salary
ANNUALIZED_FULLTIME_SALARY NUMBER Annualized Salary when the person FTE = 1
CURRENCY_CODE VARCHAR2 16 Currency of Annualized Salary Amount
GLOBAL_SALARY_AMOUNT NUMBER Salary Amount in Warehouse currency
GLOBAL_SALARY_CHANGE_AMT NUMBER Salary increase amount in Warehouse currency
GLOBAL_PREV_AMT NUMBER Previous Salary amount in Warehouse Currency
GLOBAL_ANNUALIZED_SALARY NUMBER Annualized Salary in Warehouse currency
GLOBAL_ANNUALIZED_FULLTIME_SALARY NUMBER Annualized Salary when the person FTE = 1 in Warehouse currency
GLOBAL_CURRENCY_CODE VARCHAR2 16 Global Currency Code
SALARY_CHANGE_PER NUMBER Salary increase %
ANNUALIZATION_FACTOR NUMBER Annualized factor
ELEMENT_TYPE_ID NUMBER 38 0 Element Type identifier
RATE_ID NUMBER 38 0 Rate Identifier#6 DW_GRADE_RATE_D RATE_ID
RANGE_DIFF_ID NUMBER 38 0 Salary Range differential Identifier DW_CMP_RANGE_DIFF_D RANGE_DIFF_ID
ZONE_TYPE_ID NUMBER 38 0 Compensation Zone Type Identifier DW_CMP_ZONE_TYPE_D_TL GEOGRAPHY_TYPE_ID
ZONE_ID NUMBER 38 0 Compensation Zone Identifier DW_CMP_ZONE_D_TL GEOGRAPHY_ID
RATE_NAME VARCHAR2 256 Grade Rate Name
RATE_TYPE VARCHAR2 32 Type of Rate ( Salary, Bonus, Overtime, etc)
RATE_OBJECT_TYPE VARCHAR2 32 Rate object type (Grade , Step, etc)
RATE_FREQUENCY VARCHAR2 32 Frequency of rate (Monthly, Annualy, etc)
MINIMUM NUMBER Minimum Value of salary range (After applying diff profile)
MID_VALUE NUMBER Mid point of salary range
MAXIMUM NUMBER Maxmum value of salary range
PREV_SALARY_BASIS NUMBER Previous Salary basis ID
PREV_CURR_CODE VARCHAR2 999 Previous Currency code
RANGE_DIFF_FACTOR NUMBER Salary range differentail Factor (muliplier or grade rate)
QUARTILE VARCHAR2 999 Quartile of salary
QUINTILE VARCHAR2 999 Quintile of salary
COMPARATIO NUMBER Compa Ratio of salary
PERCENTILE NUMBER Percentile of salary
ACTION_OCCURRENCE_ID NUMBER 38 0 It defines the action occurrences for an action
ACTION_ID NUMBER 38 0 Actions Identifier
ACTION_REASON_ID NUMBER 38 0 Actions reason identifier
NEXT_SAL_REVIEW_DATE DATE Next Salary Review 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
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_EDUCATION_LEVEL_D_TL HIGHEST_EDUCATION_LEVEL
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_YEARS_ID NUMBER 38 0 Age in Years of the Worker associated with the Assignment.Null stamped with default.
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_YEARS_ID NUMBER 38 0 Service Period of the Worker.Null stamped with default.
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
DAYS_SINCE_LAST_PROMOTION NUMBER 38 0 Days since last promotion or start of assignment information as of Event Date
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
FREQUENCY VARCHAR2 32 Frequency of normal working hours:week,month,year
NORMAL_HOURS NUMBER Normal working hours
BARGAINING_UNIT_CODE VARCHAR2 32 Bargaining Unit Code
PROBATION_PERIOD NUMBER Duration of probation period
PROBATION_UNIT VARCHAR2 32 Units of probation period duration
GLOBAL_CURRENCY_EXCH_RATE NUMBER Global currency conversion rate.
WORK_TERMS_ASSIGNMENT_ID NUMBER 38 0 Work Terms Assignment Identifier
CURRENT_IND NUMBER 38 0 Current date indicator

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