Penn Computing

University of Pennsylvania
Penn Computing << go backback
EMPLOYEE_BENEFITS Table   Tables and Data Elements    Salary Management Home   Data Warehouse Home

EMPLOYEE_BENEFITS Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.



Data element Definition
ADP_ELGIBILITY_GROUP

Indexed - no
Format - varchar2 (5)
May be null? yes

Eligibility group used by ADP – third party benefits administrator.

Source: UDBS-ADP-ELIG-GROUP

BRIDGING_DATE

Indexed - no
Format - date
May be null? yes
Reflects the "new date of hire" once an employee's time is bridged.
COV_CHARGE_SOURCE_DEN

Indexed - no
Format - char (1)
May be null? yes

Journal charging method for Post Doc Benefit Plan.

Source: UDBS-COV-CHARGE-SOURCE_DEN

Values:
B  Charge department the difference between the full cost
   of the coverage and the cost for single coverage for a 
   post doc in the HMO coverage area selecting the PPO plan.
	 
F  Charge department the difference between the full cost
   of the coverage and the cost for single coverage.
	 
P  Charge department the difference between the full cost
   of the coverage and the cost for single coverage for 
   medical only.
COV_CHARGE_SOURCE_MED

Indexed - no
Format - char (1)
May be null? yes

Journal charging method for Post Doc Benefit Plan.

Source: UDBS-COV-CHARGE-SOURCE_MED

Values:
B  Charge department the difference between the full cost
   of the coverage and the cost for single coverage for a 
   post doc in the HMO coverage area selecting the PPO plan.
	 
F  Charge department the difference between the full cost
   of the coverage and the cost for single coverage.
	 
P  Charge department the difference between the full cost
   of the coverage and the cost for single coverage for 
   medical only.
      
COV_CHARGE_SOURCE_VIS

Indexed - no
Format - char (1)
May be null? yes

Journal charging method for Post Doc Benefit Plan.

Source: UDBS-COV-CHARGE-SOURCE_VIS

Values:
B  Charge department the difference between the full cost
   of the coverage and the cost for single coverage for a 
   post doc in the HMO coverage area selecting the PPO plan.
	 
F  Charge department the difference between the full cost
   of the coverage and the cost for single coverage.
	 
P  Charge department the difference between the full cost
   of the coverage and the cost for single coverage for 
   medical only.
      

DATE_OF_RETIREMENT

Indexed - no
Format - date
May be null? yes

Date of retirement.

Source: ACTUAL-BENE-DATE

DEDUCTION_SCHEDULE

Indexed - no
Format - varchar2 (2)
May be null? yes

Indicates which schedule deductions (such as health/welfare) are taken.

Source: XDBS-DEDUCTION-SCHEDULE

Values:
W1, M1
DENTAL_COVERAGE_CODE

Indexed - no
Format - varchar2 (2)
May be null? yes

Coverage Code for dental plan.

Source: UDBS-DENTAL-COV-CODE

Values:
01    Employee only
03    Employee plus 1
04    Employee plus 2 or more

DENTAL_FAM_EFF_DATE

Indexed - no
Format - date
May be null? yes

Source: UDBS-DENTAL-PLAN-DATE
DENTAL_OPTION

Indexed - no
Format - varchar2 (1)
May be null? yes
Source: UDBS-DENTAL-OPTION
DENTAL_PLAN

Indexed - no
Format - varchar2 (1)
May be null? yes

Dental Plan ID.

Source: UDBS-DENTAL-PLAN

Values:
L      MetLife Dental
N      PFP Dental
P      Aetna Dental HMO (Post Doc Benefit Plan)
O      Aetna Dental PPO (Post Doc Benefit Plan)
EMPLOYEE_BASIC_PERCENT

Indexed - no
Format - number (2,2)
May be null? yes

Employee Basic Percent – can be up to 5.00 percent.

Source: UDBS-EMPL-BASIC-PCT

EXPECTED_RETIREMENT_DATE

Indexed - no
Format - date
May be null? yes

Date of expected retirement.

Source: UDBS_EXPECT_RTRMT_DT

EXTRACT_DATE

Indexed - yes
Format - date
May be null? no
The date the EMPLOYEE_BENEFITS record was extracted from the Payroll system and loaded into the Warehouse. This information is stored in the Warehouse in date format. In Business Objects, it is displayed as specified in the desktop operating system settings. It is recommended that users specify a format including 4 digits for the year. (To specify the date format in Windows, use the Control Panel's Regional Settings, selecting the Date tab and specifying the Short date style. On the Macintosh, use the Date & Time Control Panel.)
GROUP_INSURANCE_AMOUNT

Indexed - no
Format - number (6)
May be null? yes

Amount of Group Basic Life Insurance.

Source: UDBS-GROUP-INS-AMT

GROUP_INSURANCE_CODE

Indexed - no
Format - varchar2 (1)
May be null? yes

Factor of Salary that is the employee’s group life insurance amount.

Source: UDBS-GROUP-INS-CD

GROUP_INSURANCE_OPTION

Indexed - no
Format - varchar2 (1)
May be null? yes

Source: UDBS-GROUP-INS-OPTION
GROUP_INSURANCE_OPT_AMOUNT

Indexed - no
Format - number (6)
May be null? yes

Amount of optional life insurance.

Source: UDBS-GROUP-INS-OPT-AMT

GROUP_INS_BEGIN

Indexed - no
Format - varchar2 (1)
May be null? yes

Source: UDBS-GROUP-INS-BGN
GROUP_PLAN_DATE

Indexed - no
Format - date
May be null? yes

Source: UDBS-GROUP-PLAN-DATE
HEALTH_COVERAGE_CODE

Indexed - no
Format - varchar2 (2)
May be null? yes

Coverage Code for health plan.

Source: XDBS-HEALTH-COV-CODE

Values:
01     Employee only
03     Employee plus 1
04     Employee plus 2 or more
HEALTH_OPTION

Indexed - no
Format - varchar2 (1)
May be null? yes

Source: UDBS-HEALTH-OPTION
HEALTH_PLAN

Indexed - yes
Format - varchar2 (2)
May be null? yes

Health Plan ID.

Source: XDBS-HEALTH-PLAN

Values:
E,F,T,N,D,S     Aetna HMO
A, B     Ameri-health HMO
K     Keystone HMO
M     PennCare Personal Choice
C     BC/BS Plan 100
U     UPHS POS
P, PF     Aetna HMO (Post Doc Benefit Plan)
O, OF     Aetna PPO (Post Doc Benefit Plan)
HEALTH_PLAN_DATE

Indexed - no
Format - date
May be null? yes

Source: XDBS-HEALTH-PLAN-DATE
HOME_DEPT_ORG

Indexed - yes
Format - char (4)
May be null? yes

The 4-digit number that represents the organization that owns the EMPLOYEE_BENEFITS's record and is responsible for its maintenance. An organization is a subdivision of the University created for management purposes. An Organization belongs to only one School or Responsibility Center, and its organization code is unique. (The code set is from the University's General Ledger, and has been in use since July 1, 1996.)

Examples: 0104 (CHEMISTRY); 0001 (GEN UNIVERSITY)

Source: XPAR-DEPT-NO
Values:
Refer to the DWADMIN.ORG_CODES table for values.
HOME_SCHOOL_CTR

Indexed - yes
Format - char (2)
May be null? yes

A 2-digit number used to identify the school or center that owns the EMPLOYEE_BENEFITS's record and is responsible for its maintenance. The code set is from the University's General Ledger, and has been in use since July 1, 1996.

Example: 40 (School of Medicine)

Source: UDBS-HOME-SCHOOL

Values:

Refer to the DWADMIN.ORG_CODES table, for values where 
Home_School_Ctr = Center_Code.
LEAVE_ACCRUAL_DATE

Indexed - no
Format - date
May be null? yes

Vacation/Sick/Personal Date. The date is populated for all full time and part time employees (FULL_PART_TIME value of 'F' or 'P' in the EMPLOYEE table). For employees with special benefits (FULL_PART_TIME value 'S'), the date will appear as 12/31/9999.

Source: XDBS-LEAVE-ACCRL-DATE

LEAVE_ACCRUAL_SCHEDULE

Indexed - no
Format - varchar2 (2)
May be null? yes

Leave Accrual Schedule.

Source: XDBS-LEAVE-ACCRL-SCHED

LTD_DATE

Indexed - no
Format - date
May be null? yes

Long-term disability date.

Source: XDBS-LTD-DATE

MAXIMUM_403B

Indexed - no
Format - number (7,2)
May be null? yes

Individual 403B Retirement Maximum. If zero, the federal maximum is used.

Source: UDBS-403B-RED-MAX-LIMIT

MAXIMUM_457B

Indexed - no
Format - number (7,2)
May be null? yes

Individual 457B Retirement Maximum. This is only entered for the employees eligible for 457B retirement plan.

Source: UDBS-457B-RED-MAX-LIMIT

PENN_ID

Indexed - yes
Format - char (8)
May be null? yes

The 8-digit identification number assigned to an individual by Penn Community system. For example: 77777777. No two persons have the same Penn ID. Note that Penn IDs created in and assigned by Penn Community begin at 10000000. Within the Data Warehouse, the numeric range from 00000000-00999999 is used for individuals who do not yet have a Penn ID in Penn Community, to allow for reliable joins between tables using the PENN_ID column. When a Penn ID appears for the individual appear in Penn Community, it will replace the assigned value throughout the Data Warehouse.

Source: Determined by mapping SSN to Penn_ID

PHASED_RETIREMENT_DATE

Indexed - no
Format - date
May be null? yes

The date that an employee entered the Phased Retirement Program.

RETIREMENT_PLAN

Indexed - no
Format - varchar2 (1)
May be null? yes

Retirement Plan

Source: XDBS-RETRMT-PLAN

Values:
1     TDR
2     TDR (after tax)
3     RAP
7     SRA only
P     Police Pension
RETIREMENT_PLAN_DATE

Indexed - no
Format - date
May be null? yes

Date eligible for retirement.

Source: XDBS-RETRMT-PLAN-DATE

SSN

Indexed - yes
Format - char (9)
May be null? no

The 9-digit Social Security Number of the employee. A Social Security Number is a unique number assigned to an individual by the federal Social Security Administration.

Values in this column will only be displayed to individuals with access to employee SSN data; all other users will retrieve nulls in this column.

Source: XDBS-ID-NUMBER

TDR_VESTING_DATE

Indexed - no
Format - date
May be null? yes

The date that the vesting period begins (in most instances, the original hire date).
TDR_VESTING_MONTHS

Indexed - no
Format - number(2)
May be null? yes

Indicator of the number of vesting months an employee has accrued (maximum value 36).
TDR_VESTING_PERCENT

Indexed - no
Format - number(3)
May be null? yes

Designates the employee's vesting percentage in the Plan (will either be 0% or 100%).
TIAA_PERCENT

Indexed - no
Format - number (3)
May be null? yes

Percent of employee and employer basic retirement for TIAA_CREF.

Note: TIAA Percent + Vanguard Percent = 100.00 percent

Source: UDBS-TIAA-CREF-PCT

VANGUARD_PERCENT

Indexed - no
Format - char (1)
May be null? yes

Percent of employee and employer basic retirement for Vanguard.

Note: TIAA Percent + Vanguard Percent = 100.00 percent

Source: UDBS-VAN-PCT

VESTED_YEARS

Indexed - no
Format - number (2)
May be null? yes

Source: UDBS-VESTED-YRS
VISION_PLAN

Indexed - no
Format - char (1)
May be null? yes

Vision plan code.

Source: UDBS-VISION-PLAN

Values: 
D Davis Vision 
O Spectera Vision  PPO (Post Doc Benefit Plan)
VISION_COVERAGE_CODE

Indexed - no
Format - char (2)
May be null? yes

Vision coverage level.

Source: UDBS-VISION-COV-CODE

Values: 
01  employee only
03  employee plus 1
04  employee plus 2 or more

EMPLOYEE_BENEFITS Table   Tables and Data Elements    Salary Management Home   Data Warehouse Home

Questions about this page? Email us at da-staff@isc.upenn.edu

Information Systems and Computing
University of Pennsylvania
Information Systems and Computing, University of Pennsylvania