Expense Extract

The Expense Extract profile extracts data from the TESite database into a file or to an external database table. The configuration of the Expense Extract profile consists of several activities: defining the TESite table or file layout being extracted using the Export Table Setup module, configuring the TEAIM.ini file, the population of the Expense 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 Expense 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 Expense 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 Expense 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 Expense 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 Expense Extract attributes.

Section	Key	Description	Value
ARGUMENTS	POST_DATE	Expense Report Post Date. Date format: MM/DD/YYYY
	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]
BATCH	BATCH_NAME	The name of the Batch running
	BATCH_FORMAT	The format of the batch ID generated by TEAIM. Default value is: MMDDYYHHMMSS.	MMDDYYYYHHMMSS
	BATCH_PREFIX	The prefix value of the batch number generated by TEAIM.	XP - for expense report extraction. This field can be left empty.
	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.
	INVOICE_OPTION	V- Creates Invoice number grouped by Vendor. I - Creates invoice number unique by report. R - Creates invoice equal to the report ID.	Default: "V"
	EXTRACT_MODE	How the report is to be extracted.	F - to a file only T - to a table D - Direct extractB - both file and table
	PROFILE_NAME	The process name
	ENABLE_DOWNLOAD	Allow a file to be downloaded or ftp	Y - Allow N - Do Not Allow (Default)
	BATCH_ID	The value of the batch ID to be re-extracted
	EMPLY_ID	Employee ID
	REPORT_ID	Report ID
	RUN_TYPE	New Extraction or Re-extraction	N - New Extraction R - Re-extraction
QRY_SECTION	GET_LOAD_TABLES	Lists the extract target table name.	If extracting to Lawson: In('ACDBTRANS').
	XP_QRY_LIST	Retrieval query which retrieves expense reports for a given date and batch type. NOTE: Do not change the SELECT statements.	MSSQL SQL Query: select REPORT_ID from XP_REPORT_HDR where REPORT_ID not in (select REPORT_ID from XP_POST_DTL where BATCH_TYPE=@pst_status) Oracle SQL Query: SELECT REPORT_ID FROM XP_REPORT_HDR WHERE REPORT_ID NOT IN (SELECT REPORT_ID FROM XP_POST_DTL WHERE POST_STATUS =:post_status)
ADV_AMOUNT	DIS_ACCOUNT	Discount account
	DIS_ACCT_UNIT	Discount account unit

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 Expense 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 P_JAGTEAIM_AFTER_EXTRACT respectively. Figure 5 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.