Return File 3.2

1. Goals
2. Definitions
3. File Structure
4. Record format
   • Record 0 – Header
   • Record 1 - Sale Details
   • Record 10 - Credit Details
   • Record 2 – Adjustments
   • Record 100 – Anticipation Batch
   • Record 200 – Anticipated transactions
   • Record 9 – Trailer
5. Remark
   1. Limitations
   2. Template for saving to database
6. Code table
7. Version Changes

1. Goals

• The detailed return file generated on disk is intended to allow the custommer's back-end syston to download the outstanding 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 outstanding 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 generated consolidated 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 "Report" interface, requiring only the
  “Start Date”, “End Date” and version information.

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

• Os arquivos também podem ser gerados em banco de dados desde que, previamente
  configurados e criadas suas respectivas tabelas(1 tabela para cada tipo de registro) com
  colunas na correspondentes as colunas configuradas para cada um dos registros.

  Important: Any and all managonent in this database and/or tables is the customer's responsibility, the Syston only feeds the previously existing tables. If they do not exist, an error will be returned when generating the file. More details in iton 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 Receipt 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,
  Anticipation Batch, Advance Transactions 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 will be generated.

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

  1. Sale Date
  2. Idt Rede
  3. Codlojasitef
  4. TipoProduto
  5. NSU SiTef
  6. Installment Number

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

  1. Credit Date
  2. Idt Rede
  3. Codlojasitef
  4. TipoProduto
  5. NSU SiTef
  6. Installment Number

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

  1. Adjustment Date
  2. Idt Rede
  3. Codlojasitef

-The ordering, all ascending, of the records for the Anticipation Batch field will be:

1. Anticipation Credit Date
2. Idt Rede
3. Codlojasitef
4. Summary number
5. Installment

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

  1. Sale Date
  2. Idt Rede
  3. Codlojasitef
  4. TipoProduto
  5. NSU SiTef

• The default filename format for saving to disk will be:

  • Detailed file: “Det<Nome_Rede><Data_Atual>.csv”

  • Where:

    • <Nome_Rede>, Network Name, or “miscellaneous” when two or more authorizers are selected.
    • <Data_Atual> in the format YYMMDD.

• The output to database requires the creation of a table for each type of Detail Record.

3. File Structure

Saved on disk:

0 – Header
    1 – Sale Details*
    .
    .
    10 – Credit Details*
    .
    .
    2 – Adjustments
    .
    .
    100 – Anticipation Batch
    .
    .
    200 – Anticipated Transactions
    .
    .
9 - Trailer

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

Saved in a database, they need 5 tables "Sales", "Credit", "Adjustments", "Anticipation Batch" and "Anticipated Transaction":

1 – Sale Details*
.
.
10 – Credit Details*
.
.
2 – Adjustments
.
.
100 – Anticipation Batch
.
.
200 – Anticipated Transactions
.
.

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

4. Record format

Record 0 – Header

Record 1 - Sale 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

* For NON-TEF transactions, this field is filled 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 100 – Anticipation Batch

Record 200 – Anticipated Transactions

* For NON-TEF transactions, this field is filled with the information on the Receipt Number (TA06).

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

Record 9 – Trailer

5. Remark

1. Limitations

• Due to limitations of the data sent by the administrators, the Amex, Visa and Redecard have their projected installments.

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_Record 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_Record VARCHAR2 (0 2 ) 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_Record 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
);

CREATE TABLE tab_lote_antec
(
tipo_Record VARCHAR2 (03) NULL,
cod_estab VARCHAR2 (15) NULL,
codigo_loja VARCHAR2 (08) NULL,
num_resumo VARCHAR2 (10) NULL,
data_credito_antec VARCHAR2 (08) NULL,
valor_pago VARCHAR2 (16) NULL,
idt_rede VARCHAR2 (03) NULL,
num_parcela VARCHAR2 (02) NULL,
data_credito_orig VARCHAR2 (08) NULL,
valor_liquido_orig VARCHAR2 (16) NULL,
valor_bruto VARCHAR2 (16) NULL,
cod_banco VARCHAR2 (06) NULL,
cod_agencia VARCHAR2 (06) NULL,
num_conta VARCHAR2 (15) NULL,
seq VARCHAR2 (06) NULL
);

CREATE TABLE tab_trn_antec
(
tipo_Record VARCHAR2 (0 3 ) 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,
tipo_produto VARCHAR2 (01) NULL,
captura VARCHAR2 (01) NULL,
idt_rede VARCHAR2 (03) 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
);

6. Code Table

Code Table

7. Version Changes

• Version V3. 2 on 05/24/2012 –Responsible : Adriano.

  • Inclusion of the "Gross Value" column in the record 100
  • Version V3. 1 on 05/16/2012 –Responsible : Adriano.
  • Inclusion of comment in Record 10 column C11 - Net Value
  • Inclusion of new network codes

• Version V3. 1 on 12/08/2011 –Responsible : MKK.

  • Inclusion of "Anticipation" Batch and "Anticipated Transactions" Batch Records

• 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 to the 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/27/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- 11/06/2008)

  • Invoice Code Inclusion

• Version 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 Identifier 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" NON 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, bank branch, Checking account, Codlojasitef columns in the Detailing and Adjustments;

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

• Version V1.2 on 05/04/2004 –Responsible : Marcus Adolfo Lucio

• Version for V1.1;

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

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