Ubridge for the Reporting to the MAX
FILE DEFINITIONS
The concept of bridging between two software programs hinges on
precise file definitions. The exporting program must create the
transaction records to exact specifications in order for the Accounting
Series to successfully import the data. A single character out of place
will cause the entire process to fail. This chapter provides
explanations
on how to interpret the files definitions and then presents the various file
definitions used in the Universal Bridge program.
Interpreting the File Definitions
Anatomy of a File Definition If you view any of the file definitions in the
pages that follow, you will see the name of the Accounting Series
application, the Universal Bridge external file name at the top of the
page, and five columns of information. In the following paragraphs, we
provide explanations and examples of each of these columns.
Field Description
Field Descrip. Length Pos. Data Type Required
Employee Number 20 01 Alphanumeric * Department
Code 02 21 Alphanumeric default
The first column is a description of each data field within the transaction
records. These are the same as the field descriptions that appear on
your screen when processing transactions in the Accounting Series.
Column Two: Length
Field Descrip. Length Pos. Data Type Required
Account Number 15 01 Alphanumeric *
Transaction Date 08 16 Date Format default
The second column shows the length of the field which is expressed as a
number of character places. One very important thing to remember is
that data length and field length are not the same thing. For example, in
an address record with a 30-character field length for the city name, the
city name can be left blank, or be one-character, thirty characters, or any
length in between. The field, however, will always be thirty characters
long. Consequently, the external transaction records are fixed-length
records because the records always contain the same fields, and the
fields are always the same length.
Column Three: Position (Pos.)
Field Descrip. Length Pos. Data Type Required
Header Identifier 01 01 "H" *
Bank Account Code 03 02 Alphanumeric *
The third column shows the position of the first character of the field
within the record. The first field listed always occupies position one.
The position of the second field depends on the length of the first field,
but will occupy the next available position after the first field ends.
Notice that no spaces appear between the fields and no character
separates the fields. Your Accounting Series determines where a field
begins and ends by finding the position of the first character and
counting the number of places in the field.
Column Four: Data Type
Field Descrip. Length Pos. Data Type Required
Account Number 15 01 Alphanumeric *
Transaction Date 08 16 Date Format default
The fourth column, data type, is an abbreviated description of the type
of data that will occupy the field. To import successfully, the data in the
field must conform to the descriptions. For instance, if the data
description for a field is numeric, then alpha or special characters may
not occupy this field. Following are explanations of each abbreviated
data type description that you will encounter in the file definitions.
Alphanumeric: This field may contain alpha characters, numeric
characters, or special characters.
Numeric X.X (X represents any number): This field may contain
numbers that are greater than or equal to zero. The number in front of
the decimal point designates the maximum number of digits allowed in
front of the decimal point, and the number after the decimal point
designates the maximum number of digits allowed behind the decimal
point. For example, numeric 4.3 designates a number that has a
maximum of four digits in front of the decimal point and a maximum of
three digits behind the decimal point.
NR X - X (X represents any number): This field may contain whole
numbers that fall within the specified range. Decimals or fractions are
not accepted.
X Spaces (X represents any number): This field must contain the
specified number of spaces.
Flags: Flags are fields that contain either-or type information. The data
description column shows the choices and what each choice represents.
Some examples are as follows: Y=yes N=no, 0=cash 1=charge, and
D=debit C=credit. The information that actually occupies the field is the
one-character code letter or number.
Date Format: This field may contain an eight-character calendar date in
one of the following
formats:
MMDDYY__: MM is the two-digit representation of the month,
ranging from 01 for January through 12 for December; DD is the
two-digit day of the month, beginning with 01; YY represents the last
two digits of the year; and __ represents two blank spaces.
MM?DD?YY: MM is the two-digit representation of the month,
ranging from 01 for January through 12 for December; DD is the
two-digit day of the month, beginning with 01; YY represents the last
two digits of the year; and ? represents any special character (any
character but letters of the alphabet and numerals.)
YYYYMMDD: YYYY is the four-digit year; MM is the two-digit
representation of the month, ranging from 01 for January through 12 for
December; and DD is the two-digit day of the month, beginning with
01.
H: This field identifies the record as a header record and must contain
the letter H.
D: This field identifies the record as a detail record and must contain
the letter D.
Q: This field identifies the record as a quick transaction record in
Check Reconciliation and must contain the letter Q.
A: This field identifies the record as an address record in Accounts
Payable and must contain the letter A.
CR/LF: This field is the last field of every record and must contain a
carriage return/line feed. Even though you can not see it on a printed
document, a carriage return/line feed occupies a two-character field in
an ASCII file, and is an important element of each record.
Column Five: Required
Field Descrip. Length Pos. Data Type Required
Employee Number 20 01 Alphanumeric *
Department Code 02 21 Alphanumeric default
Job Number 09 23 Alphanumeric See note
The fifth column of the data file definition indicates whether or not the
field may be left blank. If an asterisk appears in the column, then the
field must contain the appropriate data and may not be blank. If the
word default appears in the column, the data is not required for the
record to import, but a value will be assigned to the field as described in
the corresponding footnote. If you do not wish to accept the default
value, then you must provide the information in the field. For some
data fields, additional information has been included in the footnotes,
and the message See note appears in this column. Remember that if a
field does not have an asterisk in this column, only the data is optional.
The field must still be included, filled with blank spaces.
Files With More Than One Record Type
Several applications in the Accounting Series require more than one file
to hold a transaction. This is true for Accounts Payable, Order Entry,
Check Reconciliation (regular transactions), and Accounts Receivable
(with extended apply type). In each case, the header information for
each transaction is stored in one file and the line detail information is
stored in a separate file.
However, the Universal Bridge program uses a single external file to
hold all of the information needed for a transaction. Instead of using
multiple files, the Universal Bridge uses multiple record types per file
(see the example below). When you use the Universal Bridge program
to import transactions to Accounting Series applications that use more
than one file, you must arrange the different records in the external file
in the correct sequence for successful completion of the import process.
Each header record that the Accounting Series finds in the external file
must be followed by at least one detail record. If the transaction carries
multiple lines of detail, each detail record must follow the header record,
and precede the next header record. In Accounts Payable, if the
transaction is a paid out and an immediate check is to be printed, the
transaction will carry an address record which follows the header record
and precedes the detail record(s).
The following example illustrates how the records must be arranged in
the external data file. The first transaction to be imported is an
Accounts Payable transaction with one line of detail. The
second has three lines of detail, and the third is a paid out that requires
an immediate check and has two lines of detail.
Example:
Header record of first transaction
Detail record of first transaction
Header record of second transaction
First detail record of second transaction
Second detail record of second transaction
Third detail record of second transaction
Header record of third transaction
Address record of third transaction
First detail record of third transaction
Second detail record of third transaction
The data file definitions are shown on the following pages. Refer to
these definitions when you create the external files and transaction
records. When creating an external file, it must be named exactly as
shown in the definition, with the company code substituted for the XXX
in the file name extension. Following the definitions you will find
guidelines for creating the external files and transaction records.
Universal Bridge File Definitions
Listed below are the Accounting Series applications that contain a
Universal Bridge option and the associated external file name. On the
pages that follow, you will find the actual file definitions.
Application Name External File Name
General Ledger GLEXT.XXX
Check Recon. (header, detail,
and quick entry records) CREXT.XXX
Accounts Receivable (header
and detail records) AREXT.XXX
Order Entry (header
and detail records) OEEXT.XXX
Inventory INVEXT.XXX
Accounts Payable (header,
address, and detail records) APEXT.XXX
Payroll PREXT.XXX
note:
Some applications contain more than one type of record within a single
file. In such files, the first byte indicates type. This variable record type
was discussed in the previous section called Files With More Than One
Record Type, and occurs in Check Reconciliation, Accounts
Receivable, Order Entry, and Accounts Payable. Please notice the first
byte of information in the appropriate definitions below.
General Ledger: External File Name: GLEXT.XXX
Field Descrip. Length Pos. Data Type Required
Account Number 15 01 Alphanumeric *
Transaction Date 08 16 Date Format default
Reference Number 10 24 Alphanumeric
Description 40 34 Alphanumeric
Debit/Credit Flag 01 74 debit/credit default
Transaction Amount 12 75 Numeric 8.2
Unused Field 40 87 40 spaces
End of Record 02 127 CR/LF *
Record Length = 128 bytes
If the transaction date is equal to zero or otherwise invalid, the date will
be set to the first day of the fiscal year.
The debit/credit flag must either be blank or contain a D for debit or a C
for credit. If left blank, the field defaults to D. The C must be in the
field for credit transactions to process correctly. If the transaction
amount is a negative number, then the import process reverses the
debit/credit flag and makes the transaction amount positive.
Check Reconciliation: External File Name: CREXT.XXX
Header Record
Field Descrip. Length Pos. Data Type Required
Header Identifier 01 01 "H" *
Bank Account Code 03 02 Alphanumeric *
Transaction Type 01 05 NR 1 - 5 *
Document Number 20 06 Alphanumeric See note
Transaction Date 08 26 Date Format default Trans.
Description 30 34
Alphanumeric
Memo Description 30 64 Alphanumeric
Address Line 1 30 94 Alphanumeric
Address Line 2 30 124 Alphanumeric
Address Line 3 30 154 Alphanumeric
Vendor/Employee 20 184 Alphanumeric
Fund Number 06 204 Alphanumeric
End of Record 02 210 CR/LF *
Record Length = 211 bytes
The document number is a required field for check type transactions
only. For all other transaction types the information is optional.
If the transaction date is equal to zero or otherwise invalid, the date will
be set to the CMS system date.
Check Reconciliation: External File Name: CREXT.XXX
Detail Record
Field Descrip. Length Pos. Data Type Required
Detail Identifier 01 01 "D" *
Line Amount 13 02 Numeric 10.2
Line Description 30 15 Alphanumeric
Invoice Number 20 45 Alphanumeric
Ledger Account 15 65 Alphanumeric See note
Unused Field 130 80 130 Spaces
End of Record 02 210 CR/LF *
Record Length = 211 bytes
The ledger account number is a required field if Check Reconciliation is
integrated to General Ledger. If Check Reconciliation is not integrated
to General Ledger, the information is optional.
Check Reconciliation: External File Name: CREXT.XXX
Quick Entry Record
(This record is used for quick entry transactions and replaces both the
header and detail records of
regular transactions.)
Field Descrip. Length Pos. Data Type Required
Quick Identifier 01 01 "Q" *
Bank Account Code 03 02 Alphanumeric *
Transaction Type 01 05 NR 1-5 *
Document Number 20 06 Alphanumeric See note
Transaction Amount 13 26 Numeric 10.2
Transaction Date 08 39 Date Format default
Trans. Description 30 47 Alphanumeric
Memo Description 30 77 Alphanumeric
Ledger Account 15 107 Alphanumeric See note
Fund Number 06 122 Alphanumeric See note
Unused Field 82 128 82 Spaces
End of Record 02 210 CR/LF *
Record Length = 211 bytes
The document number is a required field for check type transactions
only; for all other transaction types the information is optional.
If the transaction date is equal to zero or otherwise invalid, the date will
be set to the CMS system date.
The ledger account number is a required field if Check Reconciliation is
integrated to General Ledger. If Check Reconciliation is not integrated
to General Ledger, the information is optional.
Accounts Receivable: External File Name: AREXT.XXX
Header Record
Field Descrip. Length Pos. Data Type Required
Header Identifier 01 01 "H" *
Customer Number 15 02 Alphanumeric *
Transaction Code 04 17 Alphanumeric *
Source Document 10 21 Alphanumeric
Description 15 31 Alphanumeric
Terms Code 02 46 Alphanumeric default
Source Doc to Pay (0) 10 48 Alphanumeric
Source Doc to Pay (1) 10 58 Alphanumeric
Source Doc to Pay (2) 10 68 Alphanumeric
Source Doc to Pay (3) 10 78 Alphanumeric
Source Doc to Pay (4) 10 88 Alphanumeric
Source Doc to Pay (5) 10 98 Alphanumeric
Discount Override (0) 01 108 NR 0-5 or 1sp. See note
Discount Override (1) 01 109 NR 0-5 or 1sp. See note
Discount Override (2) 01 110 NR 0-5 or 1sp. See note
Discount Override (3) 01 111 NR 0-5 or 1sp. See note
Discount Override (4) 01 112 NR 0-5 or 1sp. See note
Discount Override (5) 01 113 NR 0-5 or 1sp. See note
Transaction Date 08 114 Date Format default
Transaction Amount 13 122 Numeric 10.2
End of Record 02 135 CR/LF *
Record Length = 136 bytes
If the Terms Code is blank or otherwise invalid, the default value from
the customer master record will be used.
If the discount override is left blank, the normal discount will be used.
If the transaction date is zero or otherwise invalid, the CMS system date
will be used.
Accounts Receivable: External File Name: AREXT.XXX
Detail Record
Field Descrip. Length Pos. Data Type Required
Detail Identifier 01 01 "D" *
Discount Override 01 02 NR 0-5 or 1sp.
Document to Pay 10 03 Alphanumeric
Unused Field 122 13 122 Spaces
End of Record 02 135 CR/LF *
Record Length = 136 bytes
Order Entry: External File Name: OEEXT.XXX
Header Record
Field Descrip. Length Pos. Data Type Required
Header Identifier 01 01 "H" *
Cash/Charge 01 02 0=Cash1=Charge *
Customer Number 15 03 Alphanumeric *
Shipping Customer 15 18 Alphanumeric default
Shipping Address 32 33 Alphanumeric default
Shipping Address 32 65 Alphanumeric default
Shipping Address 32 97 Alphanumeric default
Shipping Address 32 129 Alphanumeric default
Invoice Date 08 161 Date Format default
Order Date 08 169 Date Format default
Shipping Date 08 177 Date Format default
Invoice Number 10 185 Alphanumeric *
Purchase Order Number 10 195 Alphanumeric
F.O.B. 15 205 Alphanumeric
Buyer/Contact 15 220 Alphanumeric
Department Code 02 235 Alphanumeric *
Salesperson Code 03 237 Alphanumeric default
Sales Tax Code 02 240 Alphanumeric default
Terms Code 02 242 Alphanumeric default
Discount Code 02 244 Alphanumeric default
Ship Via Code1 02 246 Alphanumeric default
End of Record 02 248 CR/LF *
Record Length = 249 bytes
If this field is left blank, the default value from the customer master
record will be used.
If the invoice date is zero or otherwise invalid, the CMS system date
will be used.
If the date is left blank, the invoice date will be used.
If only one Department Code exists, the field will default to that
Department Code, otherwise the information is required.
Order Entry: External File Name: OEEXT.XXX
Detail Record
Field Descrip. Length Pos. Data Type Required
Detail Identifier 01 01 "D" *
Line Type 02 02 NR 1-6 or *
NR 11-15 or
NR 90-99
Taxable 01 04 Y=yes N=no See note
Comment Code 02 05 Alphanumeric
Stock/Code 15 07 Alphanumeric *
Description 45 22 Alphanumeric
Discount % 13 67 Numeric 2.4
Quantity Ordered 13 80 Numeric 6.4
Quantity Shipped 13 93 Numeric 6.4
Unit Price 13 106 Numeric 8.4
Misc. Price 1 13119 Numeric 6.4
Misc. Price 2/Cost/JC $ 13 132 If Price 2: Numeric 6.4
If not Price2:Numeric8.4
Unused Field 103 145 103 Spaces
End of Record 02 248 CR/LF *
Record Length = 249 bytes
This field defaults to N if left blank. If these transactions are to be
taxed, then the Y must be included and a valid Sales Tax Code must be
imported to the header record.
This field requires a valid stock number, Job Code, or Non-inventory
Code for all transaction types except 2, 12, and 90-99.
Inventory: External File Name: INVEXT.XXX
Field Descrip. Length Pos. Data Type Required
Stock Number 15 01 Alphanumeric *
Transaction Type 01 16 1=add 2=sub default
Unused Field 01 17 1 space
Transaction Date 08 18 Date Format default
Cost 12 26 Numeric 7.4
Quantity 12 38 Numeric 7.4
Unused Field 77 50 77 spaces
End of Record 02 127 CR/LF *
Record Length = 128 bytes
If this field is left blank, the default transaction type will be an Inventory
addition. If the transaction type is an inventory subtraction, the field
must be filled with a 2.
If this date is zero or otherwise invalid, the first date of the fiscal year
will be used.
Accounts Payable: External File Name: APEXT.XXX
Header Record
Field Descrip. Length Pos. Data Type Required
Header Identifier 01 01 "H" *
Vendor Number 20 02 Alphanumeric
Spaces 10 22 Spaces
Invoice Number 10 32 Alphanumeric *
PO Number 20 42 Alphanumeric
Unused Field 03 62 3 Spaces
Invoice Date 08 65 Date Format default
Due Date 08 73 Date Format default
Discount Rate 05 81 Numeric 2.2
Unused Field 03 86 3 Spaces
Paid Out 01 89 "P" if paid out
Immediate Check 01 90 "I" if immediate
check/not printed
"C" if immediate
check/printed
Check Number 10 91 Alphanumeric See note
Unused Field 26 101 26 spaces
End of Record 02 127 CR/LF *
Record Length = 128 bytes
The vendor number is not required if the paid out flag is set to P.
Otherwise, the number is required.
If the invoice date is zero or otherwise invalid, the CMS system date
will be used.
The due date will default to the invoice date if this field is left blank.
The check number is required if AP is integrated to Check
Reconciliation. If not integrated to Check Reconciliation, the check
number is required if the transaction is an immediate check type
transaction and if the print check flag is set to print the check.
Accounts Payable: External File Name: APEXT.XXX
Address Record:
(Necessary only if a transaction is a paid out and an immediate check is
to be printed.)
Field Descrip. Length Pos. Data Type Required
Address Identifier 01 01 "A" *
Address Line 1 30 02 Alphanumeric
Address Line 2 30 32 Alphanumeric
Address Line 3 30 62 Alphanumeric
Address Line 4 30 92 Alphanumeric
Unused Field 05 122 5 Spaces
End of Record 02 127 CR/LF *
Record Length = 128 bytes
Accounts Payable: External File Name: APEXT.XXX
Detail Record
Field Descrip. Length Pos. Data Type Required
Detail Identifier 01 01 "D" *
Line Type 02 02 NR 1-3 or *
NR 5-6 or
NR 11-13 or 15
Stock/Description 20 04 Alphanumeric See note
Quantity 13 24 Numeric 8.4
Cost Each 13 37 Numeric 8.4
Discount Flag 01 50 Y=Discount applies
Any other=Discount does not apply
Expense Account 20 51 Alphanumeric See note
Prepaid Account 20 71 Alphanumeric See note
Expense Date 08 91 Date Format *
Unused Field 28 99 28 Spaces
End of Record 02 127 CR/LF *
Record Length = 128 Bytes
A line type 4, job cost transaction, can not be imported.
The stock number is required for line type 5 or 15. Otherwise, the
information is optional.
The expense account number is required for line types 1, 2, 3, 11, 12, or
13. Otherwise, the information is optional.
The prepaid account number is required for line type 2 or 12.
Otherwise, the information is optional.
Accounts Payable: External File Name: APEXT.XXX (Continued)
If adding vendors on-the-fly this record can be used for name/address.
Field Descrip. Length Pos. Data Type
Record Type 01 1,1 "N"
AP Code 02 2,2 A/N
Vendor Name 30 4,30 A/N
Address Line 1 30 34,30 A/N
Address Line 2 30 64,30 A/N
City 15 94,15 A/N
State 02 109,2 A/N
Zip 05 111,5 A/N
Phone 10 116,10 EX: 2084669471
Space 01 126,1 reserved
End of Record 02 127,2 CR/LF
Record Length is 128 characters.
If the expense date is zero or otherwise invalid, the invoice date is
assigned, unless the transaction is a type 2 or a type 3. For type 2 and
type 3 transactions, a valid expense date is required.
Payroll: External File Name: PREXT.XXX
Field Descrip. Length Pos. Data Type Required
Employee Number 20 01 Alphanumeric *
Department Code 02 21 Alphanumeric default
Job Number 09 23 Alphanumeric See note
Job Process Number 06 32 Alphanumeric
Job Requirement No. 08 38 Numeric 4.3
Unused Field 03 46 3 spaces
Rate Code 01 49 N R 1-6 default
Transaction Date 08 50 Date Format *
Unused Field 07 58 Reserved
Regular Units 08 65 Numeric 4.3
Other Time 1 Units 08 73 Numeric 4.3
Other Time 2 Units 08 81 Numeric 4.3
Vacation Units 08 89 Numeric 4.3
Sick Leave Units 08 97 Numeric 4.3
Machine Units 08 105 Numeric 4.3
Units Produced 08 113 Numeric 4.3
Unused Field 08 121 8 spaces
Regular Pay 13 129 Numeric 10.2
Other Time 1 Pay 13 142 Numeric 10.2
Other Time 2 Pay 13 155 Numeric 10.2
Vacation Pay 13 168 Numeric 10.2
Sick Leave Pay 13 181 Numeric 10.2
Additions to Gross 13 194 Numeric 10.2
Unused Field 16 207 16 Spaces
End of Record 02 223 CR/LF *
Record Length = 224 bytes
If the Department Code is blank, the value from the employee master
record is used.
1If this field is left blank, the pay will be calculated using the units
entered above and the pay rate from the employee master record.
This information is required for job cost type transactions. If not a job
cost transaction, the field must be filled with spaces.
If the rate code is left blank, rate 1 from the employee master record is
used.
If the transaction date is zero or otherwise invalid, the CMS system date
is used.
Purchase Order: External File Name: POEXT.XXX
Format of External File for generating PO's. PO Header Record
Field Descrip. Length Pos. Data Type Required
Record Type 01 01 "H"
PO Number 20 02 A/N
Vendor Number 15 22 A/N
Vendor Contact 15 37 A/N
FOB 20 52 A/N
Shipping Method (Via) 20 72 A/N
Purchasing Agent 10 92 A/N
Miscellaneous Field 1 30 102 A/N
Miscellaneous Field 2 30 132 A/N
Freight Type 01 162 A/N
P=Prepaid, C=Collect, A=Added
Unused 01 163 Reserved
Confirming PO 01 164 "Y" if confirming
Order Date 08 165 Date Format
Promise (Expected) Date 08 173 Date Format
Sales Tax Percentage 08 181 Numeric 3.4
Discount Percentage 08 189 Numeric 3.4
Total Estimated Tax 10 197 Numeric 8.2
Total Estimated Freight 10 207 Numeric 8.2
Total Estimated Discount 10 217 Numeric 8.2
Spaces 22 227 Reserved
CR/LF 02 249 End of Record
Record Length = 250 bytes
Drop Ship, Paid Out, and New Vendor Add-On-The-Fly Info.
Record Type 01 01 "S", "P", or "N"
AP Code (New Vendor) 02 02 A/N
Name 30 04 A/N
Address Line 1 30 34 A/N
Address Line 2 30 64 A/N
City 15 94 A/N
State 02 109 A/N
Zip Code 05 111 A/N
Phone 10 116 EX: 2083442540
Spaces 123 126 Reserved
CR/LF 02 249 End of Record
Record Length = 250 bytes
PO Detail Record, at least one must exist.
Record Type 01 01 "D"
Line Type 01 02 "1"=GL Acct, "2"=Stock #
Stock #/GL Expense Acct 28 03 A/N Stock Number uses only 15
Vendor Stock Number 20 31 A/N
Description 30 51 A/N
Taxable Flag 01 81 "Y" if Taxable
Allow Discount Flag 01 82 "Y" if Discountable
Promise (Expected) Date 08 83 Date Format
Quantity Ordered 12 91 Numeric 7.4
Estimated Cost 12 103 Numeric 7.4
Encumbered Fund (Up to first 6 chars)/Committed GL Account (Act
Package)
15 115 A/N
Spaces 119 130 Reserved
CR/LF 02 249 End of Record
Record Length = 250 bytes
