Accounting Exports

Overview

Accounting export are a specialized version of Flat File Exports.

SecuTix experts propose to write Data Export Queries containing accounting data that an institution's specific accounting system will be able to consume directly.

The goal of those specific queries is to avoid to build intermediate pieces of software between the ticketing and the accounting systems.

SecuTix successfully integrated that way with many accounting systems.

  • Sage
  • Abacus
  • Neuvo
  • SAP

An very simple example of such flat file accounting export can be downloaded here.

Those accounting exports are built on behalf of the institution by SecuTix Service Team.

Functional perimeter

This interface exports flat files containing data « ready to be imported » inside an accounting system.

Those data in a given file are supposed to be balanced in an accounting sense.

  • Payments and sales are balanced
  • Credits and debits are balanced
  • They can contain accouting codes (defined by the operators)
  • They can contain analytics codes (defined by the operators)
  • They can contain VAT lines (if requested)
  • They can contain Third party accounts lines (if requested)
  • Thet can contain analytics lines (if requested)

accountingExportImage

The process stages are:

  1. SecuTix generates files from ticketing system (incl. orders + payments).
  2. SecuTix puts the files on an FTP or sFTP server (or exposes the files through an HTTPS URL – with basic-authentication)
  3. OPTIONAL: one tool – NOT provided by SecuTix - transforms the data. Ideally, this step is avoided.
  4. Data is inserted inside the accounting system. This step is not part of SecuTix supplied perimeter.

Conventions

Colors have been used through the diagrams:

  • Blue is used to depict SecuTix components.
  • Green is used to depict third party components (not under SecuTix responsibility)

Warning

Following features from the ticketing system are not supported yet by this interface:

  • Deposits on files
  • Overheads rebates
  • Resold tickets at a different price
  • Paying an order in a currency different than the organization’s one

The result is undetermined should this interface be used under those circumstances. All provided URLs and data inside example are not real ones; please get in touch with SecuTix team to get appropriate data.
Field names, enum values… are supposed to be correct, nevertheless a copy/paste issue can happen and in any case, the generated file is the reference. Should the reader find such a mistake, please get in touch with SecuTix team so that this document is fixed.

Export principles

Date processing

The dates mentioned in the files always represent the “reference” dates. For example, an operation dated on July, 25th at 1:30 am will be recorded under July, 24th, if organization beginning of day is 2:00 am).

Multiple organisations

If the institution has multiple organisations, there will be a separate file for every organisation.

Currency

Those exports files only handles a single currency.

File format

See Flat File Exports to see the main options for flat file exports.

The lines of the file can be formatted to fit an institution's needs.

By format, we mean:

  • Order of the fields
  • Removing some fields
  • Concatenating some fields
  • Formatting dates (eg 2015-12-24 or 24/12/15), amounts (eg EUR 12.55 or 1255) or debit/credit (eg 1 for Debit, or C for Credit…)
  • Changing field names on header line
  • Aggregating data. Aggregation must be done on already provided fields (eg, aggregate lines by date + operator + accounting code and summing amounts)
  • Filtering data (eg, not exporting fixed price season ticket sales but only their usage to get a seat for a performance ; exporting or not operations with amount = 0…)
  • Adding some hardcoded fields or separators or line prefix/suffix
  • As stated above, changing encoding, EOL, separator

Accounting codes

With a view to allowing the accounting system to record a sale operation (or other) under the adequate account, accounting codes can be configured in the interface for the following entities:

  • Contact / Structure and type of structure
  • Simple charge
  • General/Payment overheads
  • Delivery overheads
  • Product family or Product or Performance (the most precise is used)
  • Method of payment
  • Other (please request)

If no account code has been set for a structure, the interface will provide the accounting code of the associated structure type.
If no account code has been set for a performance, the interface will provide the accounting code of the product (i.e., of the event) that this performance belongs to, if none found it will provide the accounting code set for product family.
In any case, if an accounting code cannot be found for an entity, YYYYY_XXXX will be used, with YYYYY begin the type of the entity (eg, DIRECT_PAYMENT or ORDER_OVERHEADS …) and XXXXX being SecuTix code of related entity.

Note: certain analytic accounting solutions use several accounting codes, to satisfy this need, several numbers, always in the same order (accounting code 1| accounting code 2|...), separated by pipes “|” must be set in the field of the interface mapping after the general accounting code. Example: 41913100|SAI|RBI means the general accounting code will be 41913100, first analytical code is SAI and second analytical code is RBI.
The following guide (in french) explains how to setup those codes in SecuTix screens.

Generated files access

Generated files:

  • Can be fetched manually by an operator inside the execution screens
  • Are kept on SecuTix servers for at least 90 days
  • Can be pushed to a remote server as detailed in Flat File Exports.

Record selection

Exported lines satisfy those conditions:

  • They have not been exported by a previous successful execution of this interface
  • Only closed orders and paid payments, with reference date older than or equals previous day
  • Only orders + payments related to current organization
  • Only sales and refund orders are taken into account (= no reservation nor options)
  • Order + payment's related cashdesk must be in state accounting_closed for real operators, cashdesks of virtual operators (internet sales) are always exported
  • Commission, countermarks operations are not taken into account

"Simple" accounting export description

The list of fields below is a example of the content of an accounting export that can be provided by SecuTix.

It must not be considered as a standard specification for all accounting exports, but more as a list of possible fields and values from which to pick the data for your specific accounting needs.

Sales line description

Each line corresponds to one “Operation” inside SecuTix.

Name Description Type Mandatory
reference_date Date of the sales (taking into account organization’s beginning of day) date
Ex: 2009-11-06
Yes
order_id Order identifier. Long (64 bits integer)
Ex: 10254851234
Yes
lineid Technical identifier of the operation (=operation_id) Long (64 bits integer)
Ex: 10254851234
Yes
type operation_type =
ABANDON, RES_CANCELLATION, REFUND_ORGANIZATION, RESERVATION, SALE, REFUND_CLIENT, PRE_SALE
Char(25) Yes
kind operation_kind=
DELIVERY_OVERHEADS, CHARGES, ORDER_OVERHEADS, PAYMENT_OVERHEADS, OVERHEADS_REBATE, SINGLE_ENTRY, SIMPLE_PRODUCT, COMPOSED_PRODUCT, PRODUCT_COMPOSITION
Char(25) Yes
Accounting_code Accounting code of related entity, as set inside mapping table. Char(60)
Ex: 485-700
Yes
Accounting analytical code 1/2/3/4 Accounting code of related entity, as set inside mapping table Char(60)
Ex: SAISAISAI
No
name Related entity name Char(60)
Ex: La traviata
Yes
Product family Id_Code of product family Ex: INT_PRODFAM/STDYNAMIC Yes
item_date Date of the performance in case related entity is an event DateTime
Ex: 2015-12-24 20:30
No
amount Amount including VAT.
Can be positive or negative
Amount
Ex: 12.55
Yes
Vat_rate VAT Rate applied on this operation Percentage
Ex: 5.5%
Yes
Vat_code VAT code applied on this operation Char(8)
Ex: TSR
Yes
Operator_name Operator’s login (or internet sales channel code) Char(20)
Ex: F. Martin, MOSA_INTERNET
Yes
Contact_number SecuTix contact number of the purchasing contact Char(20)
Ex: 0123465
No
payment_sale "P" for Payment
"S" for Sale
Char(20) Yes

Selling a fixed price season tickets will generate one line with the actual price as a Debit, then each time this fixed price season ticket will be used to “pay” a performance, there will be a Debit for the performance and a Credit on the fixed price season ticketCalculated season tickets always have an amount of 0, the real amounts are provided by their content (PRODUCT_COMPOSITION).

Payment line description

Each line corresponds to one payment inside SecuTix.

Name Description Type Mandatory
reference_date Date of the sales (taking into account organization’s beginning of day) date
Ex.: 2009-11-06
Yes
order_id Order identifier (can be null) Long (64 bits integer)
Ex : 10254851234
No
line_id Technical identifier of the payment (=Payment_id) Long (64 bits integer)
Ex : 10254851234
Yes
kind Payment method short name Char(8) Yes
type* Payment_kind =
DEPTOR_PROFIT, DIRECT_PAYMENT, REFUND, CREDIT_WAIT_ACCOUNT, DEBIT_WAIT_ACCOUNT, CREDIT_CREDIT_NOTE, DEBIT_CREDIT_NOTE, REFUND_CREDIT_NOTE, CANCELLATION
Char(25) Yes
Accounting_code Accounting code of related entity, as entered inside correspondence table Char(60) Yes
Accounting analytical code 1/2/3/4 Accounting code of related entity, as set inside mapping table Char(60)
Ex: SAISAISAI
No
name Related entity name Char(60)
Ex: Chèque
Yes
Product family Always empty No
item_date null null No
amount Payment Amount.
Can be positive or negative
Amount
Ex: -6512.55
Yes
Vat_rate Always empty No
Vat_code Always empty No
Operator_name Operator’s login Char(20)
Ex: F. Martin
Yes
Contact_number SecuTix contact number of the purchasing contact Char(20)
Ex: 0123465
No
payment_sale "P" for Payment
"S" for Sale
Char(20) Yes

*: in case of a payment method (eg gift cards) when excess amount is possible and not returned to the client, a special line will be added to balance accounts. This line will have type = DEPTOR_PROFIT, kind being the real payment method’s code, and dedicated (overriding the regular one of this payment method) accounting_code can be defined using syntax: 99999|a1#DEPTOR_PROFIT#88888|b3 where 99999 defines the regular accounting code and a1 the first analitic cde and 888888 the accounting code in this specific case with b3 being its analytical code.

Examples

Example 1

Sales on the internet of 2 tickets of the same performance, at 2 different tariffs, paid by credit card:

51;OD;2015-03-05;4191;|Vte Over the Cloud C-05/03/15 20:30:00 |Com #79206 |Op MANEGE;10.00;1;;;;;;;;;;E
51;OD;2015-03-05;4191;|Vte Over the Cloud C-05/03/15 20:30:00 |Com #79206 |Op MANEGE;6.00;1;;;;;;;;;;E
51;OD;2015-03-05;588;|Enc direct|Meth CBWEB |Com #79206 |Op MANEGE;16.00;2;;;;;;;;;;E

Accounting export example 1

The highlighted yellow part if a hardcoded prefix+suffix.
The date is in red.
Accounting codes are in green.
Order number is in dark purple while operator name is in light purple. Those fields are concatenated using a custom separator | while other fields are separated with ;. Also note that order number is prefixed by “Com #”
Product name + performance date are concatenated and in orange, underlined part is a hardcoded prefix.
Amounts are highlighted in cyan.

The 2 first lines are sales lines and debits (underlined 1) while the 3rd is a payment and a credit (underlined 2).

Example 2

An identified contact makes an order on the box-office and buys 1 ticket for a friend to be sent by post
D|JOURN_28|01/02/15|Le Mariage de Figaro|64997|REYNALD|760-15|879|CLASS|2800|123456
D|JOURN_28|01/02/15|Frais envoie postal|64997|REYNALD|475|451|ENV|350|123456
C|JOURN_28|01/02/15|Cheque|64997|REYNALD|590||REM|3150|56487

Accounting export example 2

Same colours as previously :

  • Highlighted yellow : hardcoded
  • Red : date (format DD/MM/YY)
  • Green : accounting code, containing analytic codes concatenated with | (same as field separator)
  • Dark purple : Order number
  • Light purple : operator name
  • Orange: Product name (but performance date is not provided)
  • Highlighted cyan: amounts (provided in cents)

And this one also provides :

  • Product name + performance date
  • Highlighted pink: contact number