Processing Details
The Import Timesheets screen processes values from the input file and multiple sources to create timesheet records in Costpoint.
Processing Details
- The program checks the primary temporary table to determine whether any rows have not been imported. If there are, the system displays a message, and you can continue or cancel the process. If you continue, all temporary tables are cleared.
- Records from the input file are read and inserted into the primary temporary table.
- If you selected Comma-Separated Values in the File Format field, the program checks each field to see whether it is too large.
- The program applies defaults to any fields that are blank in the input file.
- Validation/substitution of data is performed in accordance with the selections you made in the Validation Method group box of the Configure Timesheet Suspense Values screen.
- Suspense processing is performed in accordance with the values you entered into the Suspense Values group box of the Configure Timesheet Suspense Values screen. On timesheets that have invalid projects, organizations, accounts, POA combinations, PLCs, GLCs, reference 1 numbers, and/or reference 2 numbers, the invalid values are replaced by the respective suspense values. A message is written to the Suspense section of the error report and the timesheet line is written to the Suspense table. The account, project, organization, POA combinations, PLC, GLC, reference 1 number, and/or reference 2 number in the temporary table are replaced with the default suspense values and processing continues.
- After the validations are completed, timesheets with errors are written to the error file. If there is an error on one line of the timesheet, all lines are rejected and written to the error file. These rows are then deleted from the temporary table. Timesheets with no errors, but with Warnings or Suspense replacements, are not deleted from the temporary table. The temporary table now contains only valid rows.
- The application calculates charge hours and labor cost (if blank).
- If you selected the Auto Adjust Salaried Employees check box, all timesheets for salaried employees are auto-adjusted. If you selected the Auto Adjust Hourly Employees check box, all the timesheets for hourly employees are auto-adjusted.
- The Error Report is printed. If there are errors, a message box displays on the screen to notify you that errors were found. If there are no errors, the Error Report indicates that no error records were found.
Import Details
- The application checks the dates of the rows in the temporary table to verify that they fall within the range of dates entered on the screen.
- The timesheet line key is set.
- The Timesheet Header table is checked again to ensure that the timesheets being imported do not already exist. If duplicates exist, the system displays a message and importing is not done.
- Rows from the temporary table are inserted into the Timesheet Header and Timesheet Line tables.
- If suspense processing has taken place, suspense replacement data is written to the Suspense table.
- All temporary tables are cleared.
Tables
A list of the Costpoint tables used by this preprocessor and their corresponding Costpoint screens is provided at the end of this topic.
Timesheet Header Table
The Timesheet Header table is filled as follows:
Field | Description |
---|---|
Timesheet Date (TS_DT) |
|
Employee ID (EMPL_ID) |
|
Timesheet Type Code (S_TS_TYPE_CD) |
|
Timesheet Header Sequence Number (TS_HDR_SEQ_NO) |
|
Timesheet Header Reference Sequence Number (REFERENCE_SEQ_NO) |
|
User ID (USER_ID) |
The application sets the user ID to the current user. |
Posting Sequence Number (POST_SEQ_NO) |
The posting sequence number is left blank by the application. |
Payroll Posted Flag (PR_POSTED_FL) |
The application sets the payroll posting flag to N (No). |
Leave Posted Flag (LV_POSTED_FL) |
The application sets the leave posted flag to N (No). |
Entry Date (ENTRY_DT) |
The application sets the entry date to the current system date. |
Working State (WORK_STATE_CD) |
|
Fiscal Year (FY_CD) | The fiscal year is taken from the input file or the screen. |
Period Number (PD_NO) | The period is taken from the input file or the screen. |
Subperiod Number (SUB_PD_NO) |
|
Journal Code (S_JNL_CD) |
The application sets the journal code to LD (Labor Distribution). |
Timesheet Batch ID (TS_BATCH_ID) |
The timesheet batch ID is left blank by the application. |
Correcting Reference Date (CORRECTING_REF_DT) |
|
Auto-Adjust Percentage Rate (AUTO_ADJ_PCT_RT) |
If you entered an Override Auto Adj % on the screen, the application uses that value to populate the AUTO_ADJ_PCT_RT column. If you did not enter an override on the screen, the program uses the Default Auto-Adjustment Percent for the employee's timesheet cycle (on the Manage Timesheet Periods screen) to populate the column. |
Timesheet Header Compute Code (S_TS_HDR_CMPUT_CD) |
The application sets the timesheet header compute code to O (Original). |
Labor Group Type (LAB_GRP_TYPE) |
The labor group type is taken from the Salary Information and History table. |
Pay Period (PAY_PD_CD) |
The pay period code is left blank by the application. |
Pay Period Start Date (PAY_PD_START_DT) |
The pay period start date is left blank by the application. |
Pay Period End Date (PAY_PD_END_DT) |
The pay period end date is left blank by the application. |
Timesheet Period Code (TS_PD_CD) |
The timesheet period code is taken from the Employee table. |
Home Organization (EMPL_HOME_ORG_ID) |
The home organization is taken from the Salary Information and History table. |
Inter-Company Tracking Organization (IC_TRKNG_ORG_ID) |
The program derives the inter-company tracking organization from the home organization by using the Balance Sheet Level defined in the Organization table (see the Manage Organization Structures screen). |
Home Reference Number 1 (EMPL_HOME_REF1_ID) |
The home reference number 1 is taken from the Salary Information and History table. |
Home Reference Number 2 (EMPL_HOME_REF2_ID) |
The home reference number 2 is taken from the Salary Information and History table. |
Timesheet Line Table
The Timesheet Line table is filled as follows:
Field | Description |
---|---|
Timesheet Date (TS_DT) | |
Employee ID (EMPL_ID) | |
Timesheet Type (S_TS_TYPE_CD) | |
Timesheet Header Sequence Number (TS_HDR_SEQ_NO) |
|
Timesheet Line Number (TS_LN_NO) |
The timesheet line number is set to the sequence number of the input line in the input file. |
Pay Type (PAY_TYPE) |
|
General Labor Category (GENL_LAB_CAT_CD) |
|
Timesheet Line Type (S_TS_LN_TYPE_CD) |
|
Labor Cost Amount (LAB_CST_AMT) |
|
Entered Hours (ENTERED_HRS) |
|
Workers' Comp Code (WORK_COMP_CD) |
|
Labor Location Code (LAB_LOC_CD) |
|
Compute Method (S_CMPUT_MTHD_CD) |
The compute method is set by the application. The compute method is a combination of a code indicating the source of the hourly rate and a code indicating the pay type calculation to be used. See the sections for Hourly Rate and Labor Cost Amount. In addition to the codes listed under Hourly Rate, Z is used if Entered Hours is zero. |
Charge Hours (CHG_HRS) |
The charge hours are set by the application as follows:
|
Hourly Rate (HRLY_AMT) |
The application sets the hourly rate. The hourly rate to be used is determined as follows:
|
Organization (ORG_ID) |
|
Organization Abbreviation (ORG_ABBRV_CD) |
|
Account (ACCT_ID) |
|
Project (PROJ_ID) | The project and/or the project abbreviation are taken from the input file. |
Project Abbreviation (PROJ_ABBRV_CD) |
|
Project Labor Category (BILL_LAB_CAT_CD) |
|
Notes (NOTES) |
Notes are taken from the input file. If the Notes field in the input file is all spaces, it is set to a single space by the application. |
Withholding State (WH_STATE_CD) |
|
Reference Number 1 (REF_STRUC_1_ID) |
|
Reference Number 2 (REF_STRUC_2_ID) |
|
Effective Billing Date (EFFECT_BILL_DT) |
|
Manufacturing Order (MO) Table (TS_LN_MO)
The Manufacturing Order Line table is filled as follows:
Field | Description |
---|---|
Timesheet Date (TS_DT) | |
Employee ID (EMPL_ID) | |
Timesheet Header Sequence Number (TS_HDR_SEQ_NO) |
These fields are set the same as in the Timesheet header. |
Timesheet Line Number (TS_LN_NO) |
The timesheet line number is set to the sequence number of the input line in the input file. |
Manufacturing Order (MO_ID) |
The program populates this field with the manufacturing order ID from the input file. |
Operation Sequence Number (MO_OPER_SEQ_NO) |
The program populates this field with the operation sequence number from the input file. |
Operation Step Number (MO_OPER_STEP_NO) |
The program populates this field with the operation step number from the input file. |
Work Center (WC_ID) |
The program populates this field with the work center ID from the input file. |
Charge Hours (CHG_HRS) |
The charge hours are set by the application as follows:
|
Labor Cost Amount (LAB_CST_AMT) |
|
Fiscal Year (FY_CD) |
The fiscal year is taken from the input file or the screen. |
Period Number (PD_NO) |
The period is taken from the input file or the screen. |
Subperiod Number (SUB_PD_NO) |
|
Cost Element Type Code (S_COST_ELEMENT_CD) |
This field is populated based on the account/organization from the timesheet line. If the timesheet line's account/organization matches the Manufacturing Order's work-in-process labor account and organization, this field is populated with L. If the timesheet line's account/organization matches the Manufacturing Order's work-in-process miscellaneous 1 account and organization, this field is populated with 1. Otherwise, this field is populated with 2. |
Operation Activity Type (S_ACTIVITY_TYPE) |
The program populates this field with the operation activity type from the input file. |
Sales Order (SO) Table (TS_LN_SO)
The Sales Order Line table is filled as follows:
Field | Description |
---|---|
Timesheet Date (TS_DT) | |
Employee ID (EMPL_ID | |
Timesheet Header Sequence Number (TS_HDR_SEQ_NO) |
These fields are set the same as in the Timesheet header. |
Timesheet Line Number (TS_LN_NO) |
The timesheet line number is set to the sequence number of the input line in the input file. |
Sales Order (SO_ID) |
The program populates this field with the sales order ID from the input file. |
Sales Order Release Number (SO_RLSE_NO) |
The program populates this field with the sales order release number from the input file. |
Charge Hours (CHG_HRS) |
The charge hours are set by the application as follows:
|
Labor Cost Amount (LAB_CST_AMT) |
|
Fiscal Year (FY_CD) |
The fiscal year is taken from the input file or the screen. |
Period Number (PD_NO) |
The period is taken from the input file or the screen. |
Subperiod Number (SUB_PD_NO) |
|
Technical Details
Tables Read
- Account (ACCT) (Manage Accounts)
- Account Entry Rules (ACCT_ENTRY_RULES) (Manage Account Entry Groups)
- Accounting Period (ACCTING_PD) (Manage Accounting Periods)
- Billable Labor Category (BILL_LAB_CAT) (Link Project Labor Category Rates to Projects)
- Employee (EMPL) (Manage Employee Information)
- Employee Project-Acct-Grp TS Defaults (EMPL_ACCT_GRP_DFLT) (Employee Project/Account Group Timesheet Defaults)
- Fiscal Year (FY) (Manage Fiscal Years)
- General Labor Category (GENL_LAB_CAT) (Manage General Labor Categories)
- Labor-Grp Project-Acct-Grp TS Defaults (LAB_ACCT_GRP_DFLT) (Manage Labor-Group Proj-Acct-Group Timesheet Defaults)
- Labor Location (LAB_LOCATION) (Manage Labor Locations/Locals)
- Labor Settings (LAB_SETTINGS) (Configure Labor Settings)
- Leave Type (LV_TYPE) (Manage Leave Types)
- Organization (ORG) (Manage Organization Elements)
- Organization Account (ORG_ACCT) (Link Accounts/Organizations)
- Over-Time Rules by State (OT_RULES_BY_STATE) (Manage Overtime Rules by State)
- Overtime Settings (OT_SETTINGS) (Configure Labor Settings - Overtime Settings subtask
- Pay Type (PAY_TYPE) (Manage Pay Types)
- Pay Type Restriction (PAY_TYPE_RESTRICT) (Manage Pay Type Restrictions)
- Project (PROJ) (Manage Project User Flow)
- Project Control (PROJ_CNTL) (Configure Project Settings)
- Project Employee Labor Category (PROJ_EMPL_LAB_CAT) (Manage Employee Work Force)
- Project Labor Category (PROJ_LAB_CAT) (Link Project Labor Categories to Projects)
- Project Labor Category Map (PROJ_LAB_CAT_MAP) (Link General Labor Category to Project Labor Category)
- Project Organization Account (PROJ_ORG_ACCT) (Link Projects/Accounts/Organizations)
- Project TS Defaults (PROJ_TS_DFLT) (Manage Project Timesheet Defaults)
- Reference Structure (REF_STRUC) (Manage Reference Structures)
- Salary Information and History (EMPL_LAB_INFO) (Manage Employee Salary Information)
- Sub Period (SUB_PD) (Manage Subperiods)
- Timesheet Header History (TS_HDR_HS) (View Timesheet History Inquiry)
- Timesheet Period Schedule (TS_PD_SCH) (Manage Timesheet Periods)
- Timesheet Regular Default Lines (DFLT_REG_TS) (Manage Employee Information)
- Wage Determination (WAGE_DETERM) (Manage Wage Determination Rates)
- Workers' Comp (WORK_COMP) (Manage Workers' Compensation Codes)
- Input File
- (User named)
Tables Read and Written
- Function Parameter Catalog
- (FUNC_PARMS_CATLG)
- Manufacturing Order Timesheet Line (TS_LN_MO) (Manage Timesheets)
- Posting Semaphore (POST_SEMAPHORE)
- Sales Order Timesheet Line (TS_LN_SO) (Manage Timesheets)
- Sequence Generator (SEQ_GENERATOR)
- Timesheet Header (TS_HDR) (Manage Timesheets)
- Timesheet Line (TS_LN) (Manage Timesheets)
- Timesheet Prep Parameters (T_PARMS_AOPUTLTS)
- Menu: Others - Product Interfaces - Preprocessors - Import Timesheets
Other Output
- Error File (input file name + .ERR)
- Edit Report
- Error Report