0 Read me !

This page was initially referenced as accounting interface. The content of the data exported did not change but we prefer to promote /wiki/spaces/DOCEN/pages/41621187, which provides more flexibility.

1 Quick reference

Latest version	Examples
3.2	exemples de fichier à télécharger

This document describes the interface between the SecuTix 360º ticketing system and their customers' accounting systems. These systems are applications that are independent from the ticketing system.

This document provides examples of exported accounting files and explains the customers how to select the data to import into their accounting system.

2 Interface description

2.1 Solution perimeter

The accounting interface is a generation system of text files containing accounting data which are exported from SecuTix source data.

The following scheme illustrates the accounting interface principle:

The process stages are:

1. SecuTix generates exported files from ticketing system (orders, payments, etc...). The exports are scheduled for automatic exportation once a day. This is the solution perimeter.

2. An accounting import tool, external from SecuTix, downloads these files via HTTPS and updates the data to a format supported by the accounting system (if necessary). This tool is not provided by SecuTix and must be specific for the accounting system and for its current settings for the client. These items are not included in the SecuTix supplied perimeter.

3. The accounting import tool submits the reformatted data to the accounting system. These items are not included in the SecuTix supplied perimeter.

SecuTix exports five different file types:

· Sales file: This file contains the detailed list of all orders created, including the name of the products, for sales and refunds, regardless of the sales channel.

· Cash flow file: This file lists the payments related to sales and refunds, with an association with the corresponding order lines. The system exports the payments associated with cashdesks with the "accounting closed" state. In order for the sales and cash flow files match, the cashdesks must all have the accounting closed status.

· Invoices file: This file contains the detailed set of invoices. Each invoice has a record per every operation associated with it.

· Structures file: This file contains a list of structures (companies, communities, etc.) known to the ticketing system.

· Individuals file: This file contains a list of individuals known to the ticketing system.

2.2 Export principles

4 File access

4.1 Access from the application

The generated files can be downloaded manually from the result screen of a run (by using a filter criterion to quickly access it; for example: interface type = accounting) by clicking on the link provided in one of the first INFO lines.

Generated files are kept on SecuTix servers for at least 90 days.

4.2 Direct access from a URL address

In order to access the accounting files created by SecuTix, the accounting import tool has to retrieve the files generated daily from the addresses below.

From the PRODUCTION environment, the access addresses are as follows:

· Sales file:

o https://instit.pos.secutix.com/tnai/FileReader?file_name=Ventes(_VER)_YYYYMMDD(_S).txt

· Cash flow file:

o https://instit.pos.secutix.com/tnai/FileReader?file_name= Encaissements(_VER)_YYYYMMDD-(_S).txt

· Invoices file:

o https://instit.pos.secutix.com/tnai/FileReader?file_name=Factures(_VER)_YYYYMMDD(_S).txt

· Individuals file:

o https://instit.pos.secutix.com/tnai/FileReader?file_name=Individuels(_VER)_YYYYMMDD(_S).txt

· Structures file:

o https://instit.pos.secutix.com/tnai/FileReader?file_name=Structures(_VER)_YYYYMMDD(_S).txt

Where:

· instit is the institution code

· YYYY is the year

· MM is the month with 2 digits

· DD is the day of month with 2 digits

· The version (_VER) is optional (parenthesis must not be included in the URL). It indicates the format version of the exported file. V3_2 for the current status of the documentation.

· S is a sequence number. This number is incremented at each new export of a given file at a given date. The first file of the day gets no sequence number, only the next ones get one, starting from 1.

Examples (exports from 16/08/2013)

https://nihon.pos.secutix.com:80/tnai/FileReader?file_name=Individuels_20130816.txt

https://nihon.pos.secutix.com:80/tnai/FileReader?file_name=Structures_20130816.txt

https://nihon.pos.secutix.com:80/tnai/FileReader?file_name=Factures_20130816.txt

https://nihon.pos.secutix.com:80/tnai/FileReader?file_name=Ventes_20130816.txt

https://nihon.pos.secutix.com:80/tnai/FileReader?file_name=Encaissements_20130816.txt

These access URL addresses need a SecuTix operator (either real or virtual) user name / password; the operator must have READ access to the "Contexte organisme > Module Outils > Ecran Exécutions" (Organisation context > Tools module > Runs screen).

The user name to be entered is the concatenation of the institution code and the operator user name: INSTIT#USER_LOGIN.

4.3 Direct access to the files

Files may be delivered to a system not managed by SecuTix :

plain FTP: this is not recommended because transfer is not secured!
SFTP with login and password: configure url= sftp://hostname:22?proxyPort=NO&ftpDir=XX&otherParam=value, then Connection = yourLogin and password = yourPassword.
SFTP with certificate: configure url= sftp://hostname:22?proxyPort=NO&ftpDir=XX&otherParam=value, then Connection = PUBLIC_KEY_AUTH/yourLogin and password = the certificate in openSSH format (=starts by -----BEGIN DSA PRIVATE KEY----- ....)
AWS S3: configure url = s3://bucketName/region?ftpDir=XXX then Connection = AWSaccessKeyId and password = AWSsecretAccessKey
HTTP POST with basic auth: configure url = https://hostname/path?param1=value then Connection = yourLogin and password= yourPassword

5 Exported file contents

5.1 Sales file

This file contains the set of information about completed sales.

If the accounting service so wishes, it allows you to edit the global recipe of all products, including composite products like season tickets and packages.

This file contains a record per order line (operation).

In the case of composite products (packages and season tickets), there is a record for the composite product itself and a record for each item that it comprises.

Each record concerning an item in a composite product will mention the composite product it refers to.

5.1.1 Record selection

The exported order lines satisfy the following conditions:

§ They have not yet been sent after a previous submission of the sales file.

§ They refer to closed orders.

§ They refer to orders associated with the current organisation [1].

§ They refer to a product within a sale or refund (requested by the client or by the organisation)[2]

§ The order line does not represent a commission. The sales file mentions the commission amounts for each operation it benefits from. Therefore, the accounting system may recalculate the commission for a given contract and order.

For the composite products, the sales file provides the lines matching the composite product itself as well as the lines matching the content of the composite product.

5.1.2 Product validity period

The following table mentions the information about the validity period available for the different product families.

SecuTix handles 3 types of passes:

· Non dated passes are valid during a period of time. This period may correspond to the season in which they are defined, to a period freely defined in the product catalogue or to a number of days starting from the purchase date.

· Dated pass are usually valid for a specific date chosen at the time of purchase. In that case, the validity start date and end date will be the same. However, they may be valid for several consecutive days.

· Timeslot pass are valid during a timeslot chosen at the time of purchase. The timeslot is defined by a start date and time and a duration.

Field	Product Family	Storage in SecuTix
Validity start date	Single entry (event, visit, competition)	Start date of the performance associated with the operation-related item
	Dated or undated pass	Validity start date stored in any operation movement For undated passes where the validity duration corresponds to the season, the season start date is used.
	Membership	Idem (including validity for a season)
	Transport	Idem
	Timeslot pass	Date of the timeslot associated with the operation
	Other product families	No information
Validity start time	Single entry (event, visit, competition)	Start time of the performance associated with the operation-related item
	Timeslot pass	Time of the timeslot associated with the operation
	Other product families	No information
Validity end date	Dated or undated pass	Validity end date stored in any operation movement For undated passes where the validity duration corresponds to the season, the season end date is used.
	Membership	Idem (including validity for a season)
	Transport	Idem. Only available for return tickets.
	Other product families	No information

5.1.3 File structure

The generated file name is:

· Ventes_YYYYMMDD_S.txt

The file structure is as defined in the following tables:

· 1 line per operation

No.

Description

Field (DB)

1

Record type, a flag indicating if they are:
- operations ("OP")
- a sales price breakdown ("DPV"), not explained in this document (not used yet)
Ex.: OP; DPV

Operations:
"OP"

2

Operation number (technical identifier). This number is used as the link to the cash flow file.
Data Type: NUMBER(38,0)
Ex.: 20597799

OPERATION.OPERATION_ID

3

Operation type:
- Sale (SALE)
- Refund requested by the organisation (REFUND_ORGANIZATION)
- Refund requested by the public (REFUND_CLIENT)

In case of a refund, all amounts are negative
Data Type: VARCHAR2(20 BYTE)
Ex.: SALE; REFUND_ORGANIZATION; REFUND_CLIENT

OPERATION.TYPE AS OPERATION_TYPE

4

Order number
Data Type: NUMBER(38,0)
Ex.: 3381488

ORDER_TABLE.ORDER_ID

5

Date and time of the associated order
Data Type: TIMESTAMP(3)
Ex.: 06.04.05 14:28:54.107000000
Format: YYYYMMDDHHMM

ORDER_TABLE.CREATION_DATE AS ORDER_CREATION_DATE

6

Technical number of the point of sales
Data Type: NUMBER(38,0)
Ex.: 665

POINT_OF_SALES.POS _NUM

7

Technical number of the sales channel
Data Type: NUMBER(38,0)
Ex.: 2722040

SALES_CHANNEL.SALCHAN_ID

8

Reserved for accounting code of the sales channel. This field is currently set to NULL.

Data Type: VARCHAR2(100 CHAR)
Ex.: GENSAL

INTERFACE_MAPPING.value[
type=SALES_CHANNEL] AS SALCHAN_ACC_CODE

9

Operator last name[3]
Data Type: VARCHAR2(100 CHAR)
Ex.: Joset

Note: link through ORDER_TABLE.RESPONSIBLE_USER <-> USER_TABLE.LOGIN

USER_TABLE.LASTNAME AS OPERATOR_LASTNAME

10

Operator first name
Data Type: VARCHAR2(100 CHAR)
Ex.: Céline

USER_TABLE.FIRSTNAME AS OPERATOR_FIRSTNAME

11

Operator login name
Data Type: VARCHAR2(20 BYTE)
Ex.: CJOSET

USER_TABLE.LOGIN AS OPERATOR_LOGIN

12

Cash desk number (if available)[4]
Data Type: NUMBER(38,0)
Ex.: 36078; NULL

CASH_DESK.CASHDESK_ID

13

Order line kind:

· PRODUCT_COMPOSITION: order line within a package or a season ticket

· SINGLE_ENTRY: order line for a product (event, competition, visit, etc.), not included in the package or season ticket

· ORDER_OVERHEADS: order line for general charges (related to the order)

· PAYMENT_OVERHEADS: order line for payment charges

· DELIVERY_OVERHEADS: order line for delivery charges

· CHARGES: order line for simple charges (related to the tickets)

· COMPOSED_PRODUCT: order line for the package or the season ticket themselves

· SIMPLE_PRODUCT: order line for a service or pass not included in a package or season ticket

OPERATION.KIND AS OPERATION_KIND

14

Purchase contact number
Data Type: VARCHAR2(20 BYTE)
Ex.: 3738305

CONTACT.CONTACT_NUMBER

15

Reserved for the purchase contact accounting code. Contains currently the role of the contact. Possible values are: RELAY, PUBLIC
Data Type: VARCHAR2(100 CHAR)
Ex.: RELAY, PUBLIC

Foreseen:
INTERFACE_MAPPING.value[
type=CONTACT] AS CTCT_ACC_CODE

Current:

CONTACT.ROLE

16

Purchase contact last name (if an individual)
Data Type: VARCHAR2(100 CHAR)
Ex.: Clooney; NULL

INDIVIDUAL.LASTNAME AS IND_LASTNAME

17

Purchase contact first name (if an individual)
Data Type: VARCHAR2(100 CHAR)
Ex.: Joelle; NULL

INDIVIDUAL.FIRSTNAME AS IND_FIRSTNAME

18

Purchase contact official name (if it is a structure)
Data Type: VARCHAR2(100 CHAR)
Ex.: ELCA; NULL

STRUCTURE.OFFICIAL_NAME AS STRUCT_NAME

19

Technical number of the season. The season is only indicated if the record is related to a product or a simple charge.
Data Type: NUMBER(38,0)
Ex.: 2722093

Product or simple charge:

SEASON.SEASON_ID

20

Season name. The season is only indicated if the record is related to a product or a simple charge.
Data Type: VARCHAR2(60 CHAR)
Ex.: 2009-2010

Product or simple charge:

SEASON.INTERNAL_NAME_L1 AS SEASON_NAME

21

Code of the organisation responsible for the bought product
Data Type: VARCHAR2(8 CHAR)
Ex.: 141

ORGANIZATION.CODE AS RESP_ORGANIZ_CODE

Note: link per ITEM <-> PRODUCT <-> CATALOG <-> SEASON <-> ORGANIZATION

22

Internal name of the organisation responsible for the bought product
Data Type: VARCHAR2(60 CHAR)
Ex.: Grévin

ORGANIZATION.INTERNAL_NAME_L1 AS RESP_ORGANIZ_NAME

23

Record type, a flag indicating if they are:
- products ("P")
- charges ("F")[5]
Ex.: P; F

Product:
"P"

4 types of charges:

a: delivery charges (shipment fee): "DELIVERY_OVERHEADS"

b: order charges (general charges): "ORDER_OVERHEADS"

c: payment charges (general charges): "PAYMENT_OVERHEADS"
Link per ORDER <-> OPERATION <-> OVERHEADS

d: simple charges: "CHARGES"

Charges:
a: delivery charges (Link per ORDER <-> OPERATION <-> SHIPMENT_FEE)

b, c order or payment charges (Link per ORDER <-> OPERATION <-> OVERHEADS)

d: simple charges (Link per ORDER <-> OPERATION <-> CHARGES)

24

Product or charge code
Data Type: VARCHAR2(8 CHAR)
Ex.: WE6

Product:
PRODUCT.CODE AS PRODUCT_CODE
(Note: link per OPERATION > ARTICLE > PRODUIT)

Charges:
a: SHIPMENT_FEE.CODE AS PRODUCT_CODE

b, c: OVERHEADS.CODE AS PRODUCT_CODE

d: CHARGES.CODE AS PRODUCT_CODE

25

Product or charge internal name
Data Type: VARCHAR2(60 CHAR)
Ex.: Weekend W6

Product:
PRODUCT. INTERNAL_NAME_L1 AS PRODUCT_NAME

Charge:
a: SHIPMENT_FEE.INTERNAL_NAME_L1 AS PRODUCT_NAME

b, c: OVERHEADS.INTERNAL_NAME_L1 AS PRODUCT_NAME

d: CHARGES.INTERNAL_NAME_L1 AS PRODUCT_NAME

26

Accounting code for the product, the performance or the charge (if available)
Data Type: VARCHAR2(100 CHAR)
Ex.: TBD

Product:
INTERFACE_MAPPING.value[
type=PRODUCT|PERFORMANCE] AS PRODUCT_ACC_CODE

Charges:
INTERFACE_MAPPING.value[
type=CHARGES|*_OVERHEADS]

27

Product family code

Possible values:

INT_PRODFAM/SIMPLE : Event

INT_PRODFAM/SPORTING : Competition

INT_PRODFAM/ST : Season ticket

INT_PRODFAM/MEM : Membership

INT_PRODFAM/PKG : Package

INT_PRODFAM/SERVICE : Service

INT_PRODFAM/VOUCHER : Voucher

INT_PRODFAM/D_PASS : Dated pass

INT_PRODFAM/TS_PASS : Timeslot pass

INT_PRODFAM/ND_PASS : Non dated pass

INT_PRODFAM/TRANSPORT : Transport

INT_PRODFAM/GOODS : Goods

INT_PRODFAM/PRODLIB : ~~Free~~ Open product

Data Type: VARCHAR2(8 CHAR)
Ex.: WE6

Product:
PRODUCT_FAMILY.CODE AS PRODUCT_FAMILY_CODE

28

The data contained in this field depends on the context:

For a product, it is the internal name of the product family
Data Type: VARCHAR2(40 BYTE)
Ex.: INT_PRODFAM/SIMPLE

For simple charges, operation number that the charges are attached to
Data Type: NUMBER(38,0)
Ex.: 3580117

The field is blank for other charge types.

Product:
PRODUCT_FAMILY.INTERNAL_NAME_IC AS PRODUCT_FAMILY_NAME

Charges:
OPERATION.OPERATION_ID AS LINKED_OPERATION_ID

29

Audience category code
Data Type: VARCHAR2(8 CHAR)
Ex.: OccCont

Product:
AUDIENCE_CATEGORY.CODE AS AUDIENCE_CATEGORY_CODE

30

Internal name of the audience category
Data Type: VARCHAR2(40 BYTE)
Ex.: AUDCAT_TYPE/OccCont

Product:
AUDIENCE_CATEGORY.INTERNAL_NAME_IC AS AUDIENCE_CATEGORY_NAME

31

Audience subcategory code
Data Type: VARCHAR2(8 CHAR)
Ex.: Individ

Product:
AUDIENCE_SUB_CATEGORY.CODE AS AUDIENCE_SUB_CATEGORY_CODE

32

Internal name of the audience subcategory
Data Type: VARCHAR2(60 CHAR)
Ex.: Individuel

Product:
AUDIENCE_SUB_CATEGORY.INTERNAL_NAME_L1 AS AUDIENCE_SUB_CATEGORY_NAME

33

Product validity start:

· Date and time of the performance (if a performance is associated with the operation)

· Date and time of the timeslot (in case of a timeslot pass)

· Date of the dated pass

· Validity start date of an undated pass, a transport or a membership

· This is null for any other products

Data Type: TIMESTAMP(3)
Ex.: 15.07.09 19:30:00.000000000
Format: YYYYMMDDHHMM

Performance:

PERFORMANCE.START_TIME AS VALIDITY_START

Note: link per OPERATION <-> ITEM <->PERFORMANCE
with PRODUCT_FAMILY.INTERNAL_NAME_IC = ‘INT_PRODFAM/SIMPLE’ (single entry)

Pass, membership, transport:

MOVEMENT.BEGIN_DATE AS VALIDITY_START

Timeslot pass:

TIMESLOT.START_DATE + TIMESLOT.START_TIME AS VALIDITY_START

Note: link per OPERATION > TIMESLOT

34

Validity end of the product for the passes, memberships and transports.

This field may be null for certain transportation types (e.g. one-way).

It is null for any other products.

Ex.: 15.07.09 00:00:00.000000000

Pass, membership, transport:

MOVEMENT.END_DATE

Other product or charge:
NULL

35

Single entry:

Performance identifier ~~(if a performance is associated with the operation)~~
Data Type: NUMBER(38,0)

Single entry:

~~Simple item~~:
PERFORMANCE.PERFORMANCE_ID

Goods product:

Reference of the goods item
Data Type: VARCHAR2(20 CHAR)

Goods product:

GOODS_ITEM.REFERENCE

Service/voucher:

Code of the item
Data Type VARCHAR2(8 CHAR)

SERVICE_ITEM.code

36

Contingent code (if a contingent associated with the operation)
Data Type: VARCHAR2(8 CHAR)
Ex.: JeuneP

Product:
CONTINGENT.CODE AS CONTINGENT_CODE

37

Internal name of the contingent (if a contingent associated with the operation)
Data Type: VARCHAR2(60 CHAR)
Ex.: Jeune Public

Product:
CONTIGENT.INTERNAL_NAME_L1 AS CONTINGENT_NAME

38

Seat category number (if seat categories are associated with the operation)
Data Type: VARCHAR2(8 CHAR)
Ex.: 3

Product:
SEAT_CATEGORY.CAT_NUM

39

Activity code (if an event is associated with the operation)
Data Type: VARCHAR2(8 CHAR)
Ex.: SpecViv

Product:
ACTIVITY.CODE AS ACTIVITY_CODE

40

Internal name of the activity (if there is an event associated with the operation)
Data Type: VARCHAR2(60 CHAR)
Ex.: Live show

Product:
ACTIVITY. INTERNAL_NAME_L1 AS ACTIVITY_NAME

41

Code of the composite product if the current operation corresponds to a package or season ticket line.
Data Type: VARCHAR2(8 CHAR)
Ex.: ST1018

Product:
PRODUCT.CODE AS COMP_PRODUCT_CODE

Note:
1) link per OPERATION <-> ARTICLE <-> PRODUIT (components)
2) link per OPERATION <-> LIEN_OPERATION <-> OPERATION <-> ARTICLE <-> PRODUIT (package)
with OPERATION_LINK = ‘PRODUCT_COMPOSITION’

42

Internal name of the composite product if the current operation corresponds to a package or season ticket line.
Data Type: VARCHAR2(60 CHAR)
Ex.: Tradition LM2

Product:
PRODUCT.INTERNAL_NAME_L1 AS COMP_PRODUCT_NAME

43

Type of the composite product if the current operation corresponds to a package or season ticket line.
Data Type: VARCHAR2(60 CHAR)
Ex.: Tradition

Product:
PRODUCT_TYPE.INTERNAL_NAME_L1 AS COMP_PRODUCT_TYPE

44

Accounting code of the composite product if the current operation corresponds to a package or season ticket line.
Data Type: VARCHAR2(100 CHAR)
Ex.: 2318

Product:
INTERFACE_MAPPING.value[
type=PRODUCT|PERFORMANCE]

45

Number of articles in the order line
Data Type: NUMBER(38,0)
Ex.: 1

OPERATION.QUANTITY

46

ISO code of the currency used for all amounts mentioned in the current record

Data Type : CHAR(3)

Ex : EUR, CHF, USD

CURRENCY.ISO_NUMBER of the organisation’s main currency. The organisation concerned is the one containing the point of sale of the order to which belongs the current operation.

47

Base unit price (taxes included) of the product or charge. This price is expressed in tenths of cents. The base price is the catalogue price and doesn’t take into account special discounts
Data Type: NUMBER(38,0)
Ex.: 39000 (= 3900 cents = 39,00 €)

OPERATION.BASE_UNIT_AMOUNT AS TTC_BASE_UNIT_AMOUNT

48

Unit price (taxes included) of the product or charge. This price is expressed in tenths of cents. The unit price is the price effectively paid by the customer taking into account applied discounts.

Particular cases: For an order line related to a calculated price season ticket or to a package, the figure is worth 0 (for the composite product itself, not for its contents). For an order line related to the contents of a fixed price season ticket, this figure is worth 0.

Data Type: NUMBER(38,0)
Ex.: 30000

OPERATION.UNIT_AMOUNT AS TTC_UNIT_AMOUNT

49

Net amount of the product or charge. This amount is expressed in tenths of cents. It is always based on the unit amount to be paid.

Data Type: NUMBER(38,6)
Ex.: 29380

(OPERATION.WITHOUT_VAT_UNIT_AMOUNT * OPERATION.QUANTITY) AS HT_TOTAL_AMOUNT

50

VAT rate of the product or charge, in thousandths of a percent. It is the effectively applied rate during this sale (even if the rate has been modified in the meantime in the configuration)
Data Type: NUMBER(38,0)
Ex.: 19600 (= 19,6%)

Product:
OPERATION.VAT_RATE AS VAT_RATE

51

VAT amount of the product or charge. This amount is expressed in tenths of cents.

Particular cases: For an order line related to a calculated price season ticket or to a package, the figure is worth 0 (for the composite product itself, not for its contents). For an order line related to the contents of a fixed price season ticket, this figure is worth 0.

Data Type: NUMBER(38,6)
Ex.: 13020

Product:
((OPERATION.UNIT_AMOUNT - OPERATION.WITHOUT_VAT_UNIT_AMOUNT) *
OPERATION.QUANTITY) AS VAT_AMOUNT

52

Unit amount (taxes included) of the commission, expressed in tenths of cents. This field is null if no commission has been granted.

Operation associated with an operation of the "commission" kind.

OPERATION.UNIT_AMOUNT – OPERATION.COMMISSIONNED_UNIT_AMOUNT

Operation not associated with an operation of the "commission" kind.

NULL

53

Commission rate effectively granted, in thousandths of a percent. It may be different from the rate defined in the contract

It may be null even if a commission is granted (commission defined by a fixed amount).

Operation associated with an operation of the "commission" kind.

COMMISSION.USED_RATE AS USED_COMMISSION_RATE

Operation not associated with an operation of the "commission" kind.

NULL

54

Internal name of the contract applied to this operation. This field is null if no commission has been granted.

Operation associated with an operation of the "commission" kind.

CONTRACT.INTERNAL_NAME AS CONTRACT_INTERNAL_NAME

Operation not associated with an operation of the "commission" kind.

NULL

55

Reference of the contract applied to this operation. This field is null if no commission has been granted.

Operation associated with an operation of the "commission" kind.

CONTRACT.REFERENCE AS CONTRACT_REFERENCE

Operation not associated with an operation of the "commission" kind.

NULL

56

Number of lines of the sales price breakdown. This number should be ignored for the moment. The export of the price breakdown hasn’t been implemented yet.

Data Type: INTEGER
Ex.: 3

COUNT(AMOUNT_BREAKDOWN.AMOUNTBD_ID) AS PRICE_COMP_NBR

57

Comment in the order, without line breaks or tabs

Data Type: VARCHAR2(2000 CHAR)

ORDER_TABLE.REMARK

58

Designation of the product related to the ~~order~~order line. This designation is only set for transport products and free products. It is empty for any other products.

Data Type: VARCHAR2(1000 CHAR)

OPERATION.PRODUCT_DESIGNATION AS PRODUCT_DESIGNATION

59

Number of the file the order line belongs to. Generally, an order belongs to a unique file but an order my relate to several files in some cases (e.g. refunding a large number of order lines belonging to different files).

OPERATION.FILE_ID

5.1.4 Example

Below you can find a line of the "sales" file, related to the purchase of a single entry for a competition. In this example, tabs between the fields have been replaced with the pipe symbol " | ":

OP|467004472|SALE|785916|201411101257|98|379615168||Friden|Charles|STX_CPF|466546985|SINGLE_ENTRY|1|PUBLIC|KAWASAKI|Misaki||379618904|A - A+1|TDC|Tokyo Dome Corporation|P|LadyGaga|Concert de Lady Gaga||Evén|INT_PRODFAM/SIMPLE|OccCont|AUDCAT_TYPE/OccCont|Normal|Normal|||424425137|||1|event|Evenements|||||2|USD|160000|160000|303317.535544|5500|16682.464456|||||3|||3419

OP | 420399201 | SALE | 2417 | 201205181005 | 28 | 113432393 | Siegfried | Stéphane | STX_SSI | 226659560 | SINGLE_ENTRY | | | | | 191268952 | 2012-13 | HAC | SAS Océane Stadium - TEST | P | FFF | Coupe de France | Compét | INT_PRODFAM/SPORTING | OccCont | AUDCAT_TYPE/OccCont | Adulte | Adulte | 201210092000 | | 419462708 | | | C1 | Champion | Compétitions | | | | 1 | 30000 | 30000 | 30000 | 0 | 0 | | | | 0 | |

5.2 Cash flow file

This file contains the set of data related to payments made and the projected debits. It contains one record per each payment.

Certain payments are not related to orders (e.g. a refund of the credit note). Therefore, they are included in the cash flow file but they do not have an equivalent in the sales file. The same applies to the deposits related to files that are still open.

5.2.1 Record selection

The payments to be taken into account must meet the following conditions:

§ They have not yet been sent after a previous submission of the cash flow file.

§ They are associated with cash desks that have been closed and verified by the accountant ("accounting closed" status) or with cash desks for virtual operators [6]

§ They are associated with the current organisation points of sale

§ The amount of the payments cannot be null

§ They must have the "Paid" status

Therefore, the payments corresponding to a deposit are also provided in this file, in the same way as the payments used to credit an account.

5.2.2 File structure

The generated file name is:

· YYYY/MM/DD/Encaissements_YYYYMMDD_S.txt

The file structure is as defined in the following table:

· 1 line per payment

No.	Description	Field (DB)
1	Technical identifier for the payment Data Type: NUMBER(38,0) Ex.: 35109303	PAYMENT.PAYMENT_ID
2	Date of the payment Data Type: TIMESTAMP(3) Ex.: 20091106 Format: YYYYMMDD	TRUNC(PAYMENT.REFERENCE_DATE) AS REFERENCE_DATE
3	Due date (in case of deferred payment, if the payment is associated with exactly one due date) Data Type: DATE Ex.: 20090610 Format: YYYYMMDD	TRUNC(DUE_DATE.DUE_DATE) AS DUE_DATE
4	Cash desk technical identifier number Data Type: NUMBER(38,0) Ex.: 36078; NULL	CASH_DESK.CASHDESK_ID
5	Operator's last name (à responsible for the order) Data Type: VARCHAR2(100 CHAR) Ex.: Joset	USER_TABLE.LASTNAME AS OPERATOR_LASTNAME Note: link through CASH_DESK
6	Operator first name Data Type: VARCHAR2(100 CHAR) Ex.: Céline	USER_TABLE.FIRSTNAME AS OPERATOR_FIRSTNAME
7	Operator login name Data Type: VARCHAR2(20 BYTE) Ex.: CJOSET	USER_TABLE.LOGIN AS OPERATOR_LOGIN
8	Technical identifier number of the point of sales Data Type: NUMBER(38,0) Ex.: 665	POINT_OF_SALES.POS_NUM
9	ISO code of the organisation’s currency Data Type : CHAR(3) Ex : EUR, CHF, USD	CURRENCY.ISO_NUMBER of the main currency of the organization containing the point of sales or the sales channel of the cash desk associated with the payment
10	Payment amount (negative in case of a refund), expressed in the organisation’s main currency; this amount corresponds to tenths of cents. This is the amount that must be used to make comparisons with the other files in the accounting interface. Data Type: NUMBER(38,0) Ex.: 26000 = 2600 cents = 26,00 €	PAYMENT.INSTITUTION_AMOUNT
11	Amount expressed in the buyer’s currency Data Type : NUMBER(38,0) Ex: 39000	PAYMENT.CURRENCY_AMOUNT
12	ISO code of the currency selected by the buyer Data Type : CHAR(3) Ex : EUR, CHF, USD	PAYMENT.CURRENCY_CODE
13	Payment method code Data Type: VARCHAR2(8 CHAR) Ex.: CASH; CHEQ; VISA	PAYMENT_METHOD.CODE AS PAYMENT_METHOD_CODE
14	Payment method internal name Data Type: VARCHAR2(60 CHAR) Ex.: Espèces; Chèque; Carte bancaire	PAYMENT_METHOD.INTERNAL_NAME_L1 AS PAYMENT_METHOD_NAME
15	Payment method accounting code Data Type: VARCHAR2(100 CHAR) Ex.: PM5432	INTERFACE_MAPPING.value[ type=CONTACT] AS CTCT_ACC_CODE
16	Bank code (optional for cheques, always indicated for bank debits) Data Type: VARCHAR2(32 BYTE) Ex.: 30004	BANK.BANK_CODE
17	Bank name (optional for cheques, always indicated for bank debits) Data Type: VARCHAR2(200 CHAR) Ex.: BNP Paribas	BANK.NAME AS BANK_NAME
18	Cheque number (as appropriate) Data Type: VARCHAR2(50 CHAR) Ex.: =0000460=078010003908?345050480797;	PAYMENT.IDENTIFICATION AS CHEQ_NUMBER
19	IBAN number (for bank debits) Data Type: VARCHAR2(34 CHAR) Ex.: FR7630076020511120350030005	PAYMENT.IBAN
20	Authorisation technical number returned by the external payment system (for bank cards and cheques, if available) Data Type: VARCHAR2(50 CHAR) Ex.: =0000460=078010003908?345050480797;	PAYMENT.IDENTIFICATION AS CLEARING
21	Payment kind: · Direct payment: DIRECT_PAYMENT · Deposit credit (negative amount): CREDIT_DEPOSIT · Deferred payment: POSTPONED_PAYMENT · Credit note credited (negative amount): CREDIT_CREDIT_NOTE · Payment by debiting the credit note: DEBIT_CREDIT_NOTE · Payment through the use of a deposit: DEBIT_DEPOSIT · Credit transferred to the waiting account (negative amount): CREDIT_WAIT_ACCOUNT · Payment by debiting the waiting account: DEBIT_WAIT_ACCOUNT · Refunds (negative amount): REFUND · Refund of the credit note (negative amount): REFUND_CREDIT_NOTE · Refund of a deposit: DEPOSIT_CREDIT_NOTE · Payment cancellation (after an error has been discovered): CANCELLATION Data Type: VARCHAR2(20 BYTE)	PAYMENT.PAYMENT_KIND
22	Payment state Data Type: VARCHAR2(20 BYTE) Ex.: PAID;	PAYMENT.STATE AS PAYMENT_STATE
23	Order number (if existing) Data Type: NUMBER(38,0) Ex.: 3407061	ORDER_TABLE.ORDER_ID
24	Contact number of the payer (if it exists; anonymous payments do not have a contact) Data Type: VARCHAR2(20 BYTE) Ex.: 0100403	CONTACT.CONTACT_NUMBER
25	Reserved for the accounting code of the payer (if it exists). Contains currently the role of the contact. Possible values are: RELAY, PUBLIC Data Type: VARCHAR2(100 CHAR) Ex.: RELAY, PUBLIC	Foreseen: INTERFACE_MAPPING.value[ type=CONTACT] AS CTCT_ACC_CODE Currently: CONTACT.ROLE
26	Payer's last name (in case it is an individual) Data Type: VARCHAR2(100 CHAR) Ex.: Weber; NULL	INDIVIDUAL.LASTNAME AS IND_LASTNAME
27	Payer's first name (in case it is an individual) Data Type: VARCHAR2(100 CHAR) Ex.: Jenifer; NULL	INDIVIDUAL.FIRSTNAME AS IND_FIRSTNAME
28	Contact official name (if it is a structure) Data Type: VARCHAR2(100 CHAR) Ex.: ELCA; NULL	STRUCTURE.OFFICIAL_NAME AS STRUCT_NAME
29	Number of operations associated with this payment	COUNT (OPERATION.OPERATION_ID) AS COUNT_OPERATIONS
30	Number of the file directly related to the payment. This information is provided in case of payment of a deposit.	PAYMENT.FILE_ID

• List of operations associated with the payment (substructure)

After each payment, the file contains a list of technical identifiers of order lines that are covered (at least partially) by the previous payment. The technical identifiers of command lines correspond to those in column 2 of the sales file. A record type has been added to the record so that the different lists may be distinguished easily. The format of these data is as follows:

No.	Description	Field (DB)
1	Record type	“OPE”
21	Operation identifier Data Type: NUMBER(38,0) Ex.: 35109303	OPERATION.OPERATION_ID
3	File number. Number of the file the operation belongs to Data Type: NUMBER(38,0) Ex.: 35109303	OPERATION.FILE_ID

• List of instalments associated with the payment (substructure)

After each payment, the file contains a list of technical identifiers of instalments that are covered (at least partially) by the previous payment. The technical identifiers of instalments correspond to those in column 1 of the instalments file. A record type allows distinguishing easily the different lists. The format of these data is as follows:

No.	Description	Field (DB)
1	Record type	“INS”
2	Instalment identifier Data Type: NUMBER(38,0) Ex.: 35109303	INSTALLMENT.INSTALLMENT_ID
3	Part of the instalment amount paid by the current payment. This is an amount expressed in the same currency as the instalment. This amount corresponds to tenths of cents. Data Type: NUMBER(38,0)	AT_INSTALLMENT_PAYMENT.CURRENCY_AMOUNT

5.2.3 Example

This is an example of a line in the "cash flow" file, for a payment in cash that covers three command lines. In this example, tabs between the fields have been replaced with the pipe symbol " | ": Firstly, it is the payment information and later ~~three~~ supplementary lines that indicate which order lines and which instalments (if any) are covered:

461650253|20140915||439875211|Huynh Chi Phuong|Quyen|STX_PQH|191|USD|240000|240000|USD|Cash|Espèces|||||||DIRECT_PAYMENT|PAID||28|PUBLIC|SOICHIRO|Amaya||1|

OPE|461652614|1029

INS|461657769|14400

INS|461657770|9600

~~419453313 | 20120503 | | 419455929 | Fall | Caspar | STX_CJF | 31 | 90000 | | | Cash | Espèces | | | | | | DIRECT_PAYMENT | PAID | 2066 | 1 | FALL | Caspar | SECUTIX | 3~~

~~419452941~~

~~419452940~~

~~419452937~~

5.3 Instalments file

During a sale, it is possible to define a set of payment terms if the order doesn’t need to be paid immediately. This file contains the set of instalments and mentions the related sales order.

This file doesn’t mention the payment state of each instalment. Instead, the cash flow file contains also the payments destined to pay the instalments.

5.3.1 Record selection

The instalments to be taken into account must meet the following conditions:

§ They concern orders issued by the current organisation

§ They have been created or modified after a previous submission of the instalments file. Following modifications are taken into account:

§ Due date

§ Payment method

5.3.2 File structure

The generated file name is:

· YYYY/MM/DD/Echeances_YYYYMMDD_S.txt

The file structure is as defined in the following table:

· 1 line per instalment

No.	Description	Field (DB)
1	Technical identifier for the instalment Data Type: NUMBER(38,0) Ex.: 35109303	INSTALLMENT.INSTALLMENT_ID
2	Sequence number of instalment Data Type: INTEGER	INSTALLMENT.RANK
3	Amount of instalment (without taking possible payments into account). This amount corresponds to tenths of cents. Data Type: NUMBER(38,0) Ex.: 26000 = 2600 cents = 26,00 €	INSTALLMENT.CURRENCY_AMOUNT
4	ISO code of the currency in which the previous amount is expressed Data Type : CHAR(3) Ex : EUR, CHF, USD	INSTALLMENT.CURRENCY_CODE
5	Due date Data Type: TIMESTAMP(3) Ex.: 04.07.2014	INSTALLMENT.DUE_DATE
6	Number of order related to this instalment Data Type: NUMBER(38,0)	INSTALLMENT.ORDER_ID
7	Number of invoice related to the instalment. This information is only available if the order related to the instalment has been invoiced. Data Type: VARCHAR2(100 CHAR)	INVOICE.INVOICE_NUMBER AS INVOICE_NUMBER
8	Payment method code. Payment method foreseen in order to pay the current instalment. SecuTix doesn’t forbid the buyer to use another payment method at the time of effective payment. Data Type: VARCHAR2(8 CHAR) Ex.: CASH; CHEQ; VISA	PAYMENT_METHOD.CODE AS PAYMENT_METHOD_CODE
9	Payment method internal name. Payment method foreseen in order to pay the current instalment. SecuTix doesn’t forbid the buyer to use another payment method at the time of effective payment Data Type: VARCHAR2(60 CHAR) Ex.: Espèces; Chèque; Carte bancaire	PAYMENT_METHOD.INTERNAL_NAME_L1 AS PAYMENT_METHOD_NAME

5.3.3 Example

The example below gives the list of the instalments of a given order. The order number and the invoice number allow to link the information to other files.

467006628|1|9600|USD|20141210|785913|A-A+1-000370|BankTran|Virement bancaire

467006629|2|22400|USD|20160211|785913|A-A+1-000370|BankTran|Virement bancaire

5.4 Invoices file

This file contains the set of invoices issued since the last file submission. It contains one record per each invoice. Each invoice has a record per every operation associated with it.

5.4.1 Record selection

The invoices to be taken into account must meet the following conditions:

§ They have not yet been submitted after a previous submission of the invoices file.

§ They have been issued by the current organisation.

Cancelled invoices and cancellation invoices are also submitted.

5.4.2 File structure

The generated file name is:

· YYYY/MM/DD/Factures_YYYYMMDD_S.txt

The file structure is as defined in the following table:

· 1 line per operation

No.	Description	Field (DB)
1	Invoice number Data Type: VARCHAR2(100 CHAR) Ex.: 2009_314	INVOICE.INVOICE_NUMBER
2	Invoice creation timestamp Data Type: TIMESTAMP(3)	INVOICE.INVOICING_DATE
3	ISO code of the currency in which all the amounts of the current record are expressed Data Type : CHAR(3) Ex : EUR, CHF, USD	CURRENCY.ISO_NUMBER of the main currency of the organisation containing the point of sale of the invoiced order
4	Total amount of the invoice. Invoice amount (negative in case of credit note), expressed in tenths of cents of the main currency of the organisation.Therefore, 600 € is provided as 600000. Data Type: NUMBER(38,0) Ex.: 600'000 = 60'000 cents = 600,00 €	INVOICEABLE_OBJECT.AMOUNT
5	Invoice remark Data Type: VARCHAR2(2000 CHAR)	INVOICE.REMARK
6	Invoice number cancelled by the current invoice Data Type: VARCHAR2(100 CHAR)	INVOICE. FK_INVOICE_CANCELLEDINVOICE
7	Producer name (if available), i.e. official name of the producer of the show associated with the invoice, for example Data Type: VARCHAR2(100 CHAR) Ex.: ELCA Productions	STRUCTURE.OFFICIAL_NAME as PRODUCER Note: link per INVOICE → INVOICEABLE_OBJECT → CONTACT
8	Producer accounting code (if available) Data Type: VARCHAR2(100 CHAR) Ex.: CT357159	INTERFACE_MAPPING.value[ type=CONTACT] AS CTCT_ACC_CODE
9	Generation date (= date on which the invoice document is issued) Data Type: TIMESTAMP(3) Ex.: 14.01.09	TRUNC(DOCUMENT.GENERATION_DATE) AS PRINT_DATE
10	Number of the point of sales generating the invoice Data Type: NUMBER(38,0) Ex.: 665	POINT_OF_SALES.POS _NUM Note: link per DOCUMENT.FK_DOC_PRINTINGPOS
11	Cash desk number. This field is only filled in if the invoice corresponds to a single order. In this case, it contains the identifier of the cash desk associated to the payments of the order or, if the order has no payment (because its amount is null), the cash desk identifier of the order. Data Type: NUMBER(38,0) Ex.: 36078; NULL	CASH_DESK.CASHDESK_ID
12	Last name of the operator who created the invoice document (the oldest if there are several of them) Data Type: VARCHAR2(100 CHAR) Ex.: Joset Note: link through DOCUMENT.FK_DOCUMENT_CREATIONUSER	USER_TABLE.LASTNAME AS OPERATOR_LASTNAME
13	First name of the operator who created the invoice document (the oldest if there are several of them) Data Type: VARCHAR2(100 CHAR) Ex.: Chiara	USER_TABLE.FIRSTNAME AS OPERATOR_FIRSTNAME
14	Login name of the operator who created the invoice document (the oldest if there are several of them) Data Type: VARCHAR2(20 BYTE) Ex.: CJOSET	USER_TABLE.LOGIN AS OPERATOR_LOGIN
15	Invoiced contact number Data Type: VARCHAR2(20 BYTE) Ex.: 3738305 Note: link per INVOICE → CONTACT	CONTACT.CONTACT_NUMBER
16	Reserved for the accounting code of the invoiced contact (if available). Contains currently the role of the contact. Possible values are: RELAY, PUBLIC Invoiced contact accounting code (if available) Data Type: VARCHAR2(100 CHAR) Ex.: RELAY, PUBLIC	Foreseen: INTERFACE_MAPPING.value[ type=CONTACT] AS CTCT_ACC_CODE Currently: CONTACT.ROLE
17	Contact's last name (in case it is an individual) Data Type: VARCHAR2(100 CHAR) Ex.: Clooney; NULL	INDIVIDUAL.LASTNAME AS IND_LASTNAME
18	Contact's first name (in case it is an individual) Data Type: VARCHAR2(100 CHAR) Ex.: Georges; NULL	INDIVIDUAL.FIRSTNAME AS IND_FIRSTNAME
19	Official name of the contact (in case it is a structure) Data Type: VARCHAR2(100 CHAR) Ex.: ELCA; NULL	STRUCTURE.OFFICIAL_NAME AS STRUCT_NAME

· Description of the operations included in the invoice: 1 line for each operation, including commission operation

N°

Description

Field (DB)

1

All attributes of sales file. In case a record describes a commission operation, following rules apply:

Field 23 (record type) gets the value ‘F’

Field 24 represents a commission code (instead of a product or a charge code)

Field 25 represents the commission internal name

Field 27 (product family code) is set to ‘COMMISSION’

Field 28 stores the number of an operation that gets the current commission.

See sales file

5.4.3 Example

The following is an example of a line of the "invoice" file, for an invoice containing three operations. In this example, tabs between the fields have been replaced with the pipe symbol " | ": First, we find the invoice data and later ~~tree~~ supplementary lines indicating the details of the operation:

A-A+1-000371|201411101306|USD|320000|||||201411101306|98|466546985|Friden|Charles|STX_CPF|1|PUBLIC|KAWASAKI| Misaki|

OP|467004472|SALE|785916|201411101257|98|379615168||Friden|Charles|STX_CPF|466546985|SINGLE_ENTRY|1|PUBLIC|KAWASAKI|Misaki||379618904|A - A+1|TDC|Tokyo Dome Corporation|P|LadyGaga|Concert de Lady Gaga||Evén|INT_PRODFAM/SIMPLE|OccCont|AUDCAT_TYPE/OccCont|Normal|Normal|201411292030||424425137|||1|event|Evenements|||||2|USD|160000|160000|303317.535544|5500|16682.464456|||||3|||3419

~~000004 | 201204021825 | 30000 | | | | | 27 | 228895598 | | | | 26560 | RENAUD, Maxime |~~

OP | 229473539 | SALE | 251 | 201204021822 | 27 | 225008507 | Dauzout | Virginie | VIRGINIE | 228895598 | PRODUCT_COMPOSITION | 26560 | RENAUD | Maxime | | 191268952 | 2012-13 | HAC | SAS Océane Stadium - TEST | P | Inaugur | Spectacle Inaugural | Evén | INT_PRODFAM/SIMPLE | OccCont | AUDCAT_TYPE/OccCont | AboAdul | Abonné HAC adulte | 201210312045 | | 199607916 | | | | | SUPPOR | Evénem | Evénements | PackInau | Pack Spectacle Inaugural + Match 140 ans | | 1 | 10000 | 10000 | 10000 | 0 | 0 | | | | | 0 |

OP | 229473541 | SALE | 251 | 201204021822 | 27 | 225008507 | Dauzout | Virginie | VIRGINIE | 228895598 | SINGLE_ENTRY | 26560 | RENAUD | Maxime | | 191268952 | 2012-13 | HAC | SAS Océane Stadium - TEST | P | Inaugur | Spectacle Inaugural | Evén | INT_PRODFAM/SIMPLE | OccCont | AUDCAT_TYPE/OccCont | Adulte | Adulte | 201210312045 | | 199607916 | | | | | SUPPOR | Evénem | Evénements | | | | 1 | 10000 | 10000 | 10000 | 0 | 0 | | | | | 0 |

OP | 229473540 | SALE | 251 | 201204021822 | 27 | 225008507 | Dauzout | Virginie | VIRGINIE | 228895598 | PRODUCT_COMPOSITION | 26560 | RENAUD | Maxime | | 191268952 | 2012-13 | HAC | SAS Océane Stadium - TEST | P | GalaHAC | Match de Gala, 140 ans du HAC | Evén | INT_PRODFAM/SIMPLE | OccCont | AUDCAT_TYPE/OccCont | AboAdul | Abonné HAC adulte | 201209091800 | | 199607918 | | | | | SUPPOR | Evénem | Evénements | PackInau | Pack Spectacle Inaugural + Match 140 ans | | 1 | 10000 | 10000 | 10000 | 0 | 0 | | | | | 0 |

5.5 Structures file

This file describes the set of structures that are known of a certain organisation. Each record describes one structure.

SecuTix manages structures, individuals and contacts. A structure is a company or a community. An individual is a physical person. An individual may have several roles. He may act as a simple spectator, as a journalist, as a contact person of a company. Each role corresponds to a contact. A specific contact represents the company itself (in opposition to an individual working in that company).

A contact acting as a simple spectator has the “public” role. A contact person of a company is a “relay” of that company.

5.5.1 Record selection

The structures to be taken into account must meet the following conditions:

§ They have been modified after the last submission of the file

§ They are visible from the current organisation [7]

§ Each of them is associated with a public role contact

5.5.2 File structure

The generated file name is:

· YYYY/MM/DD/Structures_YYYYMMDD_S.txt

The file structure is as defined in the following tables:

· 1 line per structure contact

No.	Description	Field (DB)
1	Record type, a flag indicating if they are: - structures ("STR") - administrative numbers ("NAD"). This record type has another format, see the following table Ex.: STR; NAD	Operations: "STR"
2	Official name Data Type: VARCHAR2(100 CHAR) Ex.: ELCA	STRUCTURE.OFFICIAL_NAME AS STRUCT_NAME
3	Structure type code Data Type: VARCHAR2(8 CHAR) (for each path element) Ex.: /> Press /> PressCom	STRUCTURE_TYPE.CODE AS STRUCT_TYPE_CODE
4	Structure type internal name (Path) Data Type: VARCHAR2(60 CHAR) (for each path element) Ex.: /> Presse /> Communication	STRUCTURE_TYPE.INTERNAL_NAME_L1 AS STRUCT_TYPE_NAME
5	Structure staff Data Type: NUMBER(38,0)	STRUCTURE.NUMBER_OF_EMPLOYEES AS STAFF
6	Number of contact of structure type (contact with a public role). If there are several of them, choose a valid contact associated with an address. If there are none, choose a valid contact. Otherwise, choose any contact that is associated with the structure Data Type: VARCHAR2(20 BYTE) Ex.: 3738305	CONTACT.CONTACT_NUMBER
7	Reserved for structure accounting code (if available). This field is currently set to NULL Data Type: VARCHAR2(100 CHAR) Ex.: CT957159	INTERFACE_MAPPING.value[ type=CONTACT] as ACC_CODE
8	Way number of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: 26	CONTACT_ADDRESS.WAY_NUMBER
9	Way type of the contact main address Data Type: VARCHAR2(2000 CHAR) Ex.: Avenue	(CONTACT_ADDRESS.WAY_TYPE_IC) INTERNAL_CODE_TRANS.VALUE AS WAY_TYPE
10	Way name of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: Champs-Elysées	CONTACT_ADDRESS.WAY
11	Way complement of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: Bureaux de la colline	CONTACT_ADDRESS.WAY_COMPLEMENT
12	Zip code of the contact main address Data Type: VARCHAR2(20 BYTE) Ex.: 75000	CONTACT_ADDRESS.ZIPCODE
13	Town of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: PARIS	CONTACT_ADDRESS.TOWN
14	Country of the contact main address Data Type: VARCHAR2(2 BYTE) Ex.: FR	CONTACT_ADDRESS.COUNTRY_CODE
15	Contact validity end date (may be null) Data Type: DATE Ex.: 31.03.2013	CONTACT.END_VALIDITY_DATE AS END_VALIDITY_DATE
16	Indication whether it is a community Data Type: BOOLEAN	STRUCTURE.COMMUNITY AS COMMUNITY
17	Additional information (e.g. floor, apartment number) of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: Apartment 53	CONTACT_ADDRESS.APARTMENT

· 1 line per administrative number valid on the file extraction date

No.	Description	Field (DB)
1	Record type, a flag indicating if they are: - structures ("STR"), see table above - administrative numbers ("NAD") Ex.: STR; NAD	Operations: "NAD"
2	Structure identifier Data Type: NUMBER(38,0) Ex.: 22720196	STRUCTURE.STRUCTUR_ID AS STRUCTURE_ID
32	Administrative number code Data Type: VARCHAR2(8 CHAR) Ex.: SIRET	ADMINISTRATIVE_NUMBER.CODE AS ADM_NUMBER_CODE
43	Administrative number internal number Data Type: NUMBER(38,0) Ex.: 22720196	ADMINISTRATIVE_NUMBER.ADMINNUM_ID AS ADM_NUMBER_ID
54	Administrative number value Data Type: VARCHAR2(8 CHAR) Ex.: 20597799	ADMINISTRATIVE_NUMBER_VALUE.VALUE AS ADM_NUMBER_VALUE

5.5.3 Example

STR|PEUGEOT|AmComité|Comité d'entreprise|34500|2820||||AVENUE DE LA CITADELLE||25000|BESANCON|F|2999-12-31 00:00:00.0|false|

NAD|467095818|APE|2720204|147258369

NAD|467095818|SIRET|2720205|863524

NAD|467095818|TVAEU|2720206|518462

~~STR|ELCA|/> Press /> PressCom|/> Presse /> Communication|550|3738305|CT985478|26|Avenue|Champs-Elysées~~
~~|Bureaux de la colline|75000|PARIS|FR|31.03.2013|F~~

~~NAD|SIRET|22720196|20597799|~~

5.6 Individuals file

This file describes the set of individuals that are known of a certain organisation. Each record describes an individual who can be attached to a structure.

SecuTix manages structures, individuals and contacts. A structure is a company or a community. An individual is a physical person. An individual may have several roles. He may act as a simple spectator, as a journalist, as a contact person of a company. Each role corresponds to a contact. A specific contact represents the company itself (in opposition to an individual working in that company).

5.6.1 Record selection

The individuals to be taken into account must meet the following conditions:

§ They have been modified after the last submission of the file

§ They are visible from the current organisation [8]

§ This individual is associated with at least one public role contact or relay

5.6.2 File structure

The generated file name is:

· YYYY/MM/DD/Individuels_YYYYMMDD_S.txt

The file structure is as defined in the following table:

· 1 line per individual

No.	Description	Field (DB)
1	Record type, possible values: IND: individuals (described in this table) CRI: Contact criterion (see table below) AUT: Authorisation for communication (see table below)	Record type
2	Title of the contact Data Type: VARCHAR2(40 BYTE) Ex.: Mademoiselle	INDIVIDUAL.TITLE
3	Contact's last name Data Type: VARCHAR2(100 CHAR) Ex.: Joset	INDIVIDUAL.LASTNAME
4	Contact's first name Data Type: VARCHAR2(100 CHAR) Ex.: Céline	INDIVIDUAL.FIRSTNAME
5	If existing, number of the public role contact associated with the individual (ROLE = "PUBLIC") Otherwise, number of the relay role contact associated with this individual (ROLE = "RELAY") Data Type: VARCHAR2(20 BYTE) Ex.: 3738322	CONTACT.CONTACT_NUMBER
6	Reserved for the accounting code of the contact associated to the individual (if available). Contains currently the role of the contact. Possible values are: RELAY, PUBLIC Data Type: VARCHAR2(100 CHAR) Ex.: RELAY, PUBLIC	Foreseen: INTERFACE_MAPPING.value[ type=CONTACT] as ACC_CODE Currently: CONTACT.ROLE
7	Way number of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: 26 COMMENT: as of JungfrauU4, this field will be emtpy	CONTACT_ADDRESS.WAY_NUMBER
8	Way type of the contact main address Data Type: VARCHAR2(40 BYTE) Ex.: Avenue COMMENT: as of JungfrauU4, this field will be emtpy	CONTACT_ADDRESS.WAY_TYPE
9	Way name of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: Champs-Elysées	CONTACT_ADDRESS.WAY
10	Way complement of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: Bureaux de la colline	CONTACT_ADDRESS.WAY_COMPLEMENT
11	Zip code of the contact main address Data Type: VARCHAR2(20 BYTE) Ex.: 75000	CONTACT_ADDRESS.ZIPCODE
12	Town of the contact main address Data Type: VARCHAR2(100 CHAR) Ex.: PARIS	CONTACT_ADDRESS.TOWN
13	Country of the contact main address Data Type: VARCHAR2(2 BYTE) Ex.: FR	CONTACT_ADDRESS.COUNTRY_CODE
14	Fixed telephone number of the contact[9] Data Type: VARCHAR2(20 BYTE) Ex.: 0123456789	COMMUNICATION_MEAN.NAT_PHONE_NUMBER AS FIXED_PHONE Note: DTYPE = "FIXED_PHONE"
15	Fixed fax number of the contact[10] Data Type: VARCHAR2(20 BYTE) Ex.: 0123456788	COMMUNICATION_MEAN.NAT_PHONE_NUMBER AS FAX_PHONE Note: DTYPE = "FAX_PHONE"
16	Cellular telephone number of the contact[11] Data Type: VARCHAR2(20 BYTE) Ex.: 0123456788	COMMUNICATION_MEAN.NAT_PHONE_NUMBER AS MOBILE_PHONE Note: DTYPE = "CELLULAR_PHONE"
17	Contact's email address[12] Data Type: VARCHAR2(4000 CHAR) Ex.: capitaine.haddock@moulinsart.be	COMMUNICATION_MEAN.VALUE AS EMAIL Note: DTYPE = "EMAIL"
18	Contact validity end date (may be null) Data Type: DATE Ex.: 31.03.2013	CONTACT.END_VALIDITY_DATE AS END_VALIDITY_DATE
19	~~Number of the~~ contact number representing the structure to which the contact belongs to (may be null) Data Type: VARCHAR2(20 BYTE)~~Number of the contact representing the structure to which the contact belongs to (may be null)~~ ~~Data Type: NUMBER(38,0)~~	AT_STRUCTURE_CONTACT à AT_STRUCTURE_CONTACT à CONTACT.CONTACT_NUMBER~~AT_STRUCTURE_CONTACT.STRUCTUR_ID AS STRUCTUR_ID~~
20	Additional information (e.g. floor, apartment number) of the contact main addresss Data Type: VARCHAR2(100 CHAR) Ex.: Apartment 53	CONTACT_ADDRESS.APARTMENT

Remarks:

§ Identifying a contact:

§ A contact is identified by its number. This number is, for instance, used in the sales file to identify the purchase contact.

§ Duplicates:

§ Sometimes, operators or internet users create an individual twice, causing duplicates in the SecuTix database. SecuTix provides a function that detects these duplicates and merges the data. However, this function doesn’t execute systematically after each creation of an individual. Therefore, following situations may occur:

§ If duplicate individuals have been created and not merged yet, the individuals file will contain both individuals with no additional information.

§ If duplicate individuals have been merged, the individuals file will contain both individuals. The merge process marks one individual as not valid anymore. The record describing the individual who is not valid anymore will contain a validity end date in the past matching the date the merge process has been run. The accounting system can decide what to do with these individuals.

Contact criteria ("CRI" record type):

No.	Description	Field (DB)
1	CRI: Contact criterion	Record type
2	Contact criterion code Data Type: VARCHAR2(8 CHAR) Ex.: CT_INTER	CONTACT_CRITERION.ID_CODE AS CRITERION_CODE
3	Contact criterion internal name Data Type: VARCHAR2(60 CHAR) Ex.: Centre d’intérêt	CONTACT_CRITERION.INTERNAL_NAME_L1 AS CRITERION_NAME
4	Contact criterion value Data Type: BLOB Ex: Sport mécanique The BLOB is a string of characters. The accounting interface doesn’t define a maximum string length.	CONTACT_CRITERION_VALUE.VALUE AS CRITERION_VALUE

Contact authorisations ("AUT" record type):

No.	Description	Field (DB)
1	AUT: Authorisations to communicate	Record type
2	Authorisation code Data Type: VARCHAR2(8 CHAR) Ex.: POSTAL	CONTACT_AUTHORIZATION.CODE AS AUTHORIZATION_CODE
3	Authorisation internal name Data Type: VARCHAR2(40) Ex: AUTHOR_INFO/POSTAL	CONTACT_AUTHORIZATION.INTERNAL_NAME_IC AS AUTHORIZATION_NAME
4	Authorisation value Data Type: BOOLEAN	CONTACT_AUTHORIZATION_VALUE.ALLOWED AS AUTHORIZATION_VALUE