E-Gate Services Technical Overview

Scheduler & e-Gate Services Component Only

Imported transaction data is stored in a simple "flat file" that is either of ASCII or DBF format. The file may be formatted as ASCII SDF, dBase III or ASCII delimited. The delimit character may be user defined. ASCII tab delimited or dBase III is recommended.

General Operation

Within CAPITAL an upload directory location is specified. The external application places its files in this directory. It is recommended that these files conform to the 8.3 file format and must have the extension .ETR. (Short for Electronic TRansaction.)

CAPITAL will scan the upload directory for new transaction files and import the information from these files into the e-Gate database.

The uploaded data may then be either deleted automatically or copied into a transfer directory where it may be archived by the system administrator.

During the import process, CAPITAL will flag all ETR transactions that contain invalid or unknown information. A report may be executed at a later time to list these transactions or invalid transactions may be viewed on-screen. This task should be performed periodically to ensure that acceptable data is being supplied by the client application generating the .ETR files.

If an electronic transaction contains an invalid record, a CAPITAL transaction is not generated until confirmation to proceed is received by an operator. This may require the operator to correct the transaction or supply any missing information, before the transaction can be processed. This task can be performed from within the CAPITAL application.

File Structure

The data file conforms to the standard structure of an ASCII or DBF file, which is that of a flat file divided into a series of records. Each record may have up to 3 sections:

• Transaction

• Payment

• Product Sale Item

The section is optional if it is not required.

If a sales order consists of 5 items then a minimum of 5 records must be included in the .ETR file. The transaction and payment sections may be repeated or defined once and then left blank for subsequent records relating to the transaction.

Sections are defined by mapping fields from different areas of the data file to the database input fields within CAPITAL. Mapping options are discussed next.

File Specification

The exact arrangement of information held in an electronic transaction is not critical. CAPITAL's external file import utility will create an import profile that will allow data to be mapped to the appropriate fields from within CAPITAL.

The following information may be imported. Fields marked with a * (asterisk) are mandatory:

Manadtory OEEGate Card File Fields

These fields are also required to exist in your import card file and should not be removed:

Calculating Transaction Amount

The transaction amount field is calculated as follows:

Amount = ( Soldfor * Qty for each item ) + Freight Charges (Freight, Charge1, Charge2, Charge3)

Consider the following example. This is for sales order 1000 which is made up of 2 records. Each record represents one sale item relating to the total sales order.

Note that the FREIGHT field (and this also applies to the charge fields), and the AMOUNT field, are duplicated in each record. The $83.25 amount is calculated as follows:

From record #1, 20 x 2 = $40 + 10 freight = $50.00

From record #2, 6.65 x 5 = $33.25

Total from record #1 + total from record #2 = $83.25

Observe that the freight (and charge) amounts are only taken up into the transaction total once, even though they are included in each record.

Calculating Transaction Tax

If the sale item SOLDFOR amounts are inclusive of tax, tax calculations do not need to be included in the transaction file. CAPITAL will automatically calculate the tax inclusive component, based on the tax rates of the stock items in the CAPITAL inventory file. However, if some sales are tax exempt (or "tax free") then SOLDFOR amounts should also be stored in the file as ex tax amounts.

If a transaction is tax exempt/tax free, then a word or number must be placed in the OTAXNO field to indicate this. For example, placing the text message "Tax Free" in this field would be sufficient. This would prevent CAPITAL from assuming that the transaction is tax inclusive.

If the sale item SOLDFOR values are exclusive of tax, but the transaction itself is NOT tax exempt/tax free, then the AMOUNT field must include the tax component. The tax component should be placed in the TAXCHARGE field for each sale item. For example:

In order to calculate Amount, the formula becomes:

(Soldfor * Qty) + Taxcharge = add to Amount

or in this case:

$100 * 2 = $200 + $20 = $220.00

An Important Note on Rounding:

All TAXCHARGE values must be rounded to 2 decimal points, otherwise rounding discrepancies will arise. (Rounding tax by line item ensures that users receive consistent reporting results whether they analyse sales by product groups or transaction ranges.)

Other Notes

Fields that are left blank are assigned default values. For example, if the delivery address is not assigned, CAPITAL uses the default delivery address of the account. If you wish to force a field-which may have a default value--blank, write the characters #*# into the field.

One record should be created for each sale item ordered. Certain values need only be specified once. For example, the shipping method or order number only needs to be included on the first record sent. It is also acceptable to include this information on any record sent. Whatever is received last is the value that is used provided that it is not blank.

Transaction numbers must normally be unique. If, for example, a sales order already exists in the system and clashes with an electronic transaction number, the electronic transaction will be rejected and marked for review. This behaviour can be changed, however, to permit an existing transaction to be overwritten by a newly imported transaction, subject to the setting preferences for e-Gate. However, this may only occur while the transaction has not yet been processed. If the sales order stored in CAPITAL has its status changed to 'delivered' or 'partial' delivery, e-Gate will not permit an imported sales order to replace the fully or partly processed sales order. An error alert will be raised for the transaction instead.

User defined fields may be added to the e-Gate database so comment fields, special instruction fields, additional contact information, etc., may easily be added.

Update Procedures

Users may import transaction data either by selecting "import" from the e-Gate user interface or transactions may be updated periodically via a scheduler that periodically executes a batch file that performs the transfer and imports tasks in batch mode.

This process is handled by command line switch settings when starting the CAPITAL INSTALLATION Workshop program. It is also possible to import customer records, supplier records and stock items in batch mode using the INSTWIN/IMPORT= filename settings of CAPITAL.

The /IMPORT= switch points to a CAPITAL DIM file (Data IMport script) that describes where to find the data to import, the type of data to import, how to import it, and which fields to update and/or which CAPITAL Business Script sub-programs to execute. Data that already exists in the system is updated/merged. If the data does not exist it is appended to the database as new records or accounts.

The purpose of the DIM file is to map how data is to be translated from the external system into CAPITAL. This allows for the file structure of either the external application or CAPITAL to change without bothering the other linked application. Only the format of the DIM file needs to be adjusted. This can be performed through a visual interface by an operator who does not need to have programming skills.

Transfer Process

Imported data is first deposited in a card file, and then this card file data is transferred to a transaction. Before data is transferred to a transaction, a script hook is called, if in use, to allow for further formatting and manipulation of the data.

Hook files must reside in the CAPITAL application or root data directory.

____________________________

Related Topics:

E-Gate Preferences

E-Gate Order Import Hook

E-Gate Services

Import External File

Trouble-Shooting The Import Process