Specification for Invoice Upload Process

Introduction

The purpose of this document is to describe the file layout required for invoice documents to be loaded into the BANNER invoice tables. BANNER does not deliver a suitable batch interface procedure and so this interface was designed at McGill for its own use. This interface format will be used process invoice documents provided by internal McGill departments (e.g. libraries, facilities management), suppliers (e.g. Fischer Scientific) and banking institutions (BofM MasterCard Purchasing Card – PCARD).

This format will work in tandem with a control record defined specifically for your SYSTEM_ID. This control record will define information specific to your interface including BANK CODE information, E-mail address to contact in case of errors, default commodity description, etc. The data contained on this control record will be determined at the time that your SYSTEM_ID is being defined to BANNER.

Format

Information passed to the Accounting Department should be sent in ASCII format according to the transaction layouts described below.

Every record in the file will require Transaction Prefix Information. Appended to that Transaction Prefix Information should be either Level 1 (Invoice Header) information, Level 2 (Accounting Distribution) information or Level 3 (Commodity Distribution) information. Level 3 information is not currently being supported but is included here for future expansion reasons only.

Every invoice document should include a single Level 1 record and as many Level 2 records as is required depending on the number of Accounting Distributions for that invoice document. Level 3 information will be determined by information supplied on the Level 1 record and from the SYSTEM_ID control record.

For example, if you want to process an invoice document with 2 Accounting Distribution records you would need to supply 3 records for that vendor invoice:

1)  Transaction Prefix Information + Level 1 (Invoice Header) record

2)  Transaction Prefix Information + first Level 2 (Accounting Distribution) record

3)  Transaction Prefix Information + second Level 2 (Accounting Distribution) record

Invoice Upload Transaction Format

Invoice Upload Transaction
Field Names / Description / General Rules on Filling
In This field /
TRANSACTION
PREFIX
INFORMATION / This information must be provided for all records in the file whether they be LEVEL_TYPE 1, 2, or 3 (not yet supported). An explanation of the meaning of these Level type follows in this document.
SYSTEM_ID
8 characters
(Alphanumeric)
/ This is the transaction (process) identifier. This field must be specified.
The SYSTEM_ID is used to determine a number of editing and processing rules concerning the rest of the data you are providing on this record. It is therefore very important that you specify the correct SYSTEM_ID for the system/process for which you are passing information. Different SYSTEM_ID’s will have different rules associated with them.
*** If you are not sure which SYSTEM_ID to use, especially if your department is responsible for submitting data to Accounting from more than one source or for more than one reason, do not use a SYSTEM_ID without first verifying with Accounting or ISR that it is the correct one.
Example of system ID’s are as follows:
ACTRECV Accounts Receivable Interface
MARTLET1 Alumni Interface for reason a
MARTLET2 Alumni Interface for reason b
BDGTFRWD Budget Carry Forward at Year End
BUDGET Budget Roll
PAYROLL Payroll Interface / Always fill in this field with the specific SYSTEM_ID assigned to you for this transaction feed.
FILE_ID
8 characters
(Alphanumeric) / The FILE_ID is designed to be used as a control mechanism to prevent any given file from being updated more than once onto the BANNER Finance system. (It will also be the file reference by which we will communicate any problems to you regarding your file.) If errors occur in your file, they will be reported to you by the FILE_ID. / A suggested format for this field might be an 8 character date (YYYYMMDD) or your own control sequence number. For control purposes, we will store the combination of SYSTEM_ID and FILE_ID upon successful update of this file to prevent the file from being posted twice.
e.g. 20000129 (date)
AB000001 (seq)
VEND_ID
9 characters
(Numeric) / A unique BANNER vendor identification number assigned to the supplier. / If you are currently using a MAPIS vendor number to submit payment vouchers to Accounts Payable, a conversion table will be made available crosswalking the MAPIS vendor number (9 characters – 5 alpha followed by 4 numeric) to the new BANNER vendor ID.
E.G. 160001234
The exact location of this data source will be publicized once it has been finalized.
VEND_INV_CODE
Up to 15 characters
(Alphanumeric) / Vendor invoice number as specified on the vendor invoice.
LEVEL_TYPE
1 character
(Numeric) / A 1 character code that indicates the type or level of invoice data for this record. / Level 1 – Invoice header record
Level 2 – Invoice Accounting distribution
Record
Level 3 – Invoice Commodity distribution
Record (not supported at the
moment)
LEVEL_TYPE_SEQ_NUM
1 character
(Numeric) / A 1 character 1-up sequence number that when combined with the Level_Type allows for a unique combination per invoice document. / LEVEL_TYPE 1 records should have ‘1’ (one) as the LEVEL_TYPE_SEQ_NUM.
LEVEL_TYPE 2 should have a sequence number starting at ‘1’ (one) and increments by one for each accounting distribution record for that specific invoice number. This implies a maximum of 9 accounting distribution records (and 9 commodity distribution records should this be implemented) per invoice payment voucher document.
LEVEL 1 INFORMATION / Invoice Header information to be appended to Transaction Prefix Information for Level 1 record types.
VEND_INV_DATE
8 characters
(yyyymmdd) / Vendor's invoice date as indicated on the vendor invoice. The payment due date will be determined based on this vendor invoice date.
TRANS_DATE
8 characters
(yyyymmdd) / This is the effective date of the transaction in YYYYMMDD format. It is important to note that the year/month of this date are used to determine the correct fiscal period for posting to the BANNER ledgers. This means you must always enter the year/month of the period for which you are posting, rather than today’s date.
For example, if you are submitting transactions on Nov 2 1999 to be posted in October, enter something like 19991031. / ·  It must be in YYYYMMDD format where year/month refers to the posting period you wish to affect.
·  Note: once a month is closed you will not be able to post to it.
BASE_AMT
13 characters (numeric), show the decimal
(e.g 1234567890.12) / Total invoice amount not including taxes nor additional amount and before any discount has been applied.
BANNER likes to call this the APPROVED amount.
GST_AMT
13 characters (numeric), show the decimal
(e.g 1234567890.12) / Total GST amount for the invoice. / Even if there is no GST, a value of 0.00 must be entered.
In the case where the GST is being self-assessed, the assessed GST amount should be entered.
QST_AMT
13 characters (numeric), show the decimal
(e.g 1234567890.12) / Total QST amount for the invoice. / Even if there is no QST, a value of 0.00 must be entered.
In the case where the QST is being self-assessed, the assessed QST amount should be entered.
ADDL_CHG_AMT
13 characters (numeric), show the decimal
(e.g 1234567890.12) / Any other charges that need to be added to the invoice amount. This amount is not taxable. / Both negative and positive additional charge amount values are accepted.
If there is no additional charge, enter 0.00
TAX_DISTR_METHOD
1 character / This value indicates whether the data provider will provide the tax distributions for each accounting distribution record (and commodity distribution record in the future) or the interface program will pro-rate the total tax amounts across the account distribution records pro rata. / Possible values are:
-  'U' for User
-  'I' for Interface program
‘U’ should be the default value
TAXGRP_OVERRIDE
4 characters / By default, the tax group for all invoices processed by McGill will have the value of MCGT. This tax group stipulates that the commodities on this invoice are all subject to GST and QST regardless whether the vendor has billed for them.
Note: When a commodity is eligible for a tax but the vendor does not invoice it, this is what we used to call “self-assessment”
Should this default tax group of MCGT need to be overridden at the invoice level, the user can enter another value. / Besides MCGT, the other possible values are:
-  GST - This tax group stipulates that the commodities on this invoice are all subject to GST only (no QST) regardless whether the vendor has billed for it.
-  QST - This tax group stipulates that the commodities on this invoice are subject to QST only (no GST) regardless whether the vendor has billed for it.
-  NT – This tax group stipulates that the commodities on this invoice are not eligible for any taxes.
CURR_CODE
4 characters / The Currency Code indicates the currency in which the transaction amount is expressed. / Possible values:
CAD – Canadian Dollars
USD – US dollars
If nothing entered, we will default to Canadian dollars CAD.
CR_MEMO_IND
1 character / Indicates whether this voucher is a credit memo.
A credit memo is processed like an invoice and has the benefit of reversing all of the tax logic associated with invoice processing. / Possible values are
-  Blank – regular invoice
-  'C' – credit memo
DISC_IND
1 characters / This indicates whether to apply a discount to the invoice amount or not. / Possible values are:
-  'Y' yes to apply discount
-  'N' do not apply discount
DISC_OVERRIDE_CODE
2 characters / A 2 character discount code to override the rate and terms of the regular default discount offered by the supplier. This discount is applied on the BASE invoice amount (without taxes). / If you entered a DISC_IND of ‘N’, then this field must be blank.
If you entered a ‘Y’ in the DISC_IND field, the you have the option of overriding the regular discount offered by the supplier.
If the DISC_OVERRIDE_CODE is not specified, then the vendor default discount code will be applied.
For a complete list of discount codes, refer to the Banner Finance Web site.
VEND_CHECK_ID
9 characters / Should you wish to have the cheque cut to a vendor other than the vendor of record (e.g. a disbursement service), specify the third party vendor ID here.
ATYP_CODE
2 characters / This represents the address type code for the address to which you wish you remit the cheque whether it be to the vendor of record or a third party.
If you do not specify an address type code here, the cheque will be remitted to an address determined by the rule of thumb described below.
Needs to indicate which vendor type code to use so that the check can be made to the right address. There is a default value for the vendor indicated; but if the department would like to have the check made for another address associated to the vendor, it should be indicated here which vendor type code to use.
Rule of thumb: If the vendor has an AP (Accounts Payable) default address, it will be used for cheque remittance, else 'BU' (Business) default address will be used. / Possible values are:
-  'BU' – Business
-  'AP' – Accounts Payable
-  blank – use default from vendor file
ATYP_SEQ_NUM
2 characters
(Numeric) / Address type sequence numbers allow for many instances of an Address Type code. For example, you may have a supplier that has two Accounts Payable addresses. These addresses can be associated to the vendor as AP/1 and AP/2.
This field, along with the ATYP_CODE that precedes it, can be used to override the default Accounts Payable addresses normally defaulted for cheque remittance.
LEVEL 2 INFORMATION / Accounting Distribution record information to be appended to Transaction Prefix Information for Level 2 record types.
PERIOD
2 characters
(Numeric) / Indicate the period in which the transaction is to be posted. / The McGill fiscal year is defined as June to May. Therefore, the period identifiers are defined as follows:
01 - June,
02 - July
03 – August
04 – September
05 – October
06 – November
07 – December
08 – January
09 – February
10 – March
11 – April
12 – May
BASE_CHARGE_AMT
13 characters (numeric), show the decimal
(e.g 1234567890.12) / Amount associated with the indicated Accounting Distribution string. This amount should not include taxes. / You must distribute the BASE_AMT (from the Level 1 Invoice Header record) across the Accounting Distribution records ensuring that the sum of BASE_CHARGE_AMT amounts across all Level 2 Accounting Distribution records equals the BASE_AMT specified on the Level 1 Invoice header record.
This amount must be greater than zero.
GST_CHARGE_AMT
13 characters (numeric), show the decimal
(e.g 1234567890.12) / GST amount associated with the indicated Accounting Distribution string. / If you have chosen a TAX_DISTR_METHOD of ‘I’ on the LEVEL 1 Invoice Header record (which instructs the interface program to calculate taxes pro-rata across the Accounting Distribution records), you can leave these fields blank.
Otherwise
You must distribute the GST across the Accounting Distribution records ensuring that the sum of GST amounts across all Level 2 Accounting Distribution records equals the GST_AMT specified on the Level 1 Invoice header record.