Accounting Exports
Overview
Accounting export are a specialized version of Flat File Exports.
S-360 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.
S-360 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 S-360 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)
The process stages are:
- S-360 generates files from ticketing system (incl. orders + payments).
- S-360 puts the files on an FTP or sFTP server (or exposes the files through an HTTPS URL – with basic-authentication)
- OPTIONAL: one tool – NOT provided by S-360 - transforms the data. Ideally, this step is avoided.
- Data is inserted inside the accounting system. This step is not part of S-360 supplied perimeter.
Conventions
Colors have been used through the diagrams:
- Blue is used to depict S-360 components.
- Green is used to depict third party components (not under S-360 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 S-360 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 S-360 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 S-360 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 S-360 screens.
Generated files access
Generated files:
- Can be fetched manually by an operator inside the execution screens
- Are kept on S-360 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 S-360.
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 S-360.
| 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 | S-360 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 S-360.
| 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 | S-360 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
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
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