Comma-Separated File Layout
Use this file layout if you are importing a Comma-Separated (CSV) file format input file on the Import Employee Data screen.
Comma-Separated Values Format
The input file layouts for the comma-separated values format follow:
Input Record 1 (EMPL, EMPL_PHONE, DFLT_REG_TS, EMPL_LAB_INFO)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|
| 1 | Record Code | N/A | Char 1 | Required | Required | N or 1 |
| 2 | Social Security Number | EMPL.SSN_ID | Numeric 9 | Required | Optional | |
| 3 | Employee ID | EMPL.empl_id
EMPL_LAB_INFO.empl_id EMPL_PHONE.empl_id DFLT_REG_TS.empl_id |
Char 12 | Required | Required | |
| 4 | First Name | EMPL.FIRST_NAME | Char 20 | Required | Optional | |
| 5 | Middle Name | EMPL.MID_NAME | Char 10 | Optional | Optional | |
| 6 | Last Name | EMPL.LAST_NAME | Char 25 | Required | Optional | |
| 7 | Hire Date | EMPL.ORIG_HIRE_DT | Char 10 | Required | Optional | YYYY-MM-DD |
| 8 | Employee Status | EMPL.S_EMPL_STATUS_CD | Char 3 | Optional if default is specified on screen.
Otherwise, required. |
Optional | ACT, FML, IAL, IN |
| 9 | Timesheet Cycle | EMPL.TS_PD_CD | Char 4 | Optional if default is specified on screen.
Otherwise, required. |
Optional | Value must exist in the TS_PD table. |
| 10 | Leave Cycle | EMPL.LV_PD_CD | Char 4 | Optional if default is specified on screen.
Otherwise, required. |
Optional | Value must exist in the LV_PD table. |
| 11 | General Labor Category | EMPL_LAB_INFO.GENL_LAB_CAT_CD | Char 6 | Optional if default is specified on screen.
Otherwise, required. |
Optional | Value must exist in the GENL_LAB_CAT table. |
| 12 | Default Pay Type | DFLT_REG_TS.PAY_TYPE | Char 3 | Optional | Optional | Value must exist in the PAY_TYPE table. |
| 13 | FLSA Exempt | EMPL_LAB_INFO.EXEMPT_FL | Char 1 | Optional if default is specified on screen.
Otherwise, required. |
Optional | Y, N |
| 14 | Employee Type | EMPL_LAB_INFO.S_EMPL_TYPE_CD | Char 1 | Optional if default is specified on screen.
Otherwise, required. |
Optional | R, P, T |
| 15 | Rate Type (Hourly/Salaried Status) | EMPL_LAB_INFO.S_HRLY_SAL_CD | Char 1 | Optional if default is specified on screen.
Otherwise, required. |
Optional | H, F, S |
| 16 | Home Organization | EMPL_LAB_INFO.ORG_ID | Char 20 | Optional if default is specified on screen.
Otherwise, required. |
Optional | Value must exist in the ORG table. |
| 17 | Home Reference 1 | EMPL_LAB_INFO.HOME_REF1_ID | Char 20 | Optional | Optional |
Value must exist in the REF_STRUC table. Not available with a Costpoint Essentials license. |
| 18 | Home Reference 2 | EMPL_LAB_INFO.HOME_REF2_ID | Char 20 | Optional | Optional |
Value must exist in the REF_STRUC table. Not available with a Costpoint Essentials license. |
| 19 | Default OT State | EMPL_LAB_INFO.WORK_STATE_CD | Char 2 | Optional if default is specified on screen.
Otherwise, required. |
Optional | Value must exist in the OT_RULES_BY_STATE table. |
| 20 | Default Worker's Comp | DFLT_REG_TS.WORK_COMP_CD | Char 6 | If the Worker’s Compensation-Require Default For Employees check box is selected in the Labor Settings screen, this is a required field. Otherwise, this is an optional field. | Optional | Value must exist in the WORK_COMP table. |
| 21 | Effective Date | EMPL_LAB_INFO.EFFECT_DT | Char 10 | Required | Required | YYYY-MM-DD |
| 22 | Rate Provided in File | N/A | Char 1 | Optional if default is specified on screen.
Otherwise, required. |
Required only if a Rate is specified. | A, H, S |
| 23 | Rate | EMPL_LAB_INFO. HRLY_MAT EMPL_LAB_INFO. SAL_AMT EMPL_LAB_INFO. ANNL_AMT (calculation based on Rate Type) | Char 20 | Required | Required only if a Rate Type is specified | |
| 24 | Default Account | DFLT_REG_TS.ACCT_ID | Char 15 | Optional | Optional | Value must exist in the ACCT table. |
| 25 | Default Organization | DFLT_REG_TS.CHG_ORG_ ID | Char 20 | Optional | Optional | Value must exist in the ORG table. |
| 26 | Default Project | DFLT_REG_TS.PROJ_ID | Char 30 | Optional | Optional | Value must exist in the PROJ table. |
| 27 | Default Reference 1 | DFLT_REG_TS.REF_STRUC_1_ID | Char 20 | Optional | Optional |
Value must exist in the REF_STRUC table. Not available with a Costpoint Essentials license. |
| 28 | Default Reference 2 | DFLT_REG_TS.REF_ STRUCT_2_ID | Char 20 | Optional | Optional |
Value must exist in the REF_STRUC table. Not available with a Costpoint Essentials license. |
| 29 | Default Labor Location | EMPL_LAB_INFO.LAB_LOC_ CD | Char 6 | Optional | Optional | Value must exist in the LAB_LOCATION table. |
| 30 | Address Line 1 | EMPL.LN_1_ADR | Char 30 | Optional | Optional | |
| 31 | Address Line 2 | EMPL.LN_2_ADR | Char 30 | Optional | Optional | |
| 32 | Address Line 3 | EMPL.LN_3_ADR | Char 30 | Optional | Optional | |
| 33 | City | EMPL.CITY_NAME | Char 25 | Optional | Optional | |
| 34 | State | EMPL.MAIL_STATE_DC | Char 15 | State/Province required if Country is
US
If both State and Country are supplied, the combination must exist in the MAIL_STATE table. |
State/Province required if Country is
US
If both State and Country are supplied, the combination must exist in the MAIL_STATE table. |
Value must exist in the MAIL_STATE table. |
| 35 | Country | EMPL.COUNTRY_CD | Char 8 | Required if a State/Province is supplied. | Required if a State/Province is supplied. | Value must exist in the COUNTRY table. |
| 36 | Postal Code | EMPL.POSTAL_CD | Char 10 | Optional | Optional | |
| 37 | Home Telephone | EMPL_PHONE.PHONE_ID (with PHONE_TYPE_DC = "HOME") | Char 25 | Optional | Optional | |
| 38 | Emergency Telephone | EMPL_PHONE.PHONE_ID (with PHONE_TYPE_DC = "EMERG") | Char 25 | Optional | Optional | |
| 39 | Locator | EMPL.LOCATOR_CD | Char 20 | Optional | Optional | Costpoint validates the Locator code upon import if at least one Locator code exists in the Locator Code table for the login company; otherwise, no validation occurs. |
| 40 | Date Of Birth | EMPL.BIRTH_DT | Char 10 | Required | Optional | YYYY-MM-DD |
| 41 | Marital Status | EMPL.MARITAL_CD | Char 1 | Optional | Optional | |
| 42 | Gender | EMPL.SEX_CD | Char 1 | Required | Optional | M, F, X |
| 43 | Employee Class | EMPL_LAB_INFO.EMPL_ CLASS_CD | Char 12 | Optional | Optional | Value must exist in the EMPL_CLASS table. |
| 44 | Union Employee Flag | EMPL.UNION_EMPL_FL | Char 1 | Optional | Required | Y or N.
Default to N for Costpoint Essentials license. |
| 45 | Race/Ethnicity | EMPL.S_RACE_CD | Char 10 | Required | Optional (will default from EMPL) | Value must exist in the RACE_ETHNIC table. |
| 46 | Manager | EMPL_LAB_INFO.MGR_EMPL_ID | Char 12 | Optional | Optional | |
| 47 | PLC | EMPL_LAB_INFO.BILL_LAB_CAT_CD | Char 6 | Optional | Optional | Must exist in the BILL_LAB_CAT table. |
| 48 | Corporate Officer Flag | EMPL_LAB_INFO.CORP_OFCR_FL | Char 1 | Optional (N assumed if null) | Optional (N assumed if null) | Y or N |
| 49 | Seasonal Employee Flag | EMPL_LAB_INFO.SEASON_EMPL_FL | Char 1 | Optional (N assumed if null) | Optional (N assumed if null) | Y or N |
| 50 | Home Phone Extension | EMPL_PHONE.PHONE_EXT_CD (where PHONE_TYPE_DC = ‘HOME’) | Char 6 | Optional | Optional | |
| 51 | Emergency Phone Extension | EMPL_PHONE.PHONE_EXT_CD (where PHONE_TYPE_DC = "EMERG") | Char 6 | Optional | Optional | |
| 52 | Timesheet Default GLC | DFLT_REG_TS. GENL_LAB_CAT_CD | Char 6 | Optional | Optional | Value must exist in the GENL_LAB_CAT table. |
| 53 | Timesheet Default Labor Location | DFLT_REG_TS. LAB_LOC_CD | Char 6 | Optional | Optional | Value must exist in the LAB_LOCATION table. |
| 54 | Vendor ID | This is not stored in the Employee table. This is used to generate a Vendor record for the employee if:
|
Char 12 | Optional | Optional
If the employee is already linked to the Vendor ID supplied, it will be ignored. If the employee is already linked to a different Vendor, a warning message will display which mentions the employee was not linked to the Vendor ID supplied. |
Value supplied in file will be converted to capitalized characters. |
| 55 | City of Birth | EMPL.BIRTH_CITY_NAME | Char 25 | Optional | Optional | |
| 56 | State/Province of Birth | EMPL.BIRTH_MAIL_STATE_DC | Char 15 | Optional | Optional | Must exist in the MAIL_STATE table. |
| 57 | Country of Birth | EMPL.BIRTH_COUNTRY_CD | Char 8 | Optional | Optional | Must exist in the COUNTRY table. |
| 58 | Variable Hours Employee | EMPL_LAB_INFO.VARIABLE_HRS_FL | Char 1 | Optional (N assumed if null) | Optional | Y or N |
Input Record 2 (EMPL and EMPL_LAB_INFO)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | Required | Required | 2 | |
| 2 | Employee ID | EMPL_ID | Char 12 | Required | Required | |
| 3 | Taxable Entity | EMPL.TAXBLE_ENTITY_ID | Char 10 | Optional | Optional | Value must exist in the TAXBLE_ENTITY table. |
| 4 | Adjusted Hire Date | EMPL.ADJ_HIRE_DT | Char 10 | Optional | Optional | YYYY-MM-DD |
| 5 | Termination Date | EMPL.TERM_DT | Char 10 | Optional | Optional | YYYY-MM-DD |
| 6 | Administrator Name | EMPL.SPVSR_NAME | Char 25 | Optional | Optional | |
| 7 | Preferred Name | EMPL.PREF_NAME | Char 10 | Optional | Optional | |
| 8 | Name Prefix | EMPL.NAME_PRFX_CD | Char 6 | Optional | Optional | |
| 9 | Name Suffix | EMPL.NAME_SFX_CD | Char 6 | Optional | Optional | |
| 10 | Prior Name | EMPL.PRIR_NAME | Char 25 | Optional | Optional | |
| 11 | Eligible for Auto Pay | EMPL.ELIG_AUTO_PAY_FL | Char 1 | Optional | Optional | Y, N |
| 12 | Email ID | EMPL.EMAIL_ID | Char 60 | Optional | Optional | |
| 13 | PR Service Employee ID | EMPL.PR_SERV_EMPL_ID | Char 12 | Optional | Optional | |
| 14 | Job Title Description | EMPL_LAB_INFO.TITLE_ DESC | Char 30 | Optional
(If licensed for HR and Detail Job Title provided, will default from Detail Job Title) |
Optional | |
| 15 | Estimated Hours | EMPL_LAB_INFO.STD_EST_HRS | Char 10 | Optional | Optional | |
| 16 | Labor Group | EMPL_LAB_INFO.LAB_GRP_TYPE | Char 3 | Optional | Optional | Value must exist in the LAB_GRP table. |
| 17 | Personnel Action Reason Description 1 | EMPL_LAB_INFO.REASON_DESC | Char 30 | Optional | Optional | |
| 18 | Security Organization ID | EMPL_LAB_INFO.SEC_ORG_ID | Char 20 | Optional
(Will default from Home Organization if not provided in file) |
Optional | Value must exist in the ORG table. |
| 19 | Work Hours in Year | EMPL_LAB_INFO.WORK_ YR_HRS_NO | Char 4 | Optional
(Will default from Home Organization if not provided in file) |
Optional | |
| 20 | Visa Type | EMPL.VISA_TYPE_CD | Char 10 | Optional | Optional | Value must exist in the VISA_TYPE table. |
| 21 | Comments | EMPL_LAB_INFO. COMMENTS | Char 254 | Optional | Optional | |
| 22 | Visa Exp Date | EMPL.VISA_DT | Char 10 | Optional | Optional | YYYY-MM-DD |
| 23 | Effective Date | EMPL_LAB_INFO. EFFECT_DT | Char 10 | Required | Required | YYYY-MM-DD |
Input Record 3 (EMPL)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | Required | Required | 3 | |
| 2 | Employee ID | EMPL.EMPL_ID | Char 12 | Required | Required | |
| 3 | Notes | EMPL.NOTES | Char 254 | Optional | Optional | |
| 4 | Effective Date | EMPL_LAB_INFO. EFFECT_DT | Char 10 | Required | Required | YYYY-MM-DD |
Input Record 4 (EMPL and EMPL_LAB_INFO)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | Required | Required | 4 | |
| 2 | Employee ID | EMPL.EMPL_ID | Char 12 | Required | Required | |
| 3 | Last Review Date | EMPL.LAST_REVIEW_DT | Date (10) | Optional | Optional | YYYY-MM-DD |
| 4 | Next Review Date | EMPL.NEXT_REVIEW_DT | Date (10) | Optional | Optional | YYYY-MM-DD |
| 5 | Display Name | EMPL.LAST_FIRST_NAME | Char 25 | Optional (Can be built using First, Last, and Middle Names) | Optional (Can be built using First, Last, and Middle Names) | |
| 6 | Disabled Flag | EMPL.DISABLED_FL | Char 1 | Optional (N assumed if null) | Optional | Y or N |
| 7 | Contact Name 1 | EMPL.CONT_NAME_1 | Char 25 | Required (if "Contact Phone 1" and/or "Contact Relationship 1" are specified) | Required (if "Contact Phone 1" and/or "Contact Relationship 1" are specified) | |
| 8 | Contact Relationship 1 | EMPL.CONT_REL_1 | Char 15 | Required (if "Contact Phone 1" and/or "Contact Name 1" are specified) | Required (if "Contact Phone 1" and/or "Contact Name 1" are specified) | |
| 9 | Contact Phone 1 | EMPL.CONT_PHONE_1 | Char 20 | Required (if "Contact Name 1" and/or "Contact Relationship 1" are specified) | Required (if "Contact Name 1" and/or "Contact Relationship 1" are specified) | |
| 10 | Contact Name 2 | EMPL.CONT_NAME_2 | Char 25 | Required (if "Contact Phone 2" and/or "Contact Relationship 2" are specified) | Required (if "Contact Phone 2" and/or "Contact Relationship 2" are specified) | |
| 11 | Contact Relationship 2 | EMPL.CONT_REL_2 | Char 15 | Required (if "Contact Phone 2" and/or "Contact Name 2" are specified) | Required (if "Contact Phone 2" and/or "Contact Name 2" are specified) | |
| 12 | Contact Phone 2 | EMPL.CONT_PHONE_2 | Char 20 | Required (if "Contact Name 2" and/or "Contact Relationship 2" are specified) | Required (if "Contact Name 2" and/or "Contact Relationship 2" are specified) | |
| 13 | Special Disabled Veteran | EMPL.VET_STATUS_S | Char 1 | Optional | Optional | Y or N |
| 14 | Vietnam Era Veteran | EMPL.VET_STATUS_V | Char 1 | Optional | Optional | Y or N |
| 15 | Active Duty Wartime or Campaign Badge Veteran | EMPL.VET_STATUS_O | Char 1 | Optional | Optional | Y or N |
| 16 | Reservist | EMPL.VET_STATUS_R | Char 1 | Optional | Optional | Y or N |
| 17 | Disabled Veteran | EMPL.VET_STATUS_D | Char 1 | Optional | Optional | Y or N |
| 18 | Armed Forces Service Medal Veteran | EMPL.VET_STATUS_A | Char 1 | Optional | Optional | Y or N |
| 19 | Military Duty Discharge/Release Date | EMPL.VET_RELEASE_DT | Date (10) | Optional | Optional | YYYY-MM-DD |
| 20 | Home E-mail ID | EMPL.HOME_EMAIL_ID | Char 60 | Optional | Optional | |
| 21 | Time Collection Work Schedule | EMPL_LAB_INFO.TC_WORK_SCHED_CD | Char 10 | Optional | Optional | |
| 22 | Time Collection Timesheet Schedule | EMPL_LAB_INFO.TC_TS_SCHED_CD | Char 10 | Optional | Optional | |
| 23 | ESS Class of Service | EMPL_LAB_INFO.S_ESS_COS_CD | Char 2 | Not Used | Not Used | Blank |
| 24 | Personnel Action Reason Description 2 | EMPL_LAB_INFO. REASON_DESC_2 | Char 30 | Optional | Optional | |
| 25 | Personnel Action Reason Description 3 | EMPL_LAB_INFO. REASON_DESC_3 | Char 30 | Optional | Optional | |
| 26 | Effective Date | EMPL_LAB_INFO. EFFECT_DT | Char 10 | Required | Required | YYYY-MM-DD |
| 27 | Plant | EMPL.PLANT_ID | Char 30 | Optional | Optional | Must exist in the Login Company’s PLANT table |
| 28 | Shop Floor Time flag | EMPL.SFT_FL | Char 1 | Optional
(The value defaults to N if there no value provided.) |
Optional | Y or N |
| 29 | Shop Floor Time Entry Type | EMPL.TIME_ENTRY_TYPE | Char 40 | Optional
(The value defaults to PUNCHED if there is no value provided.) |
Optional | ‘PUNCHED’
‘ELAPSED’ ‘EXCEPTION’ ‘NONE’ |
| 30 | Shop Floor Time Badge Group | EMPL.BADGE_GROUP | Char 60 | Required if a Shop Floor Time Badge ID is specified | Optional | Up to 40 characters can be specified for the Badge Group. Any characters past 40 will be truncated. |
| 31 | Shop Floor Time Badge ID | EMPL.BADGE_ID | Char 60 | Required if a Shop Floor Time Badge Group is specified | Optional | Up to 40 characters can be specified for the Badge Group. Any characters past 40 will be truncated. |
| 32 | Manufacturing Execution flag | EMPL.MES_FL | Char 1 | Optional
(The value defaults to N if there is no value provided) |
Optional | Y or N |
| 33 | Manufacturing Order Clocking flag | EMPL.CLOCK_FL | Char 1 | Optional
(If the employee’s Shop Floor Time flag is Y and his/her Shop Floor Time Entry Type is PUNCHED, the Manufacturing Execution Manufacturing Order Clocking check box should default to checked. Otherwise, the flag will be set to N.) |
Optional | Y or N |
| 34 | Shop Floor Time/Manufacturing Execution Login ID | EMPL.LOGIN_ID | Char 60 | Optional | Optional | |
| 35 | Protected Veteran (Declined to Self-Identify) | EMPL.VET_STATUS_p | Char 1 | Optional | Optional | Y or N |
Note: Record 4 fields
27 -
34 were added as part of Costpoint Project Manufacturing interface.
Input Record 5 (EMPL and EMPL_LAB_INFO)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | Required | Required | 5 | |
| 2 | Employee ID | EMPL.EMPL_ID | Char 12 | Required | Required | |
| 3 | Detail Job Code | EMPL_LAB_INFO.DETL_JOB_CD | Char 10 | Required (if licensed for HR) | Required (if licensed for HR) | Must exist in the DETL_JOB_TITLE table. |
| 4 | Personnel Action Code 1 | EMPL_LAB_INFO.PERS_ACT_RSN_CD | Char 10 | Optional | Optional | Must exist in the PERS_ACT_REASON table. |
| 5 | Personnel Action Code 2 | EMPL_LAB_INFO.PERS_ACT_RSN_CD | Char 10 | Optional | Optional | Must exist in the PERS_ACT_REASON table. |
| 6 | Personnel Action Code 3 | EMPL_LAB_INFO.PERS_ACT_RSN_CD | Char 10 | Optional | Optional | Must exist in the PERS_ACT_REASON table. |
| 7 | HR Org | EMPL_LAB_INFO.HR_ORG_ID | Char 20 | Optional | Optional | Must exist in the HR_ORG_MGR_DFLT table. |
| 8 | Effective Date | EMPL_LAB_INFO. EFFECT_DT | Char 10 | Required | Required | YYYY-MM-DD |
| 9 | Default Rate Group | EMPL_LAB_INFO.DFLT_RT_GRP_ID | Char 6 | Optional | Optional | Must exist in the RT_GRP table |
| 10 | Currency | EMPL_LAB_INFO.TRN_CRNCY_CD | Char 3 | Optional | Optional | Must exist in the CURRENCY table |
| 11 | Last Day Worked | EMPL.LAST_DAY_DT | Char 10 | Optional | Optional | YYYY-MM-DD |
| 12 | Not a protected veteran | EMPL.VET_STATUS_NP_FL | Char 1 | Optional | Optional | Y or N |
| 13 | Declined to provide veteran status | EMPL.VET_STATUS_DECLINED | Char 1 | Optional | Optional | Y or N |
| 14 | Recently separated veteran | EMPL.VET_STATUS_RS | Char 1 | Optional | Optional | Y or N |
| 15 | Supervisor ID for EMPL_LAB_INFO | EMPL_LAB_INFO.SPVSR_EMPL_ID | Char 12 | Optional | Optional | |
| 16 | Requisition Number | EMPL_LAB_INFO.REQ_NO | Char 10 | Optional | Optional | |
| 17 | Contractor flag | EMPL.CONTRACTOR_FL | Char 1 | Optional | Optional | Y or N |
| 18 | Blind flag | EMPL.BLIND_FL | Char 1 | Optional | Optional | Y or N |
| 19 | GovWin IQ ID | EMPL.GOVWINIQ_LOGIN_ID | Char (100) | Optional | Optional | Alphanumeric |
| 20 | ESS User | N/A - not stored | Char 1 | Optional | Optional | Y or N |
| 21 | Login ID |
N/A - not stored EMPL • Allow entry of up to 254 alphanumeric characters and special characters |
Char 254 | Optional | N/A - only used for new employees | |
| 22 | User ID | N/A - not stored EMPL | Char 20 | Optional | N/A - only used for new employees |
Note: Record 5 fields
9 -
10 were added as part of Costpoint Employee Multicurrency (MU) feature.