Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0005564
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 01. General setupmajoralways2008-10-17 12:592009-03-05 09:21
ReporterrafarodaView Statuspublic 
Assigned Torafaroda 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchpiFixed in SCM revision9071
ProjectionnoneETAnoneTarget Version
OSLinux 32 bitDatabasePostgreSQLJava version1.5
OS VersionUbuntu 7.10Database version8.3Ant version1.7
Product VersionpiSCM revision8873 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0005564: Multi currency issues in some processes and tables

DescriptionThere are multi currency issues in the following processes and tables:
   1. AD_CLIENT:
         1. PROBLEM: client does not have a "base" currency. This "base" currency is taken in some cases from the accounting schema of the client. This will not be valid anymore since one Client could have more than one accounting schema.
         2. SOLUTION: define a base currency for a client.
         3. IMPLEMENTATION: AD_CLIENT table [3] should have a mandatory Client_Currency column. 'Initial Client Setup' should fill this column (maybe based of the currency of the Chart of Accounts?). For existing clients, there should be a script that fills this column (maybe based on client's primary accounting schema currency?). This Base Currency should not be updateable, even if a client changes its accounting schemas.
   2. M_COSTING:
         1. PROBLEM: M_COSTING table [4] has two columns (Price and Cost) that do not have a corresponding currency. Three processes update these values without taking into account any currency: M_GENERATE_AVERAGE_COSTS [5], M_GENERATE_STANDARD_COSTS [6] and MA_PRODUCTION_COST [7].
         2. SOLUTION: assume that Price and Cost values are in the Base Currency of the Client.
         3. IMPLEMENTATION: Re-code M_GENERATE_AVERAGE_COSTS, M_GENERATE_STANDARD_COSTS and MA_PRODUCTION_COST processes in order that they do a conversion into the Client base currency before inserting/updating values into Price and Cost columns of M_COSTING. Additionally, for M_GENERATE_AVERAGE_COSTS process, make the corresponding conversions from invoices, orders or shipments currencies to client's base currency.
   3. MA_SEQUENCEPRODUCT:
         1. PROBLEM: MA_SEQUENCEPRODUCT table [8] (Production Management || Transactions || Process Plan || Process Plan >> Version >> Operation >> I/O Products ) has a COST column that has not an associated currency. MA_STANDARD_COST_SEQUENCE process [9] updates this value.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to MA_STANDARD_COST_SEQUENCE process in order to make conversions into the Client base currency.
   4. MA_COSTCENTER:
         1. PROBLEM: MA_COSTCENTER table [10] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   5. MA_COSTCENTER_VERSION:
         1. PROBLEM: MA_COSTCENTER_VERSION table [11] (Production Management || Setup || Cost Center || Cost Center >> Version) has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   6. MA_INDIRECT_COST_VALUE:
         1. PROBLEM: MA_INDIRECT_COST_VALUE table [12] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   7. MA_MACHINE_COST:
         1. PROBLEM: MA_MACHINE_COST table [13] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   8. MA_PL_INVOICELINE:
         1. PROBLEM: MA_PL_INVOICELINE table [14] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
   9. C_SALARY_CATEGORY_COST:
         1. PROBLEM: C_SALARY_CATEGORY_COST table [15] has a COST column that has not an associated currency. Find out which processes update/insert values in COST.
         2. SOLUTION: assume that Cost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
  10. M_PRODUCTIONLINE:
         1. PROBLEM: M_PRODUCTIONLINE table [16] has a CALCCOST column that has not an associated currency. Find out which processes update/insert values in CALCCOST.
         2. SOLUTION: assume that CalcCost values are in the base currency of the client.
         3. IMPLEMENTATION: make, if necessary, the corresponding changes to the processes that insert/update values in order to make conversions to Client base currency.
  11. M_INOUT:
         1. PROBLEM: M_INOUT table [17] (Procurement Management || Transactions || Goods Receipt || Header) has a FREIGHTAMT column that has not an associated currency. This column can be set via a process or calculated based on the values inside Warehouse Management || Setup || Shipping Company || Shipping Company >> Freight.
         2. SOLUTION: add a new non-mandatory column FREIGHT_CURRENCY to M_INOUT in order to indicate the currency of the freight amount.
         3. IMPLEMENTATION: if the freight amount is set manually one could select the currency. If it is calculated via the process, it should update the currency field.
  12. M_OFFER:
         1. PROBLEM: M_OFFER table [18] (Master Data Management || Pricing || Price Adjustments || Adjustments ) has ADDAMT and FIXED columns that have not an associated currency. These columns are used for making fixed price adjustments. The problem is that, taking a fixed discount of 50, if one customer A has a price list in EUR and a product's price is 250 €, he will get it for 150 €. If for the same product, a customer B has a price list in pesos and product's price is 10.000 Pesos, he will get it for 9.950 Pesos.
         2. SOLUTION: assume that the user setting up price adjustments is smart enough to take care of these things and registers different price adjustments for different price lists (specially if they have different currencies). Second solution will require to add a currency column to M_OFFER table to indicate the currency in which this price adjustment is done.
         3. IMPLEMENTATION: the second solution would require to review the processes that calculate the price adjustments and make the conversions between price list currency and price adjustment currency.
  13. Default currency for an user:
         1. PROBLEM: when a user accesses a multi currency report, he wants its "defect" currency to be selected by default in the currency window. Which is this "defect" currency?
         2. SOLUTION: take the base currency of the Client as the "defect" curerncy of the user?

There are other tables with a COST column:

    * M_PRODUCT -> COSTSTD
    * C_BPARTNER -> SO_CREDITLIMIT SO_CREDITUSED ACQUSITIONCOST
    * C_CAMPAIGN -> COSTS
    * I_PRODUCT -> COSTPERORDER
    * M_PRODUCT_PO -> COSTPERORDER --> HAS CURRENCY
    * C_PROJECT -> SERVCOST --> HAS CURRENCY
    * M_PRODUCTIONLINE -> CALCCOST COMPONENTCOST
    * MA_PRODUCTIONPLAN -> CALCCOST
    * MA_PL_EMPLOYEE -> CALCCOST
    * MA_PL_IC -> CALCCOST
    * MA_PL_MACHINE -> CALCCOST
    * MA_SEQUENCE_EMPLOYEE -> CALCCOST
    * MA_SEQUENCE_IC -> CALCCOST
    * MA_SEQUENCE_MACHINE -> CALCCOST
    * MA_SEQUENCE -> COSTCENTERCOST
    * MA_WRPHASEPRODUCT -> COMPONENTCOST
    * R_RESOURCEPLANCOST -> PLANCOST

[3] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/AD_CLIENT.xml [^]
[4] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_COSTING.xml [^]
[5] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml [^]
[6] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/M_GENERATE_STANDARD_COSTS.xml [^]
[7] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/MA_PRODUCTION_COST.xml [^]
[8] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_SEQUENCEPRODUCT.xml [^]
[9] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/MA_STANDARD_COST_SEQUENCE.xml [^]
[10] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_COSTCENTER.xml [^]
[11] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_COSTCENTER_VERSION.xml [^]
[12] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_INDIRECT_COST_VALUE.xml [^]
[13] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_MACHINE_COST.xml [^]
[14] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/MA_PL_INVOICELINE.xml [^]
[15] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/C_SALARY_CATEGORY_COST.xml [^]
[16] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_PRODUCTIONLINE.xml [^]
[17] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_INOUT.xml [^]
[18] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/tables/M_OFFER.xml [^]
Steps To ReproduceA branch has been creted to solve these issues: https://dev.openbravo.com/websvn/openbravo/branches/multi-currency-cost/ [^]
Proposed Solution1) Create the currency mandatory column in AD_CLIENT table (take care of sampledata and demo data). Add an onCreateDefault clause similar to:
<column name="C_CURRENCY_ID" primaryKey="false" required="true" type="VARCHAR" size="32" autoIncrement="false">
<default/>
<onCreateDefault>CASE WHEN AD_CLIENT_ID == 0 THEN '102' ELSE (SELECT C_ACCTSCHEMA.C_CURRENCY_ID FROM C_ACCTSCHEMA, AD_CLIENTINFO WHERE AD_CLIENTINFO.C_ACCTSCHEMA1_ID = C_ACCTSCHEMA.C_ACCTSCHEMA_ID) END</onCreateDefault>
</column>
2) Make changes in MA_STANDARD_COST_SEQUENCE process.
3) Make changes in M_GENERATE_AVERAGE_COSTS process.
4) Add a currency column to M_INOUT table to indicate the currency of FREIGHTAMT column. Modify the process that calculates this freight amount to update the value of this currency column.
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on backport 0007957 closedrafaroda Multi currency issues in some processes and tables 
related to defect 0005625 closedshuehner Improve messaging where there is no conversion rate 
related to defect 0005651 closedrafaroda New C_Currency_id column in M_Inout for Shipper Report 
related to defect 0010747 closedharikrishnan The ad_clientinfo.C_AcctSchema1_ID column is not filled when executing a initial client setup 

-  Notes
(0009624)
rafaroda (developer)
2008-10-17 17:31

 Final steps for this:
* Update demodata of the installer:
SELECT * FROM AD_CLIENT;

SELECT * FROM AD_CLIENTINFO;

SELECT * FROM C_ACCTSCHEMA;

SELECT C_ACCTSCHEMA.C_CURRENCY_ID
FROM C_ACCTSCHEMA, AD_CLIENTINFO, AD_CLIENT
WHERE AD_CLIENT.AD_CLIENT_ID = AD_CLIENTINFO.AD_CLIENT_ID
AND AD_CLIENTINFO.C_ACCTSCHEMA1_ID = C_ACCTSCHEMA.C_ACCTSCHEMA_ID

* Make Initial Client Setup compatible with this feature.

* Update the User Manual and Functional Documentation.
(0009626)
svnbot (reporter)
2008-10-17 17:46

 Repository: openbravo
Revision: 8887
Author: rafaroda
Date: 2008-10-17 17:46:25 +0200 (Fri, 17 Oct 2008)

Issue 0005564 1) Created the currency mandatory column in AD_CLIENT table (onCreateDefault is still missing).

---
U branches/multi-currency-cost/src-db/database/model/tables/AD_CLIENT.xml
U branches/multi-currency-cost/src-db/database/sampledata/AD_CLIENT.xml
U branches/multi-currency-cost/src-db/database/sourcedata/AD_CLIENT.xml
U branches/multi-currency-cost/src-db/database/sourcedata/AD_COLUMN.xml
U branches/multi-currency-cost/src-db/database/sourcedata/AD_FIELD.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=8887&sc=1 [^]
(0009627)
svnbot (reporter)
2008-10-17 19:20

 Repository: openbravo
Revision: 8894
Author: rafaroda
Date: 2008-10-17 19:20:56 +0200 (Fri, 17 Oct 2008)

Issue 0005564 3) Made changes in M_GENERATE_AVERAGE_COSTS process to allow multi currency.

---
U branches/multi-currency-cost/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=8894&sc=1 [^]
(0009649)
psarobe (manager)
2008-10-20 12:16

 That needs to backport
(0009664)
rafaroda (developer)
2008-10-21 09:39

 Issues found also in:
1) M_GET_PRODUCT_PRICE_DATE: takes PRICESTD from M_PRODUCTPRICE depending on a date. Conversion required to base currency.
2) M_GET_PRODUCT_PRICE: takes PRICESTD from M_PRODUCTPRICE with date now(). Conversion required to base currency.
3) M_GET_PURCHASE_PRICE: takes PRICEACTUAL from INVOICELINE or ORDERLINE or PRICESTD from M_PRODUCTPRICE. Conversion required to base currency.
(0009670)
svnbot (reporter)
2008-10-21 19:10

 Repository: openbravo
Revision: 8997
Author: rafaroda
Date: 2008-10-21 19:10:47 +0200 (Tue, 21 Oct 2008)

Issue 0005564 Modified functions:
* M_GET_PRODUCT_PRICE_DATE
* M_GET_PRODUCT_PRICE
* M_GET_PURCHASE_PRICE

---
U branches/multi-currency-cost/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml
U branches/multi-currency-cost/src-db/database/model/functions/M_GET_PRODUCT_PRICE.xml
U branches/multi-currency-cost/src-db/database/model/functions/M_GET_PRODUCT_PRICE_DATE.xml
U branches/multi-currency-cost/src-db/database/model/functions/M_GET_PURCHASE_PRICE.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=8997&sc=1 [^]
(0009690)
svnbot (reporter)
2008-10-22 19:30

 Repository: openbravo
Revision: 9041
Author: rafaroda
Date: 2008-10-22 19:30:10 +0200 (Wed, 22 Oct 2008)

Issue 0005564 Modified procedures:
* MA_STANDARD_COST
* MA_STANDARD_COST_SEQUENCE

---
U branches/multi-currency-cost/src-db/database/model/functions/MA_STANDARD_COST.xml
U branches/multi-currency-cost/src-db/database/model/functions/MA_STANDARD_COST_SEQUENCE.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=9041&sc=1 [^]
(0009691)
svnbot (reporter)
2008-10-22 19:37

 Repository: openbravo
Revision: 9043
Author: rafaroda
Date: 2008-10-22 19:37:44 +0200 (Wed, 22 Oct 2008)

Issue 0005564 Updated M_GET_PRODUCT_PRICE_DATE function

---
U branches/multi-currency-cost/src-db/database/model/functions/M_GET_PRODUCT_PRICE_DATE.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=9043&sc=1 [^]
(0009692)
svnbot (reporter)
2008-10-23 08:28

 Repository: openbravo
Revision: 9050
Author: rafaroda
Date: 2008-10-23 08:28:56 +0200 (Thu, 23 Oct 2008)

Issue 0005564 Updated C_CURRENCY_RATE function

---
U branches/multi-currency-cost/src-db/database/model/functions/C_CURRENCY_RATE.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=9050&sc=1 [^]
(0009695)
svnbot (reporter)
2008-10-23 11:18

 Repository: openbravo
Revision: 9058
Author: rafaroda
Date: 2008-10-23 11:17:33 +0200 (Thu, 23 Oct 2008)

Fixes bug 0005564 Solves multi currency issues in some functions and procedures.

---
U trunk/src/org/openbravo/erpCommon/utility/Utility.java
U trunk/src/org/openbravo/erpCommon/utility/Utility_data.xsql
U trunk/src-db/database/model/functions/C_CURRENCY_CONVERT.xml
U trunk/src-db/database/model/functions/C_CURRENCY_RATE.xml
U trunk/src-db/database/model/functions/MA_STANDARD_COST.xml
U trunk/src-db/database/model/functions/MA_STANDARD_COST_SEQUENCE.xml
U trunk/src-db/database/model/functions/M_GENERATE_AVERAGE_COSTS.xml
U trunk/src-db/database/model/functions/M_GET_PRODUCT_PRICE.xml
U trunk/src-db/database/model/functions/M_GET_PRODUCT_PRICE_DATE.xml
U trunk/src-db/database/model/functions/M_GET_PURCHASE_PRICE.xml
U trunk/src-db/database/model/tables/AD_CLIENT.xml
U trunk/src-db/database/sampledata/AD_CLIENT.xml
U trunk/src-db/database/sourcedata/AD_CLIENT.xml
U trunk/src-db/database/sourcedata/AD_COLUMN.xml
U trunk/src-db/database/sourcedata/AD_FIELD.xml
U trunk/src-db/database/sourcedata/AD_MESSAGE.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=9058&sc=1 [^]
(0009696)
rafaroda (developer)
2008-10-23 11:24

 Updated demo data: https://dev.openbravo.com/websvn/packaging/?rev=191&sc=1 [^]
(0009702)
svnbot (reporter)
2008-10-23 12:28

 Repository: openbravo
Revision: 9071
Author: rafaroda
Date: 2008-10-23 12:28:54 +0200 (Thu, 23 Oct 2008)

Fixes bug 0005564 Small fixes in functions.

---
U trunk/src-db/database/model/functions/C_CURRENCY_RATE.xml
U trunk/src-db/database/model/functions/M_GET_PRODUCT_PRICE.xml
U trunk/src-db/database/model/functions/M_GET_PURCHASE_PRICE.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=9071&sc=1 [^]
(0011026)
psarobe (manager)
2008-12-08 16:39

 Cannot be verify

- Issue History
Date Modified Username Field Change
2008-10-17 12:59 rafaroda New Issue
2008-10-17 12:59 rafaroda Assigned To => rafaroda
2008-10-17 12:59 rafaroda sf_bug_id 0 => 2174493
2008-10-17 12:59 rafaroda Regression testing => No
2008-10-17 17:31 rafaroda Note Added: 0009624
2008-10-17 17:32 rafaroda Steps to Reproduce Updated
2008-10-17 17:46 svnbot Checkin
2008-10-17 17:46 svnbot Note Added: 0009626
2008-10-17 17:46 svnbot svn_revision => 8887
2008-10-17 17:56 rafaroda Steps to Reproduce Updated
2008-10-17 19:20 svnbot Checkin
2008-10-17 19:20 svnbot Note Added: 0009627
2008-10-17 19:20 svnbot svn_revision 8887 => 8894
2008-10-18 09:50 rafaroda Steps to Reproduce Updated
2008-10-20 12:16 psarobe Status new => scheduled
2008-10-20 12:16 psarobe Note Added: 0009649
2008-10-20 12:16 psarobe fix_in_branch => trunk
2008-10-21 09:39 rafaroda Note Added: 0009664
2008-10-21 19:10 svnbot Checkin
2008-10-21 19:10 svnbot Note Added: 0009670
2008-10-21 19:10 svnbot svn_revision 8894 => 8997
2008-10-22 19:30 svnbot Checkin
2008-10-22 19:30 svnbot Note Added: 0009690
2008-10-22 19:30 svnbot svn_revision 8997 => 9041
2008-10-22 19:37 svnbot Checkin
2008-10-22 19:37 svnbot Note Added: 0009691
2008-10-22 19:37 svnbot svn_revision 9041 => 9043
2008-10-23 08:28 svnbot Checkin
2008-10-23 08:28 svnbot Note Added: 0009692
2008-10-23 08:28 svnbot svn_revision 9043 => 9050
2008-10-23 11:18 svnbot Checkin
2008-10-23 11:18 svnbot Note Added: 0009695
2008-10-23 11:18 svnbot Status scheduled => resolved
2008-10-23 11:18 svnbot Resolution open => fixed
2008-10-23 11:18 svnbot svn_revision 9050 => 9058
2008-10-23 11:24 rafaroda Note Added: 0009696
2008-10-23 11:39 rafaroda Relationship added related to 0005625
2008-10-23 12:28 svnbot Checkin
2008-10-23 12:28 svnbot Note Added: 0009702
2008-10-23 12:28 svnbot svn_revision 9058 => 9071
2008-10-24 11:05 rafaroda Relationship added related to 0005651
2008-12-08 16:39 psarobe Status resolved => closed
2008-12-08 16:39 psarobe Note Added: 0011026
2009-03-05 09:21 rafaroda Issue cloned 0007957
2009-03-05 09:21 rafaroda Relationship added depends on 0007957
2009-09-25 07:01 rafaroda Relationship added related to 0010747

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker