Customers and Sales Transactions

To import sales transactions you need to identify two import files; one for the transaction records (STRAN) and another for the analysis records (SANAL). The table shows the database files that are updated when customers and sales transactions are imported, along with the mandatory fields that must be part of the import file.

The Importer works the Open Period Accounting (OPA) feature to update the Nominal Ledger with transactions supplied in a DBF or CSV file. For more information, see the Importer & Open Period Accounting Help topic.

Type of File

Databases Updated

Mandatory Fields

Sales Customers

If an import record includes BIC and IBAN numbers (used for Single Euro Payments Area (SEPA) payments), the Importer will validate these to ensure that the codes conform to the valid ISO pattern (refer to the ISO 13616 IBAN standard and the ISO 9362 BIC standard).

The codes are optional but if one is included in the import file, the other must also be included.

If the validation fails these messages are displayed:

'31700265 Invalid BIC (SN_BIC)'
'31700266 Invalid IBAN (SN_IBAN)'

SNAME

SN_ACCOUNT

SN_NAME

Sales Transactions

1. If the STRAN table includes a VAT value greater than 0.00, then SA_VATVAL should be included in the SANAL table.

2. ST_CBTYPE and ST_ENTRY must exist in the import file if the Cashbook application is activated (although they do not have to contain data).

3. Select the Update Nominal option on the Import Wizard to update the Nominal Ledger at the same time as updating the Sales Ledger.

4. The application checks that the SA_ANCODE field exists in the Sales Codes table to determine whether to update SANAL. This is only available if your activation includes the Nominal Ledger and the Real Time Update of Nominal option is selected in the System - Maintenance - Company Profiles command.

5. ST_TRVALUE must include VAT.

Importing transactions with prompt payment discount

If one level of prompt payment discount is to be recorded, the ST_VATSET1 and SA_VATSET1 fields must be included in the import files. If two levels of discount are to be recorded the ST_VATSET2 and SA_VATSET2 fields must also be included.

STRAN

ST_ACCOUNT

ST_TRDATE

ST_TRREF

ST_TRTYPE

ST_TRVALUE

ST_VATVAL

ST_CBTYPE

ST_ENTRY

1. SA_TRVALUE must not include VAT.

2. If SA_TAXDATE is not supplied, the Importer will populate it with today's date (the computer date - not the Opera system date).

SANAL

SA_ACCOUNT

SA_TRDATE

SA_TRREF

SA_TRTYPE

SA_TRVALUE

SA_ANCODE

SA_VATCTRY

SA_VATTYPE

SA_ANVAT

Sales Ledger Receipts

A feature of the sales ledger transactions import is the ability to import receipts and update any bank account in the same currency as the transaction. The Multiple Bank Account option must be selected in the Sales Ledger - Utilities - Set Options command and on the customer's profile. This facility is only available when importing from a .dbf or from a .csv file with column headings.

To do this you need to include the following additional fields in the import file:

Field Name	Field Type/Size	Description
ST_BANK_AC	Character (8)	Bank Account code
ST_BANK_CC	Character (4)	Bank Account cost centre

If these fields are not supplied in the import file, or the fields are blank, the default bank account is used.

Sales Ledger Matched Receipts

A special feature of the sales ledger transactions import type is the ability to automatically generate a receipt that is matched in full against an invoice, or a refund matched against a credit note. This facility is only available when importing from a .dbf file as it requires additional fields which are not present in the STRAN or SANAL files and thus are impossible to represent in a .csv file. To do this you need to include the following additional fields in the STRAN file:

Field Name	Field Type/Size	Description of Use
ST_FULLAMT	Logical	If .T. then invoice or credit note is fully paid and requires a receipt or refund transaction generating for it.
ST_FULLCB	Character (2)	Cashbook type to be used for the receipt or refund transaction generated for this invoice or credit note.
ST_FULLNAR	Character (10)	Narrative to be used for the receipt or refund transaction generated for this invoice or credit note.
ST_CASH	Logical	Can be used to indicate whether the receipt or refund transaction generated for this invoice or credit is ‘cash’.

If the Cashbook application is not present, ST_FULLCB can be left blank, otherwise it must contain the cashbook type to be used for the receipt or refund.

If the ST_FULLAMT field is set to ‘.T.’, then, when the record is imported, a matching receipt or refund record is created and allocated against the invoice or credit note. This might be useful for applications such as point of sale, where the invoice and payment are raised simultaneously for cash transactions.