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 if 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 Desktop uses this length to determine at which point it meets a new field within a line in the file.

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

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

The bank file specification document identifies 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 clicking 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 is 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 if 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 with 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 Desktop 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 Desktop ignores 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 Desktop expects an additional block code to be defined in 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 is in the Group Header Record and the account number is in the normal header record. The group header record is only used if 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.

Note: Block code Lines equate to the Transactions section of the bank file.

Set Up or Maintain Automatic Reconciliation Codes

Open this form from 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 sixth 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 processes as a miscellaneous receipt).
• 011 - This is a credit code which represents interest paid (this processes as a miscellaneous payment).
• 013 - This is a credit code which represents bank fees (this processes as a miscellaneous payment).

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

The available codes for this form are:

Type R

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

For example, in the file above, transaction code 050 is assigned as type R. When the process runs, Greentree Desktop will look for a receipt transaction for the amount: $187,941.31 and a reference of 171393. If Greentree Desktop does find a match, then the existing transaction is put into a Reconciled status, and the statement balance updates. If Greentree Desktop 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 .

Type P

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

For example, in the file above, transaction code 000 is assigned as type P. When the process runs, Greentree Desktop will look for a payment transaction for the amount: $478.11 and a reference of 3228. If Greentree Desktop does find a match, then the existing transaction is put into a Reconciled status, and the statement balance updates. If Greentree Desktop 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 .

Type MR

This represents a Greentree Desktop 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 Desktop when the automatic reconciliation process runs. Examples of this include interest received, if the user is not aware of the charge until the bank statement is received.

Greentree Desktop 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 is assigned as type MR. When the process runs, Greentree Desktop 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 Desktop 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 Desktop when the automatic reconciliation process runs. Examples of this include bank fees, interest paid, and the like. if the user is not aware of the charge until the bank statement is received.

Greentree Desktop 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 is assigned as type MP. When the process runs, Greentree Desktop adds a miscellaneous line to the statement with a value of $38.00, and a reference of INT. PAID.

Note: The interface transaction codes outlined above (MR, MP, R, and P) are defined in the system. You may also create your own codes in Cash Management 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 Cash Management Bank Statement Transaction Entry form as a credit, and reduce the closing balance. The MP code is also flagged as a Miscellaneous Code which indicates that transactions with this code is reconciled during the process, and not matched (whereas those transactions with codes which are not flagged as miscellaneous will indicate Greentree Desktop should try to match them against existing Greentree Desktop transactions).

A Cash Management Analysis Code is also assigned to each transaction code, which is 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 Desktop transactions. If this option is selected, Greentree Desktop will not try to match the transaction value.

Analysis Code

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

Note: 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 Desktop while processing a reconciliation, they must also enter the GL Account that the transaction is 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 interface. They are:

The remaining fields in the bank file is 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 Desktop system fields that you can select from. The fields you are presented to select from is 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 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. Click 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 pass into Greentree Desktop exactly as they are in the file. For example, if a negative amount is prefixed by a - then a negative amount is passed into Greentree Desktop 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 Desktop 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 is 2 indicates that this amount is $458.95, when it is being matched against Greentree Desktop 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

---