Collector Flat and XML Files

Overview

Collector uploads are designed to accept feeds from departments billing other units at your institution. This process does some file validation and has built in email communications back to the customer using it. The collector is best used for feeding in General Ledger entries that are not coming from an enterprise-level system and may be prone to errors. Collector files can be in .xml format or loaded as flat text files. Uploaded Collector files will be processed the next time the collectorJob batch job is run.

In addition to the standard formatting rules that apply to all batch upload file formats, keep the following points in mind about the flat file format:

Collector Flat File Upload

• Each Collector flat file must begin with a header record.
• Any number of GL entry records may follow the header. Optional detail entries may also be added.
• Each Collector flat file must end with a trailer record summarizing the total number of records in the file and the dollar amount of that file.
• Multiple batches of entries may be submitted in a single file as long as each batch of entries has its own header and trailer record.
• The parameter, DUPLICATE_HEADER_IND, determines whether or not each batch of collector entries must have a unique header (and duplicates are not allowed).

Collector flat file format

Name	Starting Character Position	Length	Required?	Special Formatting
Header – 172 Characters
Fiscal Year	1	4	Yes
Chart of Accounts Code	5	2	Yes
Organization Code	7	4	Yes
[blank spaces]	11	5	Yes
Transmission Date	16	10	Yes	YYYY-MM-DD format
HD (literal value prefacing sequence number)	26	2	Yes	Value is always HD
Batch Sequence Number	28	1	Yes	Must be a digit between 0-9
Email Address	29	40	Yes
Department Contact Person	69	30	Yes
Department Name	99	30	Yes
Campus Mailing Address	129	30	Yes
Campus Code	159	2	Yes
Department Contact Phone Number	161	10	Yes
[blank spaces]	171	2	Yes
GL Entry – 187 Characters
Fiscal Year	1	4	No
Chart of Accounts Code	5	2	Yes	Can be left blank if accounts do not cross charts (KFS-SYS ACCOUNTS_CAN_CROSS_CHARTS_IND parameter is set to N)
Account Number	7	7	Yes
Sub-Account	14	5	No
Object Code	19	4	Yes
Sub-Object Code	23	3	No
Balance Type	26	2	Yes
Object Type	28	2	No
Fiscal Accounting Period	30	2	No
Document Type	32	4	Yes
Origination Code	36	2	Yes
Document Number	38	14	Yes
Sequence Number	52	5	No
Description	57	40	Yes
[blank space]	97	1	Yes
Transaction Dollar Amount	98	20	Yes	Money format (two decimal places) right-aligned. (minus sign for negative amounts when Balance Type requires blank Debit/Credit Code)
Debit/Credit Code	118	1	Yes	Must be blank if balance type does not require D/C codes. For example, BB, CB and MB balance types.
Transaction Date	119	10	No	YYYY-MM-DD format
Organization Document Number	129	10	No
Project Code	139	10	No
Organization Reference ID	149	8	No
Reference Document Type Code	157	4	No
Reference Origination Code	161	2	No
Reference Document Number	163	14	No
Reversal Date	177	10	No	YYYY-MM-DD format
Encumbrance Update Code	187	1	No	Valid values are R and D
Detail Record (optional) – 192 Characters
Fiscal Year	1	4	No
Chart of Accounts Code	5	2	Yes	Can be left blank if the GL Pre-Scrubber is used and accounts do not cross charts (Financials-SYS ACCOUNTS_CAN_CROSS_CHARTS_IND parameter must be set to N)
Account Number	7	7	Yes
Sub-Account Number	14	5	No
Object Code	19	4	Yes
Sub-Object Code	23	3	No
DT (literal value delineating a Detail Record)	26	2	Yes	Value is always DT
Object Type	28	2	No
Fiscal Period	30	2	No	If left blank, current fiscal period will be used. Must match the fiscal period in the GL Entry section.
Document Type Code	32	4	Yes
Origination Code	36	2	Yes
Document Number	38	14	Yes
Dollar Amount	52	20	Yes	Money format (two decimal places) right-aligned. (minus sign for negative amounts when Balance Type requires blank Debit/Credit Code)
Debit/Credit Code	72	1	Yes	Must be blank if balance type does not require D/C codes. For example, BB, CB and MB balance types.
Additional Explanation	73	120	No
Trailer Record – 112 Characters
[blank spaces]	1	25	Yes
TL (literal value indicating a Trailer Record)	26	2	Yes	Value is always TL
[blank spaces]	28	19	Yes
Number of records in the file	47	5	Yes	Include all GL Entry and Detail records but not Header and Trailer records
[blank spaces]	52	41	Yes
File Amount	93	20	Yes	Money format (two decimal places) right-aligned.

Collector XML File Upload

In addition to the standard formatting rules that apply to all batch upload file formats, keep the following points in mind about the XML Collector format:

• The file must begin with the standard XML version line.
• The root (first) tag for collector files must be the  batch  tag, and the file must contain only one batch tag. This tag also contains attributes that identify the schema to which the document adheres.
• Following the batch tag must be the header tag. This section gives general information about the set of records being transmitted. Like the batch tag, the file must contain only one header tag. Within the header tag are other tags that identify pieces of header information.
• Next comes a glEntry tag. This tag contains other tags that represent the fields of the origin entry table. Each glEntry  tag represents one row in the origin entry table.
• Following the gl entry tag is an optional  detail  tag. This tag contains other tags that represent fields of the inter-departmental billing table. Each detail tag represents one row in this table.
• The sequence of glEntry and detail tags can repeat one or many times. Since the detail tag is optional in each sequence, there can be many glEntry tags back to back.
• The final tag required for a collector batch file is the trailer tag. This contains tags for reconciliation of the file. The file must contain only one trailer tag.

GL XML Collector format

Name	Type	Max Size	Required?	Special Formatting
Header
chartOfAccountsCode	Characters	2	Yes
organizationCode	Characters	4	Yes
transmissionDate	Date		Yes	YYYY-MM-DD format
batchSequenceNumber	Integer		Yes
personUserId	Characters	8	Yes
emailAddress	Characters	70	Yes
campusCode	Characters	2	Yes
phoneNumber	Characters	20	Yes
mailingAddress	Characters	30	Yes
departmentName	Characters	30	Yes
GL Entry
universityFiscalYear	Integer	4	No
chartOfAccountsCode	Characters	2	Yes
accountNumber	Characters	7	Yes
subAccountNumber	Characters	5	No
objectCode	Characters	4	Yes
subObjectCode	Characters	3	No
balanceTypeCode	Characters	2	Yes
objectTypeCode	Characters	2	No
universityFiscalAccountingPeriod	Characters	2	No
documentTypeCode	Characters	4	Yes
originationCode	Characters	2	Yes
documentNumber	Characters	14	Yes
transactionEntrySequenceId	Integer	5	No
transactionLedgerEntryDescription	Characters	40	Yes
transactionLedgerEntryAmount	Decimal		Yes	Money Format (2 decimal places)
debitOrCreditCode	Characters	1	Yes
transactionDate	Date		Yes	YYYY-MM-DD Format
organizationDocumentNumber	Characters	10	No
projectCode	Characters	10	No
organizationReferenceId	Characters	8	No
referenceDocumentTypeCode	Characters	4	No
referenceOriginationCode	Characters	2	No
referenceDocumentNumber	Characters	14	No
documentReversalDate	Date		No	YYYY-MM-DD Format
encumbranceUpdateCode	Characters	1	No	Valid values are R and D
Detail
universityFiscalAccountingPeriod	Characters	2	Yes
universityFiscalYear	Integer	4	Yes
createDate	Date		No	YYYY-MM-DD Format
chartOfAccountsCode	Characters	2	Yes
accountNumber	Characters	7	Yes
subAccountNumber	Characters	5	No
objectCode	Characters	4	Yes
subObjectCode	Characters	3	No
collectorDetailSequenceNumber	Integer	2	Yes
originationCode	Characters	2	Yes
documentTypeCode	Characters	4	Yes
documentNumber	Characters	14	Yes
amount	Decimal		Yes	Money format (2 decimal places)
detailText	Characters	120	Yes
createDate	Date		Yes	YYYY-MM-DD Format
balanceTypeCode	Characters	2	Yes
objectTypeCode	Characters	2	No
Trailer
totalRecords	Integer		Yes
totalAmount	Decimal		Yes	Money format (2 decimal places)