Data Warehouse

IS&T Home >   Data Warehouse

MIT Data Warehouse HR APPT ACTION DETAIL Table


This table contains one record per personnel action (such as New Hire, Transfer, Annual Salary Review, Salary Change, Leave of Absence, Promotion) pertaining to an appointment for an employee. This is a new table created in July 2006 to allow an employee more than one personnel action per appointment on the same day. Prior to that, personnel action in the HR_APPT_TX_DETAIL table has always retained only the latest action for an appointment on a particular date.


Field Name

Description

Data Type

Length

ACTION_AMOUNT

XXX

NUMBER

22

ACTION_LAST_UPDATE_DATE

XXX

DATE

8

ACTION_LAST_UPDATE_USER

XXX

VARCHAR2

12

APPOINTMENT_WAGE_TYPE

XXX

VARCHAR2

25

APPOINTMENT_WAGE_TYPE_CODE

XXX

VARCHAR2

4

APPT_BEGIN_DATE

Start date for an employee's appointment to a specific MIT position.

DATE

8

APPT_END_DATE

End date for an employee's appointment to a specific MIT position or 12/31/2999 for indefinite end

DATE

8

APPT_LAST_UPDATE_DATE

XXX

DATE

8

APPT_LAST_UPDATE_USER

XXX

VARCHAR2

12

APPT_SUBTYPE

Descriptive text about the appointment, e.g., academic or non-academic, regular or short-term, exempt or non-exempt, etc.

VARCHAR2

100

APPT_SUBTYPE_CODE

4-character alphanumeric code for the appointment subtype, e.g., RGE1 - non-academic regular or STE1 - non-academic short-term exempt.

VARCHAR2

4

APPT_TX_BEGIN_DATE

Effective date of a change to an appointment, i.e. salary increase, leave, change in effort, etc.

DATE

8

APPT_TX_END_DATE

This field does not have any business meaning, and will be hidden in the future

DATE

8

APPT_TX_WORK_BEGIN_DATE

XXX

DATE

7

APPT_TX_WORK_END_DATE

XXX

DATE

7

APPT_WORK_BEGIN_DATE

XXX

DATE

7

APPT_WORK_END_DATE

XXX

DATE

7

FTE_ORIGINAL_BASE_AMOUNT

XXX

NUMBER

22

FTE_REPORT_BASE_AMOUNT

XXX

NUMBER

22

FTE_TEMP_CHANGE_BASE_AMOUNT

XXX

NUMBER

22

HR_APPT_TX_KEY

XXX

NUMBER

22

HR_APPT_TYPE_KEY

XXX

VARCHAR2

20

HR_JOB_KEY

This field should be used to join HR_POSITION and HR_JOB tables with such fact tables as HR_APPOINTMENT_DETAIL, HR_APPT_TX_DETAIL, and HR_POSITION_DETAIL and their LIMITED versions.

VARCHAR2

10

HR_ORG_UNIT_KEY

This field should be used to join HR_ORG_UNIT and HR_ORG_UNIT_HIERARCHY tables with such fact tables as HR_APPOINTMENT_DETAIL, HR_APPT_TX_DETAIL, and HR_POSITION_DETAIL and their LIMITED versions.

VARCHAR2

8

HR_PERSONNEL_ACTION_TYPE_KEY

This field is used to join HR_PERSONNEL_ACTION_DETAIL table with HR_PERSONNEL_ACTION_TYPE table.

VARCHAR2

4

HR_POSITION_KEY

This field should be used to join HR_POSITION and HR_POSITION_LIMITED tables with such fact tables as HR_APPOINTMENT_DETAIL, HR_APPT_TX_DETAIL, and HR_POSITION_DETAIL and their LIMITED versions.

VARCHAR2

10

IS_SHIFT_ELIGIBLE

Is this a shift type of position, Y or N

VARCHAR2

1

MIT_ID

MIT ID number of the person. Use this field to join with other tables containing data about people at MIT.

VARCHAR2

9

ORIGINAL_BASE_AMOUNT

Annual base salary, not including partial pay or temporary pay

NUMBER

11.2

ORIGINAL_EMPLOYMENT_PERCENT

Percent effort based on normal working hours. For Faculty and exempt staff, 100% represents full time effort. For Support staff, it is normal work hours divided by # of hours in work schedule rule

NUMBER

7.4

ORIGINAL_FTE_PERCENT_EFFORT

Percent effort based on normal working hours. For Faculty 100% represents full time effort. For Support and Service staff, 100% represents a 35, 37.5 or 40 hour work week.

NUMBER

7.4

ORIGINAL_HOURLY_RATE

Hourly rate of pay, not including partial pay or temporary pay, for hourly paid employees only. This is 0 for monthly paid employees.

NUMBER

7.2

ORIGINAL_PERIOD_AMOUNT

Monthly pay amount, not including partial pay or temporary pay, for monthly paid employees. This is 0 for hourly paid employees.

NUMBER

11.2

ORIGINAL_WORK_WEEK

Normal weekly hours for hourly paid employees only. This is 0 for monthly paid employees.

NUMBER

4.2

PAY_BASIS

Description of the timeframe for this appointment, eg. 09 months, 12 months, fall (9/1 to 1/15), temporary (< 12 months), etc.

VARCHAR2

50

PAY_BASIS_CODE

Code specifying the number of months or timeframe for this appointment, eg. 09 (for 9 months), 12 (for 12 months), fall (for 9/1 to 1/15), temp (for < 12 months), etc.

VARCHAR2

7

PAY_BASIS_NUMBER_OF_MONTHS

The actual mumber of months for a particular appointment. Examples: 12 months = 12.0, 9 months = 9.0, fall = 5.0, temporary = 12.0, etc.

NUMBER

2

PERSONNEL_ACTION_DATE

XXX

DATE

8

PERSONNEL_ACTION_SEQ_NUMBER

XXX

VARCHAR2

3

PERSONNEL_KEY

Key field used to join to the PERSONNEL table. This field should only be used for joining data, NOT for reporting.

VARCHAR2

8

RECORD_COUNTER

Used to count records within a group on a report. Always contains a value of 1.

NUMBER

1

REPORT_BASE_AMOUNT

Current annual base salary. If there is a temporary change in base amount, it will appear in this field; otherwise, the field will display the original base amount.

NUMBER

11.2

REPORT_EMPLOYMENT_PERCENT

Same as Original Employment Percent unless the person has a temporary change in effort, up or down, in which case this field will show the percent based on the temporary change.

NUMBER

7.4

REPORT_FTE_PERCENT_EFFORT

Current percent effort based on normal working hours. For Faculty, 100% represents full time effort. For Support and Service staff, 100% represents a 40 hour work week. If there is a temporary change FTE percent effort amount, it will appear in this field, otherwise the field wil display the original fte percent effort.

NUMBER

7.4

REPORT_HOURLY_RATE

Current hourly wage for hourly paid employees only. This is 0 for monthly paid employees. If there is a temporary change hourly amount, it will appear in this field, otherwise the field will display the original hourly rate.

NUMBER

11.2

REPORT_PERIOD_AMOUNT

Current monthly amount for monthly paid employees only. This is 0 for hourly paid employees. If there is a temporary change monthly amount, it will appear in this field, otherwise the field will display the original period amount.

NUMBER

11.2

REPORT_WORK_WEEK

Current normal weekly hours for hourly paid employees only. This is 0 for monthly paid employees. If there is a temporary change work week, it will appear in this field, otherwise the field will display the original work week.

NUMBER

4.2

SUPPLEMENT_TYPE

Supplements are payments outside the appointment base amount. Ex: rewards & recognition

VARCHAR2

2

SUPPLEMENT_TYPE_CODE

2 character alphanumeric code of supplement_type: EX: A1, CA, D9

VARCHAR2

1

TEMP_CHANGE_BASE_AMOUNT

Temporary annual base salary, used for partial pay or when temporary pay applies

NUMBER

11.2

TEMP_CHANGE_EMPLOYMENT_PERCENT

XXX

NUMBER

7.4

TEMP_CHANGE_FTE_PERCENT_EFFORT

Temporary percent effort based on normal working hours. For Faculty 100% represents full time effort. For Support and Service staff, 100% represents a 35, 37.5 or 40 hour work week.

NUMBER

7.4

TEMP_CHANGE_HOURLY_RATE

Temporary hourly rate for hourly paid employees. This is 0 for monthly paid employees.

NUMBER

7.2

TEMP_CHANGE_PERIOD_AMOUNT

Temporary monthly pay amount for monthly paid employees. This is 0 for hourly paid employees.

NUMBER

11.2

TEMP_CHANGE_REASON

Description of the temporary action reason, e.g., leave of absence, extended sick leave

VARCHAR2

100

TEMP_CHANGE_REASON_CODE

Code for temporary action reason, e.g., LOA, ESL

VARCHAR2

2

TEMP_CHANGE_WORK_WEEK

Temporary normal weekly hours for hourly paid employees only. This is 0 for monthly paid employees.

NUMBER

4.2

WAREHOUSE_LOAD_DATE

The date the record was loaded into the Data Warehouse from the source system.

DATE

8

WORKING_WEEK

Normal weekly hours

VARCHAR2

100

WORKING_WEEK_CODE

Code for normal weekly hours

VARCHAR2

2

WORK_SCHEDULE_RULE

Code specifying the number of normal hours for the job, e.g., 35_WW

VARCHAR2

8