Bank Statements

Principles

Processing of bank statements and automation of the processing process.

Payment Orders, their creation and sending to the bank for processing.

In this documented area, the modifications of the import of bank statements for OCs and the procedures for their processing are described.

A new task "Preparation of Bank statements" has been added to the Bank Account Card (T370). An automated task consists of processing a bank statement in several steps, which are manual processing steps. Each manual processing step is part of an automated task until the Payment Journal is created. Manual processing is a sequence of steps that are processed by user intervention. The same steps are in an automated task, but these steps happen automatically, without user intervention.

In the new task dialog window, you can select the code of the bank for which the statement is to be imported. After running the task, the system will find import files according to the specified path in the BV import format settings for the selected bank code. In BC, it creates a new BV and imports the found file into it. It then issues the statement and creates a Payment Journal. When you create a reconciliation journal, the BV entries are automatically matched according to the matching rules that you have set. If there are more files in the folder, they will be processed one by one from the oldest (file name with BV in XXX_RRRRMMDD format). If a file is processed by a task with an error, the system creates an entry in the error log and continues processing the next file. Error logs are written into a new log table "Import Bank Statement Log". The log stores information about whether the file is successfully processed, into which BV it was imported and any errors that occurred during processing.

In addition, the Log contains information about the number of rows that were imported from the file. It is important to keep track of this information. If the BV file does not contain any line, the issued bank statement will not be created and the processing will not result in an error. In case the BV file has not been saved in the correct folder, the system will import it with zero rows, due to bank account validation, which in this case will reveal a mismatch. The system will then send such a file to the archive!

After successfully processing the import of the BV file, the task moves it to another network folder used for archiving the import BV files. BC provides processed files with a timestamp and appends the date and time of processing to the file name.

The result of the job is a released BV and a payment journal prepared for further user processing.

The task can also be run separately via the system search magnifier.

Dependencies and assumptions

The dependency is a completely set bank account card.

A prerequisite is that the corresponding BV import format has been set up in the bank account card, including predefined network directories for importing and archiving files. Another prerequisite is Saved Bank Statement in the appropriate format from the bank in the corresponding/set network directory for each bank.

More information about setting up a bank account card is described in the Nastavení Finance

Task – Preparation of bank statements

A task has been added to the OC Preparation of Bank statements for automatic import and archiving of bank statements. After the BV import is successful, the job prepares Payment Reconciliation Journals to be used to process and post the bank statement.

The job can be searched for using the system magnifier, or it can be found located in:

• Bank Account List (371, List) in the Process menu

• Account Card (370, Card) in the Actions menu.

When you press the task button, the system displays a dialog box that contains:

• Field for selecting a bank account with the possibility to search for the Bank account for which BVs are to be processed, after calling up the Look up value, the Bank Account (270)) table opens

• OK button to start the task.

If the task is started from the list or from the BV card, the field for selecting BV is automatically filled with the code of the bank from which the task is invoked.

The task is started by clicking on the OK button.

Job Processing Process

When you run the task by clicking OK from the dialog box, the system performs the following steps:

• In the Bank Account (270) table, according to the set code, in the Bank Statement Import Format (113, Code) field, Default folder path in the Bank Export/Import Setup (1200)) table

• Creates a new BV for the selected bank code in the Bank Statement Header table (11704))

• In case of incorrect processing, the error log is written into the log table and the header of the created BV is removed

• It imports the found file with BV into the created BV, fills in the header and rows of the created BV with data from the imported file

• In case of incorrect processing, the error log is written into a new log table, the created BV remains for manual import of BV and subsequent manual processing (In case the user uploads a file of another bank to the folder, the system imports it with the number of rows 0 and sends it to the archive. A issued BV will not be created due to the validation of the bank account number)

• In case of successful processing of item No. 4, BV will issue by selecting "Release and create payment journal"

• In case of incorrect processing, the error log is written into the log table, the created BV remains for manual import of BV and subsequent manual processing

• After flawless processing of point No. 6, the file with BV is moved to the archive, the path to archiving is looked at in the Archive Folder Path field in the Bank Export/Import Setup (1200) table in the corresponding bank export/import setup code on the card of the selected bank account

• In case of incorrect processing, the error log is written into the log table

• After archiving, the system looks to see if there is another file in the default folder. If so, the process goes back to step 2 and repeats until it has processed all the files in the default folder. If not, move on to step 11

• At the end of the process, an information window will appear announcing success / failure and the number of the created issued BV will be entered into the log table with the possibility of opening it directly.

Once created, the Payment Reconciliation Journal can be opened from the created BV or separately from the Payment Reconciliation Journal overview. In the journal, the entries are further processed using standard procedures.

The task can also be run separately via the search magnifying glass.

Import Bank Statement by Format

Imports of daily statements are supported in OC, where each file contains one statement of one account per day.

Default import formats supported in OC:

Default objects usually need to be modified according to the requirements of a particular bank.

The task reads statement rows from the import file for the selected bank account and at the same time creates a BV header.

• Finds the beginning of the statement (bank account number, statement number, statement date)

• According to the bank account number from the statement, it will look for a Bank Account Card with the same value in the Bank Account No. field.

• According to the found Bank Account Card, it searches in the header of the Bank Statement Header (11704)) and the header of the Issued Bank Statement Header (11706)) whether the header of the statement with the same bank account number and date is not already created, i.e. it checks the fields:

  • Bank Account No. (3)

  • External Document No. (35) = Import Statement No.

  • Document Date (6) = Listing Date

    • If there is a bank statement header where the same External Document No. or Document Date exists for the same Bank Account No. as in the imported file, the system will skip generating this header and make a log entry

    • If it does not find a Bank Statement or Issued Bank Statement header (with the same External Document No. and Document Date), it searches for a blank Bank Statement header of the processed Bank Account Card (i.e. the header has the Bank Account No. field filled in)

    • If it finds a blank Bank Statement header for the Bank Account Card,

      • fills in the data from the file into the header

        • Bank Account No. (3)

        • External Document No. (35) = Import Statement No.

        • Document Date (6) = Listing Date

      • Other data, e.g. Currency Code, Account No., Bank Account No. are validated in a standard way when creating a header.

      • For a created and filled header, it creates bank statement rows in the standard way

    • If it does not find an empty header for the Bank Account Card,

      • create a new header (i.e. assign the Bank Statement No. according to the number series set on the bank account card in the Statement Numbers field (11727) and fill in the Bank Account No. Other data, e.g. Currency Code, Account No., Bank Account No. are validated in the standard way when creating the header).

      • From the file, it then fills in the data in the created header

        • External Document No. (35) = Import Statement No.

        • Document Date (6) = Statement Date.

For a created and filled header, it creates bank statement rows in the standard way. Individual lines are taken from the bank statement file, decoded, and populated into the corresponding lines of the bank statement. I.e. it tries to assign equivalent values from the file to each field of the row (amount, VS, account number / IBAN, description, ...).

Archiving an import file with a bank statement

In the settings of the Bank Export/Import Setup (1200) table, in a new field Archive folder path (Archive Folder Path) The user sets the directory to which they will be

Processed files to be transferred. These directories are defined by the user for each bank separately. These paths will be set once, and then the system will take paths from the settings to work with. If necessary, the settings can be changed.

The file in the directory with processed dumps will have the following structure:

            OriginalfilenameYYYMMDDHHMMSS.originalextension

YYYY-year

MM – Month

DD – day

HH – hour

MM-minute

SS Second

Archiving a file with BV is part of the automated task.

Bank Statement Import Log

A new table and the Import Bank Statement Log page are created in the OC. The log is searchable via the system magnifier. In addition, the Log is available:

• from the Bank Account List (371, List) in the Process menu

• from the Account Card (370, Card) in the Process menu

Entering data into the table fields is performed automatically by the system at each step of the task of importing bank statements.

Log table fields:

• Entry No.

  • Integer, PK,

  • Automatic numbering

• User ID

  • Code 50,

  • Automatic completion of data about the user who started the task

• Operation

  • Option, options:

    • File Import

    • Bank Statement Issue

    • Invoicing (Posting)

• Result

  • Option, options:

    • Success

    • Error

• Error Detail

  • Text 250,

  • The text is written in the language in which the language of the user who started the task was set

• Date and Time

  • DateTime

  • Exact date and time when the job started

• Bank Statement No.

  • Code 20

  • The number of the bank statements created during the task is entered, in case of a successful result, the number of the issued bank statement is entered and it is possible to open it by clicking on the number

• File Name

  • Text 250

  • Name of the file with which the task worked, including its location before archiving

• Bank Account No.

  • Code 20

  • The code of the bank account for which the job processed the import is written

• No. of Lines

  • Integer

  • The number of rows from the statement is written

Issued Bank Statement

Releasing BV and Creating a payment journal is part of the automatic job.

The issued bank statement is not editable and cannot be deleted/deleted. It serves as an archive. If the statement has been posted, you can view the posted entries from it using the Find Entries function in the Process option. If a payment journal is not created from this statement, e.g. due to incorrect processing of an automatic task, it is possible to create it manually from the issued bank statement and post it after processing all entries.

Bank Statement Matching

The Search Rules settings are described in Nastavení Finance

Automatic matching of payments in the Payment Journal is performed according to the Search Rules corresponding to the set Search Rules Code on the Bank Account Card.

The automatic payment application feature is based on matching priority criteria.

Search rules for matching bank statement entries:

• Each row in the Search Rule Card represents a settlement rule. The rules are settled in order from top to bottom, or by line number (Line No.)

• Account Mapping

  • Text-based account mapping lets you set up mappings based on the text found in the Description field on the lines of the Bank Statement and on specific debit accounts, credit accounts, and offset accounts so that payments are posted to specific accounts when the Payment Journal is posted.

  • It is used to account for payments that have not been settled automatically according to the Search Rules. 

The search rules are set by the customer according to their own needs.

Unmatched payments must be manually processed (paired) in the journal, or you can preset the G/L account (e.g. 395000) to which the unmatched payments are to be posted by setting the Non Associated Payment Account field on the bank account card.

Splitting a mass payment in the Payment Journal

If you do not want to charge the received mass payment from the customer by one item (e.g. due to different dimensions of matched invoices), you can split the payment using the Split payment by to Applying function. 

Splitting a Mass Payment by Applies-to ID

To split a mass payment, proceed as follows:

• On a mass payment line, use the Apply Entries () function to select and mark the customer ledger entries to apply

• Make sure that Applies-to ID is filled in on the payment line

• Run Split payment by to Apply function

This feature at startup:

• Retrieves the processed Applies-to ID (Document number from the journal line where the cursor rests when the job is run)

• Filters customer ledger entries that have the appropriate applies-to ID filled in

• Divides the payment into separate lines.

• Settles individual partial payments on a journal line   

Payment Splitting and Settlement:

• On the original journal line, fill in the Applies-to Type and Applies-to Document No. from the first entry in the set of marked customer entries (in key order: Due Date + Posting Date + Entry No.) and adjust the payment amount according to the current balance of the customer ledger entry being applied. Deletes the applies-to ID on the item

• Creates a new payment line for each additional applied item. On the new lines, it proceeds in a similar way, i.e. it applies the individual entries on the journal line one by one, adjusts the payment amount, and deletes the applies-to ID on the entry.

  • To fill in the fields on a new line:

    • Data such as Posting Date, Document Number, Description, Counter-Account Type and Number, Variable Symbol and External Document No. will be copied from the original payment

    • Copies the customer posting group from the customer card 

    • Dimension copied from the applied entry (if Dimension from paired entry = Yes is set on the Bank account card)

    • Fills in Parent Line No. – a non-editable field that identifies that the payment has been split by the Split payment by settlement function. Parent line number = 0 remains on the original journal line. All new lines created by splitting the payment will have the Line No. from the original payment filled in the Parent Line No. field. The field is used by the system when splitting is cancelled, when journal lines are deleted, when rematching using search rules, and when posting a journal. 

• If the total payment is greater than the current balance of all items to be settled (overpayment), the last partial payment based on the customer will remain unsettled. The data on the line are filled in similarly to the lines with settlement, including the Variable Symbol copied from the original payment line.

• Note: Dimensions are added in the standard way, if they were set up on the customer card or on the bank account card, they would be copied to the line from the relevant card.

• If an outstanding balance is detected at the end of the split, then the system adjusts the amount on the last line of the split payment so that the sum of the partial payments is equal to the amount of the payment before the split

• Payment Variances

  • To detect the discrepancy both when checking the current balance and when splitting the payment itself, the system uses the variance set on the bank account card (fields Matching Tolerance Type and Matching Tolerance Value fields)

  • Any payment discrepancy is applied only to the last applied item

  • However, to post the variance, the system uses the variance that is specified on the customer ledger entry (the Maximum payment variance field). Therefore, even when splitting a payment, if a variance is set "on request" in the General Ledger Setup, it is necessary to confirm whether the difference should be posted as a payment difference or whether the item should remain open on the balance

Open

If, when the function runs, no entries with a matching application ID are found, or the current balances of entries of that application ID are zero, the payment will not be split and the line will remain unchanged.

The function cannot be run on a line that has been split (neither on the original line nor on newly created lines): 

Open

Delete split payment lines

By default, journal lines can be deleted using either the Delete function or the CTRL+DEL keyboard shortcut.  

There is a restriction on the deletion of payment lines that have been split. You can only delete all of the split payment lines at once. Because the original split payment line does not contain a parent line number, it may be more difficult to identify or filter this line, so it is recommended that you cancel the split first and then delete the journal lines.

To delete rows:

• Select (mark) the journal lines that you want to delete. If these are split payment lines, you must mark all split lines, including the original line

• Run the Delete function (or CTRL+DEL)

• After the system starts:

  • If the selected rows group does not contain any split rows, these rows are deleted in the standard way

  • If at least one of the lines marked for deletion is from a split payment (original or new line), the system will check if all lines of that split are in the selection.

    • If so, it deletes all rows

    • If not, it doesn't delete any row. This is true even if there are other lines between the marked lines, even unsplit payments or other split payments completely marked. The deletion ends with an error message and the lines remain in the journal.

Split-payment re-matching

In the Payment Journal, there is a standard function Match by search rule.

This feature allows you to re-run automatic matching over marked journal lines. For repeated automatic matching, the Search Rule code must be filled in on the journal line (a non-editable field, it is filled in by the system when generating the journal from the Issued bank statement). New matching will take place only for customer entries (vendors, etc.) that are filled in on the journal line, by rules that are filled in on the journal line.

It is not possible to run this function on lines that originate from a split payment (original or new), the task will end with an error message. You must either select only the rows without splitting, or cancel the split, and then restart the rematching.

Cancel a mass payment split

If the journal line was split by Split payment by to applying, you can cancel the split by using Cancel payment splitting

To cancel a split:

• Select (mark) any split payment line. You can select multiple rows at once.

• Run the Cancel Split Rows function

System:

• For the current line, it searches for all lines of the split payment according to the parent line number and retrieves the total amount from these lines. Deletes rows with the Parent Line No. filled in. It keeps the original line (Parent Line No. = 0) but corrects the line amount to the original payment value (sum of the amounts of deleted lines + the amount from the parent line).

• If Applies-to ID was filled in on the deleted line (e.g. the user changed the matching after splitting the payment), this Applies-to ID will be deleted on the customer's ledger entries.

• If the application is on the original line (either in the Applies-to Document No. field or the Applies-to ID field), the applies-to do not change when you undo the split.

• If there were multiple split payments between the marked lines, the system will cancel all splits one by one 

• If a non-split row has been marked, the system skips that line.