US Payroll - Importing Timesheets From Oracle

Pgm: PYTIMIMP – Timesheet Import; standard Treeview path: US Payroll > Imports > Import Timesheets from Oracle

This screen allows the import of timesheets, both from and to Oracle tables. It allows users to import timesheets with the option of importing expenses, based on the Expense Type and Expense QTY/Amount fields.

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

Import ASCII File to Oracle Table

The [Import ASCII File to Oracle Table] button allows users to directly import an ASCII file to the Oracle Timesheet table PYEMPTIMSHT_IMP. This button is enabled if there is no information entered on this screen. It will open an Import File pop-up window for importing the ASCII file.

In the pop-up, select the [Choose File] button to browse for the file to import.

Select the [Import] button to import the data.

The number of lines read and imported will be displayed. Once the import is complete and the Import File pop-up is closed, the Oracle Timesheet Import procedure can begin. Rate calculation considers the shift code when determining the rate. The program will consider and include the fields for Expense Type and Expense Qty/Amt.

The Import Timesheets from Oracle program validates the expense code defined in payroll setup, based on the expense type and Qty/Amount fields. The following conditions and rules are taken into consideration for Expense Code, Expense Type, Quantity and Amount.

If the Expense Code is set up as type “Actual” on the expense master setup, the QTY/AMT value is considered as a flat amount.

If the Expense Code is set up other than the “Actual” type on the expense master setup, the import program will consider the value as quantity and multiply the value with the rate defined in the expense master screen.

In a scenario where the expense is charged elsewhere, other than the timesheet, the user should enter a separate line for the expense to post on a different Job, Phase or Category.

In a scenario where the expense and timesheet hours are provided in the same record, the program will create an hours and expense with the Job/Phase/Cat Code or Overhead Department and Account.

The import program is to insert the records into the regular timesheet table. The data will not be inserted into the Expense Claim Entry program.

NOTE: This button will only consider an ASCII file’s WBS values if the job information is available on the same line. If not, then this import routine ignores all WBS data.

The ASCII file being imported must be comma delimited and match the order below.

NOTE: Users should convert and save the txt file in CSV format and import the ASCII file in CSV format. Users can also create a CSV file with a header row entering the columns as under, when importing the first row which is the header will not be imported into the table. If, the CVS is created without a header row, then users should leave the first row for the csv file blank. Therefore, prior to converting the text (txt) file into CSV, enter a blank row top of the page and then convert and save to CSV format. Once converted to CVS user should not open the CVS file, as it will change the cell formatting for numbers, which would cause errors and rejected records.

ASCII File Layout – Import Timesheet from Oracle

  Column Name Data Type Comments
1 IMP_TSH_EMP_NO VARCHAR2 (16)  
2 IMP_TSH_DATE DATE DD-MON-YYYY
3 IMP_TSH_JOB_CODE VARCHAR2 (10)  
4 IMP_TSH_PHASE VARCHAR2 (16)  
5 IMP_TSH_CATEGORY VARCHAR2 (4)  
6 IMP_TSH_NORMAL_HOURS NUMBER (16,8)  
7 IMP_TSH_OT_HOURS NUMBER (16,8)  
8 IMP_TSH_DOT_HOURS NUMBER (16,8)  
9 IMP_TSH_OH_HOURS NUMBER (16,8)  
10 IMP_TSH_OH_TYPE VARCHAR2 (4)  
11 IMP_TSH_WORK_COMP_CODE VARCHAR2 (2)  
12 IMP_TSH_DEPT_CODE VARCHAR2 (6) Optional- based on Control File Flag
13 IMP_TSH_GL_ACC_CODE VARCHAR2 (8) Optional – based on Control File Flag
14 IMP_TSH_WORK_LOC VARCHAR2 (4)  
15 IMP_TSH_EQP_HOURS NUMBER (16,8)  
16 IMP_TSH_DOCUMENT_NO VARCHAR2 (30) Optional
17 IMP_TSH_TRADE_CODE VARCHAR2 (4) Optional
18 IMP_TSH_UNION_CODE VARCHAR2 (4) Optional
19 IMP_TSH_OH_RATE_CODE VARCHAR2 (4) Optional
20 IMP_TSH_CKLOC_CODE VARCHAR2 (16) Optional
21 IMP_TSH_SHIFT_CODE VARCHAR2 (1) Optional
22 IMP_TSH_NH_RATE NUMBER (16,8) Not Required
23 IMP_TSH_OT_RATE NUMBER (16,8) Not Required
24 IMP_TSH_DOT_RATE NUMBER (16,8) Not Required
25 IMP_TSH_OH_RATE NUMBER (16,8) Not Required
26 IMP_TSH_SECTOR_CODE    
27 IMP_TSH_SCHEDULE_CODE    
28 IMP_TSH_EXP_CODE VARCHAR2(4)  
29 IMP_TSH_EXP_QUANTITY NUMBER (16,8)  
30 IMP_TSH_EXP_RATE    
31 IMP_TSH_EXP_AMOUNT    
32 IMP_TSH_WBSV_CODE1    
33 IMP_TSH_WBSV_CODE2    
34 IMP_TSH_WBSV_CODE3    
35 IMP_TSH_WBSV_CODE4    
36 IMP_TSH_WBSV_PCI_LINE_ORASEQ    
37 IMP_TSH_OVERRIDE_REF_CODE    
38 IMP_TSH_OVERRIDE_REF_DESC    
39 IMP_TSH_PRIOR_PERIOD_FLAG    
40 IMP_TSH_ACTUAL_WORK_DATE   DD-MON-YYYY
41 IMP_TSH_ACS_CODE    
42 IMP_TSH_COMMENTS    
43 IMP_TSH_CUST_EMP_NO    
44 IMP_TSH_NH_CHARGE_RATE    
45 IMP_TSH_NH_BILL_RATE    
46 IMP_TSH_DOT_CHARGE_RATE    
47 IMP_TSH_DOT_BILL_RATE    
48 IMP_TSH_OT_CHARGE_RATE    
49 IMP_TSH_OT_BILL_RATE    
50 IMP_TSH_OH_CHARGE_RATE    
51 IMP_TSH_OH_BILL_RATE    
52 IMP_TSH_PAYMENT_MONTH NUMBER  
53 IMP_TSH_LEAVE_ADV_FLAG VARCHAR2(1)  
54 IMP_TSH_PPR_SUB_PERIOD NUMBER  
55

IMP_TSH_EXP_HOUR_TYPE

   

Import Oracle Table

After the ASCII file has been imported and the Company, Pay Run, Import into Pay Year, and Import into Pay Period fields have been filled, the [Import Oracle Table] button becomes enabled.

This button allows users to import data directly from the Oracle Timesheet table PYEMPTIMSHT_IMP. The import utility considers the fields for Expense Code and Expense QTY/Amount. The Import Timesheets from Oracle program validates the expense code defined in payroll setup, based on the expense type and Qty/Amount fields.

This screen is used when not entering timesheets directly into Enterprise. It is used to import unposted timesheet data into Enterprise’s Payroll module via a .CSV spreadsheet file. For details about the fields and format of the import file, refer to Import File Fields – CTL File Template.

The import process requires 3 steps, if there were no issues with the entries in the import file.

Step 1

The first step is to import the import file’s data into a temporary table in the system via the [Import ASCII File to Oracle Table] button.

Step 2

Once the ASCII file is imported to the Oracle table, then the user can enter the mandatory Company Code, Pay Run, Year and Period into which timesheet data will be imported. The Period LOV will display the current period and the next 3 open periods. The Default Category field assigns that category if the category imported is null. Enter the Document Code which will be assigned to the unposted timesheets created if the document code is null on the imported timesheet.

Then, the mandatory Company, Pay Run, Import into Pay Year, and Import into Pay Period fields are used to specify the company, pay run, pay year and pay period for the timesheets being imported.

The Default Category and Default Document No fields are optional.

Step 3

Then, the temporary table’s data is imported into Payroll as unposted Timesheets using the [Import Oracle Table] button.

If there were any issues with some entries in the import file during the second import, a message will be reported stating how many were rejected.

The rejected entries can be corrected via the [View Rejected Entries] button’s pop-up, and once corrected, the import process can be retried for these entries via the pop-up’s [Import] button.

Selection Parameters

Pgm: PYTIMIMP- Time Sheet(s) Import Utility

Company

Enter/select company code. A company code must be entered.

Pay Run

Enter/select pay run into which the timesheet data should be imported.

Import into Pay Year

Enter/select the pay year into which the timesheet should be imported.

Import into Pay Period

Enter/select the pay period into which the timesheet data should be imported.

Default Category

Enter/select the default category to be used with job and phase, if category is not imported with timesheets.

Default Document No

Enter/select the document code which will be assigned to the unposted timesheets created, if the document code is null on the imported timesheet.

[Import Oracle Table] - Button

[View Rejected Entries] - Button

The [View Rejected Entries] button opens a screen with rejected entries where users can edit the data until all record are valid. When the records are rectified and valid, press the [Import] button on the Rejected Timesheet Entries screen to import the corrected entry record and insert into the timesheet.

[Delete Import Oracle Table] - Button

This button deletes imported timesheet data from the PYEMPTIMSHT_IMP import table.

[Delete Rejected Entries] - Button

If there are any rejected entries left, they can be deleted with this button.

NOTE: The current payroll timesheet import program locks the entire table once modification to rejected entries has begun. Therefore no other users can modify data until the first changes are committed.
This is addressed with a new import utility that allows multiple users access to the utility at the same time. This program is designed to read and insert timesheet data into the Payroll Timesheet table and allows clients to view and edit their timesheets at the same time. For more information, refer to US Payroll - Importing Timesheet From Oracle (Multi User).

Import File Fields – CTL File Template

The following table provides details about the fields in the import file:

Import File Field Description Required Type Length
Employee Number This must be a valid employee for the company. Yes Varchar2 16
Reference Date

Actual date of Timesheet.

NOTE: Date worked MUST be of the DD-MMM-YYYY format, otherwise the hours will go to the final day in the pay period.

Yes DATE  
Job The timesheet job code. Yes, if timesheet is for a job Varchar2 10
Phase Code The timesheet phase code (cost code). Yes, if job code is entered Varchar2 16
Category The timesheet job category. Yes, if job is entered Varchar2 16
Normal Hours The number of normal hours (regular time) worked. See NOTE 1 Number 16,8
Overtime Hours Number of overtime hours worked – leave blank if not applicable. See NOTE 1 Number 16,8
Double Overtime Hours Number of double overtime hours worked –leave blank if not applicable. See NOTE 1 Number 16,8
Other Hours Number of other hours worked – leave blank if not applicable. See NOTE 1 Number 16,8
Other Hours Type The type of other hours. This must be a valid Other Hour Code in CMiC. Yes, if Other Hours are entered. Varchar2 4
Company Company handling the job. Yes Varchar2 8
Department This is the department code the time is charged to if the timesheet is a G type. Leave this field blank to auto-populate it with the payroll department from Employee Profile. See NOTE 2 Varchar2 6
GL Account Code The GL account to charge time to if not a job timesheet. Leave this field blank to auto-populate it with the wage account from the Employee Profile.

See NOTE 2

Varchar2 16
Work Location A valid work location. No Varchar2 15
Equipment Hours Number of hours equipment was used on the job. No Number 16,8
Document Number/ Code Timesheet document grouping. No Varchar2 30
Trade Code

Enter the employee trade where the employee worked this week or leave it null to populate from the Employee Profile.

No Varchar2 4
Union Enter the union code where the employee worked for this period or leave it null to populate from the Employee Profile if employee is unionized. No Varchar2 4
Other Hours Rate Code Other hours rate code

No

If blank, import

uses rate code from Employee Profile.

Varchar2 4
Check Location Code Check location code No Varchar2 16
Shift Code Shift code No Varchar2 1
Normal Hours Rate Normal hours rate No Number 16,8
Over Time Rate Overtime rate No Number 16,8
Double Time Rate Double overtime rate No Number 16,8
Other Hour Rate Other hour rate No Number 16,8
Expense Code Expense code No Varchar2 4
Expense QTY/AMT

Based on the expense type: If the Expense Code is set up as "‘Actual" on the Expense Master screen, the QTY/AMT value is considered as a flat amount and should be entered in the import file.

If the expense code is set up as something other than "Actual" type on the Expense Master screen, then the import program considers the value as a quantity and multiplies the value with the rate defined in the Expense Master screen.

Yes, if expense code is entered Number 16,8
Access Code Timesheet access codes Yes, if timesheet is being imported into E-Timesheet Varchar2 16
Comment Timesheet comments No Varchar2 2000
Customer Employee Number This column allows customer data mapping. Through customer data mapping, users can map external employee numbers, shift codes, and hour type codes to CMIC employee numbers, shift codes, and hour type codes. These mappings are then used during timesheet import if the import file contains external employee numbers, shift codes, and hour type codes. No, if not entered replace column with comma for successful import. Varchar2  

NOTE 1: There must be hours on the record in Normal, OT, DT, or OH.

NOTE 2: Either Job, Phase, Cat or GL Dept and Account must be entered.