| Data element |
Definition |
ACCOUNTING_PERIOD
Indexed - yes
Format - char (6)
May be null? yes |
The Accounting Period, based on the Encumbrance_Run_Date in
Salary Management, which is translated in the Data Warehouse to MON-YY format.
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 encumbrance was posted.
Example: PRLENC 001 19960730
|
ENCUMBRANCE_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 account from which the employee's
salary is encumbered from the Encumbrance_Start_Date through the Encumbrance_End_Date.
The first digit of the Encumbrance_Account 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 account from which the employee's
salary is encumbered.
|
|
ENCUMBRANCE_AMOUNT
Indexed - no
Format - number (9,2)
May be null? yes
|
The amount of the employee's salary encumbered for the Posted_COA_Account
(or, if prior to fiscal year 1999, the Encumbrance_Account) from the Encumbrance_Start_Date
through the Encumbrance_End_Date inclusive. The Encumbrance_Amount represents
funds that have been encumbered but have not yet been spent for a specific
approved planned expenditure (in this case, salaries). These funds cannot
be used for any other purpose, unless the encumbrance is removed. Only the
latest encumbrance is stored for a given Accounting_Period, so, for weekly
employees, the amount will likely appear to decrease through the period
as the encumbrance is relieved. Encumbrance amounts are calculated by Salary
Management based on the account distribution(s) on active Payroll Job records.
For fiscal-based accounts, encumbrances are calculated to the end of the
fiscal year or to the Distribution End Date, whichever comes first. For
project-based accounts, encumbrances are calculated to the BEN Financials
Contract End Date or to the Distribution End Date, whichever comes first.
There is no calculation if these dates are not valid.
Values:
0-9,999,999.99
|
ENCUMBRANCE_END_DATE
Indexed - no
Format - date (7)
May be null? yes |
The date on which the salary encumbrance ends for this employee
for this Posted_COA_Account for this Accounting_Period.
|
ENCUMBRANCE_END_FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null? yes |
The financial year within which the Encumbrance_End_Date falls.
Penn's fiscal year begins July 1 of one calendar year and ends June 30 of
the next calendar year.
Example: For Encumbrance_End_Date 8/31/99 the Encumbrance_End_Fiscal_Year
would be 2000 (fiscal year that began July 1, 1999, and ended June 30,
2000)
|
ENCUMBRANCE_END_YEAR
Indexed - yes
Format - char (4)
May be null? yes |
The calendar year within which the Encumbrance_End_Date falls.
Example: For Encumbrance_End_Date 8/31/99, the Encumbrance_End_Year would
be 1999
|
ENCUMBRANCE_RUN_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date the batch job was run and entered the salary encumbrance
data. (See Batch_ID for the batch job's identification.)
|
ENCUMBRANCE_SEQUENCE_NUMBER
Indexed - yes
Format - char (2)
May be null? no
|
A 2-digit number used to uniquely identify each record in
the EMPLOYEE_ENCUMBRANCE table.
|
ENCUMBRANCE_START_DATE
Indexed - no
Format - date (7)
May be null? yes
|
The date on which the salary encumbrance begins for this employee
for this Posted_COA_Account for this Accounting_Period.
|
ENCUMBRANCE_START_FISCAL_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The financial year within which the Encumbrance_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 Encumbrance_Start_Date 8/1/99 the Encumbrance_Start_Fiscal_Year
would be 2000 (fiscal year that began July 1, 1999, and ended June 30,
2000)
|
ENCUMBRANCE_START_YEAR
Indexed - yes
Format - char (4)
May be null? yes
|
The calendar year within which the Encumbrance_Start_Date
falls.
Example: For Encumbrance_Start_Date 8/1/99 the Encumbrance_Start_Year
would be 1999
|
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: 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 to which the encumbrance
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 encumbrance 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 encumbrance 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_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 encumbrance was made. The
proper job is determined as follows:
- If there is only one job, that job's Job Link ID appears on the encumbrance
row.
- If there's only one job with matching job class and appointment begin
/ end dates which fit the end date of the encumbrance accounting period,
that job's Job Link ID appears on the encumbrance 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 end date of the
encumbrance accounting period.
- If there are no jobs which fit the payment using the above three criteria,
the encumbrance 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
having primary responsibility for the job to which the encumbrance 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 encumbrance 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.
|
LAST_EXTRACT_DATE
Indexed - yes
Format - date (7)
May be null? no
|
The date the EMPLOYEE_ENCUMBRANCE record was extracted from
the Salary Management system and loaded into the Warehouse.
|
PAY_CYCLE
Indexed - no
Format - char (2)
May be null? yes
|
A 2-character code indicating the payment schedule for the
job to which the encumbrance applies. The University of Pennsylvania has
two main payroll cycles: M1 (monthly) and W1 (weekly)
Values:
M1 Monthly Paid (EMPLOYEE_JOB.Appt_Employee_Type
1, 4, 8, or 9)
M2 Monthly Paid (EMPLOYEE_JOB.Appt_Employee_Type 6)
- Note: records for this employee type are not
retained in the Data Warehouse.
W1 Weekly Paid (EMPLOYEE_JOB.Appt_Employee_Type 2,
3, or 5)
|
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.
|
PERIOD_ENCUMBRANCE_AMOUNT
Indexed - no
Format - number (9,2)
May be null? yes
|
The portion of the Encumbrance_Amount that applies to the
Accounting_Period. The Encumbrance_Amount represents funds that have been
encumbered but have not yet been spent for a specific approved planned expenditure
(in this case, salaries). These funds cannot be used for any other purpose,
unless the encumbrance is removed. Only the latest encumbrance is stored
for a given period, so, for weekly employees, the amount will likely appear
to decrease through the period as the encumbrance is relieved. Encumbrance
amounts are calculated by Salary Management based on the account distribution(s)
on active Payroll Job records. For fiscal-based accounts, encumbrances are
calculated to the end of the fiscal year or to the Encumbrance_End_Date,
whichever comes first. For project-based accounts, encumbrances are calculated
to the BEN Financials Contract End Date or to the Encumbrance_End_Date,
whichever comes first. There is no calculation if these dates are not valid.
As of this writing (August, 1999), Period_Encumbrance_Amount is not populated
in the Data Warehouse.
Values:
0-9,999,999.99
|
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
account for which the employee's salary is encumbered from the Encumbrance_Start_Date
through the Encumbrance_End_Date. 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, the Posted_COA_Account contains the
account number that was returned by the GL mapper utility, given the Encumbrance_Account.
In cases where the Encumbrance_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 Encumbrance_Account cannot be converted 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 9-digit account number.
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 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. 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 Encumbrance_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 Encumbrance_Account
could not be converted 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 indicating 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 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.
|
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 the position at the University
of Pennsylvania to which the encumbrance applies. 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.
|
