Penn Computing

University of Pennsylvania
Penn Computing << go backback
EMPLOYEE_DISTRIBUTION Table - Data Element Index   Tables and Data Elements   Salary Management Home   Data Warehouse Home

EMPLOYEE_DISTRIBUTION Table

Explanation
Payroll distribution accounts and amounts for each employee/job. The table includes multiple versions of distribution lines, organized by job sequence number and extract date. Because an employee may have up to four jobs, with up to nine distribution lines each, up to 36 distribution records may be in effect for an employee at the same time.

Common Uses
To determine the source from which an employee is to be paid, per the Payroll system.

Primary Key Indexed Data Elements Related Tables
DISTRIBUTION_SEQUENCE_NUMBER
HISTORY_STATUS
JOB_SEQUENCE_NUMBER
LAST_EXTRACT_DATE
SSN
DISTRIBUTION_ACCOUNT
DISTRIBUTION_COA_ACCOUNT
DISTRIBUTION_COA_CENTER_REF
DISTRIBUTION_COA_CNAC
DISTRIBUTION_COA_FUND
DISTRIBUTION_COA_OBJECT
DISTRIBUTION_COA_ORG
DISTRIBUTION_COA_PROGRAM
DISTRIBUTION_SEQUENCE_NUMBER
DISTRIBUTION_START_FISCAL_YEAR
DISTRIBUTION_START_YEAR
DISTRIBUTION_STOP_FISCAL_YEAR
DISTRIBUTION_STOP_YEAR
HISTORY_STATUS
HOME_DEPT
HOME_DEPT_ORG
HOME_SCHOOL
HOME_SCHOOL_CTR
JOB_CLASS
JOB_DEPT
JOB_DEPT_ORG
JOB_SCHOOL
JOB_SCHOOL_CTR
JOB_SEQUENCE_NUMBER
LAST_EXTRACT_DATE
PENN_ID
RESPONSIBLE_ORG
SSN
UNIV_POSITION_NUMBER
COMP_PERSON_DISTRIBUTION (DWCOMP)
COMP_PERSON (DWCOMP)
CENTER_REF_CODES (DWADMIN)
CNAC_CODES (DWADMIN)			
EMPLOYEE (DWADMIN)
EMPLOYEE_ADDRESS (DWADMIN)
EMPLOYEE_GENERAL (DWADMIN)
EMPLOYEE_JOB (DWADMIN)
EMPLOYEE_PAYMENT (DWADMIN)
EMPLOYEE_PAYMENT_SUMMARY (DWADMIN)
FUND_CODES (DWADMIN)
JOB_CLASS (DWADMIN)
JOB_CLASS_GENERAL (DWADMIN)
OBJECT_CODES (DWADMIN)
ORG_CODES (DWADMIN)
PAR_EARNINGS (DWPAY)
PAR_MAIN (DWPAY)
PAYROLL_EARNINGS (DWPAY)
PROGRAM_CODES (DWADMIN)

Cautions

  • The EMPLOYEE_DISTRIBUTION table contains distribution data for the employees paid via the Penn Payroll system. For individuals with Payroll records as of November 1, 2009, when PennWorks came into use, distribution information appears in both DWCOMP.COMP_DISTRIBUTION and DWADMIN.EMPLOYEE_DISTRIBUTION. (Distributions from PennWorks are mapped to a maximum of eight distributions for each of the up to four job records for the employee in the mainframe Payroll system, where they are used for Payroll processing. After that processing, the distribution data is fed from the Payroll system to the EMPLOYEE_DISTRIBUTION table.) Note: the EMPLOYEE_DISTRIBUTION table includes distribution history for employees who had Payroll records on or after July 1, 1996. For employees with Payroll records prior to October 2009, distribution data is available in the EMPLOYEE_DISTRIBUTION table only.
  • Rows with a History_Status of "C" reproduce the distributions as they currently exist in Payroll (or as they existed most recently before the employee was purged from Payroll). Rows with a History_Status of "H" represent earlier versions of the same data. Additions and subtractions to the list of an employee's appointments can change the order of jobs. 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, Last_Extract_Date, Job_Sequence_Number and Distribution_Sequence_Number. In most cases, you should look for rows with a History_Status of "C." Only when tracing distributions over time, even for defunct jobs, would you need to provide the "history" rows. "Current" does not mean "currently active." Querying by this status column will not retrieve an accurate list of currently active distributions. For most queries, limit rows by Distribution_Start_Date and Distribution_Stop_Date as well as History_Status. Historical snapshots of distribution information are available in the DWADMIN.EMPLOYEE_DIST_SNAP table.
  • You can see an EMPLOYEE_DISTRIBUTION row if you have security for the employee's home school or organization, the job organization, or the distribution account CNAC, ORG or Responsible_Org. Access to SSN is further restricted to specifically authorized individuals.
  • 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. Some classes were reused, however; only the attributes now used for those classes are available. Faculty classes were unaffected by this conversion.
  • Use of 9-digit Distribution_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, and Distribution_COA_Account should be used instead.
  • Use of 5-character Payroll department codes and 2-character Payroll school codes was phased out at the end of June 1998, when the Payroll system became fully compliant with the present General Ledger accounting structure. These columns should be null for all records from fiscal year 1999 onward; in these records, refer to Dept_Org and School_Ctr columns instead.
  • The EMPLOYEE_DISTRIBUTION table can be found in the DWADMIN schema, as DWADMIN.EMPLOYEE_DISTRIBUTION_V.
EMPLOYEE_DISTRIBUTION Table - Data Element Index   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