US Payroll - Importing Timesheets From ASCII File

Pgm: PYIMPTIM – Unposted Timesheet Import; standard Treeview path: US Payroll > Imports > Import Timesheets from ASCII file

This screen is used to import timesheets, rather than entering them manually through the Payroll module. When using this screen, a new batch number is created for the imported timesheets and these transactions remain unposted and available for editing in the Edit Timesheet program.

Pressing the [Import Timesheet] button brings up a pop-up which allows the uploading of TXT files. Any transactions that do not match this format are rejected. When the file has been uploaded, press [Import]. After a timesheet has been imported there is option to validate, edit, or delete any imported data.

The template for importing timesheets is provided below, as well as the steps to be followed to complete the import process.

Import Timesheet

To import an ASCII file, press the [Import Timesheet] button to launch the Import File pop-up window where the [Browse] button is used to select the ASCII file that is to be imported.

Once the file is selected, press the [Import] button to load the import file into a temporary table.

Once imported, a message will be displayed to indicate the number of lines that were imported.

Validate Imported Data

The [Validate Imported Data] button is used to validate the imported data and print a report of all records, valid records, or invalid records.

Press the [Validate] button to validate the data.

Next use the [Report] button to print the report.

A sample Timesheet Import Report is shown in the screenshot above.

Edit Imported Data

Clicking the [Edit Imported Data] button opens a screen where imported data can be edited and re-validated until all the records are valid.

The [Show Error] button displays all the errors on that record in a pop-up screen.

The screenshot above shows an example of the Error pop-up window launched from the [Show Error] button.

Use the [Re-Validate] button to validate imported data until all the records are valid.

When a record is valid, the 'Valid' checkbox will be checked on the record's row. User Extensions may also be defined for a record. Once all the records are valid, click the [Close] button to close the validation screen.

Build Timesheet

Once all records are valid, the [Build Timesheet] button becomes enabled.

Timesheets can now be created from the valid records.

Delete Imported Data

The [Delete Imported Data] button will remove all the records from the temporary table of imported data.

ASCII File Layout – Import Timesheets from ASCII

The import file for timesheets can be of the format .TXT, or .CSV, and includes the following fields in this order:

  Column Name Required Data Type Comments
1 TSH_COMP_CODE Y VARCHAR2(8)  
2 TSH_PRN_CODE Y VARCHAR2(4)  
3 TSH_PPR_YEAR Y NUMBER  
4 TSH_PPR_PERIOD Y NUMBER  
5 TSH_EMP_NO Y VARCHAR2(16)  
6 TSH_SEQ_NO Y NUMBER  
7 TSH_DOCUMENT_NO N VARCHAR2(16)  
8 TSH_DATE DATE Y DATE DD-MON-YYYY
9 TSH_WORK_COMP_CODE Y VARCHAR2(8)  
10 TSH_UNION_CODE N VARCHAR2(4)  
11 TSH_TRADE_CODE Y VARCHAR2(4)  
12 TSH_WORK_LOCATION Y VARCHAR2(4)  
13 TSH_DATA_TYPE Y VARCHAR2(1) Must be J or G
14 TSH_DEPT_CODE Y VARCHAR2(6)  
15 TSH_GL_ACC_CODE Y VARCHAR2(16)  
16 TSH_JOB_CODE N VARCHAR2(10) Job Code; Job Long Code required if J-line
17 TSH_PHASE N VARCHAR2(16) Required if J-line
18 TSH_CATEGORY N VARCHAR2(16) Required if J-line
19 TSH_EQUIPMENT N VARCHAR2(10)  
20 TSH_EQUIP_CATEGORY N VARCHAR2(8)  
21 TSH_EQUIP_TRAN_CODE N VARCHAR2(4)  
22 TSH_HRLY_EQUIP_RATE N NUMBER  
23 TSH_EQP_CR_ACC_CODE N VARCHAR2(16)  
24 TSH_EQP_DEPT_CODE N VARCHAR2(6)  
25 TSH_NORMAL_HOURS N NUMBER  
26 TSH_NH_RATE_CODE N VARCHAR2(4)  
27 TSH_NH_PAY_RATE N NUMBER  
28 TSH_NH_CHARGE_RATE N NUMBER  
29 TSH_NH_BILL_RATE N NUMBER  
30 TSH_NH_PAY_AMT N NUMBER  
31 TSH_NH_CHARGE_AMT N NUMBER  
32 TSH_NH_BILL_AMT N NUMBER  
33 TSH_OT_HOURS N NUMBER  
34 TSH_OT_RATE_CODE N VARCHAR2(4)  
35 TSH_OT_PAY_RATE N NUMBER  
36 TSH_OT_CHARGE_RATE N NUMBER  
37 TSH_OT_BILL_RATE N NUMBER  
38 TSH_OT_PAY_AMT N NUMBER  
39 TSH_OT_CHARGE_AMT N NUMBER  
40 TSH_OT_BILL_AMT N NUMBER  
41 TSH_DOT_HOURS N NUMBER  
42 TSH_DOT_RATE_CODE N VARCHAR2(4)  
43 TSH_DOT_PAY_RATE N NUMBER  
44 TSH_DOT_CHARGE_RATE N NUMBER  
45 TSH_DOT_BILL_RATE N NUMBER  
46 TSH_DOT_PAY_AMT N NUMBER  
47 TSH_DOT_CHARGE_AMT N NUMBER  
48 TSH_DOT_BILL_AMT N NUMBER  
49 TSH_OTHER_HOURS N NUMBER  
50 TSH_OH_TYPE N VARCHAR2(4)  
51 TSH_OH_RATE_CODE N VARCHAR2(4)  
52 TSH_OH_PAY_RATE N NUMBER  
53 TSH_OH_CHARGE_RATE N NUMBER  
54 TSH_OH_BILL_RATE N NUMBER  
55 TSH_OH_PAY_AMT N NUMBER  
56 TSH_OH_CHARGE_AMT N NUMBER  
57 TSH_OH_BILL_AMT N NUMBER  
58 TSH_EXP_CODE N VARCHAR2(4)  
59 TSH_EXP_QUANTITY N NUMBER  
60 TSH_EXP_RATE N NUMBER  
61 TSH_EXP_AMOUNT N NUMBER  
62 TSH_WCB_CODE N VARCHAR2(4)  
63 TSH_WCB_POLICY_NO N VARCHAR2(16)  
64 TSH_WCB_RATE N NUMBER  
65 TSH_WCB_AMT N NUMBER  
66 TSH_WCB_TOT_HOURS N NUMBER  
67 TSH_WCB_GROSS_AMT N NUMBER  
68 TSH_WCB_ALLOC_FLAG Y VARCHAR2(1) Must be Y or N
69 TSH_PL_CODE N VARCHAR2(4)  
70 TSH_PLC_POLICY_NO N VARCHAR2(16)  
71 TSH_PL_RATE N NUMBER  
72 TSH_PL_AMT N NUMBER  
73 TSH_PL_TOT_HOURS N NUMBER  
74 TSH_PL_GROSS_AMT N NUMBER  
75 TSH_PL_ALLOC_FLAG Y VARCHAR2(1) Must be Y or N
76 TSH_PROCESS_FLAG N VARCHAR2(1)  
77 TSH_BATCH_NUM N NUMBER  
78 TSH_USER Y VARCHAR2(30)  
79 TSH_LAST_UPD_DATE DATE Y DATE DD-MON-YYYY
80 TSH_CERT_SEQ N NUMBER  
81 TSH_USER_FIELD1 N VARCAHR2(2000)  
82 TSH_USER_FIELD2 N VARCAHR2(2000)  
83 TSH_USER_FIELD3 N VARCAHR2(2000)  
84 TSH_USER_FIELD4 N VARCAHR2(2000)  
85 TSH_USER_FIELD5 N VARCAHR2(2000)  
86 TSH_USER_FIELD6 N VARCAHR2(2000)  
87 TSH_USER_FIELD7 N VARCAHR2(2000)  
88 TSH_USER_FIELD8 N VARCAHR2(2000)  
89 TSH_USER_FIELD9 N VARCAHR2(2000)  
90 TSH_USER_FIELD10 N VARCAHR2(2000)  
91 TSH_JOB_LONG_CODE N VARCHAR2(24) Job Long Code/Job Code required if J-line
92 TSH_EQP_HOURS N NUMBER  
93 TSH_EQUIP_PHS_CODE N VARCHAR2(16)  
94 TSH_WBSV_CODE1      
95 TSH_WBSV_CODE2      
96 TSH_WBSV_CODE3      
97 TSH_WBSV_CODE4      
98 TSH_SHIFT_CODE      
99 TSH_EXP_HOUR_TYPE      
100 TSH_SECTOR_CODE      
101 TSH_SCHEDULE_CODE