Exact Synergy

RPExcelImport.exe - Background process

Background Job: BLProcess.exe

Introduction

In e-Synergy there are three ways to import financial transaction. The first way is via XML: the financial transactions are entered in a XML-document which complies to the Exact XML-schema.

The second way is via replication from for example Globe 2003. There are two Globe background applications available for this, BacoReplicator.exe and ASImport.exe.

The third way is via Excel sheets and for that there is a background application available called RPExcelImport.exe and this background job is described in this document. Via Excel sheets only budget data or period end figures can be imported into e-Synergy. First a document needs to be created in the right category and with the right division linked.

This new document should have the Excel sheet attached from which the transactions should be imported. This Excel sheets needs to have a certain layout which is described in more detail in the section Settings. After creating the document you can either wait for the background job to start or start it manually from the database server.

After the job has run the results will be displayed in that same document. When the import has succeeded the document will be archived. Below you will see the results of a successful import.

The transactions that are created as a result of the import are located in the table TransactionsPending which means that the transactions are not yet processed. The transactions can therefore be found via [Financial > Entry > Entries to be processed]. In that screen the transactions can also be processed. Processing of the imported transactions can also be automated by running the BacoImporter.exe background application.

After the imported transactions are processed the import of those transactions is complete. For budget data processing is not necessary, those transactions are taken into account immediately. The budgets need to be authorized and processed before they are shown in any overview, so this should be checked first when looking for the results of the import.

Linked to this document there is an Excel sheet with the layout needed for uploading financial data. This sheet can be used for testing this functionality.

Technical Information

The following tables are involved when running RPExcelImport.exe:

BacoDiscussionsGroups (checks if the needed categories exist)
BacoDiscussions (checks and changes the document created)
bdgvrs (in case budget data is imported, the budget periods need to exist)
gbkmut (budget data is created in this table)
TransactionsPending (period end figures are created in this table)
BacoProcessLogs (log entries are created in this table)
CompanyLogs (a log entry is also created in this table)

For this background application there are no specific parameters. This means that only the mandatory parameters should be included in the command line when scheduling this task. The command will then look like this:

C:\Synergy\bin\RPExcelImport.exe /S:VORS31266-1 /D:DeltaBike

Settings e-Synergy

There are a number of settings in e-Synergy that RPExcelImport needs to function properly. The first settings needs to be set via [Financial > Maintenance > Organization > Divisions]. On the divisioncards of the divisions you want to use RPExcelImport for you need to set the backoffice settings to Manually like in the screenshot below.

Then certain document categories need to be created in which the import documents will be created. The background application checks for these categories so it's therefore not possible to use existing categories. The following categories need to be created:

Main category = Financials || Category = Excel Import - Actual Data || Subcategory = Period end figures
Main category = Financials || Category = Excel Import - Budget Data || Subcategory = <budget period code>

The second subcategory needs to have the same name as the budget period code where you are importing transactions from. This means that if you have created a budget period with code '2004' and you want to import budget data for this period the subcategory name should be '2004'.

When creating the necessary categories some settings need to be set up correctly. The following screenshot will show the settings necessary when creating the main category 'Financials'. For the categories and subcategories there are no specific, mandatory settings needed. Those settings are up to the user and the way this functionality is going to be used.

The Excel sheet linked to the created document should have a certain layout. If the layout is different than the layout the background job expects, the import will be rejected. This goes for removing certain fields as well as adding certain fields. In both cases the Excel sheet will be rejected. The following fields should be included in the Excel sheet:

Name in the header	Description	Obliged	Remark
Division	Division code	Yes	Must exist
Year	Financial year	Yes	Must exist
Period	Financial period	Yes	Must exist
G/L	Local G/L	Yes	Must exist. If not, the G/L is created.
G/L description	Description of G/L	Yes	Used if the G/L is created
G/L Side	Default entry side	Yes	Limited List: [D] = Debit [C] = Credit Used if the G/L is created
G/L Type	G/L Type	Yes	Limited List: [B] = Balance [P] = P&L Used if the G/L is created
Cost Center	Cost center code	Yes	If empty then replace by the default cost center code of division.
Cost Unit	Cost unit code	Yes	If empty then replace by the default cost unit code of division.
Debit	Debit amount in division currency	Yes	No
Credit	Credit amount in division currency	Yes	No
Balanced	Debit - Credit
Description	Description of the entry line	Yes	No

When all these settings are checked and/or entered the transactions can be imported from the Excel sheet. The created document will be archived when the import is successful. This means the document won't be visible when searching for it, but the links to the document remain. This way the search results for documents are clean of these kind of documents.

Troubleshooting

When the settings for the RPExcelImport application aren't correct the error messages are displayed in the document which you created. Those messages indicate what the problem is and how it should be solved. The most common messages are the following:

When this message is shown in the document it indicates that the division linked to the document and the division entered in the Excel sheet aren't the same. The solution is therefore to check both entries and make sure they are identical.

The message above is shown when the format of the Excel sheet is not compatible with the format the background application expects. These messages can be fixed by entering the requested text in the mentioned location. In this case cell 'K1' should contain the text 'Credit'.

RPExcelImport.exe - Background process

Introduction

Technical Information

Settings e-Synergy

Troubleshooting

Related Topics