ER_Invoice_Import

General Information

   Caption: 1 – Innovation 2 – Maintenance (you must fill out the other fields for both processes). 

Objective

To develop a routine to register the ABAs sent by the various providers of the travel agency. Besides the regular operations of addition, edition and deletion, the routine must import TXT files, with invoicing data, sent by the airlines or by invoicing consolidators (example: BSP). 

Definition of Business Rule

The Add / Import routine is the register that receives one or more files, regarding the ABAs sent by the airlines or consolidator companies. You can send these ABAs two ways: electronically and physically; either way, you must add/import them through this register.

You can combine the electronic ABAs to contain items from various companies. Generally, you will send them to the agency through a financial or invoicing consolidation company (such as BSP), or send them through the airline itself. In both cases, the system only generates one addition/importation record, in which the key is the ABA number.

Each airline or consolidation company has its own layout. Nevertheless, the ABA items are imported to a single system table with the data duly consolidated. In other words, according to the layout entered, a process is initiated in order to identify the attributes of the ABA with the respective attributes of this table. 

As some companies send various electronic files from the same period, separated by service station (IATA), the system offers a feature for you to import all files from a directory. It is noteworthy that a record is created in the ABAs Table for each file imported, referent to the ABAs sent, correlating each item imported to its source file.

You can import the file(s) of an ABA more than once. To do so, you must delete the record of the ABA previously added/imported, also consequently deleting its respective items. Nevertheless, you can only delete an ABA if no item related to it has been approved. To meet this need, this register offers the option of undoing the item approval processes.

When you add or import an ABA, the system provides a parameter setting interface in which you can enter the import type, the provider (which can be of report), month, year, periodicity, period, issue date, due date, the number, the total value and the layout of the ABA. All such data is stored in a system table.

Electronic ABAs do not require entering the ABA data, as the aforementioned integration process is responsible for identifying the information. Regarding ABAs lacking a number entry, the system generates a provisional number, which you may change if you wish. After reconciling the ABA, you can no longer change this number.

Moreover, in the case of an electronic ABA, the system automatically imports the items into a Tourism module table. Regarding physical ABAs, you must use the same interface to normally add them and, regardless of it being physical, you can record the ABA file name in the register. However, distinctly from an electronic ABA, you must add the items of a physical ABA manually and, after adding them, run the Association process through the button displayed on the import screen.

The layouts offered for adding or importing ABAs consider the information of ABAs from air providers and other types of products sold by the travel agency. When you add or import an ABA, a field is available at the header for registering the layout of origin, from the following options: Airline Locator (GOL), Airline Ticket (BSP) and Airline Manual. 

In ABAs from airlines or consolidator companies, information on issues and reissues pertaining air product sales made by the travel agency. Once the import process is finished, the routine associates the ABA items with the possible and respective Reservation Documents. To accomplish this, Ticket Number and/or Locator data are used in accordance with the layout selected.

The import routine will be elaborated through the MILE tool. For purposes of deploying the SIGATUR module, the layout import prior settings are available in the XML format, to incorporate the layouts to the environment of the customer. Importing in MILE uses a MVC standard adapter, which shares its Model with an ABA addition routine used for manual entries (by typing) for occasions when the airline or the consolidator company does not send interface files, such as the case of Azul Airlines. The same process is used for an ABA.    

Operations

Technical Definition

• To develop the source TURA038 in Model View Controller (MVC) to register Agent Billing Analyses in header and items format, using the data dictionary from tables G6H and G6I.
• To create table G6H - Tourism Prov. Airline ABA:
• G6H_FATURA - ABA code.
• G6H_TIPO - Register/import type.
• G6H_LAYOUT - Layout of registered/imported ABA.
• G6H_MES - ABA reference month.
• G6H_ANO - ABA reference year.
• G6H_PERCID - ABA sending periodicity.
• G6H_PERIOD - Period of ABA reference month.
• G6H_FATFOR - ABA number.
• G6H_EMISSA - ABA issue date.
• G6H_VENCTO - ABA due date.
• G6H_VALOR - ABA value.
• G6H_FORNEC - Code of provider who sent the ABA.
• G6H_LOJA - Store of provider who sent the ABA.
• G6H_NREDUZ - Corporate name of provider who sent the ABA.
• G6H_REPORT - Enter whether the provider is of report.
• G6H_EFETIV - Enter whether the ABA has been activated.
• G6H_DTIMP - Date of ABA registration/import.
• G6H_ARQUIV - Name of ABA file.
• To create table G6I - Tourism Prov. Airline ABA Items:
• G6I_FATURA - ABA code.
• G6I_ITEM - ABA item code.
• G6I_TIPOIT - Item type.
• G6I_NUMLIN - Number of item row in ABA.
• G6I_NUMCIA - Number of airline related to item.
• G6I_SRBILH - Ticket serial.
• G6I_BILHET - Ticket number.
• G6I_DGBILH - Ticket verification digit.
• G6I_LOCALI - Locator number.
• G6I_EMISSA - Ticket issue date.
• G6I_TARIFA - Ticket value.
• G6I_TAXAS - Fee values.
• G6I_TAXADU - DU fee value.
• G6I_PERCOM - Commission percentage.
• G6I_VLRCOM - Commission value.
• G6I_VLRINC - Incentive value.
• G6I_VLRINC - Refund value.
• G6I_VLRLIQ - Net value.
• G6I_TPOPER - Operation type.
• G6I_TOURCD - Tour Code number.
• G6I_DIVERG - Enter whether the item has any divergence.
• G6I_CODDIV - Code of divergence found.
• G6I_ACERTO - Enter whether an Adjustment has been generated due to divergence.
• G6I_SITUAC - Item status in reconciliation.
• G6I_CONCIL - Code of reconciliation process to which the item is correlated.
• G6I_CODTAX - Code of Fee applied to reservation.
• G6I_NUMID - Single identification number of Sales Record to which the item is correlated.
• G6I_IDITEM - Sequential number of Sales Item of Sales Record to which the item is correlated.
• G6I_NUMSEQ - Identification sequence of Sales Item to which the item is correlated (groups related items).
• To develop function TURA038IMP in Model View Controller (MVC) to import ABAs and save the header (G6H) and items (G6I), utilizing the following MILE layout and function:
• when the import is Airline Ticket (BSP): use layout MULE TURA038B.
• when the import is Airline Locator (GOL): use the user function U_TURA038G.
• Field G6H_FATURA is the key of the table. You cannot repeat it, as it is sequential and filled out automatically.
• Field G6H_PERIOD is constituted by filling out field G6H_PERCID. Namely:

• Field G6H_TIPO of importation determines whether fields G6H_ARQUIV, G6H_LAYOUT, G6H_FATFOR, G6I_EMISSA, G6H_VENCTO and G6I_TARIFA are required. On the other hand, you are required to fill out all other attributes. Validations:

• If you select field G6H_TIPO as "Directory" or "File", the system automatically fills out the attributes G6H_FATFOR,G6I_EMISSA, G6H_VENCTO and G6I_TARIFA through the integration.
• If you select field G6H_TIPO as "Physical", the system automatically fills out field G6H_LAYOUT with option "Airline Manual", after which you can no longer change it.
• Fields G6H_FORNEC and G6H_LOJA are linked to the Provider Register and, as soon as you fill them out, the system automatically fills out field G6H_NREDUZ with the corporate name of the provider selected.
• The system automatically fills out field G6H_DTIMP with the date of import execution.

The layout of the GOL ABA contains airline sales, identified by the ABA number, the PNR code and the number of the ticket issued.

The GOL ABA file structure is of CSV type, with a variable number of rows reflecting the sales quantity in the period referred to by the file, including transactions of domestic sales as well as of international sales and refunds. The segments containing the transactions do not have a specific separator type, just an identifier indicating that, at some point, there is a segment with international sales, with domestic sales or with refunds.

Thus, we have the following GOL file structure in the table below:

The first ten rows of the ABAs file of the GOL layout are displayed the same way; that is, regardless of the file received, the initial structure is composed of the ten rows described in the table above. The ABA presents the sales from the tenth row onwards, which can be domestic, international or refund.

To identify the sales to be presented, the ABA presents the rows for domestic and international sales as follows:

• Domestic sales.
• International sales.

 Each of these blocks is broken by a row that contains the sub-total of sales types. For example:

• Domestic sales sub-total.
• International sales sub-total.

 In each block, of domestic as well as international sales, the refund segment is added to each sales block, containing the refund transactions of this period.

The sales transactions in the file are presented as shown below (each column in from/to corresponds to ID GOL010A to ID GOL010L):

Domestic sales:

ABK85P;2134473232;22/10/2014;0,00;856,90;107,26;-85,69;2,36;0,00;0,00;-83,33;CA 5314************

AB9U6C;2135286244;31/10/2014;0,00;1818,80;220,40;-181,88;5,00;0,00;0,00;-176,88;CA 5314************

ADCSKM;2135187290;30/10/2014;0,00;1395,80;175,19;-139,58;3,84;0,00;0,00;-135,74;CA 5314************

AEUBNW;2134582030;23/10/2014;0,00;446,90;66,26;-44,69;1,23;0,00;0,00;-43,46;CA 5314************

AEUZNW;2134384482;21/10/2014;0,00;235,90;64,03;-40,00;1,10;0,00;0,00;-38,90;CA 5314************

AM4ENB;2134712095;24/10/2014;0,00;1589,90;180,56;-158,99;4,37;0,00;0,00;-154,62;CA 5314************

BCG6QP;2134405329;21/10/2014;0,00;114,90;63,37;-40,00;1,10;0,00;0,00;-38,90;CA 5314************

BCG7QP;2134405330;21/10/2014;0,00;511,90;72,76;-51,19;1,41;0,00;0,00;-49,78;CA 5314************

BDHV2J;2134504079;22/10/2014;0,00;362,90;64,03;-40,00;1,10;0,00;0,00;-38,90;CA 5314************

BHZ4SQ;2134415609;21/10/2014;0,00;904,90;112,06;-90,49;2,49;0,00;0,00;-88,00;CA 5314************

BL6GPE;2134460302;22/10/2014;0,00;1415,80;187,18;-141,58;3,89;0,00;0,00;-137,69;CA 5314************

BMGZWB;2134631036;23/10/2014;0,00;836,80;126,82;-83,68;2,30;0,00;0,00;-81,38;CA 5314************

BYIKNV;2134632703;23/10/2014;0,00;409,80;84,12;-40,98;1,13;0,00;0,00;-39,85;CA 5314************

B9DDFY;2134401509;21/10/2014;0,00;172,90;63,37;-40,00;1,10;0,00;0,00;-38,90;CA 5314************

B9DDFY;2135189377;30/10/2014;0,00;0,00;0,00;0,00;0,00;0,00;0,00;0,00;VI 4108************

 In the example above, you can see that the same locator can occur more than once, which indicates a rebooking, change, etc., for that locator, creating different tickets.

After some rows, certain fees and fines are shown with the following row identifiers, as follows:

If the block is of international sales, the block starts as follows:

OS;;;;;20,00;;;;;;;;;

AA;;;;;240,00;;;;;;;;;

DT;;;;;-1,80;;;;;;;;;

XC;;;;;100,00;;;;;;;;;

DP;;;;;-24,02;;;;;;;;;

OCDiffs;;;;;-45,00;;;;;;;;;

OCPenalty;;;;;467,90;;;;;;;;;

OCNoShow;;;;;150,00;;;;;;;;;

OCReemb;;;;;64,12;;;;;;;;;

Notice that, in the rows above, all values displayed are arranged in column "Fees", which is the sixth column from left to right.

The table below describes all fee types occurring in the GOL integration file and their descriptions:

Fees are imported with Item Type (G6I_TIPOIT) type "4" (Reissue). Based on table G6K,
a FROM/TO of GOL Fees is executed for the internal code fees registered in table G3A. This fee code
is stored in field G6I_CODTAX.

The refund blocks start inside a block with domestic sales and/or international sales, as shown in the example below:

Refunds

A8I9WY;2408052045;22/10/2014;0,00;0,00;0,00;0,00;0,00;0,00;0,00;0,00;AX 376************

DP;;;;;-24,02;;;;;;;;;

DFKQMG;2135195565;31/10/2014;0,00;-1175,80;-38,52;0,00;0,00;0,00;0,00;0,00;CA 5314************

OCPenalty;;;;;467,90;;;;;;;;;

XC;;;;;240,00;;;;;;;;;

EEQ8QJ;2134625747;24/10/2014;0,00;-1280,90;-21,57;0,00;0,00;0,00;0,00;0,00;CA 5314************

OCPenalty;;;;;116,09;;;;;;;;;

XC;;;;;120,00;;;;;;;;;

GYQ6KV;2134673065;25/10/2014;0,00;-1201,90;-21,57;0,00;0,00;0,00;0,00;0,00;CA 5314************

OCPenalty;;;;;540,95;;;;;;;;;

XC;;;;;120,00;;;;;;;;;

Within this structure, the columns presented contain the following ABA data:

• PNR - locator number.
• Ticket - number of ticket created for the locator.
• Date - the ticket issue date.
• Tariff paid cash.
• Tariff on credit.
• Fees.
• DU Tariff.
• CC DU/SU;
• Commissions.
• Incentive.
• Net.
• Notes.

Thus, in the first 10 rows we have the ABA "header" data and, from the 11th row onwards, the items data of this ABA, its fees, refunds and fines. Such data feed the ABA tables, allowing the reconciliation routine to process the data received versus the transactions made in the system.

The file records feed entities "ABA" and "ABA Items".

When generating the ABA from the GOL layout files, you must generate the ABA number with the IATA code catenation sent in ID GOL005B plus the sequential number generated and controlled by the backoffice routine. Example:

-          IATA Code: 57518016

-          Number generated: 000001

-          ABA Number: 8016000001 (8016 + 000001)

• Details of layout MILE TURA038B:

The BSP IATA layout is structured in blocks, the rows of which contain the generated ABA data restricted to a fixed size, with no use of separator. What differentiates the regular layout from the simplified layout is the presence of all or of only some record types in the file. Every BSP IATA file may contain the following structure, as exemplified below:

Header Record

The file header is defined in the following structure:

Transaction Record

The file transaction record is defined in the following structure:

Credit Form of Payment Record

The credit record of payment method is defined in the following structure:

Related Document Record

The credit record of payment method is defined in the following structure:

Conjunction Document Record

The conjunction document record is defined in the following structure:

Trailer Record

The file trailer is defined in the following structure:

The register is developed in header format (ABA) and tabs, each tab with its respective functionality. Namely:

• Header: location where the information given by the user upon adding or importing the ABA is saved and where the attribute controlling his/her status will be found. The caption also uses the status attribute. 
• TAB - TICKETS / DOCUMENTS – in this tab you can view all items imported or deleted, which you may filter, identifying whether each item is already associated and/or approved. In this folder, when the "Layout" attribute is equal to "Airline Manual" or "Others", an action is offered regarding the process of creating reconciliations through a button placed at the right side of the screen (see prototype - ABA Import - Tickets / Documents). When you execute this action, the association processes are executed and, if needed, adjustments. This action results in the creation of one or more reconciliation processes, separated by IATAs and by the ABA Item type.

Attributes

(*) – Fields required

(**) – Key fields of table

ABA Items (validate the items along the integration process)

 Considerations:

• The attributes "ABA Code" and "Item" are the table key and cannot be repeated.
• The attribute "Item" is a sequential number, by ABA, of internal system control and the system automatically fills it out.
• When you associate an item and find a divergence, you must change the value of attribute "Divergent" to "Yes" and fill out attribute "Diverg. Note" with a short description of the divergence found.

Data Dictionary

TABLE G6H – ABAs' Headers

• File SIX – Indices

• File SX2 – Table

• File SX3 - Table Fields

TABLE G6I – ABA Items

• File SIX – Indices

• File SX2 – Table

• File SX3 - Table Fields

Screen Prototypes

Provider ABA - Browser 

Attributes of parameter setting interface for Import

Provider ABA - Header

Provider ABA - Tab: Tickets / Documents - Layout: Simplified BSP

Provider ABA - Tickets / Documents - Layout: Airline Locator

Integrity Rules

Premises

• You must use version 12 or above.
• You can only delete an ABA if no item related to it has been approved.

Release Notes

Process Flow

Entity and Relationship

Test Cases

A test case contains general information that determine how to conduct tests previously specified by the Tests Plan. Generally, they are grouped by requisite. However, you can group test cases by set of requisites, if the tests are integrally checking the requisites that belong to this set.

You must execute the test cases mentioned below to ensure the quality of the product, meeting the purpose of the project and the expected results.

1. 1.       Reusable Test Case(s)

1. 2.       Test Case(s) Specific to the Project