Setup Steps

---

When you create a bank reconciliation interface in Greentree, the bank's file format specification document is the basis for the design. In the example outlined below, the format uses the National Bank of New Zealand's file specification.

Access the Interface Selection Form

To access the Interface Selection form:

1. Select System > System Setup > Interface Maintenance.
2. Select the Bank Reconciliation Interface from the Interface type list.
3. Enter or select an Interface name.
4. The Default file is a reference to the filename and location which will commonly be accessed when the automatic bank reconciliation process runs where the bank import file resides). It defaults to the Automatic Bank Reconciliation process form for ease of data entry whenever this particular interface name is selected for processing, but you can change it before processing.
5. Click Edit interface to create or edit the interface. Greentree Desktop displays a new form called Interface Maintenance.

Identify the File Format

Determine whether the file is a Comma delimited file, or a Fixed-length file. Fixed length files must have a Length parameter defined and it must be precise, as Greentree uses this length to determine at which point it meets a new field within a line in the file.

Some fixed-length files will have a carriage return at the end of each line, and some will not. Those that do not will have the appearance of being a continuous string of data, and the file will not wrap around.

If the file format is Comma delimited, Greentree ignores the values in the Length column in the Lines section.

The bank file specification document will identify which format the statement file has, and this is the format type you should use when setting up the interface.

Select Options

The next step is to set up the options by pressing the Options button on the Interface Maintenance form.

Number of statement lines per page

Specify the number of lines per statement page. When the automatic bank reconciliation process runs, new statement pages (in Greentree) are created. The number of transactions added to the statement will be based on this parameter.

Are Heading and Transaction in separate files

This option accommodates bank files which are separated into two files (for example, ANZ Australia). This is not common, but where specified in the bank file specification, the reconciliation process merges the two files together before importing the data, simulating the input of one file (the two files always represent the header and the transactions).

Check opening and closing balance

If the bank file contains opening and closing balances, the user can check the balances in the file against the balances in Greentree Desktop. For example, if the header record contains an opening balance of $1000.00, then part of the import process includes confirming that the last statement entered into Greentree had a closing balance of $1000.00. You don't need to check for balances, even if the bank file includes them.

Ignore lines with invalid block codes

Select this option if the bank file contains lines that are not mandatory to the process. Some files may contain additional footers, or end-of-file-markers, which are not required to support the process. If you are aware that files contain redundant information, check this option so Greentree knows to ignore them.

Using the BAI2 transaction type field

BAI2 refers to a semi-generic format that several banks use. If you select this option, then Greentree will expect an additional block code to be defined, specifically the Group Header Record. This is because BAI2 file formats break the header information into two lines.

Where standard files include the statement date and the bank account number in the header, BAI2 files separate the statement date and bank account number into two lines. The date will be in the Group Header Record and the account number will be in the normal header record. The group header record is only used where the bank file is a type BAI2 format.

Prompt for Statement Date

This option is only used for interface layouts that do not have a header. If there is a header or this option is not checked the statement date is obtain from the file. If checked the user is prompted for the statement date.

Assign Block Codes

Use the bank specification document to assign the Block codes to the interface format. A block code is the unique identifier that will indicate whether the line is a header, transaction, or footer record.

Block code Lines equate to the Transactions section of the bank file, as outlined in File sections and required fields.

Set Up or Maintain Automatic Reconciliation Codes

Access this function using the Automatic Reconciliation Code Maintenance form.

See the bank specification document to help you set up transaction codes using this form. These are pre-defined codes which are used to identify the nature of the transaction as it is read in by the reconciliation process. The transaction code is found in the sixth field of the Type 3 (Lines) transaction line values range from 000-049 for credit transactions, and 050-099 for debit transactions.

The bank can provide the user with a list of what each of these codes represents. Examples of what different codes might represent include interest received, interest paid, government charges, cheques, direct credits, receipts, direct debits, bank fees, and the like.

An example of the National Bank of New Zealand file is below, with transaction codes highlighted (found in 6th field of the line block code):

3,666666,012505207861684,187941.31,000000000000,050,,,TUESDAY,171393,19/01/01,06-0556

3,666666,012505207861684,32.00,000000000000,050,,,TUESDAY,171304,19/01/01,06-0556

3,666666,012505207861684,-478.11,000000000000,000,,,TUESDAY,3228,19/01/01,06-0556

3,666666,012505207861684,-248.50,000000000000,000,,,TUESDAY,5001,19/01/01,06-0556

3,666666,012505207861684,412.00,000000000000,052,,,TUESDAY,INT. RECD,19/01/01,06-0556

3,666666,012505207861684,-38.00,000000000000,011,,,TUESDAY,INT. PAID,19/01/01,06-0556

3,666666,012505207861684,-2.50,000000000000,013,,,TUESDAY,BANK FEES,19/01/01,06-0556

3,666666,012505207861684,19.00,000000000000,050,,,TUESDAY,40003,19/01/01,06-0556

3,666666,012505207861684,-140.00,000000000000,000,,,TUESDAY,00002,19/01/01,06-0556

5,666666,012505207861684,0.00,,,OPENING BALANCE,,,DIRECTLINK L,19/01/01,

6,666666,012505207861684,0.00,,,CLOSING BALANCE,,,DIRECTLINK L,19/01/01,

In this example, the transaction codes identified are:

• 050 - This is a debit code (positive values), which should be set up to represent a receipt transaction.
• 000 - This is a credit code (negative values), which should be set up to represent a payment transaction.
• 052 - This is a debit code which represents interest received (this will process as a Greentree miscellaneous receipt).
• 011 - This is a credit code which represents interest paid (this will process as a Greentree miscellaneous payment).
• 013 - This is a credit code which represents bank fees (this will process as a Greentree miscellaneous payment).

Before setting up the bank reconciliation transaction codes for the Greentree interface format, you must determine how to classify each code. Greentree has some pre-defined classifications set up in Transaction Code Maintenance.

The available codes for this form are:

Type R

This represents a Greentree receipt. Transaction codes should be classified type R if the user expects the transaction (in the bank file) to already exist in Greentree when the automatic reconciliation process runs. Greentree expects that lines with a transaction of type R must be matched against existing transactions.

For example, in the file above, transaction code 050 will be assigned as type R. When the process runs, Greentree will look for a receipt transaction for the amount: $187,941.31 and a reference of 171393. If Greentree does find a match, then the existing transaction is put into a Reconciled status, and the statement balance updates. If Greentree does not find a match, then the transaction in the file is added to the statement as an un-matched (and unreconciled) transaction, which you can reconcile at a later time.

Type P

This represents a Greentree Payment. Transaction codes should be classified type P if the user expects the transaction (in the bank file) to already exist in Greentree when the automatic reconciliation process runs. Greentree expects that lines with a transaction of type P must be matched against existing transactions.

For example, in the file above, transaction code 000 will be assigned as type P. When the process runs, Greentree will look for a payment transaction for the amount: $478.11 and a reference of 3228. If the system does find a match, then the existing transaction is put into a Reconciled status, and the statement balance updates. If Greentree does not find a match, then the transaction in the file is added to the statement as an un-matched (and unreconciled) transaction, which you can reconcile at a later time.

Type MR

This represents a Greentree miscellaneous receipt. Transaction codes should be classified type MR if the user does not expect the transaction (in the bank file) to already exist in Greentree when the automatic reconciliation process runs. Examples of this include interest received, where the user is not aware of the charge until the bank statement is received.

Greentree does not try to match lines with transaction codes of type MR when the process runs, as it does not expect to find them. Instead, it adds them to the statement (as a user would if they were adding miscellaneous charges to a statement), reconciles them, and updates the statement balance.

In the file above, transaction code 052 will be assigned as type MR. When the process runs, Greentree adds a miscellaneous line to the statement with a value of $412.00, and a reference of INT. RECD.

Type MP

This represents a Greentree miscellaneous payment. Transaction codes should be classified type MP if the user does not expect the transaction (in the bank file) to already exist in Greentree when the automatic reconciliation process runs. Examples of this include bank fees, interest paid, and the like. where the user is not aware of the charge until the bank statement is received.

Greentree does not try to match lines with transaction codes of type MP when the process runs, as it does not expect to find them. Instead, it adds them to the statement (as a user would if they were adding miscellaneous charges to a statement), reconciles them, and updates the statement balance.

In the file above, transaction code 011 will be assigned as type MP. When the process runs, Greentree adds a miscellaneous line to the statement with a value of $38.00, and a reference of INT. PAID.

The interface transaction codes outlined above (MR, MP, R, and P) are pre-defined in the Greentree system. However, you may also create your own codes in CM Transaction Code Maintenance.

The code MP is classified as a Credit. When a transaction with this code is processed using the automatic bank reconciliation, it displays on the CM Bank Statement Transaction Entry form as a credit, and reduce the closing balance. The MP code is also flagged as being a Miscellaneous Code which indicates that transactions with this code will be reconciled during the process, and not matched (whereas those transactions with codes which are not flagged as miscellaneous will indicate Greentree should try to match them against existing Greentree transactions).

A Cash Management Analysis Code is also assigned to each transaction code, which will be assigned to transaction lines processed when the automatic bank reconciliation runs. Analysis codes are set up in CM Analysis Code Maintenance, and can be used for cash flow reporting.

You can set up your own codes using these indicators, depending on how you want the transaction to be presented when it is processed using the automatic bank reconciliation:

Debit/Credit

This indicates whether you want transactions with this code to display on the bank statement as a debit (increase closing balance) or a credit (decrease closing balance).

Is a Miscellaneous Code

This indicates whether you want try and match transactions with this code against existing Greentree transactions. If this option is selected, Greentree will not try to match the transaction value.

Analysis Code

This is the default analysis code you want to apply to transaction lines with this code.

When a bank transaction code has a miscellaneous reconciliation code assigned one that was flagged as miscellaneous in Cash Management Transaction Code Maintenance), a GL Account is required to support the addition of the miscellaneous line to the bank statement when the process runs. If the user enters a miscellaneous payment into Greentree while processing a reconciliation, they must also enter the GL Account that the transaction will be debited/credited to.

Once these codes have been set up, return to the System/Utilities/Interface Maintenance form to continue with the next step.

Assign Line Values to Blocks

Once the previous setup steps have been completed, you can now assign values to the lines for each block that is specified. You will need to see the bank specification document to complete this step. The purpose of doing this is to define the conversion rules so that the bank statement file can be converted into meaningful data.

Header Block Code

The first block code to set up is the Header. For example, the number 5 has been assigned to this block code, using the bank's specification. Header lines always be pre-fixed with the number 5, so that you can identify that it is a header line that is being read. You can tell from the bank's specification that this is the header record because it contains the statement date, account number, and opening balance.

The header line has 12 fields, however, there are only 4 fields that you are interested in for setting up the Greentree interface. They are:

The remaining fields in the bank file will be ignored as they are not required by Greentree Desktop.

To set this up, highlight the Header block in the Blocks section of the form, and move down into the Lines table below. For each line, assign a Type and a Value. If the bank file is a fixed length file, then a Length must also be entered. For comma-delimited files, this is not required.

Valid Types are System Field, or Constant. You select which type by dropping-down on the Type field in the row that is selected. System Fields are the only ones that are required when setting up automatic bank reconciliation formats.

When you have assigned type System Field to a line, you right-click the Value field in the selected row, and you are presented with two options: Set System Line, and Format. The Set System Line option displays a list of the valid Greentree system fields that you can select from. The fields you are presented to select from will be different depending on which block you are assigning lines to.

For example, the only system fields that you must assign to the header are the Block Code, Account, Statement date, and Previous Statement Closing Balance. The Ignored system field is used to assign to lines in the bank input file that are going to be ignored when the automatic reconciliation process runs.

To access formatting options, right-click on Value field. This will present various system field formatting options so that fields can be formatted according to the bank's specification. Fields that will generally require formatting are amount fields and date fields (refer to Step 7 below).

Formatting System Fields

The only system fields that will require special formatting are the Date fields and the Amount fields (this includes opening and closing balances). These fields should be formatted using the bank specification. To format a field, right-click the Value on the line you want to format, and select Format.

Date Field Format Options

Select As Date at the top if the form, and then select the Date Style using the bank's specification. Press OK.

Amount Field Format Options

Select As Number at the top if the form, and then select the other options using the bank's specification.

Negative Style

Does not apply to the import format definition.

Sign

If you select Normal it means that when amounts are read in by the process, they will pass into Greentree exactly as they are in the file. For example, if a negative amount is prefixed by a - then a negative amount will be passed into Greentree if the transaction code is a debit or a credit.

Negate is used to indicate that amounts in the bank file should be multiplied by (-1) as they are passed into Greentree Desktop. Absolute indicates that all amount fields should be read as positive amounts, regardless of whether they are pre-fixed with a + or - sign.

Decimal Places/Remove Decimal Point

These options are used with each other. If an amount field in the bank file does not have a decimal point, then the Remove decimal place option must be selected. The Decimal Places should then have a value entered to indicate how many characters Greentree assumes to represent cents at the end of the amount string.

For example, if the bank file displays an amount of 45895, and they do not show decimals, then a format of Remove decimal place is selected option selected) and Decimal Places = 2 will indicate that this amount is $458.95, when it is being matched against Greentree transactions.

If the bank file amounts do show a decimal point, then neither of these options applies.

Currency Symbol

Not applicable to import format definition.

Pad Character/Remove Leading Zeros

Not applicable to import format definition. The automatic bank reconciliation code always strips leading zeros anyway as part of the import process.

Blank if Zero

Not applicable to import format definition.

Remove Separator

Not applicable to import format definition.

Once set-up is complete for all block codes, the interface format can be saved, and it is available to use when you run the Automatic Bank Reconciliation process.

• Sample file formats
• National Bank of New Zealand file specification
• File sections and required fields
• Automatic Bank Reconciliation Process
• Transaction Code Maintenance

---