Views

A database view is a query stored in a database. This query can be accessed as a virtual database table. The view changes as the database changes.

Currently, several views are provided

Account Statement Balance

Intraday Balance

All Payments present in the system

Account Statement details

Payments View

Trax payments contain information that can be present on envelope level or on payment level. The purpose of this view is to show a list of all individual payments present in the system with all the desired fields filled out, even if these field values are not present on the payment itself but on one of the envelopes.

This view converts the currency of the payment to the defaulted exchange rate category currency using the most recent exchange rates present in the system.

The main Trax table of this query is the payments table. In this way, all payments present in the system will be retained by the view.

It should be noted that, after the introduction of transformation objects, payments can be retained twice as both their original entity as their copy will be present in the view. Users of the view should take this into account when deriving aggregated information from this view.

Debtor Account

The debtor account should be retained from the static data model. If a payment has not been enriched yet, these fields will be empty.

Number

Code

Name

Currency

Debtor Bank

The debtor bank is the bank attached to the debtor account retained from the static data model.

Code

Name

BIC

National Clearing Code

Debtor

The debtor is the holder attached to the debtor account retained from the static data model.

Code

Name

Country

Payment

The payment is the main object of this view. All payments present in the system are retained.

Requested execution date

Creation date

Own reference

Counterparty reference

Batch reference

Repository

The repository is the status in which the payment resides.

Code

Description

Workflow indication (BP for bulk payment and UP for unitary payment)

Amounts

Two amounts are retained. The first amount is the original amount of the payment. The second amount is the original amount converted to the currency of the defaulted exchange rate category. The exchange rate applied is the most recent exchange rate present in the system for the original currency. Only one default exchange rate category may exist. Otherwise, for each payment, an additional record will be retrieved for each additional defaulted exchange rate category.

If the currency of the original amount equals the currency of the defaulted exchange rate category currency, the original amount and the original currency is used to fill out the converted amount and the converted currency fields.

When no exchange rate is defined for a particular currency, the converted currency will be equal to the original currency. In this way, the converted currency column can be used by a user to check which currencies were converted by the query and for which currencies the query was not able to convert that currency. As a consequence, the converted amount will also not be converted and remain equal to the original amount.

Original instructed amount

Original instructed amount currency

Converted instructed amount

Converted instructed currency

Data owner

The data owner is the legal entity owning the payment.

Code

Name

Description

Beneficiary

The beneficiary is the counterparty of the payment.

Beneficiary account number

Beneficiary account currency

Beneficiary account bank BIC

Beneficiary account bank NCC

Beneficiary account bank Name

Beneficiary name

Beneficiary country

Signing

If the payment has already been signed by one or more users, the view shows the user who has last signed the payment (or its envelope).

Name

Account Statements View

The purpose of this view is to show the three balances present in the system for each account present in the static data model. Date stamps are provided for each balance so that a user can easily derive a particular balance on a particular date for a particular account.

The sum of the total debit and credit movement of each account statement forms the net amount of that account statement.

The accounts table in the static data model is the main table of this view. In this way, all accounts present in the static data model are retained, even if the system does not contain any account statements for a particular account.

Intraday statements are not taken into account in this view.

It should be noted that summations are present in this view. Therefore, only account statements are present being a member of one of the following inventories:

AS_ARCHIVED_NOT_PUBLISHED

AS_ARCHIVED_PUBLISHED

AS_NOT_PUBLISHED

AS_PUBLISHED

AS_VALID

AS_READYTOSEND

Account

The account information is retained from the static data model:

Code

Name

Account Number

Account Currency

Holder

The account holder:

Name

Country

Bank

The bank of the account:

Country

Name

Type

The account type:

Name

Data Owner

The data owner is the legal entity owning the account:

Name

Balances

The following balances are retained:

Opening balance

Closing balance

Closing available balance

For each balance, the following fields are present:

Amount

Currency

Date

Amounts

The following three amounts are present:

Total debit amount of the account statement

Total credit amount of the account statement

Net amount of the account statement, summing the total (negative) debit and the total credit amounts.

Intraday Account Statements View

Whereas the previous view contains all the closing balances present in the system, this view only shows the most recent closing balance for each account present in the static data model.

Additionally, the view contains an overview of the intraday account statements received after the most recent closing balance date present in the system. In this way, a user will be able to retrieve the most recent account balance.

The account movements of the intraday statements are summated. For this reason, the view assumes that the received intraday account statements are non-cumulative. When cumulative intraday account statements are received, the view will still work correctly if only one intraday account statement is received between the last two closing balances.

The actual balance of an account is computed by summating the following items:

The most recent closing balance date present in the system.

The account movements of all intraday account statement movements having a posting date more recent than the most recent closing balance date.

Only account statements will be present that were validated by Trax. More concrete, only account statements are present being a member of one of the following inventories.

Account Statements:

AS_ARCHIVED_NOT_PUBLISHED

AS_ARCHIVED_PUBLISHED

AS_NOT_PUBLISHED

AS_PUBLISHED

AS_VALID

AS_READYTOSEND

Intraday Account Statements:

ID_ARCHIVED_NOT_PUBLISHED

ID_ARCHIVED_PUBLISHED

ID_NOT_PUBLISHED

ID_PUBLISHED

ID_VALID

ID_READYTOSEND

Account

The account information is retained from the static data model.

Code

Name

Account Number

Account Currency

Holder

The account holder

Name

Country

Bank

The bank of the account

Country

Name

Type

The account type

Name

Data Owner

The data owner is the legal entity owning the account

Name

Balances

The closing balance with the most recent date is retained

For this balance, the following fields are present:

Amount

Currency

Date

Amounts

The following amounts are present:

Total of debit/credit movements of all retained intraday account statements

Total balance: sum of this total and the closing balance

Additionally, a currency is provided for these two balances. The date field contains the most recent entry date of the movements on the intraday account statements.

Account Statement Transaction View

The purpose of the view is to display all the information associated with a particular transaction on the account statement. The attributes required for the account statement detail view are listed.

Data Owner Static Data Account

The data owner is the legal entity owning the account

Code

Name

Account

The account information is retained from the static data model.

Code

Name

Account Number

Account Currency

Holder

The account holder

Name

Country

Bank

The bank of the account

Country

Name

Type

The account type

Name

Account Statement Number

Account Statement Detail Date

Value Date

Entry Date

Account Statement Detail Amounts

Currency

Signed Amount

Debit/Credit Indicator

Account Counterparty

Identifier

Account Statement Detail References

for Account Holder

for Account Servicing Institution

Account Statement Detail Information

Message Zone

Supplementary Detail

Rejection Code

Configuration

Importing into the Database

Use an SQL application such as TOAD or Squirrel SQL (http://squirrel-sql.sourceforge.net/)) to upload the views into the database. In order to do so, start the application, connect to the data- base, select the scripts and execute them.

Using in BIRT

In BIRT, Let your SQL statement use the views by using something along the lines of SELECT * FROM VIEW_AS_BAL or VIEW_ID_BAL or VIEW_PAY_TXS