Export open items to Sage treasury

This function is used to generate a file that represents the situation at a given moment of the customer and supplier payment schedules, to be sent to the cash management software.

This file, generated upon request, only concerns customer/supplier open items with the following characteristics.

Open items from validated sales, purchasing, and BP invoices excluding:

  • Entered or imported journals
  • Non-posted payments
  • Expense notes
  • Doubtful receipt entries
  • Pre-payments

Unclosed open items:

Temporary amounts do not reduce the exported balance, such as the entered payment. Only final payments that are deducted from an unclosed open item, such as a posted payment.

 The expected file is a file with a fixed length, without a recording separator.

Prerequisites

Refer to documentation on Implementation

Screen management

Entry screen

Header

Group (field GRPCPY)

Enter the company group code to which the company with the open items must belong for the open items to be exported.
This is indeed a group of companies, legal companies or group of legal companies, and not a a site or a group of sites.

field TREG

Default status code (Sage Concept Cash Management module) or Transaction type (Sage 1000 Cash Management) derived from the CASTREGDUD parameter and automatically reused in the generated open item file (see the Structure of the schedule file documentation).
On the Cash Management module side, this status/transaction type code represents a grouping criterion/filter for a set of operations (inquiry, deletion/purge, ...).
If it concerns the Sage Concept Cash Management module, this code is necessarily comprised of a maximum of 2 (uppercase) alphabetical characters.
If it concerns Sage 1000 Cash Management, this code is necessarily comprised of a maximum of 2 alphanumerical characters.

 This code is not used for Sage FRP treasury.

File generation

field TYPEXP

No help linked to this field.

File (field VOLFIL)

Block number 3

Log file (field TRC)

Used to view or not the generation trace of the open item export file.

The trace mentions the code of the export company scope, the path and the generated file and it further specifies the number of open item lines contained in the written file.

Payment schedule file structure

The generated file has a fixed length, without field separator.

No. 

Field code
Sage Concept Cash Management 

Field name
Sage Concept Cash Management

Comments vs Sage X3

Examples

Position 

Length

1/

2/

3/

BANCA

SOCIETA

CONTO

Bank code

Company code

Account code

No bank is mentioned on a Sage X3 open item. Automatic creation of the triplet FIC + company code of the open item + ISO code of the open item currency

FIC

APN..

EUR

1/

4/

9/

3/

5/

3/

4/

CAUSALE

Support code

Concatenation of the sens of the open item and the payment mode
([F:DUD]SNS=1 equals Receipt) and
([F:DUD]SNS=-1 equals Expense)

 DCHQ

12/ 

4/

5/

IMPORTO

Amount in account currency (1)

Contains the open item balance in the transaction currency.
Balance composed of the amounts in transaction currency reduced by the final amounts paid (temporary amounts not taken into account).
Amounts in 13.2.

1234567890123.12/

16/

16/ 

6/ 

DIVISA

Operation currency

ISO code of the open item original currency

JPY

32/

3/

7/

DOPE

Operation date

Use of the open item date in the format DDMMYYYY

18112007/

35/

8/

8/

TREG

Status code

Use of the status code mentioned in the export window

P

43/

2/

9/

10/

11/

12/

13/

CPTA

CDC

DESCR

CODRIF

NASS

Nature code

Annex code

Comment

Reference code

Check number

The content of these fields is defined by setup from the function Treasury file.
Pay attention to the nature and annex codes: in the cash management software, these 2 fields point at value tables.

411000..........

FAC-1107.........

Additional invoice DEL001 s/appe

ASN00018

........

45/

61/

77/

109/

117/

16/

16/

32/

8/

8/ 

Number of characters/recording

=

125/

 

(1) - The amount transmitted to the Cash Management module must be positive. In case of a negative invoice, the balance is sent as a positive amount and the sense contained in the support code is reversed.

The contents of the fields are aligned on the left, except for the amounts, that are aligned on the right.

On the Cash Management Module side, thelog file delivered to work with the export of the Sage X3 open items is the file OPH3.INI.

Payment schedule file structure

The generated file has a fixed length, without field separator.

No. 

Field name
Sage 1000 Cash Management

Comments vs Sage X3

Examples

Position 

Length

1/

2/

3/

Bank code

Company code

Account code

No bank is mentioned on a Sage X3 open item. Automatic creation of the triplet FIC + company code of the open item + ISO code of the open item currency

FIC

APN..

EUR

1/

4/

9/

3/

5/

3/

4/

Nature code

Concatenation of the sens of the open item and the payment mode
([F:DUD]SNS=1 equals Receipt) and
([F:DUD]SNS=-1 equals Expense)

 DCHQ

12/ 

4/

5/

Amount in account currency (1)

Contains the open item balance in the transaction currency.
Balance composed of the amounts in transaction currency reduced by the final amounts paid (temporary amounts not taken into account).
Amounts in 13.2.

1234567890123.12/

16/

16/ 

6/ 

Operation currency

ISO code of the open item original currency

JPY

32/

3/

7/

Operation date

Use of the open item date in the format DDMMYYYY

18112007/

35/

8/

8/

Movement type

Use of the transaction type mentioned in the export window

P4

43.

2/

9/

10/

Transfer (2)

Comment

The content of these fields is defined by setup from the function Treasury file.

VII............

Invoice complement DEL001.....

45/

173/

128/

128/

Number of characters/recording

=

301/

 

(1) - The amount transmitted to the Sage 1000 Cash Management module must be positive. In case of a negative invoice, the balance is sent as a positive amount and the sense contained in the support code is reversed.

(2) - the Transfer zone is used to know whether or not it is an international transfer and the content of this field is of Yes/No type. The values sent are free provided that they are only 2 distinct ones (Sage 1000 Cash Management then uses a transcoding table)

The contents of the fields are aligned on the left, except for the amounts, that are aligned on the right.

No.

SFRPT field name

Comments

Examples

Position

Length

1/

Bank code

No bank is mentioned on the open item.
Automatic creation with FIC+company code+ISO code of the transaction currency.
That is 11 characters.

FIC

1/

3/

2/

Company code

Identical to the above.

APN

4/

5/

3/

Currency code

Identical to the above.

EUR

9/

3/

4/

Management

Sense (SNS of DUD).

D

12/

1/

5/

Flow code

Payment mode (PAM of DUD).

VIR

13/

3/

6/

Amount in operation currency (1)

Contains the open item balance without taking temporary payment amounts (TMP) into account.
Balance in transaction currency.
Decimal 13.2 (rounding off to two decimals)

1234567890123.12/

16/

16/

7/

Operation currency

[F:DUD]CUR (but ISO code).
Three alphanumerical characters.

EUR

32/

3/

8/

Operation date

[F:DUD]DUDDAT at the format DDMMYYYY

07092009/

35/

8/

9/

Description

Content defined by setup.

Invoice F01-009 DELL001...

43/

32/

10/

Reference

Content defined by setup.

FVIRN0909ASN0001

75/

16/

(1) - The amount transmitted to the Sage FRP Treasury module must be positive. In case of a negative invoice, the balance is sent as a positive amount and the sense linked to the operation is reversed.

Batch task

This function can be run in batch mode using the standard EXPCASDUD task code.

Workflow rules

The execution of the open items export to the cash management software can come along with the sending of an information e-mail if the CASHDUD workflow rule has been activated (event code CSD).

This message stipulates the extraction scope, the generation path and the name of the created file, as well as the code of the user who has launched the function.
The generation log file is included as an attachment.

Specific Buttons

Memo Select this action to save the current settings to reuse later using a unique memo code. The memo code is linked to your user profile, not to the function or the screen.
Recall Enter or select the unique memo code you created to apply the settings saved with that code.
Delete memo This action deletes a saved memo code and cannot be undone.