|
|
|
|
FILE
DEFINITIONS 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 explainations 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">
FILE
DEFINITIONS 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 explainations 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 characterplaces. 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
|