Washington State Department of Ecology : Information Management
Washington State Department of Ecology : Information Management
Completed March 1997
Identify the high-level but comprehensive business requirements for employee data. Determine which data are core for the Facility/Site Identification System and other Information Integration Project systems. Evaluate the alternatives to implement an employee database to serve as the foundation for the Information Integration Project and other priority business requirements. Clarify the demand for "non-core" employee data needs; communicate the conclusions reached so that diverse internal interests can make decisions about whether or how to meet their informational needs now and in the future.
Following are selections from the Requirements Study. The full document in Word 6.0 can be downloaded in two files - Requirements Study (1 MB) and Appendices (204 KB).
Statement of the Problem
Background
Conceptual Employee Data Integration Opportunity (graphic)
EPIC 'Core' Data Project Vision (graphic)
Process
Summary of Technical Requirements for EPIC (updated 3/98)
EPIC Design
Logical Data Model (graphic)
EPIC Tables
EPIC Table Descriptions and Data Elements
EPIC Core Data Element Definitions
Summary of Recommendations
Return to Table of Contents
Statement of the Problem
Ecology currently has no integrated employee database available for agency-wide or program-specific systems. This has resulted in the same information being tracked in many different places, problems with data integrity and management reporting, and overall lack of efficiency in processing employee information across the agency.
Background
In the winter of 1995, an agency task team began to identify business needs for employee data. This effort was initiated for the following reasons: to serve administrative needs--such as notifying Facilities and Information Services Section staff of physical and technical service needs of employees; to create a central employee database to be accessed by internal Ecology information systems; and to serve as the foundation for a future comprehensive human resource management system, and to address the need for an accurate up-to-date phone directory for employees to access. The task team’s work was guided by an executive steering committee. From the beginning, the project was known as "EPIC"--Employee Plus Information Computer System. Conflicting expectations led the steering committee to suspend the project for re-evaluation in early 1996.
In the spring of 1996, the EPIC Steering Committee refocussed the project to address needs of the Information Integration Project (IIP). By then, there had been a general realization that the IIP would require at least some employee data, and that EPIC had the potential to serve it. In short order a project manager was chosen, the project statement was revised, and the EPIC Business Team was expanded. Roles and responsibilities of all parties were defined. (See Appendix A)
The revised project was to identify the high-level, comprehensive business requirements for employee data (work that had already been started and was mostly complete) and then concentrate on "core" employee data: common employee data currently required by multiple applications across the agency. Included in the project statement was the objective of evaluating opportunities to share data with the Department of Personnel (DOP) Data Warehouse Project and Ecology’s IIP.
The following graphics depict conceptually the opportunity for integrating
employee data and the EPIC Vision.
Conceptual Employee Data Integration Opportunity (graphic)
EPIC 'Core' Data Project Vision (graphic)
Return to Table of Contents
Process
From November 1995 to February 1996, the business team met four times and identified Human Resource Management Functions across the agency and other business areas that use employee data. They also reviewed and approved a process model developed from the functions identified; and began identifying critical employee data elements (later known as "core"). The technical team conducted a survey of agency computer information consultants to determine which automated applications use employee data.
From August 1996 to December 1996, the EPIC Business Team met five times as a team and separately in small task-specific groups. They refined the core employee data items; decided on the requirements for history, timeliness, and security of each data item; discussed access to the system; and debated responsibility for system maintenance. They also tested the EPIC prototype for look and feel in terms of their own job functions.
The EPIC Project Team (comprised of the project manager, business lead and technical lead) prepared extensively for each meeting of the business team in order to maximize the efforts of that group. At the same time, the project team carried out a number of concurrent tasks. These included conducting internal interviews with job specialties not represented on the business team and meeting with selected state agencies to review their systems for employee data. The project team also analyzed technical issues involved in linking with Ecology’s "Conveyant" (telephone routing) database system and Facility/Site Identification System as well as the State’s Time Management System and DOP’s Data Warehouse. Periodically, the project team sought the advice and guidance of the EPIC Steering Committee.
Outcome: This document provides the blueprint for an integrated database of "core" employee data. This database will provide accurate, consistent and timely information for a number of agency applications. It will go a long way toward eliminating many stand-alone, duplicative and inconsistent systems. And it will provide data for key fields in many other stand-alone systems.
EPIC will contain basic information on employees of organizations (such as the Environmental Protection Agency) that share our facilities, as well as other "persons of interest" needed primarily for Reception staff use. This data accounts for the "Plus" in EPIC.
As designed, the system is sensitive to employee safety concerns; under specified circumstances, information on employees’ names, building locations and telephone numbers will not be available to the public.
Confidential information, such as Social Security Number, will be limited to authorized persons only.
The bulk of EPIC data will be broadly accessible throughout Ecology. However, access to create, update, or delete data will be strictly limited in order to maintain data quality.
Finally, EPIC will be the vehicle for information on employee "expertise," a feature highly desired by assistant directors, division planners, and reception staff.
Looking into the Future: Once EPIC is implemented, incremental adjustments will be possible.
We recommend that you give high priority to incorporating the notification system for service requests related to employee movement. These requests relate to telephones, cubicles, telecommunications lines, network addresses, and other services essential for employee productivity. The current paper system is resource-intensive and slow.
Ecology needs organization charts, for both internal and external purposes. EPIC as designed will provide lists of people by organizational unit, but not in the familiar chart format. We recommend that a future enhancement look at enabling EPIC to create organization charts. There are numerous products on the market, such as Microsoft’s Organization Chart 2.0, that produce organization charts, but it is unknown at this time if EPIC data could be imported into any of them. It cannot be imported into Microsoft’s Organization Chart 2.0. The limited research that has been done seems to indicate that the ability to import data is generally restricted to prior versions of their own software.
EPIC provides amply for information related to persons, but not for information that is not tied to persons. An example is 800 lines operated by Ecology and other entities that the receptionists may need for referrals. We can accommodate the main receptionists’ needs, but unfortunately there may not be an elegant way to provide the same information to individual program receptionists. It may be possible to allow the individual programs’ reception staff the ability to read the "Conveyant" database to obtain this information. Additional research in this area is required to determine the potential impact on response times and the LAN/WAN traffic. In the future, it would be desirable to expand the Agency’s database to include information pertaining to phones that are not tied to persons.
Not to be forgotten in future enhancements is the need for a comprehensive human resource information system. That need was beyond the scope of this project, but EPIC can be its foundation. If the Labor and Industries system is revised to meet Ecology’s needs, it has the potential to fulfill that need since L&I will be migrating their entire Human Resource Information System to a client server environment.
Return to Table of Contents
Summary of Technical Requirements for EPIC (updated 3/98)
The assumption of the EPIC Project Team is that the EPIC "core" project must be an enterprise-wide application conforming to requirements similar to those for the Information Integration Project (IIP) and the agency Cashiering System.
Name |
Code |
Number |
| DOP_Appointment | DOP_APPOINTMNENT | 10000 |
| DOP_Job_Class | DOP_JOB_CLASS | 1500 |
| DOP_Person | DOP_PERSON | 2000 |
| DOP_Position | DOP_POSITION | 2000 |
| Ecology_Appointment | ECOLOGY_APPOINTMENT | 500 |
| Expertise | EXPERTISE | 250 |
| Expertise_Set | EXPERTISE_SET | 500 |
| Geographic_Loc | GEOGRAPHIC_LOC | 50 |
| Org_RelationShip | ORG_RELATIONSHIP | 200 |
| Organization | ORGANIZATION | 100 |
| Party | PARTY | 2000 |
| Per_WorkStation | PER_WORKSTATION | 1500 |
| Person_Contact | PERSON_CONTACT | 3000 |
| Person_Expertise | PERSON_EXPERTISE | 500 |
| Person_Name | PERSON_NAME | 2500 |
| Person_Org | PERSON_ORG | 3500 |
| Person_Phone | PERSON_PHONE | 2500 |
| Person_Type | PERSON_TYPE | 2500 |
| Person_Work_Location | PERSON_WORK_LOCATION | 2000 |
| Work_Location | WORK_LOCATION | 50 |
Return to Table of Contents
EPIC Table Descriptions and Data Elements
P = Primary Key
M = Mandatory Field
Description: DOP Appointment data for Ecology employees.
Column List
Name |
Type |
P |
M |
| DOP_Agency_NR | char(3) | No | No |
| DOP_Appoint_End_DT | datetime | No | No |
| DOP_Appoint_Start_DT | datetime | No | No |
| DOP_Appoint_Type_CD | char(10) | No | No |
| DOP_Job_Class_CD | char(5) | No | No |
| DOP_Merit_System_CD | char(1) | No | No |
| DOP_Position_CD | char(5) | No | No |
| DOP_Sub_Agency_NR | char(2) | No | No |
| Per_SSN_NR | numeric(9) | Yes | Yes |
Description: DOP Job Class Information.
Column List
Name |
Type |
P |
M |
| DOP_Job_Class_CD | char(5) | Yes | Yes |
| DOP_Job_Class_Long_DS | char(50) | No | No |
| DOP_Job_Class_Short_DS | char(16) | No | Yes |
| DOP_Merit_System_CD | char(1) | Yes | Yes |
Description: This file contains records to link Ecology employee's to additional information contained in the State's personnel payroll system. Records may exist on this table but not have matching records on the Person_Type table. Only Ecology employees will have DOP_Person records.
Column List
Name |
Type |
P |
M |
| DOP_Per_SSN_NR | numeric(9) | Yes | Yes |
| DOP_Perm_ Status CD | char(2) | No | Yes |
| DOP_Person_NM | char(20) | No | Yes |
Description: DOP Position Information.
Column List
Name |
Type |
P |
M |
| DOP_Job_Class_CD | char(5) | No | Yes |
| DOP_Merit_System_CD | char(1) | Yes | Yes |
| DOP_Position_CD | char(5) | Yes | Yes |
Description: This table contains information that pertains to some Ecology employees whose appointments require the collection of information that is not maintained by the Department of Personnel.
Column List
Name |
Type |
P |
M |
| ECY_Appoint_ID | numeric(8) | Yes | Yes |
| ECY_Appoint_Perm_Agency_CD | char(3) | No | No |
| ECY_Appoint_Perm_Org_ID | numeric(8) | No | No |
| Ecy_Appoint_Start_DT | datetime | No | No |
| Ecy_Appoint_Stop_DT | datetime | No | No |
| ECY_Appoint_Type_CD | char(1) | No | No |
| Per_Type__ID | numeric(8) | No | Yes |
Description: This table contains key words which describe areas of skill or knowledge .
Column List
Name |
Type |
P |
M |
| Expertise_DS | char(80) | No | Yes |
| Expertise_ID | numeric(8) | Yes | Yes |
Description: This table designates relationships between expertise records to create an expertise/sub-expertise set of information.
Column List
Name |
Type |
P |
M |
| Expertise_ID | numeric(8) | Yes | Yes |
| Expertise_Sub_ID | numeric(8) | Yes | Yes |
Description: For EPIC it is the table that contains various means to describe the physical location of a general area or specific work location on the surface of the earth. This includes street address, city, county, legislative district, STR, and others.
Column List
Name |
Type |
P |
M |
| GEO_LOC_ID | nchar(8) | Yes | Yes |
Description: This table defines the relationship between organizations from a chain of command perspective.
Column List
Name |
Type |
P |
M |
| Org_ID | numeric(8) | No | Yes |
| Org_Rel_End_DT | datetime | No | No |
| Org_Rel_ID | numeric(8) | Yes | Yes |
| Org_Rel_Parent_Org_ID | numeric(8) | No | No |
| Org_Rel_Start_DT | datetime | No | Yes |
Description: A table used to identify groups of persons with specific responsibilities working together to accomplish a common goal.
Column List
Name |
Type |
P |
M |
| Org_Abbrev_NM | char(16) | No | Yes |
| Org_Create_DT | datetime | No | Yes |
| Org_End_DT | datetime | No | No |
| Org_ID | numeric(8) | Yes | Yes |
| Org_NM | char(40) | No | Yes |
| Org_Type_CD | char(1) | No | Yes |
Description: A table that contains a record for each employee or individual of significance to the Department of Ecology.
Column List
Name |
Type |
P |
M |
| Alt_Phone_NR | char(15) | No | No |
| EMail_AD | char(40) | No | No |
| EMail_AD_Exclude_FL | bit | No | Yes |
| Fax_NR | char(15) | No | No |
| First_NM | varchar(20) | No | Yes |
| Internet_Exclude_FL | bit | No | Yes |
| Key_Search_Init_NM | char(1) | No | No |
| Key_Search_NM | varchar(10) | No | No |
| Last_NM | varchar(20) | No | Yes |
| Last_Upd_Init_NM | char(7) | No | No |
| Last_Upt_DT | datetime | No | No |
| Last_Upt_TM | datetime | No | No |
| Logon_ID | char(7) | No | No |
| Middle_Init_NM | char(1) | No | No |
| Party_ID | numeric(8) | Yes | Yes |
| Party_NM | varchar(40) | No | Yes |
| Party_TM | datetime | No | No |
| Phone_Ext_NR | char(5) | No | No |
| Phone_NR | char(15) | No | No |
| Tax_ID_NR | char(15) | No | No |
| Title_NM | char(2) | No | No |
| Type_CD | char(10) | No | Yes |
| Work_Hrs_DS | varchar(100) | No | No |
Description: This table contains textual information pertaining to a person's work-station/office location within a work location. Although codes are available for some work locations, there was no desire to build or maintain a table of these at this time.
Column List
Name |
Type |
P |
M |
| Per_Loc_ID | numeric(8) | No | Yes |
| Per_Work_Station_DS | char(15) | No | Yes |
| Per_Work_Station_ID | numeric(8) | Yes | Yes |
| Per_Work_Station_Primary_FL | bit | No | Yes |
Description: A table that associates one person to another. This allows for the creation of various relationships such as supervisor, contact, co-worker, etc.
Column List
Name |
Type |
P |
M |
| Party_ID | numeric(8) | No | Yes |
| Per_Con_ID | numeric(8) | Yes | Yes |
| Per_Con_Person_ID | numeric(8) | No | Yes |
| Per_Con_Type_CD | char(2) | No | Yes |
Description: This table associates a skill or area of knowledge to a person.
Column List
Name |
Type |
P |
M |
| Expertise_ID | numeric(8) | No | No |
| Expertise_Sub_ID | numeric(8) | No | No |
| Party_ID | numeric(8) | No | Yes |
| Per_Expertise_Duty_FL | bit | No | Yes |
| Per_Expertise_ID | numeric(8) | Yes | Yes |
Description: This table contains the various names by which a person may be known.
Column List
Name |
Type |
P |
M |
| Party_ID | numeric(8) | No | Yes |
| Per_Name_First_NM | char(40) | No | Yes |
| Per_Name_ID | numeric(8) | Yes | Yes |
| Per_Name_Last_NM | char(40) | No | Yes |
| Per_Name_Middle_NM | char(40) | No | No |
| Per_Name_Type_CD | char(2) | No | Yes |
Description: Certain person types, mainly Ecology Employees, will be associated to a given organization. Some persons work for more than one organization at any given time. History is required.
Column List
Name |
Type |
P |
M |
| Org_ID | numeric(8) | No | Yes |
| Per_Org_End_DT | datetime | No | No |
| Per_Org_ID | numeric(8) | Yes | Yes |
| Per_Org_Start_DT | datetime | No | Yes |
| Per_Type__ID | numeric(8) | No | Yes |
Description: Table containing the number assigned to a person's desk phone, cellular phone, beeper, fax machine, or other device that use the telephone system for communication.
Column List
Name |
Type |
P |
M |
| Party_ID | numeric(8) | No | Yes |
| Per_Phone_Area_Cd | char(3) | No | No |
| Per_Phone_Exclude_CD | char(2) | No | No |
| Per_Phone_Extension_NR | char(4) | No | No |
| Per_Phone_ID | numeric(8) | Yes | Yes |
| Per_Phone_NR | char(7) | No | No |
| Per_Phone_Primary_FL | bit | No | Yes |
| Per_Phone_Type_Cd | char(2) | No | Yes |
Description: This file defines a person's relationship to the Department of Ecology for a given period of time. It is possible to have more than one current Person_Type record for a given person.
Column List
Name |
Type |
P |
M |
| Party_ID | numeric(8) | No | Yes |
| Per_SSN_NR | numeric(9) | No | No |
| Per_Type__ID | numeric(8) | Yes | Yes |
| Per_Type_CD | char(1) | No | Yes |
| Per_Type_End_DT | datetime | No | No |
| Per_Type_Start_DT | datetime | No | Yes |
Description: A table that contains information that associates a person to a work location for a given period of time.
Column List
Name |
Type |
P |
M |
| Loc_ID | numeric(8) | No | Yes |
| Party_ID | numeric(8) | No | Yes |
| Per_Loc_End_DT | datetime | No | No |
| Per_Loc_ID | numeric(8) | Yes | Yes |
| Per_Loc_Primary_FL | bit | No | Yes |
| Per_Loc_Start_DT | datetime | No | Yes |
Description: This table contains information pertaining to the work location of a person. This is most often a building but could also be a site with no street address. A link to the physical address and spatial information is provided via the GEO_LOC_ID.
Column List
Name |
Type |
P |
M |
| GEO_LOC_ID | nchar(8) | No | No |
| Loc_Abbrev_NM | char(16) | No | Yes |
| Loc_ID | numeric(8) | Yes | Yes |
| Loc_MailStop_CD | char(5) | No | No |
| Loc_NM | char(50) | No | Yes |
| Loc_USPS_City_NM | char(15) | No | No |
| Loc_USPS_Line1_AD | char(40) | No | No |
| Loc_USPS_Line2_AD | char(40) | No | No |
| Loc_USPS_State_CD | char(2) | No | No |
| Loc_USPS_ZIP_CD | char(9) | No | No |
Return to Table of Contents
EPIC Core Data Element Definitions
ALT_PHONE_NR
Description: An alternate phone number at which party may be
reached. EPIC will place the first phone number found that is defined as one the
party's 'alternate ' phone numbers into this attribute. See Person_Phone table
and Phone_Type_CD for more information.
IIP Attribute - See F/S data dictionary for more information.
DOP_AGENCY_NR
Description: DOP Field. A number assigned to identify a specific state agency.
DOP_APPOINT_END_DT
Description: Not a DOP field but can be derived based on subsequent appointments or employee termination date. The date an employee's appointment to a particular job classification is terminated.
DOP_APPOINT_START_DT
Description: DOP field. The effective date of the most recent appointment of the employee to a particular job classification.
DOP_APPOINT_TYPE_CD
Description: A derivative of DOP's Appointment Status Code which is defined a the type of appointment an employee has to a position. The valid values of this field to be determined by Employee Services during detail design.
DOP_JOB_CLASS_CD
Description: DOP Field. A code used to identify a job classification to which an employee is appointed or that is assigned to a position.
DOP_JOB_CLASS_LONG_DS
Description: DOP Field. The full descriptive name of a job classification. Some examples include Computer Information System Specialist 1, Environmental Planner 5, and Computer Technical Specialist 2.
DOP_JOB_CLASS_SHORT_DS
Description: DOP Field. The abbreviated name of a job classification. Some examples include CISS-1, EP-5, and CTS-2.
DOP_MERIT_SYSTEM_CD
Description: DOP Field. A code that identifies to which employment group a state employee is associated.
DOP_PER_SSN_NR
Description: An employee's Social Security Number. This is a confidential field and should only be accessible by a limited number of Ecology Staff. This is used as a means to link to Ecology's EPIC data.
DOP_PERM__STATUS_CD
Description: DOP Field. Identifies whether an Ecology employee has obtained permanent status or not.
DOP_PERSON_NM
Description: DOP Field. The name of the employee as entered into the State's payroll system.
DOP_POSITION_CD
Description: DOP Field. An agency assigned number used to identify an authorized position into which an employee can be appointed.
DOP_SUB_AGENCY_NR
Description: DOP field. A code assigned to further identify an organizational structure within an agency.
ECY_APPOINT_ID
Description: A unique sequence of characters used to identify each Ecology Appointment record in the EPIC database.
ECY_APPOINT_PERM_AGENCY_CD
Description: The state agency to which an employee on a temporary assignment with Ecology is permanently assigned.
ECY_APPOINT_PERM_ORG_ID
Description: The program (organization) to which an employee on a developmental assignment is permanently assigned.
ECY_APPOINT_START_DT
Description: The date the employee started the Ecology appointment.
ECY_APPOINT_STOP_DT
Description: The date the employee's Ecology appointment terminated.
ECY_APPOINT_TYPE_CD
Description: A code to identify the specific type of appointment to which an employee is associated. A valid type is Developmental Assignment. Others are still to be determined.
EMAIL_AD
Description: An electronic mail address used for contacting a person through the Internet. Default value would be 'NO' which would allow the posting of information.
EMAIL_AD_EXCLUDE_FL
Description: A flag indicating if a person's E-Mail address should not be made available via the internet or on documents available to the general public. Default value is 'NO' which would allow the posting of information.
EXPERTISE_DS
Description: Key words that, at a general or specific level, describe areas in which a person has skill or knowledge.
EXPERTISE_ID
Description: The identifier of an expertise record that designates a person as having a particular skill or knowledge. If used in conjunction with an Expertise_Sub_ID, it identifies a general area of skill or knowledge rather than a specific skill or area of knowledge.
EXPERTISE_SUB_ID
Description: The identifier of an expertise record that designates a person as having a particular skill or knowledge that is a sub-set of a more general area of expertise.
FAX_NR
Description: The phone number of a FAX machine at which a party
will most often send or receive documents. EPIC will place the first phone
number found that is defined as one the party's 'FAX' numbers into this
attribute. See Person_Phone table and Phone_Type_CD for more information.
IIP Attribute - See F/S data dictionary for more information.
FIRST_NM
Description: Given name of a party.
IIP Attribute - See F/S data dictionary for more information.
GEO_LOC_ID
Description: Unique identifier of a Geographic_Loc record.
INTERNET_EXCLUDE_FL
Description: A flag indicating if NO information pertaining to a person should be made available via the internet or on documents available to the general public. Default value would be 'NO' which would allow the posting of information.
KEY_SEARCH_INIT_NM
Description: First character of party’s FIRST_NM.
IIP Attribute - See F/S data dictionary for more information.
KEY_SEARCH_NM
Description: First 10 characters of party's Last_NM.
IIP Attribute - See F/S data dictionary for more information.
LAST_NM
Description: Family name of party.
IIP Attribute - See F/S data dictionary for more information.
LAST_UPD_INIT_NM
Description: IIP Attribute - See F/S data dictionary for information.
LAST_UPT_DT
Description: IIP Attribute - See F/S data dictionary for information.
LAST_UPT_TM
Description: IIP Attribute - See F/S data dictionary for information.
LOC_ABBREV_NM
Description: An abbreviated version of the commonly used name to identify a person's work location.
LOC_ID
Description: A unique sequence of characters used to identify each work location in the EPIC database. On the Person_Work_Location table it identifies the work location of a specific individual.
LOC_MAILSTOP_CD
Description: A code assigned by the State of Washington for addressing mail to a state employee.
LOC_NM
Description: The commonly used name to identify a person's work location.
LOC_USPS_CITY_NM
Description: The city in which the United States Post Office that provides service to a work location is located.
LOC_USPS_LINE1_AD
Description: A portion of the address used by the United States Postal Service for the delivery of mail to a work location. This portion most frequently contains the building number, street, or PO BOX Number at which mail is delivered.
LOC_USPS_LINE2_AD
Description: A portion of the address used by the United States Postal Service for the delivery of mail to a work location. This portion most frequently contains a continuation of the building number, street, PO BOX Number, or additional information required to insure the correct delivery of mail.
LOC_USPS_STATE_CD
Description: The state in which the United States Post Office that provides service to a work location is located.
LOC_USPS_ZIP_CD
Description: A code assigned by United States Post Office to expedite the delivery of mail to a given location.
LOGON_ID
Description: A unique identifier assigned to all people, or automated processes, that require access to the Department of Ecology’s wide area network or to services provided by the Department of Information Systems.
MIDDLE_INIT_NM
Description: First character of a party's middle name.
IIP Attribute - See F/S data dictionary for more information.
ORG_ABBREV_NM
Description: The abbreviated name of an organization. ASD in place of Administrative Services Division would be an example.
ORG_CREATE_DT
Description: The date on which an organization was created.
ORG_END_DT
Description: The date an organization is disbanded and ceases to exist.
ORG_ID
Description: A unique sequence of characters used to identify each organization in the EPIC database. For the Person_Org table it identifies the organization to which an individual is assigned. On the Org_RelationShip table it identifies the base (child) organization.
ORG_NM
Description: The official name of an organization.
ORG_REL_END_DT
Description: The date one organization ceases to be attached to another organization. This can be a result of a reorganization or the disbanding of an organization.
ORG_REL_ID
Description: A unique sequence of characters used to identify each Org_RelationShip record in the EPIC database.
ORG_REL_PARENT_ORG_ID
Description: A unique sequence of characters used to identify each organization in the EPIC database. On the Org_Relationship table it identifies the parent organization to which the organization is attached.
ORG_REL_START_DT
Description: The date an organization is attached to another organization.
ORG_TYPE_CD
Description: A code that identifies an organizations designated type. Valid types include Agency, Division, Office, Program, Section, and Unit.
PARTY_ID
Description: A unique sequence of characters assigned to each person in the EPIC database. In the case of the Person_Expertise table, it identifies the person that possesses a specific expertise or an expertise and sub-expertise. In the case of the Person_Name table, it identifies the person to which a name is associated. In the case of the Person_Contact table it identifies the person for which the record is being created (parent). On the Person_Work_Location table, it designates a person as being assigned to a particular work location. For the Person_Type file it identifies the person for which the Person_Type record is being created. In the case of the Person_Phone table, it identifies the person to which a phone number is associated.
PARTY_NM
Description: Assembled name of party in Last_NM, First_NM, Middle
Initial format.
IIP Attribute - See F/S data dictionary for more information.
PARTY_TM
Description: IIP Attribute - See F/S data dictionary for information.
PER_CON_ID
Description: A unique sequence of characters used to identify each person to person relationship in the EPIC database.
PER_CON_PERSON_ID
Description: The Person_ID of the person to which the Person_Contact record is being created (child).
PER_CON_TYPE_CD
Description: A code that depicts the type of relationship created. Valid types include supervisor, receptionist, and co-worker with others still to be defined.
PER_EXPERTISE_DUTY_FL
Description: An indicator that designates if a person's skill or area of knowledge is considered part of their official job duties as defined by their positions classification questionnaire or other formal assignment.
PER_EXPERTISE_ID
Description: A unique number assigned to each Person_Expertise record for identification.
PER_LOC_END_DT
Description: The date a person was no longer assigned to Work_Location. This could be the result of the employee leaving the department, internal movement, or simply being reassigned to a new work location.
PER_LOC_ID
Description: A unique sequence of characters used to identify each Person_Work_ Location record in the EPIC database.
PER_LOC_ID
Description: A unique sequence of characters used to identify each Person_Work_Location record in the EPIC database. In this case used to identify the person and Work_Location assigned to a given workstation.
PER_LOC_PRIMARY_FL
Description: A flag used to determine if a given work location is the primary work location for a person. This flag is needed because a person may be assigned to more than one work location at the same time.
PER_LOC_START_DT
Description: The date a person was assigned to a work location.
PER_NAME_FIRST_NM
Description: The name occurring first in a person's full name.
PER_NAME_ID
Description: A unique sequence of characters used to identify each Person_Name record in the EPIC database.
PER_NAME_LAST_NM
Description: A person's family or surname.
PER_NAME_MIDDLE_NM
Description: That part of a person's full name that is after the first name but before the family name.
PER_NAME_TYPE_CD
Description: A code that identifies the type of name associated with a given person record. Valid types include Current Legal (Payroll) Name, Former Name, and Nickname. It is possible to have more than one Former Name.
PER_ORG_END_DT
Description: The date a person ceased to work for an organization.
PER_ORG_ID
Description: A unique sequence of characters used to identify each Person_Org record in the EPIC database.
PER_ORG_START_DT
Description: The date a person began work for an organization.
PER_PHONE_AREA_CD
Description: A code assigned by the 'phone company' to designate a geographical area.
PER_PHONE_EXCLUDE_CD
Description: A code that indicates that access to a phone number is restricted and only available to certain Ecology staff. The director's cellular phone number is one example. Code values have not been determined at this time.
PER_PHONE_EXTENSION_NR
Description: An additional four numbers assigned to a phone line as a result of a company owned PBX or the last four digits of the phone number used by some phone routing and voice mail systems.
PERS_PHONE_ID
Description: A unique number assigned to each Person_Phone record for identification.
PER_PHONE_NR
Description: A number assigned to a device by which communication via the phone system can take place.
PER_PHONE_PRIMARY_FL
Description: A flag used to designate a particular phone number as the primary one to contact an individual via a phone. This was needed to accommodate those individuals that have multiple work locations and phones.
PER_PHONE_TYPE_CD
Description: A code that identifies that type of device associated with a phone number.
PER_SSN_NR
Description: An employee's Social Security Number. This is a confidential field and should only be accessible by a limited number of Ecology Staff. This is used as a means to link to DOP data.
PER_TYPE__ID
Description: A unique sequence of characters used to identify each Person_Type record in the EPIC database. In the case of the Person_Org table it identifies the person assigned to a given organization. In the case of the Ecology_Appointment table it identifies an Ecology employee for which the record was created.
PER_TYPE_CD
Description: A code that describes a person's relationship to the Department of Ecology. Valid types include Employee, Contract Staff, Volunteer, Cafeteria, Security, Janitorial, EPA, DNR, Attorney General, etc.
PER_TYPE_END_DT
Description: The last day a person is identified as being a specific person type.
PER_TYPE_START_DT
Description: The effective date on which a person is identified as a particular Person_Type.
PER_WORK_STATION_DS
Description: The cubicle, office, or room number where a person's workstation is located.
PER_WORK_STATION_ID
Description: A unique sequence of characters used to identify each Person_Work_ Location record in the EPIC database.
PER_WORK_STATION_PRIMARY_FL
Description: A flag used to designate a workstation as the primary workstation for a person.
PHONE_EXT_NR
Description: An extension to the party's primary phone number. EPIC will place the Phone_Ext_NR of the party's 'Primary' phone number into this attribute. See Person_Phone table (Phone_Primary_FL & Phone_Ext_NR) for more information. IIP Attribute - See F/S data dictionary for more information.
PHONE_NR
Description: The party's phone number at which they are most
likely to be reached. EPIC will place what is defined as the party's 'Primary'
phone number into this attribute. See Person_Phone table and Phone_Primary_FL
for more information.
IIP Attribute - See F/S data dictionary for more information.
TAX_ID_NR
Description: For F/S and IIP use only - Not used by EPIC.
TITLE_NM
Description: A party's accepted title used to distinguish sex or
educational achievement. Examples include MR, MS, and DR.
IIP Attribute - See F/S data dictionary for more information.
TYPE_CD
Description: A code used to identify a party as an 'Ecology
Employee' or 'Non--Employee'.
IIP Attribute - See F/S data dictionary for more information.
WORK_HRS_DS
Description: A narrative description of a person's work days and
hours.
Note: A complete system needs to be constructed around the request and approval
process and to maintain specific information pertaining to a person's work
hours. This is beyond the scope of the EPIC core information project.
Return to Table of Contents
Return to Ecology's home page
