This function is used to import matching transfers from the Cash Management software, and to transform them into accounting journals.
The structure of the transfer file being very different from that of the journal postings, the import is sub-divided into 2 successive and linked stages:

  • first, import of the file data into a dedicated table (BNKTRSIMP),
  • then application of the standard accounting posting to the data in the table, and generation of the corresponding journal postings
    via the automatic journal of the GAUCASH setup)

In case of problems arising during one of the two phases, the file is rejected as a whole.

Prerequisite

SEEREFERTTO Refer to documentation Implementation

Screen management

Entry screen

File generation

field TYPEXP

No help linked to this field.

File (field VOLFIL)
Log file (field TRC)

If this tick box is ticked, a process generates an import trace of the balancing transfer file.
The trace mentions the type of the generated journals and their number.

Structure of the imported file

The transfer file is comprised of a minimum of six or seven types of recordings:

  • Recording 0001 Header line
    -> header recording containing mainly the accounting date of the transfers
  • Recording 0002 - Date and support line
    -> recording containing header data from journals, such as the company, the document type, the value date and a description
  • Recording 0003 - Offset line
    -> recording containing the data of the offset lines (account 5800 or 4510), such as the account, the amount, the description, the sense, the BP, a line reference code in company currency
  • Recording 0004 - Bank and account line
    -> recording containing the data of the bank line (account 5120), such as the account, the amount, the description, the sense, the BP, a line reference code in company currency
  • Recording 0005 - Currency line
    -> recording containing the transfer amount in bookkeeping currency, the bookkeeping currency
    -> recording only presented if and only if the bookkeeping currency is different from the company currency.
  • Recording 0009 Summary line
    -> recording summarizing the journal, mainly containing the journal number
  • Recording 9999 Total line
    -> recording summarizing all the transfers contained in the file

Depending on the bookkeeping currency, it is possible to have the following structures:

 Bookkeeping currency <> Company currency

Bookkeeping currency = Company currency

0001 Header line

0001 Header line

0002 Date and support line

0002 Date and support line

0003 Offset line

0003 Offset line

0004 Bank and account line

0004 Bank and account line

0005 Currency line

0009 Summary line

0009 Summary line

0002 Date and support line

0002 Date and support line

0003 Offset line

0003 Offset line

0004 Bank and account line

0004 Bank and account line

0009 Summary line 

0005 Currency line

9999 Total line

0009 Summary line

 

9999 Total line 

 

These 2 files each generate two journal postings on the two bank accounts between which the transfer has been carried out.
It is possible to have a file displaying both types of structures according to the currencies concerned.

Setup specificities

The implementation of the matching transfer import requires that certain setup pre-requisites be taken into account at e-Cash level:

Recordings

e-Cash fields

Information expected from Sage X3

Recording 0002 - Date and support line

Company matching code

The contents of this field must match the Sage X3 company code.

Recording 0002 - Date and support line

Support matching code

The contents of this field must match the Sage X3 journal type to be used (Disbursement or Cash receipt depending on the support sign). 

Recording 0003 - Offset line

Nature matching code

The contents of this field must match the Sage X3 account number for the pending transfer (5800) or the group current account (4510).

Recording 0003 - Offset line 

 Annex code matching code

When the contra account is a control account, then this field must contain the code of the associated BP.

Recording 0004 - Bank and account line

Account matching code

The first 3 characters of this field are dedicated to the the information on the journal site. Then, from the 4th character, the bank account number must be entered.

Recording 0005 - Currency line

Currency matching code

This field is used to enter the code of the currency to be used as the journal currency.

Structure of the imported file

The transfer file is comprised of N recordings, N being an even number. In effect, the lines go by two, each group of two lines representing a transfer entry (for instance 512 to 580).

No. 

Field name
Sage 1000 Cash Management 

Comments vs
Sage X3

Position

Length

Type 

1

Journal code

SEEWARNING The journal code received is set on three positions whereas the Sage X3 journal code is set on five positions.

1

3

Alphanum.

2

Journal date

Matches the Sage X3 account date.

4

6

DDMMYY format date

3

Entry type

The Sage 1000 Cash Management journal type is linked to the [bank account+company] combination. So it is not dependent on the transaction sign, whereas, in most cases, Sage X3 has available a Receipt and an Expense journal type. The setup of the standard CASH automatic journal plans to retrieve this value (therefore, to be created) but this can be modified.

10

2

Alphanum.

4

GL account

Either the cash account (X3) or the internal transfer account or the current account.
SEEWARNING Beware of the account length: the Sage X3 account is limited to 10 positions (13 for Sage 1000 Cash Management).

12

13

Alphanum.

5

Account type

This field should not be taken into account.

25

1

 Alphanum.

6

Auxiliary account or dimension

This field should not be taken into account.

26

13

Alphanum.

7

Entry reference

The setup of the standard CASH automatic journal plans to retrieve this field as the original document.

39

13

 Alphanum.

8

Entry description

The setup of the standard CASH automatic journal plans to retrieve this information in the default description in the journal header but also in the description on the line.

52

25

Alphanum.

9

Payment mode

This field should not be taken into account.

77

1

Alphanum.

10

Due date

Corresponds to the due date mentioned in the journal header.

78

6

DDMMYY format date

11

Sign

The file to be integrated contains D for Debit and C for Credit. During import, these values will be respectively transformed into 1 and -1 to match the values of the Sense field of Sage X3.

84

1

Alphanum.

12

Line amount company currency

Corresponds to the forced company amount.

85

20

Numeric

13

Entry type

This field should not be taken into account.

105

1

Alphanum.

14

Document no.

The file contains N lines grouped by 2 because they have the same item number. Each group of 2 lines having the same document number will be turned into a Sage X3 document. The standard CASH automatic journal plans to retrieve this number and use it as the journal number, but this can be modifed. 

106

7

Alphanum.

15

Reserved field

This field should not be taken into account.

113

26

Alphanum.

16

ISO code company currency

This field should not be taken into account.

139

3

Alphanum.

17

Line amount transaction currency

This field should not be taken into account.

142

20

Numerical.

18

ISO code transaction currency

This field should not be taken into account.

162

3

Alphanum.

19

Cash company code

This field should not be taken into account.

165

15

Alphanum.

20

Branch

Corresponds to the site of the entry line.

180

15

 Alphanum.

21

Amount currency line bank account

The entry line is taken into account as the transaction currency amount.

195

20

Numeric

22

ISO code bank account currency

The entry is taken into account as the transaction currency.

215

3

Alphanum.

 

 

Number of characters/recording =

218

 

 
Structure of the Sage FRP Treasury file

No. 

Field name

 Comments

Examples

Position

Length

1

Account

Either the cash account (X3) or the internal transfer account or the current account.
SEEWARNING The length of the Sage X3 account is limited to 15 characters.

512100

1

100

2

Company

X3 company code contained in the import file.
The site is determined as follows:
=> if a site is set up at bank account level, the latter will be used,
=> If not, the default site at the level of the company will be used,
=> if not, the first site referenced for the company will be selected.

APN

101

8

3

Management

The file to be integrated contains D for 'Debit' and C for 'Credit'.
During import, these values will be respectively transformed into '1' and '-1' to match the values of the Sense field of Sage X3.

E

109

1

4

Date

Matches the Sage X3 account date.

07092009

110

8

5

Amount in company currency

Matches the company amount.

1234567890123.45

118

20

6

Company currency

Currency of the main social ledger.
ISO Code.

EUR

138

3

7

Amount in operation currency

Amount expressed in the transaction currency.

1234567890123.45

141

20

8

Transaction currency

ISO code of the transaction currency.

EUR

161

3

9

Reference

Loading of field REF in Sage X3.

Operation no. 3669

164

10

10

Description (comments)

Label of the entry line.

Balancing transfer from BNP tp SG

174

30

11

G/L Type

This field specifies the document type, for instance 'ENC' ou 'DEC'.

DEC

204

10

12

Journal code

The journal code is deduced from the bank account. This read is made possible thanks to the CASH automatic document.

BNP

214

10

13

Cash Entry Site No

This field should not be taken into account.

Empty

224

3

14

Cash Entry Log No

This field should not be taken into account.

Empty

227

5

15

Cash Entry Internal No

Can be used to determine the document number

3669

232

10

16

G/L Entry No

This field should not be taken into account.

Empty

242

4

Batch task

This function can be run in batch mode. The standard task BNKTRSIMP is provided for that purpose.

Error messages

In addition to the generic error messages, the following messages can appear during the entry :

"Recording code does not exist XXXXX"

This message appears when the first 4 characters at the beginning of a line do not match those that are usually expected (0001/ 0002/0003/0004/0005/0009/9999).

"No issuer recording"

This message appears when the 0001 header recording cannot be found in the file to be imported.

"Structure of lines incorrect"

This message appears for the other structure problems (a missing 0002 recording, two 0003 recordings before a 0004 recording,...)

"Currency does not exist"

This message appears if the currency code of the 0005 recording is not correct.

"Non-existent company"

This message appears if the company code of the 0002 recording is not correct.

"Amounts equal to zero"

This information message appears if a transfer amount is null (and thus a journal amount). Then, depending on the value of the SIVNULL setup (Common Data chapter), the journal is or is not generated.

"File not imported"

This message appears when one of the previously mentioned errors arises (incorrect line structure, non-existent recording code,...)

"Journal not generated"

This message appears when the generation of the entry, via the automatic journal of the GAUCASH setup, has failed

The posted documents will have the 'Final' status for the company

If the processing is launched for a given company and this company has selected Conformity DGI no. 13L-1-06 (i.e. the value of setup FRADGI/chapter LOC/group FRA is 'yes'), the documents generated by the processing will have the status 'Final'.

The posted documents will have the 'Final' status for one or more companies

The processing is launched for all companies or a group of companies, and if at least one of the companies chose Conformity DGI no. 13L-1-06 (i.e. the value of setup FRADGI/chapter LOC/group FRA is 'yes'), the documents generated by the processing will have the status 'Final'.

Tables used

SEEREFERTTO Refer to documentation Implementation