Exporting data from Expense Management - Download_data.bat / Download_data.sh

Functionality

Download_Data.bat is used to export data files from the Expense Management database to the customer site. The file structure is the same as defined in the standard import / export mechanism documentation for the appropriate release. Download_data is used to download a data file from a single table or to download multiple data files from multiple tables. The process of downloading multiple data files is called module download, where each module refers to a set of tables. A single call to run a module results in downloading all of the data files corresponding to the tables in the selected module.

The naming convention of these downloaded data files is <tableName>_timestamp.txt. Here the tableName is the name of the table from where the data is downloaded. The log files that are generated use this naming convention, Download_<tableName>.log. In the case of module download, the log file is named as Download_<ModuleName>.log. The default location of the downloaded data file is the DownloadedData folder. This folder is created automatically in the parent directory from where the download.bat is run; which is the bin folder. Similarly, the default location for the downloaded log files is the Log Folder. This folder is created in the parent directory from where the download.bat is run, which is the bin folder. In the downloadFolderName parameter, users can specify any other folder, where the downloaded data files and the log files must be saved.

By the default rule, the data files are must be loaded to the staging tables. To enforce this rule, the alex_xmdbtool is used which is an XMDBTool specific table in the Expense Management database. This table is explained in detail in the section alex_xmdbtool specification. The tables from where the data can be downloaded, must be specified in the alex_xmdbtool table. By default, all the staging tables are specified, so that the download_data.bat file can upload data files to all the staging tables. However, in case of any special requirements, where the user has to download the data from any specific transactional table, Customer Support can help out in registering the table name in alex_xmdbtool table.

Users can filter the data so that they retrieve only the required data by using SQL queries during the data download. This can be done by setting up SQL queries in the alex_xmdbtool table for the tables from where the data must be filtered. In the xmdbtool specific table alex_xmdbtool, you can specify pre_query, inline_query, and post_query for each table reference.

From version 10.2.12, a new post_query_sftp column is added in the alex_xmdbtool table. The post_query_sftp runs when the SFTP transfer fails. The query resets the export header status back to 1 when the SFTP transfer fails.

So when both the post_query and post_query_sftp are present and defined, the post_query runs when the SFTP transfer is successful and the post_query_sftp runs when the SFTP transfer fails.

The post_query_sftp is not considered and is ignored (even if present) when a download module, is initiated from the XMDBTool client .

Example -

post_query_sftp can be set to -> update aler_expense_export_header set export_hdr_status =1,export_hdr_status_datetime = CURRENT_TIMESTAMP where export_hdr_status = 99.

Defining and using modules

Module is an effective method to download multiple data files with a single XMDBTool client request to the server. A set of tables are grouped together and are assigned a module name. You can specify the name of the module with the downloadModule parameter in the configuration properties file and send a request to the server. As a result of the module request, all of the data files from the tables specified in the module are downloaded to the client machine.

By default, four sets of modules are pre-configured in the alex_xmdbtool table. These are the ER, TP, TS, and PR modules. Each of these modules has the set of tables that enable downloading of all the export ready documents with a single request from the client machine.

For example, if the client specifies downloadModule = ER, all the export ready ER documents, which may be scattered across multiple tables, are downloaded as a set of data files to the client.

Multiple modules can be specified with the downloadModule parameter. The naming convention for multiple module download is, downloadModule = <ModuleName1>:<ModuleName2>:<ModuleName3>…..<ModuleName ‘n’ >

For example, downloadModule = ER: ER1: ER2. This setting allows you to download multiple modules where-in each module contains multiple data files.

Passing runtime values to SQL queries

Every table entry in the alex_xmdbtool table has PreQuery, InlineQuery, and PostQuery attributes. Using these attributes, you can assign a set of SQL statements to each table that run when these tables are processed.

• The PreQuery attribute runs the SQL statements and also updates statements before processing the data from this table.
• The InlineQuery attribute runs the SQL statements and generally selects the clauses for data processing.
• The PostQuery attribute runs the SQL statements and is updated after the data from this table is processed.
• The post_query_sftp attribute runs the specified SQL, in case the SFTP transfer fails.

Each of these queries can use these hard-coded values or placeholder values:

• Select * from t1 where x =’20’: 20 is the hard coded value
• Select * from t1 where x =’~var~’: ~var~ is the placeholder value that is not hard coded for the SQL statement

Placeholder variables in the queries make the queries dynamic. Placeholder variables must be enclosed with ~ ~ .Users can provide the values that replace the placeholder variables from the client side during runtime.

So, even if a single table is downloaded or if a group of tables are downloaded, we can have run the queries on the table and filter the results based on the requirement.

Users can provide dynamic values for SQL queries; for either a module or individual table using the downloadModule or downloadTableName parameters in xmdbtool.properties configuration file. For example,

• downloadTableName = t1~var=20
• downloadModule = ER ~var=20, var1=30: ER1~var2=40 : ER2

In the above examples, the downloadTable requests that the t1 table be downloaded and it passes the variable var with the value 20. On the server, the SQL queries against this t1 table are then scanned and any placeholder named var is replaced with the value 20 on the client side. In case of multiple variables, each variable can be separated by comma (,).

Similarly the downloadModule is downloading 3 modules, ER, ER1, and ER2. ER and ER1 modules have their own set of variables. In this example, the ER module is passing the ‘var’ and ‘var1’ with a value of ‘20’ and ‘30’ respectively. In addition, the ER1 module is passing the ‘var2’ variable with a value of ‘40’. The ER2 module is downloaded without passing any variables to the modules.

The SQL queries for all the tables in the ER modules are scanned and all placeholders with the name of var and var1 are replaced with the appropriate values that are sent from the client side. As shown in the example, each ER can pass its own variables.