| Data element |
Definition |
ACCOUNTING_PERIOD
Indexed - yes
Format - char (6)
May be null? yes |
The Accounting_Period within which the Check_Date falls. It should be possible to recognize entries for a given fiscal year by doing a comparison on this date. For example, a check date of July 2, 1999 corresponds to the JUL-99 Accounting_Period, within fiscal year 2000. The Accounting Period is stated in MON-YY format. MON is the calendar month (stored in upper case), and YY is the calendar year. Note that the beginning and ending dates of an accounting period are not necessarily the same as the first and last day of the calendar month.
Example: JUL-99 (July 1999, which occurs in fiscal year 2000)
Source: Derived from ACCT_CHECK_DATE, during data load.
|
ACCT_CHECK_DATE
Indexed - yes
Format - date
May be null? yes |
The Check Date of the Payroll Run for the transaction; indicates the month of posting to the General Ledger.
Source: #XPAR-CHECK-DATE
|
ADDITIONAL_ADVANCE_PAY
Indexed - no
Format - number (7,2)
May be null? yes |
The amount of addtional advance pay. Only used if an advance transaction (indicated by an 'AD' at data entry) was entered.
Source: #XPAR-ADDNTL-ADV
|
ADVANCE_PAY
Indexed - no
Format - number (7,2)
May be null? yes |
The amount of advance pay. Only used if an advance transaction (indicated by an 'AD' at data entry) was entered.
Source: #XPAR-ADV-PAY
|
AFFECTED_QTR
Indexed - no
Format - char (1)
May be null? yes |
A 1-character representation of the effected quarter of the calendar year.
Values:
1 first quarter
2 second quarter
3 third quarter
4 fourth quarter
Source: #XPAR-AFFECT-QTR
|
AFFECTED_YEAR
Indexed - no
Format - char (1)
May be null? yes |
Indicates whether year-to-date balances are affected or updated by the transaction.
Values:
Y year-to-date balances updated
N no year-to-date balances affected
Source: #XPAR-AFFECT-YEAR
|
| CALENDAR_MONTH
Indexed - yes
Format - char (2)
May be null? yes |
The number that identifies the calendar month (of the accounting period).
Example: 01 (January); 11 (November).
Values:
01 January
02 February
03 March
04 April
05 May
06 June
07 July
08 August
09 September
10 October
11 November
12 December
13 Adjustment period
Source: Derived from ACCT_CHECK_DATE, during data load.
|
| CALENDAR_YEAR
Indexed - yes
Format - char (4)
May be null? yes |
The calendar year for the ACCOUNTING_PERIOD. The calendar year begins January 1 and ends December 31. Example: 1999 (the CALENDAR_YEAR for ACCOUNTING_PERIOD JUL-99).
Source: Derived from ACCT_CHECK_DATE, during data load. |
CHECK_ADVICE_INDICATOR
Indexed - yes
Format - char(1)
May be null? yes |
A 1-character flag indicatating if the payment form was Check or Advice.
Values:
A Advice
C Check
Source: #XPAR-CK-OR-ADV |
CHECK_ADVICE_NUMBER
Indexed - yes
Format - char(7)
May be null? yes |
The number of the Check or Advice.
Source: #XPAR-CHK-ADV
|
DEDUCTION_PERIOD
Indexed - no
Format - char(1)
May be null? yes |
For records where SEPARATE_CHECK_INDICATOR is not null (i.e., contains values 1 through 9),) and VOLUNTARY_DEDUCTION_INDICATOR = ‘N’ (all normal deductions), indicates the week of the month when the deduction is taken.
Values: 1 through 5 (indicating deduction week)
Source: #XPAR-DED-CD
|
DEDUCTION_SCHEDULE
Indexed - no
Format - char(2)
May be null? yes |
Schedule according to which the employee’s normal deductions are scheduled to be taken.
Values:
W1 weekly pay
M1 monthly pay
M2 monthly exec pay
Source: #XPAR-DEDUCTION-SCHEDULE |
EMPLOYEE_TYPE
Indexed - no
Format - char(1)
May be null? yes |
Code indicating the Payroll system employee type for the employee, for the employee's first job. This code can be used to distinguish whether the individual is a monthy, weekly, full-time, paid professional, support staff or student employee.
Examples: 1 (monthly paid salaried; exempt from overtime); 3 (non-exempt hourly paid)
Values:
Refer to the DWCOMP.COMP_EMPLOYEE_TYPE_LIST table
for values.
Source: #XPAR-EMPLOYEE-TYPE |
EMPLOYMENT_DATE
Indexed - no
Format - date
May be null? yes |
The employee's most recent date of hire by the home department. Example: Jane Doe is hired on January 1, 1989; therefore the Employment_Date is initially entered as 01/01/89. Jane Doe is terminated from the University of Pennsylvania on January 2, 1996, but is rehired one year later on January 2, 1997. The Employment_Date for Jane Doe is now January 2, 1997. In the case that an employee holds more than one job, the employment date is still controlled by the home department.
Source: #XPAR-EMPLOYMT-DATE
|
EMPLOYMENT_STATUS
Indexed - no
Format - char(1)
May be null? no |
A 1-character code identifying the employee's employment relationship with the University of Pennsylvania at the time the payment was issued.
Values:
A Active. Eligible for benefits
within certain employee categories.
L Leave of Absence Without Pay.
Benefits are suspended. Employee
must arrange with the Benefits Office
to pay for or waive benefits.
P Leave of Absence with Pay. Eligible
for benefits.
T Separated or Terminated. No longer
eligible for University-paid benefits.
Source: #XPAR-EMPL-STATUS |
EMPLOYMENT_STATUS_DATE
Indexed - no
Format - date
May be null? yes |
The date the employee's employment status went into effect.
Source: #XPAR-STATUS-EFF-DATE
|
EXTRACT_DATE
Indexed - yes
Format - date
May be null? no |
The date the record was extracted from the Payroll system and loaded into the Warehouse.
|
FICA_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
FICA Gross amount for the transaction.
Source: #XPAR-FICA-GROSS
|
FISCAL_MONTH_SEQ
Indexed - yes
Format - char (2)
May be null? yes |
The month (or accounting period) of the fiscal year. This field is used for sorting.
Examples: 01 (July); 11 (May). See also FISCAL_YEAR
Values:
01 July
02 August
03 September
04 October
05 November
06 December
07 January
08 February
09 March
10 April
11 May
12 June
13 Adjustment period
Source: Derived from ACCT_CHECK_DATE, during data load.
|
FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null? yes |
The financial year within which the Accounting_Period falls. Penn's fiscal year begins July 1 of one calendar year and ends June 30 of the next calendar year.
Example: for a Check_Date of December 1, 1999, 2000 (fiscal year that began July 1, 1999, and ended June 30, 2000)
Source: Derived from ACCT_CHECK_DATE, during data load. |
FUT_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Federal Unemployment Tax Gross amount for the transaction.
Source: #XPAR-FUT-GROSS
|
FWT_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Federal Gross amount for the transaction.
Source: #XPAR-FWT-GROSS
|
GROSS_OVERRIDE
Indexed - no
Format - char (1)
May be null? yes |
Indicates whether gross calculations are overriden for the transaction.
Values:
<empty> Normal
N Grosses calculated (used for hand-drawn
checks, pay_trans_code = ‘80’)
Y Override calculated grosses with input grosses
used for cancellations, pay_trans_code = ’75’)
Source: #XPAR-GRS-OVERRIDE
|
HOME_DEPT_ORG
Indexed - yes
Format - char(4)
May be null? no |
The 4-digit number that represents the organization that owns the employee'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)
Values:
Refer to the DWADMIN.ORG_CODES table for values.
Source: #XPAR-DEPT-NO |
HOME_SCHOOL_CTR
Indexed - yes
Format - char(2)
May be null? no |
A 2-digit number used to identify the school or center that owns the employee'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)
Values:
Refer to the DWADMIN.ORG_CODES table, for values
where Home_School_Ctr = Center_Code.
Source: #UDPT-REPORTING-LVL(2,2) |
LAST_PAY_DATE
Indexed - no
Format - date
May be null? yes |
The date of the last pay. This should be the same as the Pay_End_Date for the record.
Source: #XPAR-LAST-PAY-DATE |
LOCAL1_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Local 1 Gross amount for the transaction.
Source: #XPAR-LOCAL1-GROSS
|
LOCAL2_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Local 2 Gross amount for the transaction.
Source: #XPAR-LOCAL2-GROSS
|
MEDICARE_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Medicare Gross amount for the transaction.
Source: #XPAR-MEDICARE-GROSS
|
NET_PAY
Indexed - no
Format - number(11,2)
May be null? yes |
The net payment amount for this transaction.
Source: #XPAR-NET-PAY
|
PAR_CHECK_DATE
Indexed - yes
Format - date
May be null? yes |
Date on which the payment is available to the employee, or the effective date of an adjustment. The date printed on the check.
Source: #XPAR-CHECK-DATE
|
PAR_NAME
Indexed - yes
Format - char(30)
May be null? no |
The employee name on pay stub at the time the payment was issued. This field will not be updated if the employee name changes in the EMPLOYEE table.
Source: #XPAR-NAME
|
PAR_SEQUENCE_NUMBER
Indexed - yes
Format - number(5)
May be null? no |
The sequence number for PAR record, assigned during the data extract process. |
PAY_CYCLE
Indexed - yes
Format - char(2)
May be null? no |
The pay cycle.
Values:
W1 – weekly cycle
M1 – monthly cycle
M2 – monthly exec cycle
XX – W2 adjustment cycles
Source: #XPAR-PAY-CYCLE
|
PAY_END_DATE
Indexed - yes
Format - date
May be null? no |
Date through which the transaction was made to the employee.
Source: #XPAR-PAY-END-DATE
|
PAY_SCHEDULE
Indexed - yes
Format - char(3)
May be null? no |
Three digit schedule indicating the pay within the month.
Values:
001 – first weekly pay
002 – second weekly pay
003 – third weekly pay
004 – fourth weekly pay
005 – fifth weekly pay
006 – monthly pay
007 – monthly Executive Pay
998 – W-2 Executive adjustment cycle
999 – W-2 adjustment cycle
Source: #XPAR-SCHEDULE |
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
|
PENSION_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Pension Gross amount for the transaction; used only for pension earnings (PEN).
Source: #XPAR-PENSION-GROSS
|
PRIOR_PAY_DATE
Indexed - no
Format - date
May be null? yes |
The pay date prior to the Last_Pay_Date.
Source: #XPAR-PRIOR-PAY-DT
|
RETIREMENT_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Retirement Gross amount for the transaction.
Source: #XPAR-RETR-GROSS
|
SDI_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
State Disability Gross amount for the transaction.
Source: #XPAR-SDI-GROSS
|
SEPARATE_CHECK_INDICATOR
Indexed - yes
Format - char (1)
May be null? yes |
Flag indicating separate taxing calculations.
Values:
<blank>
1 through 9 (indicates separate taxing done)
Source: #XPAR-SEP-CHK-IND |
SEPARATE_CHECK_PAY_END_DATE
Indexed - no
Format - date
May be null? yes |
For records where the transaction is a separate chech (SEPARATE_CHECK_INDICATOR is null and TRANSACTION_CODE ’85’, the date of the latest activity for the transaction.
Source: #XPAR-SEP-CHK-PPE-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 Admininistration.
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: #XPAR-ID-NO
|
STATE_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
State Gross amount for the transaction.
Source: #XPAR-STATE-GROSS
|
SUT_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
State Unemployment Tax Gross amount for the transaction.
Source: #XPAR-SUT-GROSS
|
TOTAL_GROSS
Indexed - no
Format - number(11,2)
May be null? yes |
Total Gross amount for the transaction.
Source: #XPAR-TOTAL-GROSS
|
TRANSACTION_CODE
Indexed - yes
Format - char(2)
May be null? no |
A 2-character code indicating the type of transaction.
Values:
65 Expense Transfer
70 Cancellation
75 Overpayment
80 Hand-Drawn Check
85 Current Payroll Activity
Source: #XPAR-TRANS-SEQ-CD |
VOLUNTARY_DEDUCTION_INDICATOR
Indexed - no
Format - char(1)
May be null? yes |
For records where SEPARATE_CHECK_INDICATOR is not null (i.e., contains values 1 through 9), indicates the types of deductions taken from the pay.
Values:
N all normal deductions taken
T only tax deductions taken
E tax and retirement deductions only taken
Source: #XPAR-VOL-DED-IND |
