GlobalWare Invoice Data Import Specifications

This topic provides file layout specifications and other information required by GlobalWare's Import Invoices function.

Notes:

Prior to release 9.5, Import Invoices only added new records. With release 9.5, the Import Invoices function can be configured to update existing records.
Tables in the Record Layouts section of this topic include guidance on creating import files (specifically, in the "User Notes," "If Invalid," and "If Missing" table columns).
The Import Invoices function does not import or create refund and exchange notice (REN) data. You can add this information by using the Invoice Edit & Query function.
The Import Invoices function stores N in the Invoice table's CCRecFlag field, which indicates that the invoice should not be loaded into Credit Card Reconciliation (CCR).

For details about the Import Invoices function options, the recommended import invoices procedure, and importing invoices from the command line, see Import Invoices.

Import file specifications

Data items in the files must be tab-delimited (no quotes). The tab character in ASCII is 9 (decimal and hex).

The import files are: IMPACCT.DAT, IMPINV.DAT, and COMTIMP.TXT. These files can be placed anywhere on your computer, but see Import Invoices Procedure (File Location description) for specific details.

The following table describes the field types used by the import files.

Field Type	Valid Values
char	Alphanumeric characters only. Do not use wildcard characters ( * ? _ % ). For account IDs and invoice numbers, only letters and digits are valid. Wildcard characters will be stripped from all other fields.
decimal	Include decimal for cents; decimal not required if no cents. For negative values, precede the amount with a minus sign (for example, ‑41.00).
integer	Whole numbers only. A decimal (or any non-numeric character) will result in zero being stored. For negative values, precede the amount with a minus sign (for example, ‑41).
date	Must be MM/DD/YYYY. Leading zeros are not required. Four-digit year is required.

Record Layouts

IMPACCT.DAT

The IMPACCT.DAT file contains information for importing customer and provider account IDs. Customer and provider account IDs are primary keys in the Invoice table. Therefore, the account IDs must exist in GlobalWare before invoices with the account IDs can be imported.

This file is not required. The account IDs might already exist in GlobalWare, or the user could choose to add them manually in GlobalWare before importing invoices.

This file should contain the customer account IDs and provider account IDs for tours, cruises, and travel insurance providers.

The following table provides the record layout for the IMPACCT.DAT file.

Note: The IMPACCT.DAT file has one record layout.

Column Name	Type	Length	Req?	User Notes
ImpAccountId	char	10	Y	Customer or provider account ID.
ImpAccountType	char	1	Y	Account-type code. Valid codes are C (for customer account) or P (for provider account).
ImpCust/TravelType	char	1	Y	Customer-type code (for customer account records) or travel-type code (for provider account records). Valid codes are in GlobalWare's CustTypes and TravelTypes tables.
ImpName	char	30	Y	Account name.
ImpShortCode	char	3		Airline alpha code or car/hotel chain code.
ImpSortName	char	13		Sort name for the account.
ImpAddr1	char	30		Account's first address line.
ImpAddr2	char	30		Account's second address line.
ImpAddr3	char	30		Account's third address line.
ImpCity	char	15		City name for the account address.
ImpState	char	2		State code for the account address.
ImpZip	char	10		ZIP/postal code for the account address. This can be ZIP+4 (for example, 80012-2425).
ImpCountry	char	20		Country code or name for the account address.
ImpEMail	char	50		Account's e-mail address.
ImpBusPhone	char	15		Account's business number.
ImpFaxPhone	char	15		Account's fax number.
ImpHomePhone	char	15		Account's home phone number.
ImpComPct	integer	5		Percent of commission usually paid by this provider. This column applies to provider accounts only.
ImpReportToId	char	10		Report To ID. This account ID groups multiple customer account IDs together for reporting purposes. It must be a valid customer account ID. This column applies to customer accounts only.
ImpLastArBal	decimal	15		Last accounts receivable statement balance amount for this account.
ImpLastArDate	date	10		Last accounts receivable statement date for this account. Use the format MM/DD/YYYY.

IMPINV.DAT

The IMPINV.DAT file contains information for importing invoice data. It has the following four record layouts.

Record Layout	Description
01	Invoices (see 01 - Invoice Record)
02	Air segments (see 02 - Segments Record)
03	Comments (see 03 - Comments Record)
04	Group passenger comments (see 04 - Group Passenger Comments Record)

Only record type 01 (invoices) is required. 02, 03, and 04 records that follow a 01 record, up to the next 01 record, will be associated with the preceding 01 record. The IMPINV.DAT file must start with a 01 record.

01 - Invoice Record

The following table provides the 01 record layout for the IMPINV.DAT file.

Column Name

Type

Length

Req?

User Notes

If Invalid

If Missing

ImpRecordType

char

Type of record in the IMPINV.DAT file. Because this is an invoice record, the value must be set to 01 (must include the leading zero).

Record not stored

ImpBranch

char

Branch number (booking location). This must be a valid general ledger branch. Because the branch number must have four digits, include leading zeros (for example, 0001; not 1).

Record not stored

ImpInvoiceNumber

char

Invoice number. This must be at least one character. Alpha characters are allowed, but are not recommended. Wildcard characters are not allowed. See also the information about field types in the Import File Specifications section above.

Record not stored

ImpInvoiceDate

date

Invoice date. This must be a valid date. Use the format MM/DD/YYYY.

Record not stored

ImpCustomerAccount

char

Customer account ID. This must be a valid customer account ID.

Record not stored

ImpSTP

char

Ticketing location. This must be a valid ticketing location. Because this number must have four digits, include leading zeros (for example, 0001; not 1).

Record not stored

ImpSettle

char

Settlement-type code. Valid codes are:

A	ARC
D	Direct
I	Individual invoice
T	Tracking

The combination of settlement- and revenue-type codes must exist in the processing table.

Record not stored

ImpRevenueType

char

Revenue-type code. Valid codes are in GlobalWare's RevenueTypes table. The combination of settlement- and revenue-type codes must exist in the processing table.

Record not stored

ImpTraveler

char

Traveler name. This must be at least one valid character. Recommended format is last name followed by a slash (/) and as much of the traveler's first name that will fit (for example, MARTIN/TIMOTHY).

Record not stored

ImpFOP

char

Form of payment code. Valid codes are:

C	Cash or check
R	Accounts receivable
P	Plastic credit card where the provider/ARC is the merchant
A	Agency plastic credit card where the agency is the merchant

Unlike interface, a temporary cash receipt will not be generated for form of payment C.

Record not stored

ImpPrevBilled

char

Indicates whether this invoice item was previously billed. This must be Y (Yes) or N (No).

With release 9.5, when you want to update a record that was previously added, use J for update and V for void.

Record not stored

ImpBase/Rate

decimal

Base fare amount for this invoice item.

ImpCommPercent

decimal

Commission percentage for this invoice item. This overrides the commission amount (ImpCommAmt) if provided.

ImpCommAmt

decimal

Commission amount for this invoice item. If commission percentage (ImpCommPercent) is provided, it will override this amount.

ImpAirline/Chain

char

Airline alpha code or car/hotel chain code.

Record not stored

ImpProvider

char

Provider account ID. This must be a valid provider account ID. For airlines, this is the three-digit airline code (for example, 016 for United). For cars or hotels, this is the chain code.

Record not stored

ImpTicket/Confirm

char

Ticket or confirmation number. This is mandatory for ARC transactions.

ImpDepartDate

date

Traveler's departure date. For cars and hotels, this is the IN date. Use the format MM/DD/YYYY.

ImpInvoiceDate

ImpReturnDate

date

Traveler's return date. For cars and hotels, this is the OUT date. Use the format MM/DD/YYYY.

Invoice.DepartDate

ImpBkAgt

char

Booking agent's sine code (two- or three-character code).

999

ImpTktAgt

char

Ticketing agent's sine code (two- or three-character code).

999

ImpSellAgt

char

Selling agent's sine code (two- or three-character code).

999

ImpDocType

char

Document type. This identifies non-standard ARC document types. The standard document type is blank. Valid types are:

AAD	Agency adjustment for commission
CRE	Credit memo
DAR	Debit memo - accounts receivable adjustment
DCM	Debit memo - commission only
EXC	Exchange
HND	Indicates non-ARC ticket numbers
MCO	Miscellaneous charge order (usually indicates a service fee)
REF	Refund
EMD	Electronic miscellaneous document

blank

ImpTicketType

char

Ticket type.

blank

ImpConjunct

integer

Number of consecutive tickets used for an itinerary. Each set of four segments in the itinerary counts as one ticket. For example, if the itinerary is ORD-LAX-AKL-SYD-BKK-TYO-ORD, the number in this column should be 2.

ImpPnrLocator

char

Passenger Name Record (PNR) locator. This is used to associate a PNR with an invoice.

ImpResSystem

char

Reservation system originating the transaction. Valid values are:

AM	Amadeus
AP	Apollo
GA	Travelport+
SA	Sabre
WS	Worldspan

Leaving this column blank indicates manual entry.

blank

ImpDomInt

char

Domestic or international. Valid values are:

D	Domestic
I	International

ImpReportToID

char

Report To ID. This account ID groups multiple customer account IDs together for reporting purposes. It must be a valid customer account ID.

Report To ID for ImpAccountId

ImpMaxFare

decimal

Maximum fare amount. The maximum fare the customer could have paid (that is, full coach fare).

ImpSavingsCode

char

Fare savings code. Savings codes are defined by the agency. Valid codes are in the Savings Code (SavingsCode) table.

ImpLowFare

decimal

Lowest fare amount. The lowest fare available the customer could have paid.

ImpLostSavingsCode

char

Lost savings code. Savings codes are defined by the agency. Valid codes are in the Savings Code (SavingsCode) table.

ImpSavingsComment

char

Generally, a comment providing further explanation of the savings (for example, why a certain savings was or was not realized). For exchanges, it is "EX - " plus the original ticket number (for example, EX - 7115245949).

ImpSort1

char

First-level sort key for corporate reports.

The four Sort columns can be used for sorting and subtotaling corporate reports and statements. For example, to subtotal a report by departments within separate divisions, use Sort 1 for division and Sort 2 for department.

The four Sort columns are populated from the PNR Name Field Remarks.

ImpSort2

char

Second-level sort key for corporate reports.

ImpSort3

char

Third-level sort key for corporate reports.

ImpSort4

char

Fourth-level sort key for corporate reports.

ImpCustType

char

Customer Type. Valid codes are in GlobalWare's CustTypes table.

ImpTax1

decimal

Amount of US tax or tax 1 for this invoice item.

ImpTax2

decimal

Amount of passenger facility charge (PFC) or tax 2 for this invoice item.

ImpTax3

decimal

Tax 3 for this invoice item.

ImpTax4

decimal

Tax 4 for this invoice item.

ImpMiscCharge

decimal

Amount of miscellaneous charges and exchange penalties for this invoice item.

ImpDiscount/Markup

decimal

Amount of discount (minus amount) or markup (plus amount). This must be zero (0) if the form of payment is plastic (ImpFOP = P).

ImpExchange

decimal

Amount of exchange. This must be zero (0) or less.

ImpBookedUnits

integer

Number of booked units (for example rooms, cabins, or cars). This must be greater than zero (0).

ImpSaleNumber

integer

Sale number. Identifies multiple, scheduled payments for the same service. For more information, see Multiple Items for One Service. For example:

Provider	Payment	Sale Number
016	Air ticket	1
MLT	Deposit	2
MLT	Final payment	2
HY	Hotel reservation	3
ZE	Car reservation	4

ImpCCCompany

char

Credit card company code (for example, AX or MC).

ImpCCNumber

char

Credit card number. Blanks, dashes, and other special characters are allowed.

ImpCCExpDate

char

Credit card expiration date. Use the format DD-Mmm (for example, 15-Dec).

ImpCCApprovalCode

char

Credit card approval code.

ImpItinerary/ProvName

char

Itinerary (for example, DEN-LAX-SFO-DEN).

ImpDestination

char

Destination city code (for example, CUN for Cancun).

ImpProperty

char

Property provider account ID. For hotels, this is the property phone number.

ImpCustDueDate

date

Date on which payment by the customer is due. Use the format MM/DD/YYYY.

ImpInvoiceDate

ImpProvDueDate

date

Date on which payment to the provider is due. Use the format MM/DD/YYYY.

ImpInvoiceDate

ImpGroupId

char

Group ID (for purposes related to tracking groups).

ImpPartyId

char

Party ID (for purposes related to tracking groups).

ImpBookDate

date

Date on which this invoice item was booked. Use the format MM/DD/YYYY.

ImpInvoiceDate

ImpStatus

char

Status of the invoice item. Valid values are:

E	Exchanged
O	Open
R	Refunded
V	Voided

ImpCTStatusReason

char

Reason for a voided status (ImpStatus must be V, and ImpSettle must be T). Valid values are:

N	No Show
X	Canceled
V	Voided

ImpMarketId

char

Account ID of the customer to whom you want to market. This must be a valid customer account ID. See the Market ID section in Customer Accounts Overview.

Record not stored

02 - Segments Record

The following table provides the 02 record layout for the IMPINV.DAT file.

Column Name

Type

Length

Req?

User Notes

If Invalid

If Missing

ImpRecordType

char

Type of record in the IMPINV.DAT file. Because this is a segments record, the value must be set to 02 (must include the leading zero).

Record not stored

ImpSegmentNum

integer

Segment number. Must be between 1 and 99.

Record not stored

ImpAirline

char

Three-digit airline code for this segment (for example, 016 for United).

Record not stored

ImpFlight

char

Flight number for this segment.

Record not stored

ImpConnect

char

Must be O (origin) or X (connection). This must be correct for accurate generation of true Origin-Destination (OD) table.

Record not stored

ImpDepartCity

char

Departure city code (for example, DEN for Denver).

Record not stored

ImpArrivalCity

char

Arrival city code (for example, CUN for Cancun).

Record not stored

ImpDepartDate

date

Departure date. Use the format MM/DD/YYYY.

Invoice.DepartDate

ImpArrivalDate

date

Arrival date. Use the format MM/DD/YYYY.

Invoice.DepartDate

ImpDepartTime

char

Departure time. Use the format HHMMx (for example, 0915A or 1104P).

ImpArrivalTime

char

Arrival time. Use the format HHMMx (for example, 0915A or 1104P).

ImpFareBasis

char

Fare basis code.

ImpFare

decimal

Segment fare amount. For a refund, this should be a negative amount.

ImpClass

char

Class of service. For example, F (first) or Y (coach). Use the reservation system's class codes.

ImpDomInt

char

Domestic or international. Valid values are:

D	Domestic
I	International

ImpMealCode

char

Meal code. Use the reservation system's meal codes.

ImpTicketDesignator

char

Ticket designator code. This is typically used to indicate the type of discount to apply, such as for a child or infant, or an airline employee. Use the reservation system's ticket designator codes.

ImpFreqFlyerNum

char

Frequent flyer number for the segment.

ImpFreqFlyerMiles

integer

Frequent flyer miles for the segment.

ImpNauticalMiles

integer

Nautical miles for the segment.

ImpFreqFlyerCarrier

char

Frequent flyer carrier code. This is the three-digit airline code (for example, 016 for United).

03 - Comments Record

The following table provides the 03 record layout for the IMPINV.DAT file.

Column Name	Type	Length	Req?	User Notes	If Invalid	If Missing
ImpRecordType	char	2	Y	Type of record in the IMPINV.DAT file. Because this is a comments record, the value must be set to 03 (must include the leading zero).	Record not stored	Record not stored
ImpLineNum	integer	3	Y	Comment line number. Must be between 1 and 99.*	Record not stored	Record not stored
ImpData	char	50		Comment text (alphanumeric characters; no special characters).

* Comment lines over 100 are reserved for the following (all lines not listed are reserved for future use).

Comment Line	Description
101	Check number for form of payment C transactions.
102	Market code.
103	Car or hotel room type.
104	Non-air frequent flyer number.
105	Non-air frequent flyer miles.
106-110	Delivery address.
111	ETDN vendor.
112	Transaction Control Number (TCN).
113	IT number (tour code).
114	# pax (future, for AAA).
115	Apollo invoice number (for a subsequent payment stored with the original deposit).
116	On the original ticket, the ticket number it has been exchanged for.
117	Record sequence number.
118	Hotel rate type.
119	Corporate ID number (for a car segment).

04 - Group Passenger Comments Record

The following table provides the 04 record layout for the IMPINV.DAT file.

Column Name	Type	Length	Req?	User Notes	If Invalid	If Missing
ImpRecordType	char	2	Y	Type of record in the IMPINV.DAT file. Because this is a group passenger comments record, the value must be set to 04 (must include the leading zero).	Record not stored	Record not stored
ImpLineNum	integer	3	Y	Must be between 1 and 999.	Record not stored	Record not stored
ImpData	char	30		Comment text.

COMTIMP.TXT

The COMTIMP.TXT file contains information for importing comment, sort, and non-accounting data.

The following table provides the record layout for the COMTIMP.TXT file.

Note: The COMTIMP.TXT file has one record layout.

Column Name

Type

Length

Req?

User Notes

Matched 1

Varies

Values in the Matched and Variable Matched columns must reflect the Match 1 through Match 5 fields that are set up on the Import Invoices screen (see Import Invoices Procedure). For example, if Match 1 on the Import Invoices screen is set to Invoice #, you must specify an invoice number in the Matched 1 column of the COMTIMP.TXT import file. Data type and length requirements for these columns are based on the match criteria selected on the Import Invoices screen.

Match 1 through Match 5 fields can be set to one of the following: PNR Locator, Invoice #, (Like) Traveler, Confirmation #, (Like) Itinerary, (Like) Sort 1, (Like) Sort 2, (Like) Sort3, (Like) Sort 4, or Pay ID.

The Matched 1 and Matched 2 columns are required because Match 1 and Match 2 on the Invoices screen are required. Because the Variable Match columns are not required, leave any of these blank if the corresponding Match 3 through Match 5 fields are set to None on the Import Invoices screen.

Matched 2

Varies

Variable Match 3

Varies

Variable Match 4

Varies

Variable Match 5

Varies

ReportToId 6

char

Report To ID. This account ID groups multiple customer account IDs together for reporting purposes. It must be a valid customer account ID.

Traveler 7

char

Traveler name. This must be at least one valid character. Recommended format is last name followed by a slash (/) and as much of the traveler's first name that will fit (for example, MARTIN/TIMOTHY).

BkAgt 8

char

Booking agent's sine code (two- or three-character code).

TktAgt 9

char

Ticketing agent's sine code (two- or three-character code).

SellAgt 10

char

Selling agent's sine code (two- or three-character code).

MaxFare 11

decimal

Maximum fare amount. The maximum fare the customer could have paid (that is, full coach fare).

LowFare 12

decimal

Lowest fare amount. The lowest fare available the customer could have paid.

DomInt 13

char

Domestic or international. Valid values are:

D	Domestic
I	International

Destination 14

char

Destination city code (for example, DEN for Denver).

Itinerary 15

char

Itinerary information (for example, DEN-LAX/SFO-DEN).

SavingsCode 16

char

Fare savings code. Savings codes are defined by the agency. Valid codes are in the Savings Code (SavingsCode) table.

SavingsComment 17

char

Generally, a comment providing further explanation of the savings (for example, why a certain savings was or was not realized). For exchanges, it is "EX - " plus the original ticket number.

BookDate 18

date

Date on which the item was booked. Use the format MM/DD/YYYY.

Property 19

char

Property provider account ID. For chain hotels, this is usually the property phone number. GlobalWare stores property phone numbers as provider account IDs, so this must be a valid provider account ID.

LostSavingsCode 20

char

Lost savings code. Savings codes are defined by the agency. Valid codes are in the Savings Code (SavingsCode) table.

PartyID 21

char

Party ID (for purposes related to tracking groups).

MarketId 22

char

Account ID of the customer to whom you want to market. This must be a valid customer account ID. See the Market ID section in Customer Accounts Overview.

Sort1 23

char

First-level sort key for corporate reports.

The four Sort columns are populated from the PNR Name Field Remarks.

Sort2 24

char

Second-level sort key for corporate reports.

Sort3 25

char

First-level sort key for corporate reports.

Sort4 26

char

First-level sort key for corporate reports.

TicketNo 27

char

Ticket or confirmation number.

GlobalWare will not update the item with this number if the item is in a closed ARC period.

If this column is not populated (blank or space), GlobalWare skips the column and proceeds to the next column, leaving any existing ticket number for the item unchanged.

Comment (nn-) 28

char

256

Must be in the format nn-comment where nn is a valid comment number (in the range 1 to 99) and comment is the comment text. For example, for comment "99-Payment split", the comment number is "99" and the comment text is "Payment split".

While the COMTIMP.TXT template includes nine columns for comments, you can add up to 90 more columns in the template to include the additional comments. The template includes 35 columns with headers, but headers are not required for the potential 90 additional columns you can add for comments.

To remove a comment line, use the format nn- where nn is a valid comment number (in the range 1 to 99). After the dash, if no comment exists, GlobalWare will remove the comment line.

Comment (nn-) 29

char

256

Comment (nn-) 30

char

256

Comment (nn-) 31

char

256

Comment (nn-) 32

char

256

Comment (nn-) 33

char

256

Comment (nn-) 34

char

256

Comment (nn-) 35

char

256

Comment (nn-) 36

char

256

Additional Notes

Accounts Receivable statement balances and dates

If you include last accounts receivable statement balances and last accounts receivable statement dates in the IMPACCT.DAT file, run statements immediately and confirm the data is correct. Data can be changed with a special program (requires password).

Refunds and exchanges

Refunds: All amounts should be negative. All refunded segments should be included with negative fares. DocType should be REF. Original sales status should be R.

Exchanges: Amounts should be for new ticket. Total amount from old ticket should be entered in Exchange as negative number (for example, Base = 300, Tax = 24, PFC = 12, Exchange = -444; the total will be -108, an exchange with refund). Segments for new ticket should be included. DocType should be EXC. Original ticket’s status should be E.