Introduction
Sherlock's is a secure multi-channel e-commerce payment solution that complies with the PCI DSS standard. It allows you to accept and manage payment transactions by taking into account business rules related to your activity (payment on despatch, deferred payment, recurring payment, payment in instalments, etc.).
The purpose of this document is to explain the implementation steps of the Sherlock’s Office Batch solution and of initial payment and cash management tests.
Who does this document target?
Sherlock’s Office Batch aims to provide you with Sherlock’s Office functions in Batch mode. These functions are bases on offline file exchanges. Some of the online mode options are not available, e.g. 3-D Secure authentication.
This implementation guide is intended for your technical team.
To get an overview of the Sherlock's solution, we advise you to consult the following documents:
- Functional presentation
- Functionality set-up guide
Prerequisites
Knowledge of file transfer protocols and standards for programming languages used today, such as Java, PHP or .Net, is necessary to develop a connection to Sherlock’s Office Batch.
Understanding payments with Sherlock’s Office Batch
The processing of files by Sherlock’s Office Batch can be divided into several steps:
1. The merchant uploads request files to an external FTPS or SFTP account supplied by LCL.
2. LCL's file transfer gateway receives the request files and sends them to the Sherlock’s Office Batch engine.
3. The Sherlock’s Office Batch engine processes the request files one by one and generates one response file per request file.
4. The Sherlock’s Office Batch engine sends the response files to the external FTPS or SFTP account through the file transfer gateway.
5. The merchant retrieves the response files from the external FTPS or SFTP account supplied by LCL.
6. The Sherlock’s Office Batch engine, via the file transfer gateway, destroys the response files after a first successful download by the merchant.
General rules about transfers
- The merchant can choose either FTPS or SFTP as transfer method.
- LCL provides a dedicated merchant account (with a username and password). The LCL account must be the same for request files and response files; however some file name restrictions apply.
- LCL's SFTP and FTPS servers also verify the merchant’s IP address in addition to their username and password.
- LCL gives the response file a name that differs from that of the request file.
- After a given period (1 week), response files are deleted from the FTPS or SFTP accounts even if they were not downloaded.
Managing the operations from several merchants
The remitter is a partner who acts as a technical operator managing the file exchanges with the Sherlock's payment platform. A remitter can send the operations from several merchants in the same file provided these operations are declared in the name of this remitter during the registration step.
It should be noted that a remitter can also be a merchant themselves.
Understanding the format of the exchanged files
The request and response files exchanged with Sherlock’s Office Batch are in XML format.
Each file consists of four successive sections:
- FILE TYPE corresponds to the file type (read the next section for an explanation of the various types)
- HEADER contains the file header
- BODY contains all operations
- END marks the end of the file
The file header contains an ID in the form of a sequence number. This sequence number must be:
- numerical
- unique for all your files (with no time limit)
- ascending i.e. must start at 1 and increase from 1 to 1
The file body contains several records. A record corresponds to a transaction or cash management operation, or a related function (such as wallet management, tokenisation or fraud management).
- Sample request or response file main structure:
FILE;…
HEADER;…
<OPERATION>;value1;value2;…
<OPERATION>;value1;value2;…
END;…In the CSV format, each record is prefixed with the uppercase name of the element type.
Field values of each element are specified one after the other and are separated by semicolons, without spaces and without being prefixed with their names. The order of these fields must be complied with.
Several response files can be processed over the same day. When several request files are available on the external FTPS account, Sherlock’s Office Batch processes them one by one successively (not simultaneously) and in their order of arrival on the FTPS account.
There is one response file for each request file even if the file processing generates errors.
General rules about the exchanged files
- The file size cannot exceed 100 Mb or 100,000 operation records.
- A file is dedicated to a single remitter.
- A request file cannot contain several operations on the same transaction. For instance, a transaction cannot be created and cancelled in the same request file.
- The order of operations in the body of the response file can differ from the order of operations in the request file.
The CSV syntax contains specific functions that will be described in detail throughout this document. In this syntax, the order of fields must be complied with.
Please get in touch with your usual contact if the 100 MB or 100 000 records limit causes constraints in your integration.
Understanding the format of the request file
The file type is based on the service used.
All the fields of the “file type” element of the request are mandatory and in ANS20 format. They are returned unchanged in the response.
- The tag name is file.
- The type field must be request for the request and response for the response.
- The format and version fields depend on the type of service called.
| Format | Version | Service description |
|---|---|---|
| office | The value must 19 | Acceptance of transactions and cash management operations. |
| token | The value must be 1 | PAN tokenisation and detokenisation. |
- Sample file type:
...
FILE;request;office;19
...The header is based on a record that contains the following fields:
| Fields | Presence | Format | Description | CSV field number |
|---|---|---|---|---|
| The tag name is header | Mandatory | ANS20 | Indicates a header record. | 1 |
| remitterId | Mandatory | N15 | Remitter's ID. | 2 |
| date | Mandatory | XML Date | Date, in the merchant's time zone, on which the file was created (YYYY-MM-DD+hh:mm). | 3 |
| time | Mandatory | XML Time | Time, in the merchant's time zone, on which the file was created (hh:mm:ss+hh:mm). | 4 |
| sequence | Mandatory | N6 | File sequence number. You may add leading zeros on the left (e.g. 000001 for the first sequence number). | 5 |
- Sample header:
...
HEADER;023101122334455;2012-06-11+0200;14:28:00+0100;86
...The body contains operations according to the service declared in the file element. Please refer to the next section for details on the fields for every operation.
In the CSV format, there is no special header for the “body” section of the file: all operations are specified directly after the “header” section.
- Sample "body" section for the office service
…
CARDORDER;1;012323232323231;SIM201206810160;1000;0;VALIDATION;470;
201201;209910;4975497549754975;1;978;test@worldline.com;123;127.0.0.1;
2012-11-29T17:04:30Z;INTERNET;123456;;context;;origin;all;;;;;;;;;FRA
;;PAN;;;;;;VISA;APPLIED_DEFAULT;;;;;;
...The final section is based on a single record that contains the following fields:
| Fields | Presence | Format | Description | CSV field number |
|---|---|---|---|---|
| The tag name is end | Mandatory | ANS20 | Indicates the end of the record. | 1 |
| nbRecord | Mandatory | N6 | Number of operations in the "body" section. | 2 |
- Sample "end" element
END;227Request file naming scheme
The request file must be sent in a ZIP archive. The archive name must be OFBREQxx.ZIP, where "xx" is a number between 01 and 99.
An archive must only contain one request file.
You can name this request file freely; however we recommend you to comply with the following naming scheme: SOB.Alias.Date-Time.csv
where:
- SOB is the request file to be sent to Sherlock’s Office Batch
- Alias is the Sherlock's alias/merchantId of the remitting shop
- Date is the file date in YYMMDD format
- Time is the file time in HHMMSS format.
Understanding the format of the response file
The header is based on a record that contains the following fields:
| Fields | Format | Description | CSV field number |
|---|---|---|---|
| The tag name is header | ANS20 | Indicates a header record. | 1 |
| remitterId | N15 | Remitter's ID. | 2 |
| date | XML Date | Date, in the merchant's time zone, on which the file was created (YYYY-MM-DD+hh:mm). | 3 |
| time | XML Time | Time, in the merchant's time zone, on which the file was created (hh:mm:ss+hh:mm). | 4 |
| sequence | N6 | File sequence number. | 5 |
| processingResponseCode | AN2 | Processing response code | 6 |
| beginProcessTime | ANS25 ISO8601 |
File processing starting timestamp, in the merchant's time zone. | 7 |
| endProcessTime | ANS25 ISO8601 |
File processing ending timestamp, in the merchant's time zone. | 8 |
- Sample header:
...
HEADER;023101122334455;2012-06-11+0200;14:28:00+0100;86;00;
2012-06-07T11:30:47+02:00;2012-06-07T11:31:43+02:00
...The “error-details” element is only returned when an error occurs during the checking of the request file. “error-details” is a character string that describes the error.
- Sample header:
...
ERRORDETAILS;ERROR_FILE_ALREADY_PROCESSED: processing_response_code = [02] : Error in the file sequence number. The file has already been processed.
Expected sequence number [2] - Request file sequence number [1]
...The response file contains one element for each operation of the request file. The attributes of this element are filled in during the return. The returned fields are described in the next section.
The end of the response file is similar to the end of the request file and contains the following fields:
| Fields | Format | Description | CSV field number |
|---|---|---|---|
| The tag name is end | ANS20 | Indicates the end of the record | 1 |
| nbRecord | N6 | Number of operations in the "body" section | 2 |
- Sample "end" element:
END;227Response file naming scheme
The response file is sent in a ZIP archive. This archive is called s******.OFBREP**.zip.
Where:
- s****** is a unique and non-customisable sequence number.
- OFBREP** is a number between 01 and 99; this number is identical to that of the request file.
The name of the response file contained in the archive complies with the following naming scheme: OFFUBZ.OFFBAREP.$alias.$date (for instance OFFUBZ.OFFBAREP.MM20LEQUIPE0861.181216).
Where:
- $alias is the Sherlock's alias of the webshop.
- $date is the file processing date in YYMMDD format.
Reconciliation of request and response files
To make it easier to reconcile request and response files, each request file is identified by a sequence number that is also returned with the response.
All the fields of the request file are also returned in the response file, except for the following fields because of compliance with the PCI DSS standard:
- cardNumber can be returned hidden in the maskedPan field.
- cardEffectiveDate (card effective date).
- cardExpiryDate (card expiry date),
- cardSeqNumber (card sequence number if present),
- cardCSCValue (card security code).
Sherlock's can modify the value of the following field if the creation of the transaction is followed by a banking authorization:
- transactionDate
Getting started with Sherlock’s Office Batch in 4 steps
Step 1: requesting the creation of a FTPS account
To this end, you must return the Sherlock’s Office Batch registration form given to you by your LCL technical contact. The creation of a FTPS account takes about 12 days from the receipt of the form if the latter has been filled in properly.
E-mails are then exchanged to test the FTPS account in validation mode before implementation in the production environment.
Step 2: using the available functions
The various possible functions are the subject of specific requests. The list of the functions and the details of the requests and responses are available on this page.
A request consists of generic fields and container-type fields.
A container is a data structure that groups data functionally.
If a field is that of a container, it is named <container name>.<field name>.
Analysing errors when checking the file
There are several response code levels when Sherlock’s Office Batch processes a file. Several overall checks are carried out before the file is processed. If one of these checks fails, the file is completely rejected. (the processingResponseCode is neither 00 nor 01).
The returned response file contains the overall processing result code in the processingResponseCode field, in the file header.
Overall processing result codes
| Code | Meaning |
|---|---|
| 00 | File processed correctly. The file contains the list of operations. |
| 01 | File processed correctly. An operation has been associated with a merchant that is not related to the remitter ID. The operation will set the value of the officeBatchResponseCode field to 80. |
| 02 | File already processed The file sequence number is lower than it should be. The correct number is returned in the message that describes the error. |
| 03 | Sequence broken in the file sequence number. The file sequence number is higher than it should be. The correct number is returned in the message that describes the error. |
| 04 | Technical issue. Internal issue |
| 05 | File too big |
| 06 | The number of operations exceeds the maximum authorised amount. The maximum number of operations has been reached. |
| 07 | The number of operations counted is different from the number specified in the nbRecord field. |
| 08 | Duplicate operation |
| 09 | Incorrect record |
| 10 | Incorrect file format. The file format is not correct (the error description will be returned in the error-details tag). |
| 11 | Incorrect remitter. The remitter declared in the header is not correct. |
| Autres codes | Incorrect file (these codes apply to older Sherlock’s Office Batch versions). |
| Response code | Cause | Solution |
|---|---|---|
| Different from 00 and 01 | Restarting processing | The request file must be returned in its entirety with the same file sequence number. |
| 03 | File sequence number interruption | The file was completely refused. If necessary, the sequence number must be corrected and the file returned. |
| 04 | Technical error | An operation caused a technical error. File processing was not interrupted. In this case, the processing can be very fast since all operations with code 25 or 90 will be refused (responseCode field). For now, LCL has not supplied any mechanism to defer the processing until a new connection is established. |
| None | CSV file format error | When the batch file processing service of the version of
the file cannot be identified, or its format is incorrect (e.g.
blank entry in the file, etc.), a generic response is sent to the
merchant. This response looks like this: |
Analysing the errors caused by an operation
Each operation is considered as independent. Each operation has its own stored response code (officeBatchResponseCode code). The code indicates the field that causes the issue.
If an operation fails, the processing is not interrupted. The operation is refused with the typical Sherlock's response code (responseCode field).
| Codes | Fields in question |
|---|---|
| 00 | None (all fields are correct) |
| 01 | merchantId error |
| 03 | transactionReference error |
| 04 | merchantTransactionDateTime error |
| 05 | amount error |
| 06 | captureDay error |
| 07 | captureMode error |
| 08 | operationAmount error |
| 09 | operationOrigin error |
| 11 | currencyCode error |
| 12 | customerIpAddress error |
| 13 | customerEmail error |
| 14 | customerId error |
| 16 | orderId error |
| 17 | orderChannel error |
| 18 | transactionOrigin error |
| 19 | returnContext error |
| 20 | fromTransactionReference error |
| 21 | cardExpiryDate error |
| 22 | cardNumber error |
| 23 | cardCSCValue error |
| 24 | cardEffectiveDate error |
| 25 | cardSeqNumber error |
| 26 | paymentMeanBrand error |
| 27 | authorisationId error |
| 28 | merchantWalletId error |
| 29 | paymentMeanId error |
| 30 | paymentPattern error |
| 31 | number error |
| 32 | statementReference error |
| 33 | panType error |
| 34 | mandateId error |
| 35 | valueDate error |
| 36 | paymentMeanAlias error |
| 37 | account error |
| 38 | bankCode error |
| 39 | transactionActors error |
| 45 | Date fields format error |
| 46 | settlementMode error |
| 47 | comment error |
| 48 | validationIndicator error |
| 50 | s10TransactionId error |
| 51 | s10TransactionIdDate error |
| 52 | s10FromTransactionId error |
| 53 | s10FromTransactionIdDate error |
| 54 | fraudData error |
| 55 | riskManagementDynamicParam error |
| 56 | riskManagementDynamicValue error |
| 57 | riskManagementDynamicSettingList error |
| 58 | fraudListReason error |
| 59 | fraudListType error |
| 60 | fraudListLevel error |
| 61 | fraudListElementType error |
| 62 | fraudListElementValue error |
| 63 | lastRecoveryIndicator error |
| 64 | order context field error |
| 65 | travel context field error |
| 66 | Delivery data field error |
| 67 | Address field error |
| 68 | Contact field error |
| 69 | cardAuthPolicy error |
| 70 | Shopping Cart Detail field error |
| 71 | merchantExternalId error |
| 72 | paymentMeanBrandSelectionStatus error |
| 73 | settlementArchivingReference error |
| 74 | settlementMerchantSpecificData error |
| 75 | fromTransactionAcceptor error |
| 76 | initialAuthenticationCavv error |
| 77 | bancontactMerchantCustomerAuthenticationMethod error |
| 78 | invoiceReference error |
| 79 | subMerchantCategoryCode error |
| 80 | Merchant not registered with Sherlock’s Office Batch or not associated with the remitter declared in the header |
| 81 | subMerchantLegalId error |
| 82 | subMerchantShortName error |
| 83 | subMerchantContractNumber error |
| 84 | subMerchantUrl error |
| 85 | subMerchantAddress error |
| 86 | subMerchantId error |
Step 3: testing in the customer's validation environment
The objective is to validate that the file structure and query syntax are correct. For this first step, there is no connection to the payment acquirer: card authorisation requests are simulated. Transactions are stored in the Sherlock's back office and you can test cash management operations on these transactions.
Contact the technical support to have a shop configured in the validation environement and request that file transfer be implemented between your site and Sherlock's.
Step 4: validating the transition to live operation
First, submit a file containing a limited number of operations to validate the transition to live operation. Check the response file to make sure all operations were carried out correctly:
- Monitor the acceptance rate (number of responseCode 00/total number of operations).
- Check the nature of non-banking refusals:
- technical issue: responseCode 90, 97, 99,
- acquirer fraud: responseCode 34.
