Tables and Data Elements   Salary Managment Home   Data Warehouse Home

Salary Management Collection Reference Tables

Explanation
Throughout the PennWorks system, reference tables are used to display "friendly names" for a variety of elements. These tables are joined to other tables using the common codes, as detailed below. The reference tables, whose names usually end with "lookup" or "list", generally have the following columns in common:

Individual reference tables:

COMP_ACTION_TYPE_LOOKUP

• Usage: Reference table for PennWorks Action codes, containing codes and their descriptions. Note: Values may be related to, but are distinct from, those stored in DWPAY.PAYROLL_ACTION_CODES, which holds reference information specific to the mainframe Payroll system.
• Unique Columns:
  • ACTION_TYPE_CODE: The unique code identifying an action type. Refer to the Comp_Action_Type_Code_Desc to find the description which applies to the action type.
  • ACTION_TYPE_CODE_DESC: The description corresponding to a action type.
  • COMP_ACTION_TYPE_LOOKUP_ID: Uniquely identifies a record in the COMP_ACTION_TYPE_LOOKUP table.
• Related Table/Element:
  • DWCOMP.COMP_CHANGE_LOG.ACTION_CODE

COMP_ACADEMIC_CATEGORY_LOOKUP

• Usage: Reference table for faculty Academic Category codes, containing codes and their descriptions. Academic Category is a higher-level grouping of faculty classes used for aggregating faculty into groups such as Standing Faculty, Standing Faculty C-E (Clinician-Educator), Associated Faculty, Academic Support Staff, and Emeritus.
• Unique Columns:
  • COMP_ACAD_CAT_CODE: The unique code identifying an academic category. Refer to the Comp_Acad_Cat_Code_Description to find the description which applies to the academic category.
  • COMP_ACAD_CAT_CODE_DESCRIPTION: The description corresponding to an academic category.
  • COMP_ACAD_CAT_LOOKUP_ID: Uniquely identifies a record in the COMP_ACADEMIC_CATEGORY_LOOKUP table.
• Related Table/Element:
  • DWCOMP.FACULTY_CLASS_LOOKUP.ACADEMIC_CATEGORY

COMP_AFFILIATION_LIST

• Usage: Reference table for affiliation codes. For employees of the School of Medicine, this is the 3-character code that identifies the primary work location of the employee. Affiliation_Code is invalid for other employees.
• Unique Columns:
  • COMP_AFFILIATION_CODE: The unique code identifying an affiliation. Refer to the Comp_Affiliation_Desc to find the description which applies to the affiliation.
  • COMP_AFFILIATION_DESC: The description corresponding to an affiliation.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.AFFILIATON_CODE
  • DWCOMP.COMP_PERSON.AFFILIATON_CODE

COMP_CATEGORY_LOOKUP

• Usage: Reference table for compensation categories maintained in PennWorks (e.g., Academic Base Salary, Base Salary Supplement, Faculty Salary, Staff Salary, etc).
• Unique Columns:
  • COMP_CATEGORY_CODE: The unique code identifying a compensation category. Refer to the Comp_Category_Code_Desc to find the description which applies to the category.
  • COMP_CATEGORY_CODE_DESC: The description corresponding to a compensation category.
  • COMP_CATEGORY_LOOKUP_ID: Uniquely identifies a record in the COMP_CATEGORY_LOOKUP table.
• Related Table/Element:
  • DWCOMP.COMP_PERSON_SALARY.SALARY_TYPE

COMP_COUNTRY_LIST

• Usage: Reference table for country codes used in citizenship and foreign address fields. Note: This table is also the source for records in the DWADMIN.COUNTRY_CODES table.
• Unique Columns:
  • COMP_COUNTRY_CODE: The unique code identifying a country. Refer to the Comp_Country_Code_Desc to find the description which applies to the country.
  • COMP_COUNTRY_CODE_DESC: The description corresponding to a country code.
  • COMP_COUNTRY_LIST_ID: Uniquely identifies a record in the COMP_COUNTRY_LIST table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.COUNTRY_OF_CITIZENSHIP
  • DWCOMP.EMPLOYEE_ADDRESS.COUNTRY
  • DWCOMP.COMP_PERSON.COUNTRY_OF_CITIZENSHIP
  • DWCOMP.COMP_PERSON_ADDRESS.COUNTRY

COMP_CITIZENSHIP_LOOKUP

• Usage: Reference table for codes indicating whether an employee is a citizen of the United States.
• Unique Columns:
  • COMP_CITIZENSHIP_CODE: The unique code identifying whether an employee is a citizen of the United States. Refer to the Comp_Citizenship_Code_Desc to find the description which applies to the code.
  • COMP_CITIZENSHIP_CODE_DESC: The description corresponding to a citizenship code.
  • COMP_CITIZENSHIP_LOOKUP_ID: Uniquely identifies a record in the COMP_CITIZENSHIP_LOOKUP table.
• Related Table/Element:
  • DWCOMP.COMP_PERSON_ADDRESS.CITIZENSHIP

COMP_EMPLOYEE_GROUP_LIST

• Usage: Reference table for employee group codes.This code can be used to group employee job classes into broader categories, such as exempt salaried staff, faculty, non-exempt hourly staff, etc.
• Unique Columns:
  • COMP_EMPLOYEE_GROUP_CODE: The unique code identifying an employee type. Refer to the Comp_Employee_Group_Desc to find the description which applies to the employee group.
  • COMP_EMPLOYEE_GROUP_DESC: The description corresponding to an employee group.
  • COMP_EMPLOYEE_GROUP_LOOKUP_ID: Uniquely identifies a record in the COMP_EMPLOYEE_GROUP_LIST table.
• Related Table/Element:
  • DWCOMP.COMP_INCREASE_ROLE_TEMPLATE.EMPLOYEE_GROUP_ID

COMP_EMPLOYEE_TYPE_LIST

• Usage: Reference table for employee type codes.This code can be used to distinguish whether the related role for a monthly, weekly, full-time, paid professional, support staff or student position.
• Unique Columns:
  • COMP_EMPLOYEE_TYPE_CODE: The unique code identifying an employee type. Refer to the Comp_Employee_Type_Code_Desc to find the description which applies to the employee type.
  • COMP_EMPLOYEE_TYPE_DESC: The description corresponding to an employee type.
  • COMP_EMPLOYEE_TYPE_LIST_ID: Uniquely identifies a record in the COMP_EMPLOYEE_TYPE_LIST table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE_JOB.APPT_EMPLOYEE_TYPE
  • DWADMIN.EMPLOYEE_PAYMENT.APPT_EMPLOYEE_TYPE
  • DWADMIN.JOB_CLASS.JOB_CLASS_EMPLOYEE_TYPE
  • DWADMIN.PAYROLL_CHANGE_LOG.EMPLOYEE_TYPE
  • DWCOMP.COMP_PERSON_ROLE.APPT_EMPLOYEE_TYPE
  • DWPAY.PAR_MAIN.EMPLOYEE_TYPE

COMP_EXT_DIST_SOURCE_LIST

• Usage: Reference table for sources of external distributions.
• Unique Columns:
  • COMP_EXT_DIST_SOURCE_CODE: The unique code identifying an external funding source. Refer to the Comp_Ext_Dist_Source_Desc to find the description which applies to the source code.
  • COMP_EXT_DIST_SOURCE_DESC: The description corresponding to an external funding source.
  • COMP_EXT_DIST_SOURCE_LOOKUP_ID: Uniquely identifies a record in the COMP_EXT_DIST_SOURCE_LIST table.
• Related Table/Element:
  • DWCOMP.COMP_DISTRIBUTION_EXT.EXT_DISTRIBUTION_SOURCE

COMP_FACULTY_CLASS_LOOKUP

• Usage: Reference table for faculty class codes.
• Unique Columns:
  • COMP_FACULTY_CLASS_CODE: The unique code identifying a faculty class. Refer to the Comp_Faculty_Class_Code_Desc to find the description which applies to the faculty class code.
  • COMP_FACULTY_CLASS_DESC: The description corresponding to a faculty class code.
  • COMP_FACULTY_CLASS_LOOKUP_ID: Uniquely identifies a record in the COMP_FACULTY_CLASS_LOOKUP table.
• Related Table/Element:
  • DWADMIN.JOB_CLASS.FACULTY_CLASS
  • DWADMIN.JOB_CLASS_GENERAL_V.FACULTY_CLASS

COMP_GENDER_LIST

• Usage: Reference table for gender codes.
• Unique Columns:
  • COMP_GENDER_CODE: The unique code identifying a gender. Refer to the Comp_Gender_Code_Desc to find the description which applies to the gender code.
  • COMP_GENDER_DESC: The description corresponding to a gender code.
  • COMP_GENDER_LIST_ID: Uniquely identifies a record in the COMP_GENDER_LIST table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.SEX
  • DWCOMP.COMP_PERSON.SEX

COMP_INCREASE_BATCH_BARG_UNIT

• Usage: Reference table associating union bargaining units with Salary Increase Program batches.
• Unique Columns:
  • BARGAINING_UNIT: The unique code identifying a union bargaining unit associated with a Salary Increase Program batch.
  • INCREASE_BATCH_ID: Uniquely identifies a Salary Increase Program batch in the COMP_INCREASE_BATCH table.
  • INCREASE_BATCH_BARG_UNIT_ID: Uniquely identifies a record in the COMP_INCREASE_BATCH_BARG_UNIT table.
• Related Table/Element:
  • DWADMIN.JOB_CLASS.BARGAINING_UNIT
  • DWCOMP.COMP_INCREASE_BATCH.INCREASE_BATCH_ID

COMP_INCREASE_BATCH_TYPE_LIST

• Usage: Reference table for Salary Increase Program processing batches.
• Unique Columns:
  • INCREASE_BATCH_TYPE_CODE: The unique code identifying a salary increase batch. Refer to the Increase_Batch_Type_Desc to find the text description which applies to the increase processing batch.
  • INCREASE_BATCH_TYPE_DESC: The description corresponding to a salary increase processing batch.
  • INCREASE_BATCH_TYPE_LOOKUP_ID: Uniquely identifies a record in the COMP_INCREASE_BATCH_TYPE_LIST table.
• Related Table/Element:
  • DWCOMP.COMP_INCREASE_BATCH.INCREASE_BATCH_ID
  • DWCOMP.COMP_INCREASE_BATCH_BARG_UNIT.INCREASE_BATCH_ID
  • DWCOMP.COMP_INCREASE_ROLE.INCREASE_BATCH_ID

COMP_JOB_FAMILY_LIST

• Usage: Reference table for job family codes. The job family is a 2-character code that indicates the field of knowledge for the Job_Class. Job families are designed to group jobs of the same nature which require different skill and responsibility levels.
• Unique Columns:
  • COMP_JOB_FAMILY_CODE: The unique code identifying a job family. Refer to the Comp_Job_Family_Desc to find the description which applies to the family code.
  • COMP_JOB_FAMILY_DESC: The description corresponding to a job family code.
• Related Table/Element:
  • DWADMIN.JOB_CLASS.FAMILY

COMP_JOB_GRADE_LIST

• Usage: Reference table for job grade codes. The job grade is a 3-character code indicating the pay grade for the Job_Class. Each Job_Grade has a specific salary range assigned to it. University exempt (monthly-paid; not eligible for overtime time) and non-exempt (weekly-paid; eligible for overtime pay) staff positions are classified within a 12-grade structure, numbered from 21 to 32. A separate Information Technology (IT) salary broadband structure, represented by the letters A - H, applies to IT positions at the University.
• Unique Columns:
  • COMP_JOB_GRADE_CODE: The unique code identifying a job grade. Refer to the Comp_Job_Grade_Desc to find the description which applies to the grade.
  • COMP_JOB_GRADE_DESC: The description corresponding to a job grade code.
• Related Table/Element:
  • DWADMIN.JOB_CLASS.JOB_GRADE

COMP_JUSTIFICATION_LIST

• Usage: Reference table for salary increase justification codes.
• Unique Columns:
  • COMP_JUSTIFICATION_ID: The unique code identifying a salary increase justification. Refer to the Comp_Justification_Desc to find the text description which applies to the justification.
  • COMP_JUSTIFICATION_DESC: The description corresponding to a salary increase justification code.
• Related Table/Element:
  • DWCOMP.COMP_INCREASE_JUSTIFICATION.JUSTIFICATION_ID
  • DWCOMP.COMP_INCREASE_PAY_ADJUSTMENTS.JUSTIFICATION_ID

COMP_LEAVE_REASON_LIST

• Usage: Reference table for codes representing leave of absence reasons.
• Unique Columns:
  • COMP_LEAVE_REASON_CODE: The unique code identifying a leave of absence reason. Refer to the Comp_Leave_Reason_Code_Desc to find the description which applies to the reason code.
  • COMP_LEAVE_REASON_DESC: The description corresponding to a leave reason code.
  • COMP_LEAVE_REASON_LIST_ID: Uniquely identifies a record in the COMP_LEAVE_REASON_LIST table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.LEAVE_REASON
  • DWCOMP.COMP_PERSON.LEAVE_REASON

COMP_MARITAL_STATUS_LIST

• Usage: Reference table for codes representing an employee's marital status used to determine benefits enrollment.
• Unique Columns:
  • COMP_MARITAL_STATUS_CODE: The unique code identifying a martial status. Refer to the Comp_Marital_Status_Code_Desc to find the description which applies to the martial status code.
  • COMP_MARITAL_STATUS_DESC: The description corresponding to a marital status.
  • COMP_MARITAL_STATUS_LIST_ID: Uniquely identifies a record in the COMP_MARITAL_STATUS_LIST table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.MARITAL_STATUS
  • DWCOMP.COMP_PERSON.MARITAL_STATUS

COMP_MILITARY_LIST

• Usage: Reference table for codes representing an employee's military (veteran) status.
• Unique Columns:
  • COMP_MILITARY_CODE: The unique code identifying a military status. Refer to the Comp_Military_Status_Code_Desc to find the description which applies to the military status code.
  • COMP_MILITARY_DESC: The description corresponding to a military status.
  • COMP_MILITARY_LIST_ID: Uniquely identifies a record in the COMP_MILITARY_STATUS_LOOKUP table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.MILITARY_STATUS
  • DWCOMP.COMP_PERSON.MILITARY_STATUS

COMP_PAY_ALLOWANCE_TYPE_LIST

• Usage: Reference table for Additional Pay allowance type codes.
• Unique Columns:
  • PAY_ALLOWANCE_TYPE_CODE: The unique code identifying a type of Additional Pay allowance. Refer to the Pay_Allowance_Type_Desc to find the description which applies to the allowance type.
  • PAY_ALLOWANCE_TYPE_DESC: The description corresponding to a type of Additional Pay allowance.
  • COMP_ALLOWANCE_TYPE_LOOKUP_ID: Uniquely identifies a record in the COMP_PAY_ALLOWANCE_TYPE_LIST table
• Related Table/Element:
  • DWCOMP.COMP_COMP_PAY_REQUEST.ALLOWANCE_TYPE_ID

COMP_PAY_AWARD_TYPE_LIST

• Usage: Reference table for Additional Pay award type codes.
• Unique Columns:
  • PAY_AWARD_TYPE_CODE: The unique code identifying a type of Additional Pay award. Refer to the Pay_Award_Type_Desc to find the description which applies to the award type.
  • PAY_AWARD_TYPE_DESC: The description corresponding to a type of Additional Pay award.
  • COMP_AWARD_TYPE_LOOKUP_ID: Uniquely identifies a record in the COMP_PAY_AWARD_TYPE_LIST table
• Related Table/Element:
  • DWCOMP.COMP_COMP_PAY_REQUEST.AWARD_TYPE_ID

COMP_PAY_EXTRA_SERVICES_LIST

• Usage: Reference table for Additional Pay extra services codes.
• Unique Columns:
  • PAY_EXTRA_SERVICES_CODE: The unique code identifying a type of Additional Pay extra service. Refer to the Pay_Extra_Services_Desc to find the description which applies to the type of extra service.
  • PAY_EXTRA_SERVICES_DESC: The description corresponding to a type of Additional Pay extra service.
  • PAY_EXTRA_SERVICES_LOOKUP_ID: Uniquely identifies a record in the COMP_PAY_EXTRA_SERVICES_LIST table
• Related Table/Element:
  • DWCOMP.COMP_COMP_PAY_REQUEST.EXTRA_SERVICES_CODE_ID

COMP_PAY_STATUS_LOOKUP

• Usage: Reference table for Additional Pay request status codes.
• Unique Columns:
  • PAY_STATUS_CODE: The unique code identifying the status of an Additional Pay request. Refer to the Pay_Status_Desc to find the description which applies to the request status.
  • PAY_STATUS_DESC: The description corresponding the status of an Additional Pay request.
  • PAY_STATUS_LOOKUP_ID: Uniquely identifies a record in the COMP_PAY_STATUS_LOOKUP table
• Related Table/Element:
  • DWCOMP.COMP_COMP_PAY_REQUEST.REQUEST_STATUS

COMP_PAY_TYPE_LOOKUP

• Usage: Reference table for Additional Pay request type codes.
• Unique Columns:
  • PAY_TYPE_CATEGORY: Indicates whether the pay type is for an Individual (I) or Group (G) request.
  • PAY_TYPE_CODE: The unique code identifying the type of an Additional Pay request. Refer to the Pay_Type_Desc to find the description which applies to the request type.
  • PAY_TYPE_DESC: The description corresponding the type of an Additional Pay request.
  • PAY_TYPE_LONG_DESC: Instructional text describing the type of an Additional Pay request.
  • PAY_TYPE_LOOKUP_ID: Uniquely identifies a record in the COMP_PAY_TYPE_LOOKUP table
• Related Table/Element:
  • DWCOMP.COMP_COMP_PAY_REQUEST.REQUEST_TYPE_ID

COMP_PUB_MAIL_CODE_LIST

• Usage: Reference table for publications mail codes. For faculty of the School of Medicine, this is the code indicating the location where internal mail for the employee should be delivered. Comp_Pub_Mail_Code is, in practice, used only by the School of Medicine.
• Unique Columns:
  • COMP_PUB_MAIL_CODE: The unique code identifying a publications mail code. Refer to the Comp_Pub_Mail_Code_Desc to find the description which applies to the publications mail code.
  • COMP_PUB_MAIL_CODE_DESC: The description corresponding to a publications mail code.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.PUBLICATIONS_MAIL_CODE
  • DWCOMP.COMP_PERSON.PUBLICATIONS_MAIL_CODE

COMP_ROLE_DIST_STATE_LOOKUP

• Usage: Reference table for codes representing a the state of person, role or distribution record.
• Unique Columns:
  • COMP_ROLE_DIST_STATE_CODE: The unique code identifying a person, role or distribution record status. Refer to the Comp_Role_Dist_State_Code_Desc to find the description which applies to the record state.
  • COMP_ROLE_DIST_STATE_DESC: The description corresponding to a record state.
  • COMP_ROLE_DIST_STATE_LOOKUP_ID: Uniquely identifies a record in the COMP_ROLE_DIST_STATE_LOOKUP table.
• Related Table/Element:
  • DWCOMP.COMP_PERSON.PERSON_STATE
  • DWCOMP.COMP_PERSON_DISTRIBUTION.DISTRIBUTION_STATE
  • DWCOMP.COMP_PERSON_ROLE.ROLE_STATE
  • DWCOMP.COMP_PERSON_SALARY.SALARY_STATE

COMP_ROLE_LOOKUP

• Usage: Reference table for codes representing an employee role at the University.
• Unique Columns:
  • COMP_ROLE_CODE: The unique code identifying a type of employee role. Refer to the Comp_Role_Code_Desc to find the description which applies to the role.
  • COMP_ROLE_CODE_DESC: The description corresponding to a role.
  • COMP_ROLE_LOOKUP_ID: Uniquely identifies a record in the COMP_ROLE_LOOKUP table.
• Related Table/Element:
  • DWCOMP.COMP_PERSON_ROLE.ROLE_TYPE
  • DWCOMP.COMP_ROLE_JOB_CLASS_XREF.ROLE_ID

COMP_SALARY_TYPE_LOOKUP

• Usage: Reference table for codes representing the salary status of an employee.Note: This code set applies to Full_Part_Time indicator at the employee level, and is not related to the Appt Employee Type at the role level.
• Unique Columns:
  • COMP_SALARY_TYPE_CODE: The unique code identifying the salary status of an employee. Refer to the Comp_Salary_Type_Desc to find the description which applies to the salary status.
  • COMP_SALARY_TYPE_DESC: The description corresponding to a salary status.
  • COMP_SALARY_TYPE_LOOKUP_ID: Uniquely identifies a record in the COMP_SALARY_TYPE_LOOKUP table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.FULL_PART_TIME
  • DWCOMP.COMP_PERSON.FULL_PART_TIME

COMP_SEPARATION_REASON_LIST

• Usage: Reference table for codes representing reasons for an employee's termination of employment at Penn.
• Unique Columns:
  • COMP_SEPARATION_REASON_CODE: The unique code identifying the separation reason. Refer to the Comp_Separation_Reason_Desc to find the description which applies to the reason code.
  • COMP_SEPARATION_REASON_DESC: The description corresponding to a separation reason.
  • COMP_SEPARATION_REASON_ID: Uniquely identifies a record in the COMP_SEPARATION_REASON_LIST table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.SEPARATION_REASON
  • DWCOMP.COMP_PERSON.SEPARATION_REASON

COMP_STATE_LIST

• Usage: Reference table for United States state codes used in addresses.
• Unique Columns:
  • COMP_STATE_CODE: U.S. Postal Service code representing a state. Refer to the Comp_State_Code_Desc to find the name of the state.
  • COMP_STATE_CODE_DESC: The description corresponding to a state code.
  • COMP_STATE_LIST_ID: Uniquely identifies a record in the COMP_STATE_LIST table.
• Related Table/Element:
  • DWCOMP.EMPLOYEE_ADDRESS.STATE
  • DWCOMP.COMP_PERSON_ADDRESS.STATE

COMP_TERM_LOOKUP

• Usage: Reference table for pay term codes, used to indicate how an employee's work schedule for the job is related to the pay schedule.
• Unique Columns:
  • COMP_TERM_CODE: The unique code identifying a pay term. Refer to the Comp_Pay_Term_Code_Desc to find the description which applies to the pay term.
  • COMP_PAY_TERM_CODE_DESC: The description corresponding to a pay term code.
  • COMP_PAY_TERM_LOOKUP_ID: Uniquely identifies a record in the COMP_PAY_TERM_LOOKUP table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE_JOB.PAY_TERM
  • DWCOMP.COMP_PERSON_ROLE.PAY_TERM

COMP_UNION_LIST

• Usage: Reference table for union bargaining unit codes.
• Unique Columns:
  • COMP_UNION_ID: The unique code identifying a bargaining unit. Refer to the Comp_Union_Name column to find the corresponding name of the bargaining unit.
  • COMP_UNION_NAME: The description corresponding to a bargaining unit.
  • BARGAINING_UNIT: The bargaining unit number, corresponding to specific job classes.
  • UNION_REFERENCE: The name of the union associated with the bargaining unit.
• Related Table/Element:
  • DWADMIN.JOB_CLASS.BARGAINING_UNIT

COMP_VISA_LIST

• Usage: Reference table for codes types of visas held by employees who are not U.S. citizens.
• Unique Columns:
  • COMP_VISA_CODE: The unique code identifying the type of visa. Refer to the Comp_Visa_Code_Desc to find the description which applies to the visa type.
  • COMP_VISA_CODE_DESC: The description corresponding to a visa type.
  • COMP_VISA_LIST_ID: Uniquely identifies a record in the COMP_VISA_LIST table.
• Related Table/Element:
  • DWADMIN.EMPLOYEE.VISA
  • DWCOMP.COMP_PERSON.VISA

Tables and Data Elements   Salary Management Home   Data Warehouse Home