Input File Layout
You supply the name for the input file. The input file can be a fixed-format file, a comma-separated file, or an ADP-formatted file.
If the file is a fixed-format file, you must fill every position with either an appropriate character or a space. It is not necessary to zero-fill numbers; you can use spaces to maintain the proper format. If the input file is a comma-separated file, you do not need to fill every position. However, the file must contain the correct number of commas. Single character values in the file should be in uppercase.
You will need to provide the values in some fields regardless of the settings in Costpoint. For existing employees, the only unconditionally required fields are Record Code and Employee ID. For new employees, refer to fields that are marked as Required in the Required Optional for New Employees column.
Note: You cannot use commas in the
Notes field.
Note: The input file layout may contain fields that are available only in Costpoint Advanced. If you are using Costpoint Essentials, these fields are excluded from the import process and may be left blank or null when you create the input file.
Input File Excel Template
Go to the Developer Resources page of the Costpoint Information Center to download the Excel template file specifically designed for this preprocessor. Templates for other Costpoint preprocessors are also available at the same location.
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. |
| 18 | Home Reference 2 | EMPL_LAB_INFO.HOME_REF2_ID | Char 20 | Optional | Optional |
Value must exist in the REF_STRUC table. |
| 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. |
| 28 | Default Reference 2 | DFLT_REG_TS.REF_ STRUCT_2_ID | Char 20 | Optional | Optional |
Value must exist in the REF_STRUC table. |
| 29 | Default Labor Location | DFLT_REG_TS.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. |
| 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 |
Note: Record 5 fields
9 -
10 were added as part of Costpoint Employee Multicurrency (MU) feature.
Fixed-Length File Format
The input file layouts for the fixed-length file format follow:
Input Record 1 (EMPL, EMPL_PHONE, DFLT_REG_TS, EMPL_LAB_INFO)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Columns | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | 1 | Required | Required | N or 1 | |
| 2 | Social Security Number | EMPL.SSN_ID | Numeric 9 | 2-10 | Required | Optional | |
| 3 | Employee ID | EMPL.empl_id
EMPL_LAB_INFO.empl_id EMPL_PHONE.eple_id DFLT_REG_TS.empl_id |
Char 12 | 11-22 | Required | Required | |
| 4 | First Name | EMPL.FIRST_NAME | Char 20 | 23-42 | Required | Optional | |
| 5 | Middle Name | EMPL.MID_NAME | Char 10 | 43-52 | Optional | Optional | |
| 6 | Last Name | EMPL.LAST_NAME | Char 25 | 53-77 | Required | Optional | |
| 7 | Hire Date | EMPL.ORIG_HIRE_DT | Char 10 | 78-87 | Required | Optional | YYYY-MM-DD |
| 8 | Employee Status | EMPL.S_EMPL_STATUS_CD | Char 3 | 88-90 | Optional if default is specified on screen.
Otherwise, required. |
Optional | ACT, FML, IAL, IN |
| 9 | Timesheet Cycle | EMPL.TS_PD_CD | Char 4 | 91-94 | 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 | 95-98 | 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 | 99-104 | 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 | 105-107 | Optional | Optional | Value must exist in the PAY_TYPE table. |
| 13 | FLSA Exempt | EMPL_LAB_INFO. EXEMPT_FL | Char 1 | 108 | Optional if default is specified on screen.
Otherwise, required. |
Optional | Y, N |
| 14 | Employee Type | EMPL_LAB_INFO.S_EMPL_TYPE_CD | Char 1 | 109 | Optional if default is specified on screen.
Otherwise, required. |
Optional | R, P, T |
| 15 | Hourly/Salaried Status | EMPL_LAB_INFO.S_HRLY_SAL_CD | Char 1 | 110 | Optional if default is specified on screen.
Otherwise, required. |
Optional | H, F, S |
| 16 | Home Organization | EMPL_LAB_INFO.ORG_ID | Char 20 | 111-130 | 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 | 131-150 | Optional | Optional | Value must exist in the REF_STRUC table. |
| 18 | Home Reference 2 | EMPL_LAB_INFO.HOME_REF2_ID | Char 20 | 151-170 | Optional | Optional | Value must exist in the REF_STRUC table. |
| 19 | Default OT State | EMPL_LAB_INFO.WORK_STATE_CD | Char 2 | 171-172 | 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 | 173-178 | 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 | 179-188 | Required | Required | YYYY-MM-DD |
| 22 | Rate Type | Used to interpret Rate and calculate other rates | Char 1 | 189 | Optional if default is specified on screen.
Otherwise, required. |
Required only if a Rate is specified. | A, H, S |
| 23 | Rate | EMPL_LAB_INFO. ANNL_AMT, HRLY_AMT, SAL_AMT (calculation based on Rate Type) | Num 14 | 190-203 | Required | Required only if a Rate Type is specified | |
| 24 | Default Account | DFLT_REG_TS. ACCT_ID | Char 15 | 204-218 | Optional | Optional | Value must exist in the ACCT table. |
| 25 | Default Organization | DFLT_REG_TS.CHG_ORG_ ID | Char 20 | 219-238 | Optional | Optional | Value must exist in the ORG table. |
| 26 | Default Project | DFLT_REG_TS.PROJ_ID | Char 30 | 239-268 | Optional | Optional | Value must exist in the PROJ table. |
| 27 | Default Reference 1 | DFLT_REG_TS.REF_STRUC_1_ID | Char 20 | 269-288 | Optional | Optional | Value must exist in the REF_STRUC table. |
| 28 | Default Reference 2 | DFLT_REG_TS.REF_ STRUCT_2_ID | Char 20 | 289-308 | Optional | Optional | Value must exist in the REF_STRUC table. |
| 29 | Default Labor Location | EMPL_LAB_INFO.lab_loc_cd | Char 6 | 309-314 | Optional | Optional | Value must exist in the LAB_LOCATION table. |
| 30 | Address Line 1 | EMPL.LN_1_ADR | Char 30 | 315-344 | Optional | Optional | |
| 31 | Address Line 2 | EMPL.LN_2_ADR | Char 30 | 345-374 | Optional | Optional | |
| 32 | Address Line 3 | EMPL.LN_3_ADR | Char 30 | 375-404 | Optional | Optional | |
| 33 | City | EMPL.CITY_NAME | Char 25 | 405-429 | Optional | Optional | |
| 34 | State | EMPL.MAIL_STATE_DC | Char 15 | 430-444 | State/Province required if Country is
USA.
If both State and Country are supplied, the combination must exist in the MAIL_STATE table. |
State/Province required if Country is
USA.
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 | 445-452 | 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 | 453-462 | Optional | Optional | |
| 37 | Home Telephone | EMPL_PHONE.PHONE_ID (with PHONE_TYPE_DC = "HOME") | Char 25 | 463-487 | Optional | Optional | |
| 38 | Emergency Telephone | EMPL_PHONE.PHONE_ID (with PHONE_TYPE_DC = "EMERG") | Char 25 | 488-512 | Optional | Optional | |
| 39 | Locator | EMPL.LOCATOR_CD | Char 20 | 513-532 | Optional | Optional | The application 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 | 533-542 | Required | Optional | YYYY-MM-DD |
| 41 | Marital Status | EMPL.MARITAL_CD | Char 1 | 543 | Optional | Optional | |
| 42 | Gender | EMPL.SEX_CD | Char 1 | 544 | Required | Optional | M, F |
| 43 | Employee Class | EMPL_LAB_INFO.EMPL_ CLASS_CD | Char 12 | 545-556 | Optional | Optional | Value must exist in the EMPL_CLASS table. |
| 44 | Union Employee Flag | EMPL.UNION_EMPL_FL | Char 1 | 557 | Optional | Required | Y, N |
| 45 | Race/Ethnicity | EMPL.S_RACE_CD | Char 10 | 558-567 | Required | Optional (will default from EMPL) | Value must exist in the RACE_ETHNIC table. |
| 46 | Manager | EMPL_LAB_INFO.MGR_EMPL_ID | Char 12 | 568-579 | Optional | Optional | |
| 47 | PLC | EMPL_LAB_INFO.BILL_LAB_CAT_CD | Char 6 | 580-585 | Optional | Optional | Must exist in the BILL_LAB_CAT table. |
| 48 | Corporate Officer Flag | EMPL_LAB_INFO.CORP_OFCR_FL | Char 1 | 586 | 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 | 587 | 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 | 588-593 | Optional | Optional | |
| 51 | Emergency Phone Extension | EMPL_PHONE.PHONE_EXT_CD (where PHONE_TYPE_DC = "EMERG") | Char 6 | 594-599 | Optional | Optional | |
| 52 | Timesheet Default GLC | DFLT_REG_TS. GENL_LAB_CAT_CD | Char 6 | 600-605 | Optional | Optional | Value must exist in the GENL_LAB_CAT table. |
| 53 | Timesheet Default Labor Location | DFLT_REG_TS. LAB_LOC_CD | Char 6 | 606-611 | 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 | 612-623 | 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. |
The value supplied in file will be converted to capitalized characters. |
| 55 | City of Birth | EMPL.BIRTH_CITY_NAME | Char 25 | 624-648 | Optional | Optional | |
| 56 | State/Province of Birth | EMPL.BIRTH_MAIL_STATE_DC | Char 15 | 649-663 | Optional | Optional | Must exist in the MAIL_STATE table. |
| 57 | Country of Birth | EMPL.BIRTH_COUNTRY_CD | Char 8 | 664-671 | Optional | Optional | Must exist in the COUNTRY table. |
| 58 | Variable Hours Employee | EMPL_LAB_INFO.VARIABLE_HRS_FL | Char 1 | 672 | 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 | Columns | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | 1 | Required | Required | 2 | |
| 2 | Employee ID | EMPL.empl_id
EMPL_LAB_INFO.empl_id EMPL_PHONE.empl_id DFLT_REG_TS.empl_id |
Char 12 | 2-13 | Required | Required | |
| 3 | Taxable Entity | EMPL.TAXBLE_ENTITY_ID | Char 10 | 14-23 | Optional | Optional | Value must exist in the TAXBLE_ENTITY table. |
| 4 | Adjusted Hire Date | EMPL.ADJ_HIRE_DT | Char 10 | 24-33 | Optional | Optional | YYYY-MM-DD |
| 5 | Termination Date | EMPL.TERM_DT | Char 10 | 34-43 | Optional | Optional | YYYY-MM-DD |
| 6 | Administrator Name | EMPL.SPVSR_NAME | Char 25 | 44-68 | Optional | Optional | |
| 7 | Preferred Name | EMPL.PREF_NAME | Char 10 | 69-78 | Optional | Optional | |
| 8 | Name Prefix | EMPL.NAME_PRFX_CD | Char 6 | 79-84 | Optional | Optional | |
| 9 | Name Suffix | EMPL.NAME_SFX_D | Char 6 | 85-90 | Optional | Optional | |
| 10 | Prior Name | EMPL.PRIR_NAME | Char 25 | 91-115 | Optional | Optional | |
| 11 | Eligible for Auto Pay | EMPL.ELIG_AUTO_PAY_FL | Char 1 | 116 | Optional | Optional | Y, N |
| 12 | Email ID | EMPL.EMAIL_ID | Char 60 | 117-176 | Optional | Optional | |
| 13 | PR Service Employee ID | EMPL.PR_SERV_EMPL_ID | Char 12 | 177-188 | Optional | Optional | |
| 14 | Job Title Description | EMPL_LAB_INFO.TITLE_ DESC | Char 30 | 189-218 | 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 | Num 10 | 219-228 | Optional | Optional | |
| 16 | Labor Group | EMPL_LAB_INFO.LAB_GRP_TYPE | Char 3 | 229-231 | Optional | Optional | Value must exist in the LAB_GRP table. |
| 17 | Personnel Action Reason Description 1 | EMPL_LAB_INFO.REASON_DESC | Char 30 | 232-261 | Optional | Optional | |
| 18 | Security Organization ID | EMPL_LAB_INFO.SEC_ORG_ID | Char 20 | 262-281 | Optional | Optional | Value must exist in the ORG table. |
| 19 | Work Hours in Year | EMPL_LAB_INFO.WORK_ YR_HRS_NO | Char 4 | 282-285 | Optional | Optional | |
| 20 | Visa Type | EMPL.VISA_TYPE_CD | Char 10 | 286-295 | Optional | Optional | Value must exist in the VISA_TYPE table. |
| 21 | Comments | EMPL_LAB_INFO. COMMENTS | Char 254 | 296-549 | Optional | Optional | |
| 22 | Visa Exp Date | EMPL.VISA_DT | Char 10 | 550-559 | Optional | Optional | YYYY-MM-DD |
Input Record 3 (EMPL)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Columns | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | 1 | Required | Required | 3 | |
| 2 | Employee ID | EMPL.EMPL_ID | Char 12 | 2-13 | Required | Required | |
| 3 | Notes | EMPL.NOTES | Char 254 | 14-267 | Optional | Optional |
Input Record 4 (EMPL and EMPL_LAB_INFO)
| Field | Field Name | Costpoint Table/Column | Data Type and Size | Columns | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | 1 | Required | Required | 4 | |
| 2 | Employee ID | EMPL.EMPL_ID | Char 12 | 2-13 | Required | Required | |
| 3 | Last Review Date | EMPL.LAST_REVIEW_DT | Date (10) | 14-23 | Optional | Optional | YYYY-MM-DD |
| 4 | Next Review Date | EMPL.NEXT_REVIEW_DT | Date (10) | 24-33 | Optional | Optional | YYYY-MM-DD |
| 5 | Display Name | EMPL.LAST_FIRST_NAME | Char 25 | 34-58 | 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 | 59-59 | Y or N | ||
| 7 | Contact Name 1 | EMPL.CONT_NAME_1 | Char 25 | 60-84 | 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 | 85-99 | 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 | 100-119 | 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 | 120-144 | 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 | 145-159 | 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 | 160-179 | 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 | 180 | Optional | Optional | Y or N |
| 14 | Vietnam Era Veteran | EMPL.VET_STATUS_V | Char 1 | 181 | Optional | Optional | Y or N |
| 15 | Active Duty Wartime or Campaign Badge Veteran | EMPL.VET_STATUS_O | Char 1 | 182 | Optional | Optional | Y or N |
| 16 | Reservist | EMPL.VET_STATUS_R | Char 1 | 183 | Optional | Optional | Y or N |
| 17 | Disabled Veteran | EMPL.VET_STATUS_D | Char 1 | 184 | Optional | Optional | Y or N |
| 18 | Armed Forces Service Medal Veteran | EMPL.VET_STATUS_A | Char 1 | 185 | Optional | Optional | Y or N |
| 19 | Military Duty Discharge/Release Date | EMPL.VET_RELEASE_DT | Date (10) | 186-195 | Optional | Optional | YYYY-MM-DD |
| 20 | Home E-mail ID | EMPL.HOME_EMAIL_ID | Char 60 | 196-255 | Optional | Optional | |
| 21 | Time Collection Work Schedule | EMPL_LAB_INFO.TC_WORK_SCHED_CD | Char 10 | 256-265 | Optional | Optional | |
| 22 | Time Collection Timesheet Schedule | EMPL_LAB_INFO.TC_TS_SCHED_CD | Char 10 | 266-275 | Optional | Optional | |
| 23 | ESS Class of Service | EMPL_LAB_INFO.S_ESS_COS_CD | Char 2 | 276-277 | Not Used | Not Used | Blank |
| 24 | Personnel Action Reason Description 2 | EMPL_LAB_INFO. REASON_DESC_2 | Char 30 | 278-307 | Optional | Optional | |
| 25 | Personnel Action Reason Description 3 | EMPL_LAB_INFO. REASON_DESC_3 | Char 30 | 308-337 | Optional | Optional | |
| 26 | Plant | EMPL.PLANT_ID | Char 30 | 338-367 | Optional | Optional | Must exist in the Login Company’s PLANT table |
| 27 | Shop Floor Time flag | EMPL.SFT_FL | Char 1 | 368 | Optional
(The value defaults to N if there is no value provided.) |
Optional | Y or N |
| 28 | Shop Floor Time Entry Type | EMPL.TIME_ENTRY_TYPE | Char 40 | 369-408 | Optional
(The value defaults to PUNCHED if there is no value provided) |
Optional |
‘ELAPSED’ ‘EXCEPTION’ ‘NONE’ |
| 29 | Shop Floor Time Badge Group | EMPL.BADGE_GROUP | Char 60 | 409-468 | 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. |
| 30 | Shop Floor Time Badge ID | EMPL.BADGE_ID | Char 60 | 469-528 | 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. |
| 31 | Manufacturing Execution flag | EMPL.MES_FL | Char 1 | 529 |
(Will default to ‘N’ if no value provided) |
Optional | Y or N |
| 32 | Manufacturing Order Clocking flag | EMPL.CLOCK_FL | Char 1 | 530 | 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 |
| 33 | Shop Floor Time/Manufacturing Execution Login ID | EMPL.LOGIN_ID | Char 60 | 531-590 | Optional | Optional | Up to 30 characters can be specified for the Badge Group. Any characters past 30 will be truncated. |
| 34 | Protected Veteran (Declined to Self-Identify) | EMPL.VET_STATUS_p | Char 1 | 591 | Optional | Optional | Y or N |
Note: Record 4 fields
26 - 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 | Columns | Required/Optional for New Employees | Required/Optional for Existing Employees | Contents |
|---|---|---|---|---|---|---|---|
| 1 | Record Code | Char 1 | 1 | Required | Required | 5 | |
| 2 | Employee ID | EMPL.EMPL_ID | Char 12 | 2-13 | Required | Required | |
| 3 | Detail Job Code | EMPL_LAB_INFO.DETL_JOB_CD | Char 10 | 14-23 | 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 | 24-33 | Optional | Optional | Must exist in the PERS_ACT_REASON table. |
| 5 | Personnel Action Code 2 | EMPL_LAB_INFO.PERS_ACT_RSN_CD | Char 10 | 34-43 | Optional | Optional | Must exist in the PERS_ACT_REASON table. |
| 6 | Personnel Action Code 3 | EMPL_LAB_INFO.PERS_ACT_RSN_CD | Char 10 | 44-53 | Optional | Optional | Must exist in the PERS_ACT_REASON table. |
| 7 | HR Org | EMPL_LAB_INFO.HR_ORG_ID | Char 25 | 54-78 | Optional | Optional | Must exist in the HR_ORG_MGR_DFLT table.
This field only accepts 20 characters. You will need to fill remaining five characters with spaces. |
| 8 | Default Rate Group | EMPL_LAB_INFO.DFLT_RT_GRP_ID | Char 6 | 79-84 | Optional | Optional | Must exist in the RT_GRP table |
| 9 | Currency | EMPL_LAB_INFO.TRN_CRNCY_CD | Char 3 | 85-87 | Optional | Optional | Must exist in the CURRENCY table |