Return File 3.0

1. Goals
2. Definitions
3. File Structure
4. Record format
   • Record 0 – Header
   • Record 1 - Sale Details
   • Record 10 - Credit Details
   • Record 2 – Settings
   • Record 9 – Trailer
5. Observation
   1. Limitations
   2. Template for saving to database
6. Attachment look in the Code Table tab
7. Version Changes

1. Goals

• The detailed return file generated on disk is intended to allow the customer's back-end system to download outstanding bank slips, tickets or bills. '

Target Audience: Any and all customers that need to save data to disk.

• The detailed return file generated in the customer's database is intended to allow
  that the back-end system can download the bank slips, tickets or bills to a table
  created and managed by the customer.

Target Audience: Environments that use database as an interface and are managed by > DBAs.

• The consolidated generated file is used to check the values ​​recorded in the map of
  store cash against the value recorded by SiTef, for payment transactions with
  credit, debit and benefit cards.

2. Definitions

• The generation of the file is done through the interface "SiTefWeb Conciliation Reports", being
  only necessary the information of the "Start Date" and "End Date".

• Files saved to disk will be generated in CSV format, that is, the fields are
  semicolon separated (“;”).

• Files can also be generated in a database provided that, in advance,
  configured and created their respective tables (1 table for each type of record) with columns
  in the order of the corresponding layout respecting its limit.

Important: Any and all management in this database and/or tables is the customer's responsibility, the System only feeds the previously existing tables. If they do not exist, an error will be returned when generating the file. Further details in item 5.2.

• Only the fields available according to each network are present.

• In case there is no content for the field, for files saved to disk, there will be the
  separator (“;”), making the number of fields of each “Fixed” record independent of the network.
  In the case of output to database, the field will be sent as “blank”.

• In the case of Non-TEF transactions, the NSU Sitef field will contain the Voucher Number.

• If the user selects the date type as "Sale Date", only the Record data
  Sale details (in addition to Header and Trailer) will be filled. This change has occurred so
  that the created file is immutable regardless of the present date.
  In the case of "Credit Date", the other fields will be filled, they are: Credit Details
  and Adjustments, in addition to the Header and Trailer;

• In the case of output to database, neither Header nor Trailer will be generated, only
  Details.

• The ordering, all ascending, of the records for the Sale Details field will be:

  1. Sale Date
  2. Network ID
  3. Codlojasitef
  4. ProductType
  5. NSU SiTef
  6. Installment Number

• The ordering, all ascending, of the records for the Adjustment field will be:

  1. Adjustment Date
  2. Network Id
  3. Codlojasitef

3. File Structure

Saved to disk:

0 – Header
    1 – Sale Date*
    .
    .
    10 – Credit Details*
    .
    .
    2 – Adjustments
    .
    .
9 - Trailer

* For transactions in installments, one (1) record of this type will be generated per installment.

Saved in a database, they need 3 tables "Sales", "Credit" and "Adjustments” :

1 – Sale Details*
.
.
10 – Credit Details*
.
.
2 – Adjustments
.
.

* For transactions in installments, one (1) record of this type will be generated per installment.

4. Record format

Record 0 – Header

Record 1 - Sales Details

*For NON-TEF transactions, this field is filled in with the Voucher Number information (V06).

** In the case of Redecard Debit, Goodcard and Tecban, this data is not available in electronic statements.

Record 10 - Credit Details

| C12 - Valor Líquido Original | 0 | 16 | No cent separators
Original installment value, if paid in installments. Mainly used to indicate a cancellation by Redecard. Empty if there is no unscheduling. |
| C13 - Credit Date | 8 | 8 | AAAAMMDD |
| C14 - Original Credit Date | 0 | 8 | AAAAMMDD Mainly used to indicate a Visa acceleration. Empty if there is no acceleration |

| C15 - Installment number | 2 | 2 | Installment Number |
| C16 - Product Type | 1 | 1 | ‘C’ - Credit ‘D’ – debit ‘V’ – voucher ‘S’ – withdraw
|
| C17 – Capture | 1 | 1 | ‘0’, ‘2’, ‘3’ – SiTef ‘1’, ‘4’, ‘5’ – Others
|
| C18 - Network Id Code | 1 | 3 | [see table I](code table)
|
| C19 - Bank Number | 1 | 6 | ‘’ |
| C20 - bank branch number | 1 | 6 | ‘’ |
| C21 - checking account number | 1 | 15 | ‘’ |
| C22 - commission amount | 3 | 16 | No cent separators |

| C23 - Service Fee Value | 3 | 5 | No cent separators Ex: 199 = 1,99 % |
| C24 - CodLojaSiTef | 8 | 8 | |
| C25 - Authorization Code | 0 | 12 | ** |
| C26 - invoice | 0 | 20 | Invoice

| C27 - Credit Card Company Code | 4 | 4 | [see table II](code table)
| C28 - File Record Sequence | 6 | 6 | |

* For NON-TEF transactions, this field is filled in with the receipt Number information (C06).

** In the case of Redecard Debit, Goodcard and Tecban, this data is not available in electronic statements.

Record 2 – Adjustments

Record 9 – Trailer

5. Remark

1. Limitations

• Due to limitations in the data sent by the administrators, the Amex, Visa and Redecard networks have their projected installment.

2. Template for saving to database

• 1st step is to create 3 tables for each record, Sale, Credit and Adjustments, with their respective columns.
• examples for Oracle:

CREATE TABLE tab_venda
(
tipo_registro VARCHAR2 (01) NULL,
id_cliente VARCHAR2 (30) NULL,
cod_estab VARCHAR2 (15) NULL,
data_venda VARCHAR2 (08) NULL,
num_resumo VARCHAR2 (10) NULL,
num_comprovante VARCHAR2 (12) NULL,
nsu_sitef VARCHAR2 (12) NULL,
num_cartao VARCHAR2 (19) NULL,
valor_bruto VARCHAR2 (16) NULL,
total_parcelas VARCHAR2 (02) NULL,
valor_liquido VARCHAR2 (16) NULL,
data_credito VARCHAR2 (08) NULL,
num_parcelas VARCHAR2 (02) NULL,
tipo_produto VARCHAR2 (01) NULL,
captura VARCHAR2 (01) NULL,
idt_rede VARCHAR2 (03) NULL,
cod_banco VARCHAR2 (06) NULL,
cod_agencia VARCHAR2 (06) NULL,
num_conta VARCHAR2 (15) NULL,
valor_comissao VARCHAR2 (16) NULL,
taxa_servico VARCHAR2 (05) NULL,
codigo_loja VARCHAR2 (08) NULL,
codigo_autoriz VARCHAR2 (12) NULL,
cupom_fiscal VARCHAR2 (20) NULL,
codigo_bandeira VARCHAR2 ( 0 4) NULL,
data_venda_sitef VARCHAR2 ( 08 ) NULL,
hora_venda_sitef VARCHAR2 ( 06 ) NULL,
seq VARCHAR2 (06) NULL
);

CREATE TABLE tab_credito
(
tipo_registro VARCHAR2 (01) NULL,
id_cliente VARCHAR2 (30) NULL,
cod_estab VARCHAR2 (15) NULL,
data_venda VARCHAR2 (08) NULL,
num_resumo VARCHAR2 (10) NULL,
num_comprovante VARCHAR2 (12) NULL,
nsu_sitef VARCHAR2 (12) NULL,
num_cartao VARCHAR2 (19) NULL,
valor_bruto VARCHAR2 (16) NULL,
total_parcelas VARCHAR2 (02) NULL,
valor_liquido VARCHAR2 (16) NULL,
valor_liquido_orig VARCHAR2 (16) NULL,
data_credito VARCHAR2 (08) NULL,
data_credito_orig VARCHAR2 (08) NULL,
num_parcelas VARCHAR2 (02) NULL,
tipo_produto VARCHAR2 (01) NULL,
captura VARCHAR2 (01) NULL,
idt_rede VARCHAR2 (03) NULL,
cod_banco VARCHAR2 (06) NULL,
cod_agencia VARCHAR2 (06) NULL,
num_conta VARCHAR2 (15) NULL,
valor_comissao VARCHAR2 (16) NULL,
taxa_servico VARCHAR2 (05) NULL,
codigo_loja VARCHAR2 (08) NULL,
codigo_autoriz VARCHAR2 (12) NULL,
cupom_fiscal VARCHAR2 (20) NULL,
codigo_bandeira VARCHAR2 ( 0 4) NULL,
data_venda_sitef VARCHAR2 ( 08 ) NULL,
hora_venda_sitef VARCHAR2 ( 06 ) NULL,
seq VARCHAR2 (06) NULL
);

CREATE TABLE tab_ajuste
(
tipo_registro VARCHAR2 (01) NULL,
cod_estab VARCHAR2 (15) NULL,
data_ajuste VARCHAR2 (08) NULL,
valor_ajuste VARCHAR2 (16) NULL,
valor_liquido_ajuste VARCHAR2 (16) NULL,
num_resumo VARCHAR2 (10) NULL,
num_cartao VARCHAR2 (19) NULL,
num_comprovante VARCHAR2 (12) NULL,

data_venda VARCHAR2 (08) NULL,
cod_ajuste VARCHAR2 (15) NULL,
descrição_ajuste VARCHAR2 ( 64 ) NULL,
num_referencia VARCHAR2 (1 5 ) NULL,
num_resumo_orig VARCHAR2 (1 0 ) NULL,
mês_referencia VARCHAR2 (0 6 ) NULL,

idt_rede VARCHAR2 (03) NULL,
cod_banco VARCHAR2 (06) NULL,
cod_agencia VARCHAR2 (06) NULL,
num_conta VARCHAR2 (15) NULL,
valor_comissao VARCHAR2 (16) NULL,
taxa_servico VARCHAR2 (05) NULL,
codigo_loja VARCHAR2 (08) NULL,
seq VARCHAR2 ( 0 6) NULL
);

• Finally, in the "Detailed Return File" module, a new "Format" option will be presented
  Database, with this item selected, the save will be made in the newly created tables.

6. Code table

[Code Table](code table)

7. Version Changes

• Version V3.0 on 09 /27 /2011 – Responsible : Adriano.

  • Inclusion of the SITEC Sale Date and SITF Sale Time columns in records 1 and 10

• Version V3.0 on 08/29/2011 – Responsible : Adriano.

  • Templates for saving to database

• Version V3.0 on 05/27/2011 – Responsible : Adriano.

  • Correction in the maximum sizes of fields V07 Record 1 and C07 Record 10 of 9
    for 12 characters

• Version V3.0 on 10/03/2011 – Responsible : Adriano.

  • Inclusion of a new column: Credit Card Company Code, in Type 1 and 10 Records
  • Possibility of saving in database.

• Version V2.0 on 03/04/2011 – Responsible : Adriano.

  • Inclusion of new Network codes

• Version V2.0 on 04/09/2010 – Responsible : Adriano.

  • Inclusion of Capture =5 as Non-TEF

• Version V2.0 on 01/07/2010 – Responsible : Adriano.

  • Inclusion of the comment in columns V07 and C

• Version V2.0 on 09/01/2009 – Responsible : MKK.

  • Inclusion of the product type = "S"(Withdraw)

• Version V2.0 on 01/21/2009 – Responsible : MKK.

  • Creation of the Credit Date and Sale Date record

  • Deleting the Negative Values ​​record

  • Inclusion of the withdraw product type ('S') for Redecard and Visanet

• Version V1.9 on 11/06/2008 – Responsible : MKK. ( Return file version: 1.6. 3 - 11/06/2008)

  • Modification in the Redecard Installment form

• Version V1.8 on 11/06/2008 – Responsible : Adriano. ( Return file version: 1.6.1-06/11/2008)

  • Inclusion of Invoice Code

• Versão V1.7 on 10/09/2008 – Responsible : MKK.

  • Inclusion of the Authorization Code

• Version V1.6 on 01/31/2008 – Responsible : Adriano Galati.

  • Changing the Network Identity Code column with new codes

• Version V1.5 on 04/20/2007 – Responsible : Adriano Galati.

  • Inclusion of product type "V" - voucher

  • Inclusion of captures "2", "3" - TEF and "4" NOT TEF on 06/08/2007 - Adriano

  • Changing the delimiter to“;” (RMISERVER v. 1.5.3 06/11/2007)

• Version V1.4 on 08/01/2006 – Responsible : Adriano Galati.

  • Inclusion of the identRede column in the Header;
  • Inclusion of a new type of record 3 – Negative sales;
  • Inclusion of Bank, Agency, checking account number, Codlojasitef columns in the Detailing and Adjustments;

• Version V1.3 on 07/05/2005 – Responsible : Adriano Galati.

• Version V1.2 em 05/04/2005 – Responsible : Marcus Adolfo Lucio

• Version for V1.1;

  • Inclusion of record type '2' - Adjustment record;

• Version V1 on 01/05/2003 - Responsible: Alexandro Alves Moreira