Timesheet Extract

The Timesheet Extract profile extracts data from the TESite tables into a file or to another external database. The configuration of the Timesheet Extract profile consists of several activities: defining the TESite table or file layout being extracted using the Export Table Setup module, configuring the TEAIMSetup utility, the population of the Timesheet Extract profile attributes, and the creation and compilation of the TEAIM extract event. Each of these activities will be outlined below with some of the activity descriptions being referenced to other sections of the guide.

Export Table Setup
The purpose of the Export Table Setup is two-fold:

To give TEAIM the file layout that the extracted must adhere to, or
To define the table structure of the table being extract to. This allows TEAIM to perform the necessary data type and data length validations on the data being exported.

For more detailed setup information on the Export Table Setup module, please refer to the section called Setup Export Table Definition.

The setup process for data extraction to a file requires some additional information. The process for extracting information to a file is basically a two-step process (usually done by the Administrator). The first step is to create a table in the TESite database that fits the data element requirements that it will store. Then build that same table structure in TEAIM using the Export Table Setup module. This provides a data dictionary of the table and this is what TEAIM uses to determine the file layout specifications. Each column type and length in the Export Table Setup is then transformed into the field type and length in the data file. TEAIM only supports several types of file layouts: Fixed-Width, Tab-Delimited, CSV, HTML, and Microsoft Excel. The second step is to extract the data to that configured table and TEAIM will then build the data file according to the Export Table Setup specification.

TEAIM Setup
The second step in the setup process is the configuration of the directory structure for the TEAIM Timesheet Extract profile. This is necessary for the TEAIM engine to know the location of where the data files should be sent upon successful completion. In addition, the directory structure for the TEAIM log files is also configured in the TEAIMSetup utility. Please refer to the section called TEAIM DBMS and File Directory Setup for further reference.

Profile Creation
The following steps outline the process for creating a Timesheet Extract profile type. Note: On every entry screen in TEAIM, an entry field with an asterisk "*" indicates a required field.

Under Profiles in the Main Menu, select the Profile link.
The Profile screen will be displayed.
To create a new profile, select the New button.
Enter in an appropriate name for the new profile in the Name field.
In the Profile Type field, select the type Timesheet Extract.
If the Stop on Error option is set to yes, TEAIM will stop importing data when an error occurs. Otherwise, TEAIM will skip the error and continue processing.
Click the Save button to save the new profile.
The new profile can be seen by selecting the Profile link.

Profile Attribute Setup
A profile of type Timesheet Extract requires a certain amount of profile attributes to be populated depending on the type of extract being performed. The table below outlines and defines the different Timesheet Extract attributes. A few comments should be made regarding the different types of timesheet extract configurations that can be performed. TEAIM can support the extraction of timesheets by a range of specified period end dates or on a daily basis. If extracting by period end dates then the POST_BASED_ON attribute should be set to 'P' and the START_DATE and END_DATE attributes refer to the range of pay period end dates. If extracting on a daily basis, then the POST_BASED_ON should be set to 'D' and the START_DATE and END_DATE attributes refer to a range of actual calendar days.

TEAIM supports two different ways to report total number of hours: daily hour totals or total hours per line. Depending on the payroll system, the EXTRACT_MODE attribute should be set to 'S' if you want the total number of hours per line to be extracted, or 'D' if you want the total per line per individual day to be extracted.

TEAIM supports two types of extract types - Final and Daily extracts. Extracts where the RUN_TYPE attribute is set to 'F' is a final extract. This tells the TEAIM engine to not only extract the specified data, but to post the data as well. If the RUN_TYPE attribute is set to 'D', then TEAIM will extract a daily extract where the timesheet data is extracted, but not posted. The main reason behind these two types are certain payroll systems like to have a daily feed of timesheet information for budgeting purposes prior to the end of the reporting period. This enables TEAIM supply payroll systems with information to keep them up to date in the middle of a payroll period without affecting the individual timesheets.

Section	Key	Description	Value
ARGUMENTS	START_DATE	The start date of the reporting period to be extracted. Date format: MM/DD/YYYY
	END_DATE	The end date of the reporting period to be extracted. Date Format: MM/DD/YYYY
	POSTING_LEVEL	WBS (Activity) posting level. Here are the combinations of the posting levels.	1 - Level 1 only. 12 - Level 1 concatenated with level2. 123 - level 1 concatenated with level 2 and Level 3. 1234 - level 1 concatenated with level 2, level 3, and level 4. 234 - level 2 concatenated with level 3 and level 4. 34 - level 3 concatenated with level 4. 99 - lowest WBS posting level minus one level. 100 - lowest WBS posting level
	TRIM_COMPANY	Trim value for Company Code, which is Operating Unit. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_EMPLOYEE	Trim value for Employee ID. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_LEVEL1	Trim value for Level1_code. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_LEVEL2	Trim value for Level2_code. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_LEVEL3	Trim value for Level3_code. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_LEVEL4	Trim value for Level4_code. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_LEVELN	Trim value for Level_N_code. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_LOCATION	Trim value for Location Code.This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_PAY_CODE	Trim value for Pay Code. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	TRIM_VENDOR	Trim value for Vendor. This is an integer value that tells how many characters need to be trimmed from the leftmost character. 0 means no trimming.
	ACTIVITY_LEVEL_OLD	Lawson: Old activity level to be replaced.
	ACTIVITY_LEVEL_NEW	Lawson: New activity level value.
	ACCNT_CAT_LEVEL_OLD	Lawson: Old account category level to be replaced.
	ACCNT_CAT_LEVEL_NEW	Lawson: New account category value.
	ACCOUNT_CATEGORY	Lawson: The value of the default account category.
	RESOURCE_CODE	Value used if an alternated employee mapping is needed. The value in the brackets corresponds to the employee ID replacing the value of the EMPLOYEE.EMPLY_ID provided in the extract.	[EMPLY_ID] , [SSN], or [EMAIL2]
	EMPLOYEE_COST_RATE	The cost rate calculation for the employee being extracted.	Y - Calculate the cost rate. N - Do not calculate the cost rate.
	EVENT_DATE_FROM	If the POST_BASED_ON is set to 'D for daily, then EVENT_DATE_FROM corresponds to the start of the date range to be extracted.
	EVENT_DATE_TO	If the POST_BASED_ON is set to 'D for daily, then EVENT_DATE_FROM corresponds to the end of the date range to be extracted.
BATCH	BATCH_FORMAT	The format of the batch ID generated by TEAIM. Default value is: MMDDYYHHMMSS.	MMDDYYYYHHMMSS
	BATCH_TYPE	The Batch Type labels the extracted transactions according to the system that prompted the extraction. If more batch types are needed, the Administrator can manually add more types in the TE_BATCH_TYPE table.	LA - Lawson PR - Payroll
EMAIL	EMAIL_ON	An Option to send an Email at the conclusion of an extract.	C: Sends an email when the process is completed. F: Sends an email when/if the process fails. B: Sends an email if process succeeds or fails. Default: Empty for not to send email.
	EMAIL_TO	The email address of the recipient.
EXTRACT	EXTRACT_MODE	Determines the output format of the extraction: to a file or to another RDBMS table.	F -to a file only. T - to a table through TEAIM to a remote client database. B - Extract to both a table and a corresponding file. M - Extract Multiple Related Tables to one file. D - Direct extraction to other database using DBLink. In this option, the SP_TM_EXTRACT_EVENT takes care of the target format.
	FILE_NAME	Determines the filename of the output file.	B: File name is the batch number. J: File name will be the TEAIM job number. H: File name will be the interface header name. JH: File name will be the job number concatenated with the interface header name. BH: File name will be the batch number and interface header name.
	FILE_TYPE	The file type of the timesheet file.	CSV (Without Heading) CSV_H (With Heading) TEXT-TAB (Without Heading) TEXT-TAB_H (With Heading) HTMLTable (Without Heading) HTMLTable_H (With Heading) Excel (Without Heading) Excel_H (With Heading) XML
OPTIONS	CLOSE_REPORTS	Close timesheet after extract.	Y - Close timesheets after extract. N - Do not close timesheets after extraction.
	EXTRACT_TYPE	The timesheet extraction type.	D - Detailed extraction information per event date per project. S- Summary extraction
	POST_BASED_ON	The range of timesheets to extract.	P- Reporting Period Range. D-Event Date Range.
	RUN_TYPE	The daily or final extraction of timesheets.	F - Final run mean the timesheet is posted to the system and will not run again unless there is an adjustment. D - Trial Post - the extraction of timesheets on a daily basis without posting the timesheets in TESite system. C - Re-run extraction - Extract a previous batch of timesheets.
	DATE_FORMAT	The date format to be used in the profile attribute values.	MM/DD/YYYY
	BATCH_ID	The value of the batch ID to be re-extracted.
QRY_SECTION	TM_QRY_LIST	Lists the extract target table name.	If extracting to Lawson: In('ACDBTRANS').
	GET_LOAD_TABLES	A retrieval query which retrieves the timesheets for a given period and batch type. NOTE: Do not change any of the SELECT statements.	MSSQL SQL Query: SELECT DISTINCT th.emply_id, CAST(th.hdr_wk_end as varchar(11)) FROM TM_DTL_COL as tdc, TM_HDR as th WHERE tdc.emply_id = th.emply_id AND tdc.hdr_wk_end = th.hdr_wk_end AND tdc.hdr_wk_end >= @start_dt AND tdc.hdr_wk_end <= @end_dt AND th.hdr_status in ("R") AND NOT EXISTS (SELECT "X" FROM tm_post_dtl as tpd WHERE tdc.hdr_wk_end = tpd.hdr_wk_end AND tdc.emply_id = tpd.emply_id AND tdc.t_line_no = tpd.t_line_no AND tdc.t_sub_column = tpd.t_sub_column AND tdc.event_date = tpd.event_date AND tpd.post_status =@pst_status AND tpd.run_type = "F") ORACLE SQL Query: SELECT DISTINCT th.emply_id, TO_CHAR(th.hdr_wk_end,"mm/dd/yyyy") FROM TM_DTL_COL tdc, TM_HDR th WHERE tdc.emply_id = th.emply_id AND tdc.hdr_wk_end = th.hdr_wk_end AND tdc.hdr_wk_end >= :v_start AND dc.hdr_wk_end <= :v_end AND th.hdr_status in ("R") AND NOT EXISTS (SELECT "X" FROM tm_post_dtl tpd WHERE tdc.hdr_wk_end = tpd.hdr_wk_end AND tdc.emply_id = tpd.emply_id AND tdc.t_line_no = tpd.t_line_no AND tdc.t_sub_column = tpd.t_sub_column AND tdc.event_date = tpd.event_date AND tpd.post_status = :post_status AND pd.run_type = "F")

The profile information is stored in the TESite database in two tables: DB_PROFILE_HDR and DB_PROFILE_DTL. The DB_PROFILE_HDR table stores general information about the profile. The DB_PROFILE_DTL table stores profile attribute information. In each of these two tables, the Profile ID is the unique key identifier.

TEAIM Timesheet Extract Custom Events
The execution of a profile in TEAIM is a multi-step process. Each step in the process is initiated and monitored by the TEAIM engine. There are basically three steps in the profile execution process. The first and the last steps in the process are TEAIM Before and After "events" that are basically areas for client customization that may need to occur either prior or post the execution of the profile. The events are called SP_JAGTEAIM_BEFORE_EXTRACT and SP_JAGTEAIM_AFTER_EXTRACT respectively. Figure 4 illustrates the process path for the execution of a profile:
The TEAIM events are stored procedures that are capable of executing client-customized SQL statements either before or after each profile execution. The table that follows outlines the stored procedure definition:

Event Names: SP_JAGTEAIM_BEFORE_EXTRACT, SP_JAGTEAIM_AFTER_EXTRACT

PARAMETER	DATATYPE	DESCRIPTION
AS_BATCH_NO	VARCHAR(50)	Unique identifier for the complete batch.
AS_TABLE_NAME	VARCHAR(50)	Where applicable, the table name in either the local or remote database where the data will be extracted.
AS_PROFILE_ID	VARCHAR(20)	The profile ID of the process. This will allow the procedure to acquire profile information from the DB_PROFILE_DTL table.
AS_JOB_NUMBER	INTEGER	The system generated job number of the specific load process. This will be used to log any errors that occur into the TE_JOB_PROCESS_LOG table.
AD_SERVER_DATE	DATE	System date of the application server.

These pre-defined functions as well as any new functions that are created specifically for a client must follow the standards for return values that are inherent in the TEAIM application. For all standard and customized functions, if an error occurs, then a function must return a value of negative one (-1). This will ensure the proper handling of error messages and "trapped" database messages. If no error occurs and the function logic is successful, then a return value of zero (0), will allow TEAIM to continue processing the profile.

TEAIM supports a complete customized extract interface with the use of the SP_TM_EXTRACT_EVENT function. A batch of timesheets will be extracted one at a time. After each timesheet extraction iteration (on a per timesheet basis), the main extract code calls the SP_TM_EXTRACT_EVENT function. This function will determine the format and content of the extracted data and where it is to be extracted since this is the only place to make client-based modifications. Any modifications to this function will require the Administrator to recompile.

Continue to Profile Execution.