| Data element |
Definition |
DISTRIBUTION_ACCOUNT
Indexed - yes
Format - char (9)
May be null? yes |
For fiscal year 1998 and earlier, the 9-digit account number
(from the Payroll system) indicating the source of this payroll distribution
for 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 Distribution_COA_Account
to determine the source of the payroll distributions for the employee for
fiscal year 1999 and later.
|
|
DISTRIBUTION_AMOUNT
Indexed - no
Format - number (9,3)
May be null? yes
|
For salaried employees, the amount per pay period to be paid
to the employee from the Distribution_COA_Account (or, if prior to fiscal
year 1999, the Distribution_Account), from the distribution start date through
the distribution stop date, inclusive. (For employees who are not salaried,
the value of Distribution_Amount should be 0.)
Values:
0 to 999,999.999
|
DISTRIBUTION_COA_ACCOUNT
Indexed - yes
Format - char (26)
May be null? yes
|
The 26-digit account number from the present General Ledger
accounting structure (in use at Penn since July 1, 1996) indicating the
source of this payroll distribution for 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 Distribution Account. In cases where
the subcode portion of the Distribution_Account could not be translated
to an Object code, OBJT was used as the Object code. Example: 01111011000000OBJT00000000,
where OBJT indicates that there was not an object code mapping for the requested
subcode portion of the 9-digit Distribution_Account. The Distribution_COA_Account
consists of seven segments:
Distribution_COA_CNAC
Distribution_COA_Org
Distribution_COA_BC
Distribution_COA_Fund
Distribution_COA_Object
Distribution_COA_Program
Distribution_COA_Center_Ref
See also the definitions for Distribution_COA_CNAC, Distribution_COA_Org,
Distribution_COA_BC, Distribution_COA_Fund, Distribution_COA_Object, Distribution_COA_Program,
and Distribution_COA_Center_Ref.
|
|
DISTRIBUTION_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. Distribution_COA_BC is the
third segment of the Distribution_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
|
DISTRIBUTION_COA_CENTER_REF
Indexed - yes
Format - char (4)
May be null? yes |
The Center Reference Code. This is a 4-character identifier
uniquely defined by each School or Center. Distribution_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 Distribution_COA_Account. The
value of the Distribution_COA_Center_Ref is unique within a school or center.
Distribution_COA_Center_Ref is the seventh and last segment of the Distribution_COA_Account.
Examples (for center 02): 4045 (Graduate Programs); 4091 (SAS Newsletter)
Values:
Refer to the DWADMIN.CENTER_REF_CODES table for values.
|
DISTRIBUTION_COA_CNAC
Indexed - yes
Format - char (3)
May be null? yes |
The 3-character Center Net Asset Class. The first two positions
of the Distribution_COA_CNAC identify the school or center. The last position
identifies the Net Asset Class: 0 (Unrestricted); 1 (Temporarily Restricted);
2 (Permanently Restricted). Distribution_COA_CNAC is the first segment of
the Distribution_COA_Account.
Examples: 880 (Medical Center, Unrestricted); 921 (Human Resources, Temporarily
Restricted)
Values:
Refer to the DWADMIN.CNAC_CODES table for values.
|
DISTRIBUTION_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.
Distribution_COA_Fund is the fourth segment of the Distribution_COA_Account.
If your organization is the Responsible_Org for the fund (even if it is
not the Distribution_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.
|
DISTRIBUTION_COA_OBJECT
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character Object Code. Identifies the asset, liability,
revenue, or expense. 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 subcode section
of the Distribution Account could not be translated to an Object code, OBJT
will be the value for Distribution_COA_Object. Distribution_COA_Object is
the fifth segment of the Distribution_COA_Account.
Examples: 5220 (EXTERNAL OFFICE SUPPLIES); 1149 (CASH SUSPENSE).
Values:
Refer to the DWADMIN.OBJECT_CODES table for values.
|
DISTRIBUTION_COA_ORG
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character code that identifies the Organization. An
Organization 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 Distribution_COA_Org. Distribution_COA_Org is the second segment of
the Distribution_COA_Account.
Examples: 0702 (Wharton Finance); 0705 (Wharton Legal Studies)
Values:
Refer to the DWADMIN.ORG_CODES table for values.
|
DISTRIBUTION_COA_PROGRAM
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character program code. A program 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). Distribution_COA_Program is the sixth segment of the Distribution_COA_Account.
Examples: 7311 (Alumni Relations); 0001 (Discretionary)
Values:
Refer to the DWADMIN.PROGRAM_CODES Table for values.
|
DISTRIBUTION_PERCENT
Indexed - no
Format - number (5,2)
May be null? yes
|
For hourly employees, the percentage of standard hours to
be paid from the Distribution_COA_Account (or, if prior to fiscal year 1999,
the Distribution_Account). The percentage is used in Time Reporting to calculate
the number of hours to print on the Time Report Transactions. Example: A
weekly paid employee with a Distribution_Percent of 50, in a period containing
40 hours, would have 20.00 hours generated on the Time Report Transaction
Form (40 times 50%). Entries include the decimal point. Example: 50.00 (50%).
This data element is zero for salaried employees (EMPLOYEE_JOB/Appt_Employee_Types
1, 2, 4, 8, or 9). When a job appointment has more than one distribution
line, a percentage must be listed on each line. The sum of the entries in
Distribution_Percent should equal 100% for this appointment for the employee.
|
DISTRIBUTION_SEQUENCE_NUMBER
Indexed - yes
Format - char (2)
May be null? no
|
Number used to distinguish one distribution record from another
for this employee and job sequence number.
|
DISTRIBUTION_START_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date on which payments will begin to be charged to the
Distribution_COA_Account (or, for records prior to fiscal year 1999, the
date on which payments began to be charged to the Distribution_Account).
The date must be greater than or equal to the appointment begin date, and
less than or equal to the appointment end date.
|
DISTRIBUTION_START_FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The financial year within which the Distribution_Start_Date
falls. Penn's fiscal year begins July 1 of one calendar year and ends June
30 of the next calendar year.
Example: For Distribution_Start_Date 8/15/99, the Distribution_Start_Fiscal_Year
would be 2000 (the fiscal year that began July 1, 1999, and ended June
30, 2000)
|
DISTRIBUTION_START_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The calendar year within which the Distribution_Start_Date
falls.
Example: For Distribution_Start_Date 8/15/99, the calendar year is 1999.
|
DISTRIBUTION_STOP_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date when payment from this Distribution_COA_Account (or,
for records prior to fiscal year 1999, the date when payment from the Distribution_Account)
will cease. The date must be greater than or equal to the appointment begin
date, and less than or equal to the appointment end date. Note: If there
is no definite end date, Distribution_Stop_Date will default to the latest
available date in the Warehouse, which is 12/31/9999 (while the default
for distributions with no end dates is actually stored in Payroll as 9999/99/99).
|
DISTRIBUTION_STOP_FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The financial year within which the Distribution_Stop_Date
falls. Note: If there is no definite end date for the distribution, Distribution_Stop_Fiscal_Year
will default to the latest available year in the Warehouse, which is 9999.
Penn's fiscal year begins July 1 of one calendar year and ends June 30 of
the next calendar year.
Example: for Distribtuion_Stop_Date 8/31/99, the Distribution_Stop_Fiscal_Year
would be 2000 (the fiscal year that began July 1, 1999, and ended June
30, 2000).
|
DISTRIBUTION_STOP_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The calendar year within which the Distribution_Stop_Date
falls. Note: If there is no definite end date for the distribution, Distribution_Stop_Year
will default to the latest available year in the Warehouse, which is 9999.
Example: For Distribution_Stop_Date 8/31/99, the Distribution_Stop_Year
is 1999.
|
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.
Examples: RMO (regular monthly pay); OT2 (overtime at double time)
Values:
Refer to the DWPAY.PAYROLL_EARNINGS table for values.
|
HISTORY_STATUS
Indexed - yes
Format - char (1)
May be null? no
|
The 1-character code that indicates whether the EMPLOYEE_DISTRIBUTION
data is Current (C) or History (H). Rows with a History_Status of "C" reproduce
the EMPLOYEE_DISTRIBUTION records as they currently exist in the Payroll
system (or as they existed immediately before the employee was purged from
the Payroll system). Rows with a History_Status of "H" represent earlier
versions of the data. Note that "current" does not mean "currently active."
Setting a condition of History_Status equal to "C" will not retrieve an
accurate list of currently active EMPLOYEE_DISTRIBUTION records. Along with
this condition, set conditions on Distribution_Start_Date and Distribution_Stop_Date
to select currently active distribution lines. (See also History_Status_Date.)
Values:
C Current (that is, the most recent
in the Payroll system)
H History
|
HISTORY_STATUS_DATE
Indexed - no
Format - date (7)
May be null? yes
|
Date on which the History_Status for this EMPLOYEE_DISTRIBUTION
record changed from C (current) to H (history). The date will be null for
current records. This date will match the Last_Extract_Date for the current
record that supersedes the history record. That is, a history record was
in effect for any data greater than or equal to its Last_Extract_Date and
less than its History_Status_Date. If you are investigating job and distribution
history, you must take the Job_Dept_Org and Job_Class into account, as well
as the History_Status_Date, the Last_Extract_Date, the Job_Sequence_Number
and the Distribution_Sequence_Number. The
|
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 to which the distribution
applies. 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 to which the distribution applies. 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 to which the distribution applies. 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_SCHOOL
Indexed - yes
Format - char (2)
May be null? yes
|
The 2-digit Payroll code used to identify the school or center
having primary responsibility for the job to which the distribution applies.
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 to which the distribution applies. 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
|
JOB_SEQUENCE_NUMBER
Indexed - yes
Format - char (1)
May be null? no
|
A 1-digit number that distinguishes the employee's appointment
record to which the distribution applies. An employee may have up to four
jobs at a time. (About 2% of employees hold more than one job with the same
Job_Class and the same Job_Dept_Org.) Additions and subtractions to the
list of an employee's appointments can change the order of jobs.
Values:
1
2
3
4
|
LAST_EXTRACT_DATE
Indexed - yes
Format - date (7)
May be null? no
|
The date the EMPLOYEE_DISTRIBUTION record was extracted from
the Payroll system and loaded into the Warehouse.
|
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 account
number in the financial system used before July 1, 1996. This is the number
corresponding to the salary position within a grade or range (salary amount
positioned in horizontal increments), or the code identifying the budgeted
position filled by the employee. 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.
|
RESPONSIBLE_ORG
Indexed - yes
Format - char (4)
May be null? yes
|
The 4-character code for the organization responsible for
managing the Distribution_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 Distribution_COA_Org in the Distribution_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.
|
