Penn Computing

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

PAR_MAIN Table - Data Element Index

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



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

PAR_MAIN 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