Interface Profile Menu

The Interface Profile Menu provides access to the available file uploads into TSC. Up to 35 different file formats can be defined for each type of interface.
When the Format option is selected, the system presents the following menu:

Cheque File

• The Cheque File program provides the ability to define and enter cheques from an external source to the TSC system. The cheque file must be in an ASCII format and the following format profile gives you the ability to describe the location of the fields within your file. Once completed, this profile can be used in the cheque file processing module to import the file and load it to the TSC cheque file.
• The interface format for the cheque file looks like this:

Dictionary File

• The Dictionary File can be created from an external file using this module. The following elements defined in the profile are used to describe the location of the fields in the input text file (ASCII). Since one of these files can be created by a report run from either the atrieveFinance system or any other system, this can be used to create a dictionary for the TSC accounting system. This file can also be created using a text editor.
• The interface format for the dictionary file looks like this:
• Each field requires the entry of a start position and the length of field found in the external file to be interfaced to the system.
• The 'MAX' fields show the maximum length of field used by the TSC system.

Start Position

• Enter the start position in the interface input file where the field data begins. The first position in the file is '001'.

Length

• Enter the length of the field in your interface input file (cannot exceed maximum indicated).

Record Length or Record Segment

• Enter the physical length of the record in the interface file. This length is used by the program to open the file and Must BE the file's proper record length.

Format Name

• This is the descriptive field that is displayed on the processing selection and on profile maintenance screens so that you can identify various formats.

Budget File

• The Budget File in atrieveFinance can be created or updated by using an external file. This file can be a standard text file or a delimited interface file (created by a spreadsheet). The following format specifications must be completed to load budget transactions from an external source. The standard text interface file format is used to describe the location of the fields in the input text file (ASCII).
• Sample profile for a fixed format file:
• Sample profile for a comma delimited file:

Format Name

• This descriptive field displays on the processing selection and profile maintenance screens so that you can identify various formats.
• This process will support more than one interface profile for various budget interfaces. An interface from an Excel spreadsheet is normally a delimited format.

Delimited/Fixed Format 

• Your interface will be in one of the following two formats:
• Enter 'D' for a delimited format and indicate the delimiter (tab or comma).
• Enter 'F' for if the information is in a fixed format.
• A Fixed format file is a file where the information can be extracted by specifying a start position and length of each field.

eg. 01010044112019970102107.00 7.00 CANADIAN TIRE

| GL account | date | amount | GST | Vendor name |

• A Delimited format is a file where the information is separated by a delimiter (tab or comma).

eg. 010100441120,19970102,107.00,7.00,CANADIAN TIRE

• This field will be different depending on whether the file is delimited or fixed.

Record Length

• Enter the physical length of the record in the interface file. This length is used by the program to open the file and Must be the file's proper record length.

Comma/Tab Delimited

• Enter 'C' if the fields in the interface file are separated by commas.
• Enter 'T' if the fields in the interface file are separated by tabs.

Backup Location

• Enter the location of the directory where the interface files will be stored once processed.
• The default directory is stored in SRB$INTBCK. If this logical is defined, its location is displayed when you add a new profile. You can override this location by typing in another backup location. If you leave the backup location blank, the interface files will not be backed up.

Field Order

• Fields 5-11 display if your file is in a delimited format. Identify the order of the fields in the interface file by using the numbers 1-7 with 1 being the first item of data. The example above corresponds to a sample spreadsheet format.

Start Position - Length

• These fields display if your interface file is in a fixed format. You must define the start position and length for each field in your interface file that corresponds to fields Ø5-11. The length cannot exceed the maximum length defined beside each field.

Source Code

• This is a 2-character code identifying the source of the entries when using budget transaction inquiry in the GL inquiry. This interface automatically sets up the source code. It does not need to be defined in this profile. Source codes used will be:

BO - Original Budget entry

and

BR - Revised Budget entry.

Transaction Date

• This field is not used in this interface. The transaction date will automatically load in as the system date.

GL Account Code

• This is the location of the GL account code (maximum 20 characters) and must be an account code which is valid in the current year of your general ledger.

SL Account Code

• This field is not used in this interface.

Reference Data

• This is the descriptive information stored in the reference area on the budget transaction details. This reference data can be loaded in from your interface file to a maximum of 27 characters. This field is commonly used to describe the reason for budget entry or revision and can be included in the file or entered at run time. If this field is entered in the file, entering a description at run time will NOT override the description from the file.

Budget Amount

• This is the location of any dollar amount that is to affect the budget in the atrieveFinance system. The amount must be entered as a normal string and may contain decimals.
• Negative amounts must have the minus sign in front of the number on the left side. It must not contain dollar signs or commas.

Budget Ref #

• This is the location of a reference or document number which identifies the source of the budget. This can be filled with a common description such as budget/98 if the field is not to be utilized for any specific document numbers. This field can be included in the file or entered at run time. If this field is entered in the file, entering a reference number at run time will allow you the option of overriding the description in the file.

Invoice File

• The Invoice File interface program reads two interface files, one containing invoice information and the second containing the distributions or charges to the various GL codes associated with each invoice. The invoice number is issued as a matching item between the two files so that charges related to the individual invoices can be accumulated and balanced against the invoice amount. If they don't balance or if for any reason one of the documents related to the set is rejected, the entire set is written out to files containing the error records. There is an output invoice error file and an output journal entry error file. Since both files are text files, corrections can be made and the items can then be used as input again without re-entry.
• These programs give you the capability of loading transactions created on other systems into your TSC accounting files for payment through accounts payable. If you think you have an application for the use of these modules and are not certain how to get the information into them, please contact the support office for assistance.
• Both files must be variable length sequential.
• The interface file for the Invoice File looks like this:
• The Invoice Interface for MAR Refunds looks like this:
• Note: Field 16 would represent the number of the journal entry interface profile for MAR refunds.
• The Invoice Interface for PAYROLL Invoices looks like:
• Note: Field 16 would represent the number of the journal entry interface profile for the payroll invoice.
• The Invoice Interface for CRS Refunds (cash/cheque) looks like this:
• Note: Field 16 would represent the number of the journal entry interface profile for CRS refunds.
• Each field requires the entry of the start position and length of field found in the external file to be interfaced to the system.
• The 'MAX' fields show the maximum length of field used by the TSC system.

Start Position

• Enter the start position in the interface input file where the field data begins. The first position in the file is '001'.

Length

• Enter the length of the field in your interface input file (cannot exceed maximum indicated).

Vendor #

• This field location must contain a valid vendor number found in the vendor file of your accounting system. The vendor number may be the number of a miscellaneous vendor in which case the vendor name and address information found in the following fields must be completed.

Invoice #

• This is the invoice number to be used to load the invoice document and must be unique. If the number is found to be duplicated within the vendor, the document will be rejected along with its related journal entries. In addition, this number must be found in Field #9, Expenditure Ref Number, of the journal entry file attached to this invoice document. The interface program will accumulate all journal entries found in the journal entry file that have the same invoice number. The total amount of the distributions from those invoices must equal the total dollar value of this invoice included in Field 5. If they do not balance, the invoice and its related journal entries are rejected and included in reject files which can be corrected and re-enter in a second pass.

Due Date

• This can be the due date of the invoice documents and must be entered as a valid date in the format specified, 4 digits for year, 2 for month, 2 for day.

Invoice Date

• This is the date of the invoice in the same format as the due date.

Invoice Amount

• This is the invoice amount and must be entered as a valid numeric string. It may contain a minus sign on the left-hand side of the number. It may also contain a decimal point. It must not include dollar signs or commas. This amount will be used to compare to the total of the distributions found in the journal entry file to balance the invoice.

Reference

• You may enter up to 99 characters of referenced text in your invoice interface file. This will be loaded into the reference field area on the invoice.

Vendor Name

• The vendor name is to be filled in on any transaction where the type related to the vendor number used for the transaction is a Type 99. Type 99 is a miscellaneous vendor and the transaction expects to find name and address information in the following fields.

Vendor Address1

Vendor Address2

Vendor Address3

Vendor Address4

• The 4 address lines may contain address information to be included for the invoice document for miscellaneous vendors.

Postal Code

• This field should contain a valid postal code or zip code.

Bank Number

• Bank number is used in accounts payable to collect invoices into groups for payment. You can use this field on the interface record to put invoices from a single interface batch into more than one bank number. This field is optional. If it is not taken from the interface file it will be taken from the batch code used for the interface run.

Tax Reportable

• The field on the interface record allows you to mark invoices as tax reportable. When invoices are marked, they can be selected for T4A's by the T4A selection process. A 'Y' in the interface record will mark the invoice as tax reportable. Blank or any other characters will be ignored.

Record Length or Record Segment

• Enter the physical length of the record in the interface file. This length is used by the program to open the file and Must BE the file's proper record length.

JE Format Number

• This is the number of the journal entry format set up in Section 2 of the interface profiles. In that profile you must describe the format of your journal entry file that corresponds to this invoice file. The number used for that format is displayed on the menu when you do maintenance on that profile item. That number must be included here. During interface processing, the program uses this number to read the journal entry format which is then used to read the journal entries which correspond to each invoice.

Format Name

• This is the descriptive field that is displayed on the processing selection and on profile maintenance screens so that you can identify various formats.

Automatic Cheque Reconciliation

• The Automatic Cheque Reconciliation program provides the ability to receive a formatted file containing cheque clearing information and to automatically process it against the atrieveFinance cheque file, changing the cheque status from outstanding to returned.
• The automatic cheque reconciliation program should work for most banks data files. The main difference that has been noted is the date format field, which tends to be bank specific.
• Date formats currently supported are:

YYMMDD - Standard international date format (any order ddmmyy etc)

YYYYMMMDD

or

YYYYMMDD - Extended international date format (any order ddmmmyyyy

ddmmyyyy etc)

DDMM - CIBC date format (requires user profile setting to 'Y')

• The profile is separated into two sections. The first (upper) section provides atrieveFinance with defaults pertaining to the treatment of the information being interfaced. The second (lower) section defines the layout in the file received from the bank.
• Profiles

Microsoft Open Financial Connectivity

CIBC

CIBC OFC FORMAT

BANK OF HONG KONG/BANK OF BC

BANK OF MONTREAL

Format Name

• This descriptive field displays on the processing selection and profile maintenance screens so that you can identify various formats.

Delimited/Fixed/OFC

• This field describes the file format of the interface file. Supported formats are: Delimited (Tab and Comma) Fixed Length, and Microsoft OFC. What type of file you are working with is information normally provided by the bank along with how the information is structured. Use this information to setup the second section of the profile.
• A fixed format file is one whose information can be extracted because the starting position and length of each data field is known.

eg. 80 19970102 -180.00

| MICR# | DATE | AMOUNT|

• A delimited format file's information is stored with a specified character separating each field.

eg. 80,19970102,-180.00

• If you select Delimited as the file format you will be further prompted to indicate what the delimiting character is, comma or tab.
• The information in a Microsoft OFC file is separated with Tags.

eg. <STMTTRN>

<TRNTYPE>1

<DTPOSTED>19970102

<TRNAMT>-180.00

<FITID>103117119000012010249124510298

<CHKNUM>80

<Name>Cheque # 80

<\STMTTRN>

• Note: Royal Bank of Canada format uses a <TRNTYPE> of CHECK or DEBIT.

Date Format

• Enter the format of the date field in the file, e.g. 'YYMMDD' or 'DDMMMYYYY'.
• CIBC requires 'DDMM' in this field. Refer to your bank documentation for the appropriate format.
• Note: You Must set up field #26 - 'USE CIBC Date, format for Auto Cheque in the User profile if you are using the CIBC date format.

Tape Format

• This field is normally set to 'ASCII'. The 'EBCDIC' setting is for tapes cut on an IBM main frame. (Currently only the Bank of Montreal delivers tapes in this format)

Tape Drive Name

• This field allows you to enter a tape drive name, allowing the reconciliation program to read data straight from a tape drive. This feature is currently used by Bank of Montreal customers as Bank of Hong Kong and CIBC deliver their data on a PC floppy disk or allow the customer to access their computer and retrieve the data themselves.

Match On Cheque/Micr #

• For most customers this will be set to MICR #. Matching on cheque number is an option available to users of Acuprint cheque printing systems, where the cheque number is the same as the MICR # on each cheque.

Fields 07 To 09

• Delimited: Enter the order in which the fields appear in the file.
• Fixed Length: Each field requires the entry of a start position and the length of field found in the external file to be interfaced to the system.
• The 'MAX' fields show the maximum length of field used by the TSC system.
• Start Position: Enter the start position in the interface input file where the field data begins. The first position in the file is '001'.
• Length: Enter the field length in your interface input file. The length cannot exceed the maximum length indicated on the field.
• This file format requires that the additional field Record Length. Enter the physical length of the record in the interface file. This length is used by the program to open the file and Must be the file's proper record length.
• MICROSOFT OFC: Enter in the Tag that corresponds with each required field.

Purchasing Card Interface Profile

• The Purchasing Card Interface Profile enables the user to define how atrieveFinance is to treat information being interfaced from the file provided by a financial purchasing card application. This profile can also be used to upload invoices for miscellaneous vendors. 
• If using the PCARD to upload miscellaneous vendor invoices:
• VISA Vendor must be the miscellaneous vendor number.
• Vendor Validation must be set to 'No'.
• Store name should be used for the JE description as the cardholder is not applicable. This will require the vendor name to be defined in the file.
• Miscellaneous vendor address information must be included in the file otherwise this information will not be included on the invoice.
• The profile is separated into two sections. The first screen provides atrieveFinance with defaults pertaining to the treatment of the information being interfaced. The second screen defines the layout in the pcard transaction file.
• Screen 1 – Profile Information
• Format name
• Enter the name you would like to associate with this file format. The PCard Interface can also be used for summary billings from specific vendors. Up to 35 different formats can be defined.

Delimited/Fixed Format

• The file exported by the bank's software will come in one of two formats. What type of file you are working with is information normally provided by the bank along with how the information is structured. Use this information to setup the second section of the profile. The file format cannot be changed once accepted. A new file format would need to be created.
• A fixed format file is one that's information can be extracted because the starting position and the length of each data field is known.

eg. 0010220340519970102 123.34 8.63 CANADIAN TIRE

| G/L | DATE | AMOUNT|GST/HST| VENDOR Name |

• A delimited format file's information is stored with a specified character separating each field.

eg. 00102203405,19970102,123.34,8.63,CANADIAN TIRE

• If you select Delimited as the file format you will be further prompted to indicate what the delimiting character is, comma or tab.

VISA Vendor

• Enter the vendor number being paid for the goods acquired using a purchasing card.

Source Code

• Enter the source code (maximum 2) to use to identify transactions posted via the purchasing card interface.

Payables Type Code

• Enter payables type code (maximum 2) these transactions are to be affiliated with.

Default Batch Code

• Enter the batch code that will be used for this process.

Compress Visa Cheque Stub

• Enter 'Y' if the cheque stub information on this VISA vendor is to be eliminated from the cheque printing process. The final cheque run and online cheque process will look at this profile to determine if compression should take place for this vendor.
• Enter 'N' if the invoice information is to appear on the cheque stubs in the cheque printing process. If your interface file is large, it can cause the cheque printing program to cancel many cheques in order to print out the stub information.

Exclude Public Bodies

• Enter 'Y' if the Public Bodies information is not to be created by this process. This would be for any customers who do not have to produce a public bodies report at the end of each year. (ie Statement of Financial Information).
• Enter 'N' if Public Bodies information is to be created in the reference field on the invoice file during the interface process. This should be set to 'N' for any customers who have to produce a public bodies report at the end of the year.

Vendor Validation

• Enter 'Y' to validate the vendor against the vendor file. If the vendor does not exist in the vendor file, an error is generated for that vendor and the user will have the ability to correct the vendor with a vendor from the vendor file.
• If the Vendor Validation is set to Y, the system will search the Vendor File for a matching vendor name. When a partial name match is detected this process will now display both the original name (from upload) and the matched names so you can visually verify that the correct vendor has been selected.
• Note: This was an enhancement request to provide a means of showing the entry clerk that an exact vendor match was not found. It is merely a warning message to advise that the system match should be verified. The interface can be accepted when the message of 'Partial' is present.

Invoice # Prefix

• Enter a user defined invoice prefix identifier (maximum 2). The default is 'PC'.

PCard GST Application Rate

• Enter the override GST rate you want applied to the transaction amount. If GST is active ('Y') in the atrieveFinance master profile (), the tax amount is based on the following order of precedence;

1. 1. GST rate defined in this field
   2. GST rate defined in the atrieveFinance user profile ( - field #50)
   3. GST amount in the upload file

• Therefore, if the GST tax amount is defined in the upload file, this field, and the atrieveFinance user profile field must both be blank.
• If you want to define a GST rate, consider the following example:
• To set up this rate, consider a sample transaction of $114.00 (includes $7 PST and $7 GST) If you know that this transaction includes GST of $7, then the GST Application Rate you should enter here is equal to $114.00 divided by $7, or 0.061403508. This same rate will then be used to calculate GST on all PCard transactions.

Pcard Cardholder or Store Name On JE

• This option allows the user to control what goes into the journal entry reference field. If this field is left blank, the store name will be used.
• Enter 'C' for Cardholder or 'S' for Vendor Name to be stored on the Journal Entry.

Field # To Change

• Enter the number from the screen for the field you want to modify. It will then take you to that field and let you modify it according to its set of rules.
• Depending on what was entered at field 02, (Fixed or Delimited) one of the following processes will be presented. In both cases, the fields offered are the same; the difference will be your answer to the prompts.
• Screen 2 – File Format Information
• Depending on what was entered in field 02 on the first screen, (Fixed or Delimited) one of the following two screens will display.
• Screen 2 - Option 1 Fixed Format Files

Start Position

• Enter the start position in the interface input file where the data begins. The first position in the file is '001'.

Length

• Enter the length of the field in your interface input file (cannot exceed maximum indicated).
• Screen 2 Option 2 Delimited Format Files

Field Order

• Currently 11 different pieces of information are interfaced through this process.
• Enter the number 1 through 11 that corresponds to the data's position in the record, one being the first piece of data in the record.
• The following menu options are available for both fixed and delimited file formats:

Field # To Change

• Enter the number from the screen for the field you want to modify. It will then take you to that field and let you modify it according to its set of rules.
• Specific field formats:

G/L account

• Sub-ledgers can be interfaced in the following format:

GL = G

SL = S

GGGG.SS

GGGGSS

.SS

Transaction Date

• Note: 8 and 9 will only work if all characters have been defined (ex: 08/08/02 will work 8/8/02 will Not work.)

Transaction Amount

• Credits must be formatted as: -###.##.

Stock File Format

• The stock file format would provide the ability to import a file that is in a user defined format, provided the file is in a fixed or delimited format and includes the required fields.

Fields 1-4

• Define the format name, the type of file format (fixed, comma delimited or tab delimited), and the option of defining a backup location (the import file is deleted once uploaded, by defining a backup location a copy of the file is saved).

Fields 5-16

• Define the fields that can be included in the import file and loaded into the TSC stock file.