| Data element |
Definition |
ACCOUNTING_PERIOD
Indexed - yes
Format - char (6)
May be null? yes |
The Accounting_Period for 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 the same as the first and last day
of the calendar month.
Example: JUL-99 (July 1999, which occurs in fiscal year 2000)
|
|
BATCH_ID
Indexed - yes
Format - char (30)
May be null? yes
|
The identifier for the BEN Financials batch job under which
the paycheck was posted.
Example: PRLPAY 000 19960702
|
CHECK_DATE
Indexed - yes
Format - date (7)
May be null? no
|
Date on which the payment is available to the employee, or
the effective date of an adjustment. The date printed on the check.
|
|
EARNINGS_TYPE
Indexed - no
Format - char (3)
May be null? yes
|
A 3-character code indicating the type of earnings. Currently,
the Salary Management collection does include some Employee Benefits, and
vacation and sick time data are only available for weekly paid employees.
There is one Employee_Payment record for each unique combination of person,
period, account combination and earnings type. (Benefits payments are summarized
at the 26-digit account level in the BALANCES table in the General Ledger
collection (using the appropriate object codes, such as 5190, EB Full Time).)
Examples: RMO (regular monthly pay); OT2 (overtime at double time)
Values:
Refer to the DWPAY.PAYROLL_EARNINGS table for values.
|
EMPLOYEE_TYPE
Indexed - no
Format - char (1)
May be null? yes |
Code indicating the Payroll system employee type for the employee,
for the job. This code can be used to distinguish whether the individual
is a monthy, weekly, full-time, paid professional, support staff or student
employee. Note: An employee with more than one job (for example, a faculty
member who also has an administrative job) may have more than one value
for Employee_Type.
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.
|
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
|
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)
|
HOME_DEPT
Indexed - yes
Format - char (5)
May be null? yes
|
The 5-digit Payroll code identifying the department that owns
the employee's record and is responsible for its maintenance. Department
numbers in the Personnel/Payroll and related systems were converted to four-digit
organization codes on July 16, 1999. All records after that date will have
null values for Home_Dept; Home_Dept_Org should be used in its place.
Example: 17101 (Data Administration)
Values:
Refer to the DWADMIN.ORG_OLD_TO_NEW table for values.
|
HOME_DEPT_ORG
Indexed - yes
Format - char (4)
May be null? yes
|
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.
|
HOME_SCHOOL
Indexed - yes
Format - char (2)
May be null? yes
|
The 2-digit Payroll code used to identify the school or center
that owns the employee's record and is responsible for its maintenance.
Home_School codes in the Personnel/Payroll and related systems were converted
to Home_School_Ctr codes on July 16, 1999. All records after that date will
have null values for Home_School; Home_School_Ctr should be used in its
place.
Example: 54 (School of Medicine)
Values:
Refer to the DWADMIN.ORG_OLD_TO_NEW 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'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.
|
JOB_CLASS
Indexed - yes
Format - char (6)
May be null? yes
|
The 6-digit code indicating the job for which the payment
was made. Key to the job classification table where the job attributes are
listed. Refer to Job_Title in the JOB_CLASS or JOB_CLASS_GENERAL tables
to find the title which currently corresponds to the Job_Class. As a result
of the University's Classification Redesign Project, a new job classification
system for staff positions was developed in 1998. Job classes in the online
Payroll system and the Data Warehouse were converted to this new system
over April 8 - 9, 1998. For jobs that were reclassified, records before
this date will contain the old class, and records after this date will reflect
the new scheme. Some classes were reused, however (and only the attributes
now used for those classes are available). Faculty classes were unaffected
by this conversion.
Example: 226010 (Associate Professor C-E)
Values:
Refer to the DWADMIN.JOB_CLASS table for values.
|
JOB_DEPT
Indexed - yes
Format - char (5)
May be null? yes
|
The 5-digit Payroll code identifying the department having
primary responsibility for the job for which the payment was made. The value
for Job_Dept is computed based on the EMPLOYEE_DISTRIBUTION table's records
for the Distribution_COA_Account (or, if prior to fiscal year 1999, the
Distribution_Account) for the Job_Class for the employee. If the employee
has more than one job with the same Job_Class and the same Distribution_COA_Account
(or Distribution_Account), Job_Dept is the department for the employee's
primary appointment. Department numbers in the Personnel/Payroll and related
systems were converted to four-digit organization codes on July 16, 1999.
All records after that date will have null values for Job_Dept; Job_Dept_Org
should be used in its place.
Example: 17101 (Data Administration)
Values:
Refer to the DWADMIN.ORG_OLD_TO_NEW table for values.
|
JOB_DEPT_ORG
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-digit number identifying the Organization having primary
responsibility for the job for which the payment was made. The value for
Job_Dept_Org is computed based on the EMPLOYEE_DISTRIBUTION table's records
for the Distribution_COA_Account (or, if prior to fiscal year 1999, the
Distribution_Account) for the Job_Class for the employee. If the employee
has more than one job with the same Job_Class and the same Distribution_COA_Account
(or Distribution_Account), Job_Dept_Org is the organization for the employee's
primary appointment. 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.
|
|
JOB_LINK_ID
Indexed - yes
Format - number (9,0)
May be null? no
|
The unique sequence number that identifies the
job (in the EMPLOYEE_JOB table) for which the payment was made. The proper
job is determined as follows:
- If there is only one job, that job's Job Link ID appears on the payment
row.
- If there is only one job with matching job class and appointment
begin / end dates which fit the Pay Period End Date, that job's Job
Link ID appears on the payment row.
- The first distribution (lowest job sequence / distribution sequence
number) with (a) matching job class, (b) distribution start / stop dates
and (c) matching distribution account which fit the Pay Period End Date.
- If there are no jobs that fit the payment using the above three criteria,
the payment is assigned to the primary appointment.
|
JOB_SCHOOL
Indexed - yes
Format - char (2)
May be null? yes
|
The 2-digit Payroll code used to identify the school or center
that having primary responsibility for the job for which the payment was
made. The value for Job_School is computed based on the EMPLOYEE_DISTRIBUTION
table's records for the Distribution_COA_Account (or, if prior to fiscal
year 1999, the Distribution_Account) for the Job_Class for the employee.
If the employee has more than one job with the same Job_Class and the same
Distribution_COA_Account (or Distribution_Account), Job_School is the school
for the employee's primary appointment. Job_School codes in the Personnel/Payroll
and related systems were converted to Job_School_Ctr codes on July 16, 1999.
All records after that date will have null values for Job_School; Job_School_Ctr
should be used in its place.
Example: 54 (School of Medicine)
Values:
Refer to the DWADMIN.ORG_OLD_TO_NEW table for values.
|
JOB_SCHOOL_CTR
Indexed - yes
Format - char (2)
May be null? yes
|
A 2-digit number used to identify the school or center having
primary responsibility for the job for which the payment was made. The value
for Job_School_Ctr is computed based on the EMPLOYEE_DISTRIBUTION table's
records for the Distribution_COA_Account (or, if prior to fiscal year 1999,
the Distribution_Account) for the Job_Class for the employee. If the employee
has more than one job with the same Job_Class and the same Distribution_COA_Account
(or Distribution_Account), Job_School_Ctr is the center for the employee's
primary appointment.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 Job_School_Ctr = Center_Code.
|
LAST_EXTRACT_DATE
Indexed - yes
Format - date (7)
May be null? no
|
The date the EMPLOYEE_PAYMENT record was extracted from the
Payroll system and loaded into the Warehouse.
|
PAY_PERIOD_END_DATE
Indexed - no
Format - date (7)
May be null? yes
|
Date through which the payment was made to the employee.
Values:
For weekly employees:
1) The pay period end date is always a Sunday
date (payments are made for Monday through
the pay period end date).
2) The check date is the following Friday date.
For monthly employees:
1) The pay period end date is always the last
day of the month.
2) The check date is the last weekday of the month.
|
PAYMENT_AMOUNT
Indexed - no
Format - number (9,2)
May be null? yes
|
The amount of the Earnings_Type paid from the Posted_COA_Account
(or, for fiscal year 1998 and earlier, from the Requested_Account) to the
employee for the job on the Check_Date; or the amount of the reallocation.
Credits to an account will appear as negative amounts. There may be more
than one record per employee per account per period, depending upon Earnings_Type.
Values:
-9,999,999.99 - 9,999,999.99
|
PAYMENT_SEQUENCE_NUMBER
Indexed - yes
Format - char (3)
May be null? no
|
A two-digit number used to distinguish one payment or reallocation
from another for the employee, Posted_COA_Account, and check date.
|
PENN_ID
Indexed - no
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.
|
POSITION_NUMBER
Indexed - no
Format - char (3)
May be null? yes
|
A 3-digit suffix used to particularize the 9-digit Requested_Account
in the financial system used before July 1, 1996. This Position_Number is
not to be confused with Univ_Position_Number. Use of 9-digit account numbers
was phased out at the end of June 1998, when the Payroll system became fully
compliant with the present General Ledger accounting structure. This column
should be null for all records from fiscal year 1999 onward.
|
POSTED_COA_ACCOUNT
Indexed - yes
Format - char (26)
May be null? no
|
The 26-digit account number from the present General Ledger
accounting structure (in use at Penn since July 1, 1996) indicating the
source of the payment to the employee. The Payroll system was rewritten
to accept the 26-digit account code combination in place of legacy 9-digit
account numbers for fiscal year 1999. Effective June 28, 1998, all Payroll
transansactions that require an account are completed using the 26-digit
account code combination. For records prior to fiscal year 1999, this data
element contains the account number that was returned by the GL mapper utility,
given the Requested_Account. In cases where the Requested_Account cannot
be converted, Posted_COA_Account is a suspense account, using the CNAC and
Org that do convert - or, if they do not convert, the CNAC and Org are based
on the Home_Dept. Suspense Accounts have 9501 as the value for Posted_COA_Object.
In cases where the subcode portion of the Requested_Account cannot be translated
to an Object code, OBJT will be used as the Object code. Example: 01111011000000OBJT00000000,
where OBJT indicates that there is not an object code mapping for the requested
subcode portion of the Requested_Account.
The Posted_COA_Account consists of seven segments:
Posted_COA_CNAC
Posted_COA_Org
Posted_COA_BC
Posted_COA_Fund
Posted_COA_Object
Posted_COA_Program
Posted_COA_Center_Ref
See also the definitions for Posted_COA_CNAC, Posted_COA_Org, Posted_COA_BC,
Posted_COA_Fund, Posted_COA_Object, Posted_COA_Program, and Posted_COA_Center_Ref.
|
POSTED_COA_BC
Indexed - no
Format - char (1)
May be null? yes
|
The budgetary control character that represents the level
at which funds availability checking occurs for the salary expense. Posted_COA_BC
is the third segment of the Posted_COA_Account.
Example: 1 (CNAC/ORG/FUND Year-To-Date)
Values:
0 Project-To-Date for capital projects
1 CNAC/ORG/FUND Year-To-Date
2 CNAC/ORG/FUND/CREF Year-To-Date
4 CNAC/ORG/FUND/CREF Project-to-Date
|
POSTED_COA_CENTER_REF
Indexed - yes
Format - char (4)
May be null? yes
|
The Center Reference Code. A 4-character identifier uniquely
defined by each School or Center. Posted_COA_Center_Ref is used to record
information that is important to a school or center but is inappropriate
for any other segment of the Posted_COA_Account. The value of the Posted_COA_Center_Ref
is unique within a school or center. Posted_COA_Center_Ref is the seventh
and last segment of the Posted_COA_Account.
Examples (for center 02): 4045 (Graduate Programs); 4091 (SAS Newsletter)
Values:
Refer to the DWADMIN.CENTER_REF_CODES table for values.
|
POSTED_COA_CNAC
Indexed - yes
Format - char (3)
May be null? yes
|
The 3-character Center Net Asset Class. The first two positions
of the Posted_COA_CNAC identify the school or center. The last position
identifies the Net Asset Class: 0 (Unrestricted); 1 (Temporarily Restricted);
2 (Permanently Restricted). Posted_COA_CNAC is the first segment of the
Posted_COA_Account.
Examples: 880 (Medical School, Unrestricted); 921 (Human Relations, Temporarily
Restricted)
Values:
Refer to the DWADMIN.CNAC_CODES table for values.
|
POSTED_COA_FUND
Indexed - yes
Format - char (6)
May be null? yes
|
The 6-character fund code. A fund is the unique identifier
for a specific set of financial resources that needs tracking or management.
Posted_COA_Fund is the fourth segment of the Posted_COA_Account. If your
organization is the Responsible_Org for the fund (even if it is not the
Posted_COA_Org, the Home_Dept_Org, or the Job_Dept_Org) you will have access
to the record.
Examples: 000000 (General Unrestricted Fund); 010201 (SAS: GEN FUND 1)
Values:
Refer to the DWADMIN.FUND_CODES table for values.
|
POSTED_COA_OBJECT
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character Object Code that identifies the asset, liability,
revenue, or expense. Posted_COA_Object is the fifth segment of the Posted_COA_Account.
The Payroll system was rewritten to accept that 26-digit account code combination
in place of legacy 9-digit account numbers for fiscal year 1999. Effective
June 28, 1998, all Payroll transactions that require an account are completed
using the 26-digit account code combination. For records prior to fiscal
year 1999, in cases where the Requested_Account could not be converted,
Posted_COA_Account is a suspense account, and 9501 is the value for the
Posted_COA_Object. In cases where the subcode section of the Requested_Account
could not be translated to an Object_Code, OBJT will be the value for Posted_COA_Object.
Examples: 5220 EXTERNAL OFFICE SUPPLIES); 1149 (CASH SUSPENSE)
Values:
Refer to the DWADMIN.OBJECT_CODES table for values.
|
POSTED_COA_ORG
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character code identifying the Organization. This is
a subdivision of the University created for management purposes. An Organization
belongs to only one responsibility center, and its Organization code is
unique. That is, no two Organizations will have the same values for Posted_COA_Org.
Posted_COA_Org is the second segment of the Posted_COA_Account.
Examples: 0702 (Wharton Finance); 0705 (Wharton Legal Studies)
Values:
Refer to the DWADMIN.ORG_CODES table for values.
|
POSTED_COA_PROGRAM
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character code indicating the program. This is an activity
or work process commonly defined across the University for tracking a program's
activity across Schools and Centers (or across organizations within a school
or center). Posted_COA_Program is the sixth segment of the Posted_COA_Account.
Examples: 7311 (ALUMNI RELATIONS); 0001 (DISCRETIONARY)
Values:
Refer to the DWADMIN.PROGRAM_CODES table for values.
|
REALLOCATION_CODE
Indexed - no
Format - char (2)
May be null? yes
|
A two-character code that specifies the reason an employee
payment has been reallocated. The only valid reallocation codes are 01,
02, 03. In the Salary Management mainframe database the reallocation code
data element is defined as a numeric field of 2 bytes. If the record does
not contain a reallocation code, the value is null, and extracted to the
Data Warehouse as 00. Although this field may be updated in the Payroll
system, values for this column in the Data Warehouse are only imported when
the payment record is initially loaded to the Warehouse. The Data Warehouse
is not an appropriate source for investigating reallocations, as these values
are not updated at any later date.
Example: 01 (No change in effort on project. Salary cost transferred
to continuation year.)
Values:
01 - No change in effort on project. Salary cost
transferred to continuation year.
02 - Error correction; identified after review of
detail with Principal Investigator.
03 - Error correction; data entry error.
|
REALLOCATION_STATUS
Indexed - no
Format - char (1)
May be null? yes
|
The 1-character code that indicates the status of the payment
or reallocation. Although this field may be updated in the Payroll system,
values for this column in the Data Warehouse are only imported when the
payment record is initially loaded to the Warehouse. The Data Warehouse
is not an appropriate source for investigating reallocations, as these values
are not updated at any later date.
Example: R (Payment has been reallocated.)
Values:
N - Never : An initial salary payment or
adjustment, or a payment record added as the
result of a reallocation transaction.
R - Reallocated : Payment has been reallocated.
P - Pending : A payment has a reallocation
entered, but the reallocation has not yet
been processed.
H - Hold : A reallocation has been entered, and
the account mapping has become invalid.
These records or the account mappings must
be corrected.
|
REQUESTED_ACCOUNT
Indexed - yes
Format - char (9)
May be null? yes
|
For fiscal year 1998 and earlier, the 9-digit account number
(from the financial system used before July 1, 1996) indicating the source
of the payment to the employee. The first digit indicates the ledger. Digits
1-6 are the account number. Digits 7-9 are the subcode. Use of 9-digit account
numbers was phased out at the end of June 1998, when the Payroll system
became fully compliant with the present General Ledger accounting structure.
This column should be null for all records from fiscal year 1999 onward;
refer to Posted_COA_Account to determine the source of the payment to the
employee.
|
RESPONSIBLE_ORG
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character code for the organization responsible for
managing the Posted_COA_Fund. Many organizations may use the same fund,
but only one organization -- the Responsible_Org -- is accountable for managing
the fund. Those who are authorized to access records for the Responsible_Org
for the fund may access all records for the fund, regardless of the value
of the Posted_COA_Org in the Posted_COA_Account for the record.
Values:
Refer to the DWADMIN.ORG_CODES table for values.
|
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.
|
UNIV_POSITION_NUMBER
Indexed - yes
Format - number (7)
May be null? yes
|
The 7-digit number that identifies a position at the University
of Pennsylvania. A position is an assignment of a person to perform a specific
job in a specific organization that is supported by specific funding source(s).
Over time, the person, job classification, organization, and funding sources
associated with a position may change. As of this writing (August, 1999),
faculty, temporary workers, and student workers do not have a number. In
instances where no number is available, the element Univ_Position_Number
will be zero.
|
