View source for Exchange Rates (Design and Test)

== Summary ==
This wiki page describes the behaviour of two screens:
* Setup Daily Exchange Rates
* Setup Corporate Exchange Rates
These screens have a number of features in common, but the Daily Exchange Rate screen has a number of additional features.  This wiki page has been written to 
* help you understand the capabilities of these two screens
* help testers to construct suitable tests for all the features of the screens.

The Daily Exchange Rate screen can be launched from the main menu as a standard set-up screen.  In addition it can be launched as a modal dialog from three other main finance screens.  The Corporate screen is never launched modally.

The screens look deceptively simple - much like many other set-up screens in OpenPetra.  But don't be deceived!  There is a lot going on behind the scenes!

== Underlying Tables ==
The Corporate Exchange Rate screen is backed by a single table:
* a_corporate_exchange_rate table (read/write)

The Daily Exchange Rate screen is backed by the 
* a_daily_exchange_rate table (read/write)
But '''in addition''' this screen is also loosely linked to these additional tables:
* a_corporate_exchange_rate table (read only)
* a_journal table (read/write)
* a_gift_batch (read/write)
None of these additional tables are linked as references.  But they can be implicitly linked by having common data values in certain columns.

You will need data in all these tables when testing the screens.

== Initial Set-up ==
While it is possible to start with an empty database, the best starting point is to start with a database that already contains some financial records including GL batches (posted and unposted) and Gift batches.  The two exchange rate tables should initially both be empty.

== Standard Workflow ==
You should start by defining your corporate exchange rates.  This screen is launched from the main menu -> Finance Setup -> Exchange Rates (Cross Ledger).  On the screen you can add a number of rows of currency pairs, effective dates and rates.  The 'To Currency' will always be the ledger currency that you work with.  The 'From Currency' will be the 'foreign' currency that you may need to convert to the ledger currency.  Over time you will build up a sequence of exchange rates for a given currency pair.  By default the suggested effective date will be the first day of a month.  You may change the corporate rate each month, or you may decide to work with a rate from 1 January for the whole year.

When you create your corporate rates try to remember to include a rate for a date that pre-dates all your batches because, by default, OpenPetra will suggest the first day of the current month as the effective date for a new row.  This is normally a good choice, but if you have already added batches of transactions before setting up exchange rates you will miss out on some useful features if you do not have at least one rate for each currency pair that is effective for the start of your data.

There are two alternative workflows for Daily Exchange Rate.  If you rarely have a foreign currency transaction you can set up an exchange rate direct from the GL Batch / Gift Batch / GL Revaluation screen.  At a later time you can review the rates you have used by loading the Daily Rate screen from the main menu.  Alternatively, if you work with a number of foreign currency transactions it may be preferable to set up a new daily rate from the main menu first, and then access that rate from the other screens.

By the way - as we have seen these screens are both '''Cross-Ledger'''.  The rates that you set up are not necessarily tied to a specific ledger, although specific ledgers may contain these rates.  When you launch one of the screens from the main menu you are certainly not tied to a specific ledger.  When you launch the Daily Rate screen from one of the finance screens you pass the ledger currency as the 'To Currency', but there is no referential database link between the exchange rate table and any other table.  So the data in the exchange rate table is truly cross-ledger.

== Common Behaviours of Corporate and Daily Exchange Rate Screens ==
Both screens have a 'From Currency', a 'To Currency' and an 'Effective Date'.  The Daily Rate screen also has an 'Effective Time'.  Together these fields make up the primary key for each row (and so must be unique).  Each row has an 'Exchange Rate'.  Whenever you add new rows OpenPetra automatically adds equivalent 'inverse rows'.  Thus if you add a row for A to B at a rate of 2.0, an additional row for B to A at 0.5 will also be added for the same date (and time).  This action happens '''when you save your changes'''.

The 'To Currency' is typically the currency that you do your work in, so the grid does two things automatically to help you
* it automatically sorts the columns
** sorting is by: To Currency + From Currency + Date (+Time) where records are latest first.
** thus if you work in, say GBP, you will find a group of GBP records in the To Currency column in which all the From Currencies are alphabetical and with the latest rates at the top of each From/To group.
** if you only work in GBP, but with conversions to five 'foreign' currencies (e.g. EUR, USD, JPY etc), you will also have five smaller groups of 'To' currencies for each of these.
* it provides a checkbox so that you can hide all the other 'To' currencies apart from the one currently highlighted.
** this provides a means of 'hiding' all the inverses of your standard GBP in the example described here.

Both screens have an 'Invert Value' button.  This simply takes the current rate in the textbox and substitutes the reciprocal value.  This is particularly useful because OpenPetra usually requires the inverse of the rate that is often available in a newspaper, for example.  As an Englishman I am used to British pounds being quoted as 1.6 GBP to the dollar or 80 pence to the euro.  But the value that OpenPetra wants is the inverse of that.  '''Please note that if you click the Invert Value button twice you may not get back to the number you first entered'''.  This behaviour is not a bug - it is a consequence of rounding errors.

To further help with ensuring that the correct rates are entered there are two help labels at the bottom of the screen to make it quite clear what a single unit of each currency is worth in the other currency.  These labels dynamically update as you type into the rate box.

=== Adding a New Row ===
In common with all the other screens in OpenPetra, when you add a new row the software fills in the fields with suggested values.  While the two screens differ slightly in the detail of the algorithm, this much is common to both.
* First there is an algorithm to determine the '''From Currency''' and '''To Currency'''.
* Second there is an algorithm to suggest a date and a rate.
Having populated the controls with the suggested values the focus is placed on the '''Effective Date''' control.  Depending on the context one or both of the two currencies may be fixed by disabling changes to the combo boxes.

=== Editing an Existing Row ===
Where a row can be edited the behaviour is the same as in Petra 2.x, inasmuch as any change to either currency causes the second part of the '''New Row''' algorithm described above to be re-run with the new currency pairing.  This results in an automatic change to the content of the date and rate controls.  This is unusual in OpenPetra, but in this case is a very useful behaviour.

=== Validation ===
Manual validation over and above the standard database-level checks includes:
* that the date is not empty
* that the rate is not negative or zero.  The software will suggest a rate of zero for a new record if it cannot come up with a better suggestion - thereby forcing the user to enter a valid rate.
* on Daily Rate '''modal''' screens that the date is in the range allowed by the parent screen

== Specific Behaviours of the Corporate Exchange Rate Screen ==

=== Adding a New Row ===
The logic for adding a new row is the same as 2.x.
==== The algorithm to suggest a currency pair ====
* If the table is empty
** If the '''LedgerNumber''' property was set when launching the screen, the '''To Currency''' will be the ledger currency and the '''From Currency''' will be USD, unless the ledger currency is USD, in which case the '''From Currency''' will be GBP.  (Note that the ledger number is not set anywhere in the current build of OpenPetra) - otherwise
** If a ledger has been created that the user has access rights to, the '''To Currency''' will be the first accessible ledger currency where the ledger status is TRUE and the '''From Currency''' will be USD, unless the ledger currency is USD, in which case the '''From Currency''' will be GBP. - otherwise
** The '''From Currency''' will be GBP and the '''To Currency''' will be USD.
* If the table has at least one row
** The two currencies for the new row will be the same as the currently selected row.
==== The algorithm to suggest a date and rate ====
* The suggested date will be the first of the current month.  If that date has already been used for the currency pair, the software will try the first of the next month and so on until it reaches a month (in the future) that has not been used.
* The suggested rate will be the the last rate in the sequence for the currency pair (not necessarily the same as the highlighted row rate if it is not the latest in the sequence).
** If there is no previously entered rate for the currency pair, 0.0 will be proposed.
==== The algorithm to Add an Inverse Rate ====
When you save your changes the software will add a new inverse rate '''or update an existing one''' for the inverse currency pair.  However please note that if you edit an existing rate the software does '''not''' try to find an equivalent inverse and change that rate.  This is by design and is because the inverse rate might have already been used as the rate for another table.


=== Editing an Existing Row ===
When you edit an existing row in the corporate exchange rate screen, the algorithm to suggest a date and rate described above will be used if you change either of the currencies.  If the currency pair has been used before the software will suggest the next available month for that currency pair (which might be a different date from the original suggestion).  The rate will be the rate described above in '''New Row'''. 

== Specific Behaviours of the Daily Exchange Rate Screen ==
As noted above the Daily Rate screen has loose informal links to the Journal, Gift Batch and Corporate Rate tables.  Using these tables the screen can provide the following additional capabilities, which give rise to informational or warning tool-tips.
* A warning if the rate entered is more than 10% different from the neighbouring rate(s) for the currency pair.
* An information tool-tip identifying the places in the journal/gift batch tables where the rate has been used.
* Rates cannot be deleted if they have been posted in a journal and they are not used in any unposted batches.
* Rates can only be edited if they have not yet been posted.
* If a rate is edited that appears in an unposted journal/batch, the corresponding rate in the related batch table is also modified.

It is important to realise that several of these features are only activated when you click the '''Allow Edit/Delete''' button.

An important difference between the Daily and Corporate screens is that the Daily Rate screen can be invoked modally.  In this case the '''From Currency''' and the '''To Currency''' are usually fixed and the latest date is also sometimes known since it is defined by the batch.

=== Adding a New Row ===
==== The algorithm to suggest a currency pair on a Modeless Screen ====
* If the table is empty
** If the '''LedgerNumber''' property was set when launching the screen, the '''To Currency''' will be the ledger currency and the '''From Currency''' will be USD, unless the ledger currency is USD, in which case the '''From Currency''' will be GBP.  (Note that the ledger number is not set anywhere in the current build of OpenPetra) - otherwise
** If a ledger has been created that the user has access rights to, the '''To Currency''' will be the first accessible ledger currency where the ledger status is TRUE and the '''From Currency''' will be USD, unless the ledger currency is USD, in which case the '''From Currency''' will be GBP. - otherwise
** The '''From Currency''' will be GBP and the '''To Currency''' will be USD.
* If the table has at least one row
** The two currencies for the new row will be the same as the currently selected row.
==== The algorithm to suggest a currency pair on a Modal Screen ====
When the screen is invoked modally the '''To Currency''' will be known and cannot be changed.  The '''From Currency''' may also be fixed as well.
* If the table is empty
** The fixed currencies are used where known
** If the '''From Currency''' is not fixed, it will be USD, unless that is the '''To Currency'''.  In that case  GBP is suggested.
* If the table has at least one row
** The two currencies for the new row will be the same as the currently selected row.
==== The algorithm to suggest a date and rate (Modal and Modeless) ====
The date range may be restricted on a modal screen.  There may be a minimum start date or a maximum end date or both.
* If the end date is specified, that will be the suggested date - otherwise
* The suggested date will today's date
* The suggested time will be 2:00AM.  If that date/time has already been used for the currency pair, the software will try successive ten minute intervals until it reaches a date/time that has not been used.
* The suggested rate will be
** the the most recent rate prior to the suggested date for the currency pair (not necessarily the same as the highlighted row rate if it is not the latest in the sequence), or
** the corporate exchange rate effective on the suggested date, if there is one, or
** 0.0 otherwise.


=== Editing an Existing Row ===
Like the corporate rate screen, any change to the currency pair will automatically update the date/time and rate
== Corporate Rate Validation in 2.x That Is Not Implemented in OpenPetra ==
In 2.x the software validates that there is only one rate per accounting period and that it is on the first day of the accounting period.  However this seems difficult to do reliably because we cannot know for sure which ledger is being used.
== Testing the Screens ==
There are numerous tests that need to be made, particularly on the Daily Rate screen.
=== On Both Screens ===
Start with empty exchange rate tables
* Launch from the main menu
** Check the screen loads correctly with empty fields
** Add a new row. Check that the currencies make sense.  Check that the date can only be in a range that makes sense.
** Add multiple further rows in more than one currency (if possible).
** When you save some rates, re-load the screen from the main menu and check that the inverse rates are present.
** Check the 'Hide other 'To' currencies' functionality.
=== Additional Daily Rate Testing ===
* Launch from GL Batch, Gift Batch and recurring gift batch screens.
** Check the screen loads correctly with empty fields
** Add a new row. Check that the currencies make sense.  Check that the date can only be in a range that makes sense.
** Add multiple further rows in more than one currency (if possible).
* Re-launch from a different, earlier, batch that pre-dates the entries you just worked on.  You should not see those rates listed because they become effective after the new batch date.
* Re-launch from a different, later, batch that post-dates the entries you worked on.  You should still see those rates listed.
* Test the functionality of the Revaluation screen.
* Activate Edit/Delete functionality
** You should see information pop-ups for rates that are used in the batch and journal tables.
** If you edit a rate that is used in an un-posted batch, you should find that the rate is modified in that table as well - and the exchange rate screen should still recognise that rate as used.
** Test that the information messages are correct - that they give the correct date or date range over which the rate is used in the journal.  (If the rate is used on more than one date the message should give a From/To date range.)
** Test the messages that you get if you enter an exchange rate that differs from its 'neighbour' by more than 10%.  (Note that the message will be displayed as you cursor through '''both''' rows).
** Check that the only rows that can be edited are the ones that do not use the rate in a posted journal.
** Check that the only rows that can be deleted are the ones that do not use the rate in a posted or un-posted journal.