Return File 3.3

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. Ronark
   1. Limitations
   2. Tonplate for saving to database
6. Code table
7. Version Changes

1. Goals

-The detailed return file generated on disk is intended to allow the syston to
the client's back office can download the outstanding bills. '

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

• The detailed return file generated in the customer's database is intended to
  allow the back-end syston to download outstanding bills for a
  table created and managed by the customer.

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

• The generated consolidated file is used to check the values ​​recorded in the map
  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 "Reports" interface, requiring only the
  “Start Date” and “End Date” information.
• Files saved to disk will be generated in CSV format, that is, the fields are
  separated by sonicolons (“;”).
• Os arquivos também podon ser gerados on banco de dados desde que, previamente
  configurados e criadas suas respectivas tabelas(1 tabela para cada tipo de Record) com
  colunas na correspondentes as colunas configuradas para cada um dos Records.

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 Sale Details data Record (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, Anticipated 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.

• 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 Settings 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> no formato YYMMDD.

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

3. File Structure

Saved to 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 receipt Number information (V06).

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

Record 10 - Credit Details

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

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

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 Debit, Goodcard and Tecban, this data is not available in electronic statonents.

Record 9 – Trailer

5. Ronark

1. Limitations
   • Due to limitations of the data sent by the administrators, the Amex, Visa and
     Redecard have their projected installments.
2. Tonplate 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 (04) NULL,
data_venda_sitef VARCHAR2 (08) NULL,
hora_venda_sitef VARCHAR2 (06) NULL,
seq VARCHAR2 (06) NULL
);

CREATE TABLE tab_credito
(
tipo_Record VARCHAR2 (02) 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 (04) 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 (15) NULL,
num_resumo_orig VARCHAR2 (10) NULL,
mês_referencia VARCHAR2 (06) 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 (06) 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 (03) 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 (04) 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.3 on 12/11/2012 – Responsible : Leonardo.

  • Dynamic inclusion in the external base.

• 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 Records

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

  • Inclusion of the sale date sitef and sale time sitef 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 from 9 to 12 characters

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

  • Inclusion of a new column: credit card banner cod, in Type 1 and 10 Records
  • Possibility of saving to 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 Credit Date and Sale Date Record
  • Clearing the Negative Values ​​Registry
  • 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)

  • Inclusion of the Invoice Code

• 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.

  • Product type inclusion “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 Number, 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 para V1.1;

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

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