Exchange Rates

From UG

(Difference between revisions)

Jump to: navigation, search

Current revision as of 15:29, 18 February 2015

[edit] Info

parent mantis - http://ct.jaguarfreight.com/mantis/view.php?id=837

Scope:

This covers "Currencies and Exchange Rates" admin part of Accounting component.

[edit] Business Needs and Requirements

[edit] Formulas

Exchange rates are used in many places in the system to convert from one currency to another.

We use several different formulas now in different places.

[edit] v1

Rate used is based on the date associated with invoice:

For Sales Invoices it is Sales Invoices#Issuing Date

For Purchase Invoices it is Purchase_Invoices#Date

[edit] v2

base exg rates on "CT created on" date until "actual arrival" date is set (for both Purchase and Sales invoices)
after actual arrival is set base exg rates on that date
in case of group use dates from CT with lowest CT number

Note
-----
Currently, On PI level in case of Master if say amount posted is in euro then code will first identify euro
amount for each CT and then convert separately and then combine them to identify PI amount for
the group.

[edit] Admin

See interface on Fig below.

Location: Acc > Currencies and Exchange Rates

[edit] Office specific or Global?

These components (except Manage Currencies!) are office specific - see an "Office" dropdown

[edit] Currencies

[edit] Summary

Here is a table of currencies that can be used for calculations in the CT2. This table is shared by all Jaguar offices.

[edit] User Interface and Functionality

See example on Figure 1
Typical CT2 interface.

[edit] View All Table

attributes listed:

all, see Entities and Attributes

sort by:

name

paging:

none

[edit] Add

Click "Add".
Form in Pop-up will appear
All fields are blank

[edit] Edit

Select one item on the list and click "Edit"
Form in Pop-up will appear.

[edit] Remove

Select one item on the list and click "Remove"
System presents message:

"Are you sure you want to delete all selected Currencies? [Y, N]"

If currency is already in use then System presents message:

"You cannot delete this Currency as it is used in the system. [OK]"

[edit] Entities and Attributes

Currency entity has the following attributes:

Abbreviation

- meaning: currency code, this will show up on invoices, etc

- format: 3 chars, caps, letters only

- example: USD

- unique

Name

- meaning: full currency name

- format: 0 to 50 chars, mixed case

- example: US Dollars

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: Manage Currencies. View and Edit.

[edit] DB

Tbl Currency

[edit] Exchange Rates and View Exchange Rates

[edit] Summary

Exchange Rate is a number assigned to an ordered pair of currencies (Currency1, Currency2). Example: (USD, GBP, 0.63).

In finance, the exchange rates between two currencies specifies how much one currency is worth in terms of the other (wiki).

Each Jaguar Office has its own Exchange Rates.

[edit] User Interface and Functionality

CT2 User can view and edit the Exchange Rates on the separate tabs of Currencies And Exchange Rates Home:

View - on View Exchange Rates tab
Edit - on Exchange Rates tab

[edit] View

For each currency X in the system displays all pairs (currency Y, exchange rate from X to Y ) for current office. See Figure 1
columns:
- Currency From (X)
- Currency To (Y)
- Exchange Rate

[edit] Edit

see Figure 2
steps:

select "Currency From:" from the dropdown list

edit whole and decimal parts for required Currency To

click "Save All Changes"

(re)defining exchange rate from X to Y would automatically (re)define exchange rate from Y to X

(In other words, if X is set then Y will be automatically re-calculated as Y=1/X. And vice versa).

[edit] Entities and Attributes

Currency From

available currencies in the system

Currency To

available currencies in the system

whole part of exchange rate

integer from 0 to 9999999

decimal part of exchange rate

integer from 0 to 99

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: View Exchange Rates.

[edit] Figure 2: Manage Exchange Rates.

[edit] DB

See Tbl Currency Exchange Rate

[edit] Exchange Rates History

[edit] Info

tbd

[edit] Business Needs and Requirements

[edit] Technical Specification

[edit] Summary

Exchange Rates History is the report that contains all changes of Exchange Rates.

[edit] User Interface and Functionality

See example on Figure 1
Typical CT2 interface:

- Output HTML table uses standart ZK Paging feature. Each page contains 20 lines.

- Sorting of table proceeds by "Date and Time" column, in ascending order.

[edit] Entities and Attributes

Exchange Rates History entity has the following attributes:

Operator Name

- meaning: Name of CT2 User who made the change of Exchange Rate

- format: First Name + Last Name

- example: Marc Selter

Date and Time

- meaning: Date and Time of change

- format: datetime stamp (with Time Zone)

- example: 10-Aug-2010 09:35 AM

Client Company

- meaning: The name of the Client Company for which made this change

- format: string

- example: ABA Packaging

Currency From

- meaning: name of base currency

- format: abbreviated currency code

- example: GBP

Currency To

- meaning: name of calculated currency

- format: abbreviated currency code

- example: USD

Old Rate

- meaning: Exchange Rate value before changing

- format: real number with 2 decimal places

- example: 2.00

New Rate

- meaning: Exchange Rate value after changing

- format: real number with 2 decimal places

- example: 1.50

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: Exchange Rates History.

[edit] History

[edit] Non Critical Bugs and RFC

[edit] DB

[edit] Client Company Exchange Rates

[edit] Info

tbd

[edit] Business Needs and Requirements

[edit] Technical Specification

[edit] Summary

Client Exchange Rate is a number assigned to an ordered triple of (Client Company, Currency1, Currency2). Example: (Coty, USD, GBP, 0.63).

Each Jaguar Office has its own Client Exchange Rates.

[edit] User Interface and Functionality

CT2 User can manage the Client Exchange Rates on the special tab of Currencies And Exchange Rates Home - Client Company Exchange Rates. See Figure 1.

[edit] View

attributes listed:

all, see Entities and Attributes

sort by:

Client Company

paging:

none

[edit] Add

Click "Add".
Form in Pop-up will appear
All fields are blank by default

[edit] Edit

Select one item on the list and click "Edit"
Form in Pop-up will appear.

NOTE: Unlike the general Exchange Rates, (re)defining exchange rate from X to Y wouldn't automatically (re)define exchange rate from Y to X. It means that setting (Client Company, Currency1, Currency2) will not automatically set (Client Company, Currency2, Currency1).

[edit] Remove

Select one or more items on the list and click "Remove"
System presents message:

"Are you sure you want to delete all selected Client Company Exchange Rates?"

Clicking of the "OK" button confirms the removing of selected Rates. Clicking of the "Cancel" button cancels the removing.

[edit] Entities and Attributes

Client Company

available Client Companies in the system

Currency From

available Currencies in the system

Currency To

available Currencies in the system

Rate

real number with 2 decimal places

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: Manage Client Company Exchange Rates.

[edit] History

[edit] Non Critical Bugs and RFC

[edit] DB

[edit] Change Requests

[edit] SOW 0

See #Solution and next.

[edit] SOW 1

0002812: bugs/changes: 1) bug: Two GBP currencies listed in the system 2) add archive feature for currencies

[edit] Core Requirements

Archiving feature has the following on Currencies Tab:

add Archive/Restore and Replace buttons and "Hide archived" checkbox with appropriate functionality for new 2012 standard
- "Archive" button: get user confirm, mark as archived with image
- "Restore" button: get user confirm, remove "archived" mark
- "Replace" button: get user confirm, "merge" two items into one
- "Hide archived" checkbox:
  - if checked then don't display archived Currencies in table,
  - if unchecked then display all
add column with a garbage can to mark archived items in table of Currencies

[edit] Related Functionality

Provide recording (and displaying) of all archive-related actions with Currencies in System Log
For every place in the system where currency is appearing:
- if currency is tagged as archived then it should not appear on the list

[edit] SOW 2

0003577: add Currency Code validation to prevent duplication of Currencies

[edit] Core Requirements

We need some sort of validation of what user enters to make sure it's not a duplicate.

Example: gbp (in lower case) & GBP (in upper case) are the same so the system should not allow it to be saved because its a duplicate.

[edit] Solution

Every Currency Abbreviation should be unique in CT2.
System should provide entering of Currency Abbreviation in CAPITAL letters only.
Validation should work upon creating and editing of Currency (in Add/Edit Currency window).
Comparison should include archived Currencies also.

[edit] Related Functionality

Archive feature for Currencies will be done under mantis 2812

[edit] SOW 3

0004723: Redesign of Exchange Rate UI and functionality

[edit] Core Requirements

Stop mirroring rates between currencies

2 converted Currencies DOES NOT REFLECT / MIRROR / RELATE / IMPACT / ANYTHING

Duplicate rate interface - for SALES and for PURCHASES

We need to duplicate the exchange rate interface, and apply 1 side for SALES and the other side for PURCHASES

Per Office? NOT CONFIRMED

[edit] Solution

Stop mirroring

Any two Currencies should not use reverse calculation of Exchange rate between them
Each direction of Exchange Rate should be updated separately from its pair
Example: "USD to GBP and "GBP to USD" are 2 separate entries in the System

Sales/Purchase Rate type

From release 2.38.0 we started to use two types of Exchange rate instead of the one used previously:

"Sales/Internal" Rate Type should be used for calculation in Sales and Internal Invoices and related reports
"Purchase" Rate Type should be used for calculation in Purchase Invoices and related reports

Saving the updates entered in one Type section should be proceeded before switching to other Type section
Exchange Rate history is kept in DB for each Type of Rate

Global level of Exchange rates

ONLY 1 SET OF EXCHANGE RATES FOR THE ENTIRE SYSTEM - NO MORE "PER OFFICE" EXCHANGE RATES.
Date when we starting to use Global Rates: 24-Jan-2015.

[edit] Figure 1: Redesigned Exchange Rates UI.

[edit] Related Functionality

0004764: Correct some Exchange Rates and re-calculate all related conversions.
0004780: Provide 6 decimals in Exchange Rates.

[edit] DB



-- ----------------------------

-- Table structure for tblCurrencyExchangeRateChange

-- ----------------------------

DROP TABLE IF EXISTS `tblCurrencyExchangeRateChange`;

CREATE TABLE `tblCurrencyExchangeRateChange` (
  `Id` int(10) NOT NULL auto_increment,
  `OfficeId` int(10) NOT NULL,
  `FromCurrencyAbbreviation` varchar(3) NOT NULL default '',
  `ToCurrencyAbbreviation` varchar(3) NOT NULL default '',
  `OldRate` double default NULL,
  `NewRate` double default NULL,
  `DateTime` datetime NOT NULL,
  `GenericUserId` int(10) NOT NULL,
  `ClientCompanyId` int(10) default NULL,
  PRIMARY KEY  (`Id`),
  KEY `OfficeId` (`OfficeId`),
  KEY `GenericUserId` (`GenericUserId`),
  KEY `ClientCompanyId` (`ClientCompanyId`),
  CONSTRAINT `tblCurrencyExchangeRateChange_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
  CONSTRAINT `tblCurrencyExchangeRateChange_fk1` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE,
  CONSTRAINT `tblCurrencyExchangeRateChange_fk2` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1;





mysql> select * from tblCurrencyExchangeRateChange where OfficeId = "3";

+-----+----------+--------------------------+------------------------+---------------------+---------------------+---------------------+---------------+-----------------+
| Id  | OfficeId | FromCurrencyAbbreviation | ToCurrencyAbbreviation | OldRate             | NewRate             | DateTime            | GenericUserId | ClientCompanyId |
+-----+----------+--------------------------+------------------------+---------------------+---------------------+---------------------+---------------+-----------------+
|  33 |        3 | USD                      | HKD                    | NULL                |   0.129533678756477 | 2009-01-13 19:30:07 |            44 | NULL            |
|  34 |        3 | EUR                      | HKD                    | NULL                |  0.0869565217391304 | 2009-01-13 19:30:07 |            44 | NULL            |
|  35 |        3 | HKD                      | USD                    | NULL                |                7.72 | 2009-01-13 19:30:07 |            44 | NULL            |
|  36 |        3 | HKD                      | EUR                    | NULL                |                11.5 | 2009-01-13 19:30:07 |            44 | NULL            |
|  37 |        3 | USD                      | HKD                    |   0.129533678756477 |                7.72 | 2009-01-13 19:32:02 |            44 | NULL            |
|  38 |        3 | HKD                      | USD                    |                7.72 |   0.129533678756477 | 2009-01-13 19:32:02 |            44 | NULL            |
|  39 |        3 | EUR                      | HKD                    |  0.0869565217391304 |                11.5 | 2009-01-13 19:32:15 |            44 | NULL            |
|  40 |        3 | HKD                      | EUR                    |                11.5 |  0.0869565217391304 | 2009-01-13 19:32:15 |            44 | NULL            |
| 151 |        3 | 111                      | nnn                    | NULL                |              123.45 | 2009-12-18 15:05:45 |           114 | NULL            |
| 152 |        3 | nnn                      | 111                    | NULL                | 0.00810044552450385 | 2009-12-18 15:05:45 |           114 | NULL            |
| 153 |        3 | 111                      | nnn                    |              123.45 |              999.99 | 2009-12-18 15:07:33 |           114 | NULL            |
| 154 |        3 | nnn                      | 111                    | 0.00810044552450385 |     0.0010000100001 | 2009-12-18 15:07:33 |           114 | NULL            |
+-----+----------+--------------------------+------------------------+---------------------+---------------------+---------------------+---------------+-----------------+
12 rows in set

mysql> 

mysql> describe tblCurrencyExchangeRateChange;
+--------------------------+------------+------+-----+---------+----------------+
| Field                    | Type       | Null | Key | Default | Extra          |
+--------------------------+------------+------+-----+---------+----------------+
| Id                       | int(10)    | NO   | PRI | NULL    | auto_increment |
| OfficeId                 | int(10)    | NO   | MUL | NULL    |                |
| FromCurrencyAbbreviation | varchar(3) | NO   |     |         |                |
| ToCurrencyAbbreviation   | varchar(3) | NO   |     |         |                |
| OldRate                  | double     | YES  |     | NULL    |                |
| NewRate                  | double     | YES  |     | NULL    |                |
| DateTime                 | datetime   | NO   |     | NULL    |                |
| GenericUserId            | int(10)    | NO   | MUL | NULL    |                |
| ClientCompanyId          | int(10)    | YES  | MUL | NULL    |                |
+--------------------------+------------+------+-----+---------+----------------+
9 rows in set



-- ----------------------------

-- Table structure for tblCurrencyExchangeRate

-- ----------------------------

DROP TABLE IF EXISTS `tblCurrencyExchangeRate`;

CREATE TABLE `tblCurrencyExchangeRate` (
  `Id` int(10) NOT NULL auto_increment,
  `FromCurrencyId` int(10) NOT NULL,
  `ToCurrencyId` int(10) NOT NULL,
  `Rate` double default NULL,
  `OfficeId` int(10) NOT NULL,
  `ClientCompanyId` int(10) default NULL,
  `DateTime` datetime default NULL,
  PRIMARY KEY  (`Id`),
  KEY `FromCurrencyId` (`FromCurrencyId`),
  KEY `ToCurrencyId` (`ToCurrencyId`),
  KEY `OfficeId` (`OfficeId`),
  KEY `ClientCompanyId` (`ClientCompanyId`),
  CONSTRAINT `tblCurrencyExchangeRate_fk` FOREIGN KEY (`FromCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tblCurrencyExchangeRate_fk1` FOREIGN KEY (`ToCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tblCurrencyExchangeRate_fk2` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tblCurrencyExchangeRate_fk3` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2094 DEFAULT CHARSET=latin1;





mysql> describe tblCurrencyExchangeRate;
+-----------------+----------+------+-----+---------+----------------+
| Field           | Type     | Null | Key | Default | Extra          |
+-----------------+----------+------+-----+---------+----------------+
| Id              | int(10)  | NO   | PRI | NULL    | auto_increment |
| FromCurrencyId  | int(10)  | NO   | MUL | NULL    |                |
| ToCurrencyId    | int(10)  | NO   | MUL | NULL    |                |
| Rate            | double   | YES  |     | NULL    |                |
| OfficeId        | int(10)  | NO   | MUL | NULL    |                |
| ClientCompanyId | int(10)  | YES  | MUL | NULL    |                |
| DateTime        | datetime | YES  |     | NULL    |                |
+-----------------+----------+------+-----+---------+----------------+
7 rows in set

mysql> select * from
 tblCurrencyExchangeRate;
+------+----------------+--------------+--------------------+----------+-----------------+---------------------+
| Id   | FromCurrencyId | ToCurrencyId | Rate               | OfficeId | ClientCompanyId | DateTime            |
+------+----------------+--------------+--------------------+----------+-----------------+---------------------+
|  133 |              9 |            1 |  0.854700854700855 |        1 | NULL            | 2009-03-23 08:02:49 |
|  137 |             11 |            1 |             8.5654 |        1 | NULL            | 2008-12-07 20:54:24 |
|  173 |              9 |            3 |  0.746268656716418 |        1 | NULL            | NULL                |
|  177 |             11 |            3 |               1.25 |        1 | NULL            | NULL                |
|  217 |             11 |            9 |               9.56 |        1 | NULL            | NULL                |
|  237 |              9 |           11 |              0.345 |        1 | NULL            | NULL                |
|  369 |             15 |            1 | 0.0392156862745098 |        1 | NULL            | NULL                |
|  373 |             15 |            3 |               0.05 |        1 | NULL            | NULL                |
|  377 |             15 |            9 | NULL               |        1 | NULL            | NULL                |
|  381 |             15 |           11 | 0.0285714285714286 |        1 | NULL            | NULL                |
|  393 |              9 |           15 | NULL               |        1 | NULL            | NULL                |
|  397 |             11 |           15 |                 35 |        1 | NULL            | NULL                |
|  401 |             16 |            1 |  0.598802395209581 |        1 | NULL            | 2008-12-12 12:44:31 |
|  405 |             16 |            3 |               11.5 |        1 | NULL            | NULL                |
|  409 |             16 |            9 |             0.0001 |        1 | NULL            | 2009-01-13 19:21:59 |
|  413 |             16 |           11 |               14.2 |        1 | NULL            | NULL                |
|  417 |             16 |           15 |                 25 |        1 | NULL            | NULL                |
|  425 |              3 |           16 | NULL               |        1 | NULL            | NULL                |
|  429 |              9 |           16 |              10000 |        1 | NULL            | 2009-01-13 19:21:59 |
|  433 |             11 |           16 | NULL               |        1 | NULL            | NULL                |
|  437 |             15 |           16 |               0.04 |        1 | NULL            | NULL                |
|  441 |              3 |            1 | NULL               |        2 | NULL            | 2009-03-06 14:45:21 |
|  442 |              9 |            1 | NULL               |        2 | NULL            | 2009-03-06 14:45:21 |
|  443 |             11 |            1 | NULL               |        2 | NULL            | 2009-03-06 14:45:21 |

mysql> describe tblClientExchangeRate;
+------------------------+----------+------+-----+---------+----------------+
| Field                  | Type     | Null | Key | Default | Extra          |
+------------------------+----------+------+-----+---------+----------------+
| Id                     | int(10)  | NO   | PRI | NULL    | auto_increment |
| ClientID               | int(10)  | NO   |     | NULL    |                |
| ExchangeRateNumber     | int(10)  | NO   |     | NULL    |                |
| ThisClientExchangeRate | double   | NO   |     | NULL    |                |
| LastUpdated            | datetime | NO   |     | NULL    |                |
+------------------------+----------+------+-----+---------+----------------+
5 rows in set

mysql> select * from tblClientExchangeRate;
Empty set

@@ Line 1: / Line 1: @@
-[[Category:Currencies And Exchange Rates]]
+[[Category:Acc]]
-== Mantis ==
+== Info ==
-[http://mantis.jaguarfreight.com/mantis/view.php?id=tbd tbd]
+* parent mantis - http://ct.jaguarfreight.com/mantis/view.php?id=837
+'''Scope:'''
+This covers "Currencies and Exchange Rates" admin part of Accounting component.
 == Business Needs and Requirements ==
-== Technical Specification ==
-=== Summary ===
-This component combines all features related to currencies:
+== Formulas ==
-* Currencies And Exchange Rates Home - described in this article.
+Exchange rates are used in many places in the system to convert from one currency to another.
-* see [[Manage Currencies (CER)]]
-* see [[Manage / View Exchange Rates (CER)]]
-* see [[Client Company Exchange Rates (CER)]]
-* see [[Exchange Rates History (CER)]]
-=== User Interface and Functionality ===
+We use several different formulas now in different places.
-* Location: Acc > Currencies and Exchange Rates
+=== v1 ===
-* All of these components can be found on multitab view - see [[#Figure_1:_Currencies_And_Exchange_Rates_Home. | Figure 1]]
-==== Office specific or Global? ====
+Rate used is based on the date associated with invoice:
-* These components (except Manage Currencies!) are office specific
+* For [[Sales Invoices]] it is [[Sales Invoices#Issuing Date]]
-* There is an "office" dropdown (see [[#Figure_1:_Currencies_And_Exchange_Rates_Home. | Figure 1]]) that controls for what office to display/manage data
-=== Entities and Attributes ===
+* For [[Purchase Invoices]] it is [[Purchase_Invoices#Date]]
-=== Special Cases and Misc ===
+=== v2 ===
-=== Figures ===
+* base exg rates on "CT created on" date until "actual arrival" date is set (for both Purchase and Sales invoices)
+* after actual arrival is set base exg rates on that date
+* in case of group use dates from CT with lowest CT number
+ Note
+ -----
+ Currently, On PI level in case of Master if say amount posted is in euro then code will first identify euro
+ amount for each CT and then convert separately and then combine them to identify PI amount for
+ the group.
+== Admin ==
+See interface on Fig below.
-==== Figure 1: Currencies And Exchange Rates Home. ====
 [[File:Currencies And Exchange Rates Home.JPG]]
-== History ==
+Location: Acc > Currencies and Exchange Rates
-== RFC ==
+==== Office specific or Global? ====
+* These components (except Manage Currencies!) are office specific - see an "Office" dropdown
+=== '''Currencies''' ===
+==== Summary ====
+Here is a table of currencies that can be used for calculations in the CT2. This table is shared by '''all''' Jaguar offices.
+==== User Interface and Functionality ====
+* See example on [[#Figure 1: Manage Currencies. View and Edit. | Figure 1]]
+* Typical CT2 interface.
+==== View All Table ====
+* attributes listed:
+: all, see[[#Entities and Attributes | Entities and Attributes]]
+* sort by:
+: name
+* paging:
+: none
+==== Add ====
+* Click "Add".
+* Form in Pop-up will appear
+* All fields are blank
+==== Edit ====
+* Select one item on the list and click "Edit"
+* Form in Pop-up will appear.
+==== Remove ====
+* Select one item on the list and click "Remove"
+* System presents message:
+:: '' "Are you sure you want to delete all selected Currencies? [Y, N]" ''
+* If currency is already in use then System presents message:
+:: '' "You cannot delete this Currency as it is used in the system. [OK]" ''
+==== Entities and Attributes ====
+Currency entity has the following attributes:
+* '''Abbreviation'''
+: - ''meaning'': currency code, this will show up on invoices, etc
+: - ''format'': 3 chars, caps, letters only
+: - ''example'': USD
+: - '''''unique'''''
+*'''Name'''
+: - ''meaning'': full currency name
+: - ''format'': 0 to 50 chars, mixed case
+: - ''example'': US Dollars
+==== Special Cases and Misc ====
+==== Look And Feel ====
+==== Figures ====
+===== Figure 1: Manage Currencies. View and Edit. =====
+[[File:Manage_Currencies.JPG]]
+==== DB ====
+[[Tbl Currency]]
+=== '''Exchange Rates and View Exchange Rates''' ===
+==== Summary ====
+Exchange Rate is a number assigned to an ordered pair of currencies (Currency1, Currency2). Example: (USD, GBP, 0.63).
+ In finance, the '''exchange rates''' between two currencies specifies how much one currency is worth in terms of the other [http://en.wikipedia.org/wiki/Exchange_rate (wiki)].
+Each Jaguar Office has its own Exchange Rates.
+==== User Interface and Functionality ====
+CT2 User can view and edit the Exchange Rates on the separate tabs of [[Currencies And Exchange Rates Home]]:
+* View - on View Exchange Rates tab
+* Edit - on Exchange Rates tab
+===== View =====
+* For each currency '''X''' in the system displays all pairs (currency '''Y''', exchange rate from X to Y ) for current office. See [[#Figure 1: View Exchange Rates. | Figure 1]]
+* columns:
+** Currency From (X)
+** Currency To (Y)
+** Exchange Rate
+===== Edit =====
+* see [[#Figure 2: Manage Exchange Rates. | Figure 2]]
+* steps:
+:* select '''"Currency From:"''' from the dropdown list
+:* edit whole and decimal parts for required '''Currency To'''
+:* click "Save All Changes"
+* (re)defining exchange rate from X to Y would automatically (re)define exchange rate from Y to X
+: ''(In other words, if '''X''' is set then '''Y''' will be automatically re-calculated as '''Y=1/X'''. And vice versa).''
+==== Entities and Attributes ====
+* Currency From
+: available currencies in the system
+* Currency To
+: available currencies in the system
+* whole part of exchange rate
+: integer from 0 to 9999999
+* decimal part of exchange rate
+: integer from 0 to 99
+==== Special Cases and Misc ====
+==== Look And Feel ====
+==== Figures ====
+===== Figure 1: View Exchange Rates. =====
+[[File:View Exchange Rates.JPG]]
+===== Figure 2: Manage Exchange Rates. =====
+[[File:Manage Exchange Rates.JPG]]
+==== DB ====
+See [[Tbl Currency Exchange Rate]]
+=== '''Exchange Rates History''' ===
+==== Info ====
+[http://mantis.jaguarfreight.com/mantis/view.php?id=tbd tbd]
+==== Business Needs and Requirements ====
+=== Technical Specification ===
+===== Summary =====
+'''Exchange Rates History''' is the report that contains all changes of Exchange Rates.
+===== User Interface and Functionality =====
+* See example on [[#Figure 1: Exchange Rates History.  | Figure 1]]
+* Typical CT2 interface:
+: - Output HTML table uses standart [http://mantis.jaguarfreight.com/wiki/Paging#ZK_standard ZK Paging] feature. Each page contains 20 lines.
+: - Sorting of table proceeds by "Date and Time" column, in ascending order.
+===== Entities and Attributes =====
+Exchange Rates History entity has the following attributes:
+* '''Operator Name'''
+: - ''meaning:'' Name of CT2 User who made the change of Exchange Rate
+: - ''format:'' First Name + Last Name
+: - ''example:'' Marc Selter
+* '''Date and Time'''
+: - ''meaning:'' Date and Time of change
+: - ''format:'' datetime stamp (with Time Zone)
+: - ''example:'' 10-Aug-2010 09:35 AM
+* '''Client Company'''
+: - ''meaning:'' The name of the Client Company for which made this change
+: - ''format:'' string
+: - ''example:'' ABA Packaging
+* '''Currency From'''
+: - ''meaning:'' name of base currency
+: - ''format:'' abbreviated currency code
+: - ''example:'' GBP
+* '''Currency To'''
+: - ''meaning:'' name of calculated currency
+: - ''format:'' abbreviated currency code
+: - ''example:'' USD
+* '''Old Rate'''
+: - ''meaning:'' Exchange Rate value before changing
+: - ''format:'' real number with 2 decimal places
+: - ''example:'' 2.00
+* '''New Rate'''
+: - ''meaning:'' Exchange Rate value after changing
+: - ''format:'' real number with 2 decimal places
+: - ''example:'' 1.50
+===== Special Cases and Misc =====
+===== Look And Feel =====
+===== Figures =====
+====== Figure 1: Exchange Rates History. ======
+[[File:Exchange Rates History.JPG]]
+==== History ====
+==== Non Critical Bugs and RFC ====
+==== DB ====
+=== '''Client Company Exchange Rates''' ===
+==== Info ====
+[http://mantis.jaguarfreight.com/mantis/view.php?id=tbd tbd]
+==== Business Needs and Requirements ====
+==== Technical Specification ====
+===== Summary =====
+Client Exchange Rate is a number assigned to an ordered triple of (Client Company, Currency1, Currency2). ''Example'': (Coty, USD, GBP, 0.63).
+Each Jaguar Office has its own Client Exchange Rates.
+===== User Interface and Functionality =====
+CT2 User can manage the Client Exchange Rates on the special tab of [[Currencies And Exchange Rates Home]] - Client Company Exchange Rates. See [[#Figure 1: Manage Client Company Exchange Rates. | Figure 1]].
+====== View ======
+* attributes listed:
+: all, see [[#Entities and Attributes | Entities and Attributes]]
+* sort by:
+: Client Company
+* paging:
+: none
+====== Add ======
+* Click "Add".
+* Form in Pop-up will appear
+* All fields are blank by default
+====== Edit ======
+* Select one item on the list and click "Edit"
+* Form in Pop-up will appear.
+NOTE: Unlike the general [[# http://mantis.jaguarfreight.com/wiki/Manage_/_View_Exchange_Rates_(CER) | Exchange Rates]], (re)defining exchange rate from X to Y wouldn't automatically (re)define exchange rate from Y to X. It means that setting (Client Company, Currency1, Currency2) will not automatically set (Client Company, Currency2, Currency1).
+====== Remove ======
+* Select one or more items on the list and click "Remove"
+* System presents message:
+:: '' "Are you sure you want to delete all selected Client Company Exchange Rates?" ''
+* Clicking of the "OK" button confirms the removing of selected Rates. Clicking of the "Cancel" button cancels the removing.
+===== Entities and Attributes =====
+* '''Client Company'''
+: available Client Companies in the system
+* '''Currency From'''
+: available Currencies in the system
+* '''Currency To'''
+: available Currencies in the system
+* '''Rate'''
+: real number with 2 decimal places
+===== Special Cases and Misc =====
+===== Look And Feel =====
+===== Figures =====
+====== Figure 1: Manage Client Company Exchange Rates. ======
+[[File:Manage Client Specific Exchange Rates.JPG]]
+==== History ====
+==== Non Critical Bugs and RFC ====
+==== DB ====
+== Change Requests ==
+=== SOW 0 ===
+See [[#Solution]] and next.
+=== SOW 1 ===
+'''[http://ct.jaguarfreight.com/mantis/view.php?id=2812 0002812]: bugs/changes:''' <span style="text-decoration:line-through">1) bug: Two GBP currencies listed in the system</span> '''2) add archive feature for currencies'''
+==== Core Requirements ====
+Archiving feature has the following on Currencies Tab:
+* add Archive/Restore and Replace buttons and "Hide archived" checkbox with appropriate functionality for [http://ct.jaguarfreight.com/wiki/Archive_%28Feature%29#New_2012_standard new 2012 standard]
+** "Archive" button: get user confirm, mark as archived with image
+** "Restore" button: get user confirm, remove "archived" mark
+** "Replace" button: get user confirm, "merge" two items into one
+** "Hide archived" checkbox:
+*** if checked then don't display archived Currencies in table,
+*** if unchecked then display all
+* add column with a garbage can to mark archived items in table of Currencies
+==== Related Functionality ====
+* Provide recording (and displaying) of all archive-related actions with Currencies in System Log
+* For every place in the system where currency is appearing:
+** if currency is tagged as archived then it should not appear on the list
+=== SOW 2 ===
+'''[http://ct.jaguarfreight.com/mantis/view.php?id=3577 0003577]:''' add Currency Code validation to prevent duplication of Currencies
+==== Core Requirements ====
+We need some sort of validation of what user enters to make sure it's not a duplicate.
+* Example: '''gbp''' ''(in lower case)'' & '''GBP''' ''(in upper case)'' are the same so the system should not allow it to be saved because its a duplicate.
+==== Solution ====
+* Every Currency Abbreviation should be '''unique''' in CT2.
+* System should provide entering of Currency Abbreviation in '''CAPITAL''' letters only.
+* Validation should work upon '''creating and editing''' of Currency ''(in Add/Edit Currency window)''.
+* Comparison should include '''archived''' Currencies also.
+==== Related Functionality ====
+* [[#SOW 1 |Archive feature for Currencies]] will be done under mantis [http://ct.jaguarfreight.com/mantis/view.php?id=2812 2812]
+=== SOW 3 ===
+'''[http://ct.jaguarfreight.com/mantis/view.php?id=4723 0004723]:''' Redesign of Exchange Rate UI and functionality
+==== Core Requirements ====
+* Stop mirroring rates between currencies
+:* 2 converted Currencies DOES NOT REFLECT / MIRROR / RELATE / IMPACT / ANYTHING
+* Duplicate rate interface - for SALES and for PURCHASES
+:* We need to duplicate the exchange rate interface, and apply 1 side for SALES and the other side for PURCHASES
+* Per Office? NOT CONFIRMED
+==== Solution ====
+* '''Stop mirroring'''
+:* Any two Currencies should not use reverse calculation of Exchange rate between them
+:* Each direction of Exchange Rate should be updated separately from its pair
+:* ''Example'': "USD to GBP and "GBP to USD" are 2 separate entries in the System
+* '''Sales/Purchase Rate type'''
+:* From release [[CT2_Releases#1.2F23.28ver_2.38.0.29 |2.38.0]] we started to use two types of Exchange rate instead of the one used previously:
+::* "Sales/Internal" Rate Type should be used for calculation in Sales and Internal Invoices and related reports
+::* "Purchase" Rate Type should be used for calculation in Purchase Invoices and related reports
+:* Saving the updates entered in one Type section should be proceeded before switching to other Type section
+:* Exchange Rate history is kept in DB for each Type of Rate
+* '''Global level of Exchange rates'''
+:* ONLY 1 SET OF EXCHANGE RATES FOR THE ENTIRE SYSTEM - NO MORE "PER OFFICE" EXCHANGE RATES.
+:* Date when we starting to use Global Rates: 24-Jan-2015.
+===== Figure 1: Redesigned Exchange Rates UI. =====
+[[File:Global-Exchange-Rates.png |border]]
+==== Related Functionality ====
+* [http://ct.jaguarfreight.com/mantis/view.php?id=4764 0004764]: Correct some Exchange Rates and re-calculate all related conversions.
+* [http://ct.jaguarfreight.com/mantis/view.php?id=4780 0004780]: Provide 6 decimals in Exchange Rates.
+== DB ==
+<pre>
+-- ----------------------------
+-- Table structure for tblCurrencyExchangeRateChange
+-- ----------------------------
+DROP TABLE IF EXISTS `tblCurrencyExchangeRateChange`;
+CREATE TABLE `tblCurrencyExchangeRateChange` (
+  `Id` int(10) NOT NULL auto_increment,
+  `OfficeId` int(10) NOT NULL,
+  `FromCurrencyAbbreviation` varchar(3) NOT NULL default '',
+  `ToCurrencyAbbreviation` varchar(3) NOT NULL default '',
+  `OldRate` double default NULL,
+  `NewRate` double default NULL,
+  `DateTime` datetime NOT NULL,
+  `GenericUserId` int(10) NOT NULL,
+  `ClientCompanyId` int(10) default NULL,
+  PRIMARY KEY  (`Id`),
+  KEY `OfficeId` (`OfficeId`),
+  KEY `GenericUserId` (`GenericUserId`),
+  KEY `ClientCompanyId` (`ClientCompanyId`),
+  CONSTRAINT `tblCurrencyExchangeRateChange_fk` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON UPDATE CASCADE,
+  CONSTRAINT `tblCurrencyExchangeRateChange_fk1` FOREIGN KEY (`GenericUserId`) REFERENCES `tblGenericUser` (`Id`) ON UPDATE CASCADE,
+  CONSTRAINT `tblCurrencyExchangeRateChange_fk2` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1;
+mysql> select * from tblCurrencyExchangeRateChange where OfficeId = "3";
++-----+----------+--------------------------+------------------------+---------------------+---------------------+---------------------+---------------+-----------------+
+| Id  | OfficeId | FromCurrencyAbbreviation | ToCurrencyAbbreviation | OldRate             | NewRate             | DateTime            | GenericUserId | ClientCompanyId |
++-----+----------+--------------------------+------------------------+---------------------+---------------------+---------------------+---------------+-----------------+
+|  33 |        3 | USD                      | HKD                    | NULL                |   0.129533678756477 | 2009-01-13 19:30:07 |            44 | NULL            |
+|  34 |        3 | EUR                      | HKD                    | NULL                |  0.0869565217391304 | 2009-01-13 19:30:07 |            44 | NULL            |
+|  35 |        3 | HKD                      | USD                    | NULL                |                7.72 | 2009-01-13 19:30:07 |            44 | NULL            |
+|  36 |        3 | HKD                      | EUR                    | NULL                |                11.5 | 2009-01-13 19:30:07 |            44 | NULL            |
+|  37 |        3 | USD                      | HKD                    |   0.129533678756477 |                7.72 | 2009-01-13 19:32:02 |            44 | NULL            |
+|  38 |        3 | HKD                      | USD                    |                7.72 |   0.129533678756477 | 2009-01-13 19:32:02 |            44 | NULL            |
+|  39 |        3 | EUR                      | HKD                    |  0.0869565217391304 |                11.5 | 2009-01-13 19:32:15 |            44 | NULL            |
+|  40 |        3 | HKD                      | EUR                    |                11.5 |  0.0869565217391304 | 2009-01-13 19:32:15 |            44 | NULL            |
+| 151 |        3 | 111                      | nnn                    | NULL                |              123.45 | 2009-12-18 15:05:45 |           114 | NULL            |
+| 152 |        3 | nnn                      | 111                    | NULL                | 0.00810044552450385 | 2009-12-18 15:05:45 |           114 | NULL            |
+| 153 |        3 | 111                      | nnn                    |              123.45 |              999.99 | 2009-12-18 15:07:33 |           114 | NULL            |
+| 154 |        3 | nnn                      | 111                    | 0.00810044552450385 |     0.0010000100001 | 2009-12-18 15:07:33 |           114 | NULL            |
++-----+----------+--------------------------+------------------------+---------------------+---------------------+---------------------+---------------+-----------------+
+rows in set
+mysql>
+mysql> describe tblCurrencyExchangeRateChange;
++--------------------------+------------+------+-----+---------+----------------+
+| Field                    | Type       | Null | Key | Default | Extra          |
++--------------------------+------------+------+-----+---------+----------------+
+| Id                       | int(10)    | NO   | PRI | NULL    | auto_increment |
+| OfficeId                 | int(10)    | NO   | MUL | NULL    |                |
+| FromCurrencyAbbreviation | varchar(3) | NO   |     |         |                |
+| ToCurrencyAbbreviation   | varchar(3) | NO   |     |         |                |
+| OldRate                  | double     | YES  |     | NULL    |                |
+| NewRate                  | double     | YES  |     | NULL    |                |
+| DateTime                 | datetime   | NO   |     | NULL    |                |
+| GenericUserId            | int(10)    | NO   | MUL | NULL    |                |
+| ClientCompanyId          | int(10)    | YES  | MUL | NULL    |                |
++--------------------------+------------+------+-----+---------+----------------+
+rows in set
+</pre>
+<pre>
+-- ----------------------------
+-- Table structure for tblCurrencyExchangeRate
+-- ----------------------------
+DROP TABLE IF EXISTS `tblCurrencyExchangeRate`;
+CREATE TABLE `tblCurrencyExchangeRate` (
+  `Id` int(10) NOT NULL auto_increment,
+  `FromCurrencyId` int(10) NOT NULL,
+  `ToCurrencyId` int(10) NOT NULL,
+  `Rate` double default NULL,
+  `OfficeId` int(10) NOT NULL,
+  `ClientCompanyId` int(10) default NULL,
+  `DateTime` datetime default NULL,
+  PRIMARY KEY  (`Id`),
+  KEY `FromCurrencyId` (`FromCurrencyId`),
+  KEY `ToCurrencyId` (`ToCurrencyId`),
+  KEY `OfficeId` (`OfficeId`),
+  KEY `ClientCompanyId` (`ClientCompanyId`),
+  CONSTRAINT `tblCurrencyExchangeRate_fk` FOREIGN KEY (`FromCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `tblCurrencyExchangeRate_fk1` FOREIGN KEY (`ToCurrencyId`) REFERENCES `tblCurrency` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `tblCurrencyExchangeRate_fk2` FOREIGN KEY (`OfficeId`) REFERENCES `tblOffice` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `tblCurrencyExchangeRate_fk3` FOREIGN KEY (`ClientCompanyId`) REFERENCES `tblClientCompany` (`Id`) ON UPDATE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=2094 DEFAULT CHARSET=latin1;
+mysql> describe tblCurrencyExchangeRate;
++-----------------+----------+------+-----+---------+----------------+
+| Field           | Type     | Null | Key | Default | Extra          |
++-----------------+----------+------+-----+---------+----------------+
+| Id              | int(10)  | NO   | PRI | NULL    | auto_increment |
+| FromCurrencyId  | int(10)  | NO   | MUL | NULL    |                |
+| ToCurrencyId    | int(10)  | NO   | MUL | NULL    |                |
+| Rate            | double   | YES  |     | NULL    |                |
+| OfficeId        | int(10)  | NO   | MUL | NULL    |                |
+| ClientCompanyId | int(10)  | YES  | MUL | NULL    |                |
+| DateTime        | datetime | YES  |     | NULL    |                |
++-----------------+----------+------+-----+---------+----------------+
+rows in set
+mysql> select * from
+ tblCurrencyExchangeRate;
++------+----------------+--------------+--------------------+----------+-----------------+---------------------+
+| Id   | FromCurrencyId | ToCurrencyId | Rate               | OfficeId | ClientCompanyId | DateTime            |
++------+----------------+--------------+--------------------+----------+-----------------+---------------------+
+|  133 |              9 |            1 |  0.854700854700855 |        1 | NULL            | 2009-03-23 08:02:49 |
+|  137 |             11 |            1 |             8.5654 |        1 | NULL            | 2008-12-07 20:54:24 |
+|  173 |              9 |            3 |  0.746268656716418 |        1 | NULL            | NULL                |
+|  177 |             11 |            3 |               1.25 |        1 | NULL            | NULL                |
+|  217 |             11 |            9 |               9.56 |        1 | NULL            | NULL                |
+|  237 |              9 |           11 |              0.345 |        1 | NULL            | NULL                |
+|  369 |             15 |            1 | 0.0392156862745098 |        1 | NULL            | NULL                |
+|  373 |             15 |            3 |               0.05 |        1 | NULL            | NULL                |
+|  377 |             15 |            9 | NULL               |        1 | NULL            | NULL                |
+|  381 |             15 |           11 | 0.0285714285714286 |        1 | NULL            | NULL                |
+|  393 |              9 |           15 | NULL               |        1 | NULL            | NULL                |
+|  397 |             11 |           15 |                 35 |        1 | NULL            | NULL                |
+|  401 |             16 |            1 |  0.598802395209581 |        1 | NULL            | 2008-12-12 12:44:31 |
+|  405 |             16 |            3 |               11.5 |        1 | NULL            | NULL                |
+|  409 |             16 |            9 |             0.0001 |        1 | NULL            | 2009-01-13 19:21:59 |
+|  413 |             16 |           11 |               14.2 |        1 | NULL            | NULL                |
+|  417 |             16 |           15 |                 25 |        1 | NULL            | NULL                |
+|  425 |              3 |           16 | NULL               |        1 | NULL            | NULL                |
+|  429 |              9 |           16 |              10000 |        1 | NULL            | 2009-01-13 19:21:59 |
+|  433 |             11 |           16 | NULL               |        1 | NULL            | NULL                |
+|  437 |             15 |           16 |               0.04 |        1 | NULL            | NULL                |
+|  441 |              3 |            1 | NULL               |        2 | NULL            | 2009-03-06 14:45:21 |
+|  442 |              9 |            1 | NULL               |        2 | NULL            | 2009-03-06 14:45:21 |
+|  443 |             11 |            1 | NULL               |        2 | NULL            | 2009-03-06 14:45:21 |
+mysql> describe tblClientExchangeRate;
++------------------------+----------+------+-----+---------+----------------+
+| Field                  | Type     | Null | Key | Default | Extra          |
++------------------------+----------+------+-----+---------+----------------+
+| Id                     | int(10)  | NO   | PRI | NULL    | auto_increment |
+| ClientID               | int(10)  | NO   |     | NULL    |                |
+| ExchangeRateNumber     | int(10)  | NO   |     | NULL    |                |
+| ThisClientExchangeRate | double   | NO   |     | NULL    |                |
+| LastUpdated            | datetime | NO   |     | NULL    |                |
++------------------------+----------+------+-----+---------+----------------+
+rows in set
+mysql> select * from tblClientExchangeRate;
+Empty set
+</pre>

Exchange Rates

From UG

Current revision as of 15:29, 18 February 2015

Contents

[edit] Info

[edit] Business Needs and Requirements

[edit] Formulas

[edit] v1

[edit] v2

[edit] Admin

[edit] Office specific or Global?

[edit] Currencies

[edit] Summary

[edit] User Interface and Functionality

[edit] View All Table

[edit] Add

[edit] Edit

[edit] Remove

[edit] Entities and Attributes

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: Manage Currencies. View and Edit.

[edit] DB

[edit] Exchange Rates and View Exchange Rates

[edit] Summary

[edit] User Interface and Functionality

[edit] View

[edit] Edit

[edit] Entities and Attributes

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: View Exchange Rates.

[edit] Figure 2: Manage Exchange Rates.

[edit] DB

[edit] Exchange Rates History

[edit] Info

[edit] Business Needs and Requirements

[edit] Technical Specification

[edit] Summary

[edit] User Interface and Functionality

[edit] Entities and Attributes

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: Exchange Rates History.

[edit] History

[edit] Non Critical Bugs and RFC

[edit] DB

[edit] Client Company Exchange Rates

[edit] Info

[edit] Business Needs and Requirements

[edit] Technical Specification

[edit] Summary

[edit] User Interface and Functionality

[edit] View

[edit] Add

[edit] Edit

[edit] Remove

[edit] Entities and Attributes

[edit] Special Cases and Misc

[edit] Look And Feel

[edit] Figures

[edit] Figure 1: Manage Client Company Exchange Rates.

[edit] History

[edit] Non Critical Bugs and RFC

[edit] DB

[edit] Change Requests

[edit] SOW 0

[edit] SOW 1

[edit] Core Requirements

[edit] Related Functionality

[edit] SOW 2

[edit] Core Requirements

[edit] Solution

[edit] Related Functionality

[edit] SOW 3

[edit] Core Requirements

[edit] Solution