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.