Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0007957Openbravo ERP01. General setuppublic2009-03-05 09:212009-03-26 18:44
rafaroda 
rafaroda 
immediatecriticalalways
closedfixed 
20Ubuntu 7.10
pi 
2.40MP4 
Core
No
0007957: Multi currency issues in some processes and tables
There 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 [^]
1) 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.
No tags attached.
depends on backport 0008096 closed marvintm DBSourceManager: Constraint enabling should be done after onCreateDefault 
related to backport 0008246 closed AinhoaPagola Initial Client Setup fails - ERROR: null value in column "c_currency_id" violates not-null constraint 
related to defect 0010089 closed rafaroda on 2.40 the pl-function ma_standard_cost_sequence does not compile (call non-existant other pl-function) 
blocks defect 0005564 closed rafaroda Multi currency issues in some processes and tables 
blocks backport 0007956 closed rafaroda Sales Reports do not handle currencies 
blocks backport 0007305 closed rafaroda Procurement reports do not handle currencies 
Issue History
2009-03-05 09:21rafarodaNew Issue
2009-03-05 09:21rafarodaAssigned To => rafaroda
2009-03-05 09:21rafarodaRegression testing => No
2009-03-05 09:21rafarodaIssue generated from0005564
2009-03-05 09:21rafarodaRelationship addedblocks 0005564
2009-03-05 09:22rafarodaStatusnew => scheduled
2009-03-05 09:22rafarodafix_in_branch => 2.40
2009-03-05 09:22rafarodaRelationship addedblocks 0007956
2009-03-05 09:24rafarodaRelationship addedblocks 0007305
2009-03-12 15:51rafarodaRelationship addeddepends on 0008096
2009-03-18 13:31hgbotCheckin
2009-03-18 13:31hgbotNote Added: 0014777
2009-03-18 13:31hgbotFixed in SCM revision => http://code.openbravo.com/erp/stable/2.40/rev/3e6cc5386f261c48ead873dcf57ab8bcb438c2e2 [^]
2009-03-18 13:31hgbotCheckin
2009-03-18 13:31hgbotNote Added: 0014778
2009-03-18 13:31hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/3e6cc5386f261c48ead873dcf57ab8bcb438c2e2 [^] => http://code.openbravo.com/erp/stable/2.40/rev/8e7ce118506c8f3935eef5675d630a8e0ec898e4 [^]
2009-03-18 13:31hgbotCheckin
2009-03-18 13:31hgbotNote Added: 0014779
2009-03-18 13:31hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/8e7ce118506c8f3935eef5675d630a8e0ec898e4 [^] => http://code.openbravo.com/erp/stable/2.40/rev/fe9b886ea349d69ed3e823d8fc66baece5306f08 [^]
2009-03-18 13:31hgbotCheckin
2009-03-18 13:31hgbotNote Added: 0014780
2009-03-18 13:31hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/fe9b886ea349d69ed3e823d8fc66baece5306f08 [^] => http://code.openbravo.com/erp/stable/2.40/rev/be1214e99281cf5dc2e02e299d934aa0cfa90f29 [^]
2009-03-18 13:32hgbotCheckin
2009-03-18 13:32hgbotNote Added: 0014781
2009-03-18 13:32hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/be1214e99281cf5dc2e02e299d934aa0cfa90f29 [^] => http://code.openbravo.com/erp/stable/2.40/rev/fee07653f206ccb8ad181365ab3e374e42f72a55 [^]
2009-03-18 13:32hgbotCheckin
2009-03-18 13:32hgbotNote Added: 0014782
2009-03-18 13:32hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/fee07653f206ccb8ad181365ab3e374e42f72a55 [^] => http://code.openbravo.com/erp/stable/2.40/rev/43237535501763c8ecb9a4a3d75d92aad6f1dc32 [^]
2009-03-18 13:32hgbotCheckin
2009-03-18 13:32hgbotNote Added: 0014783
2009-03-18 13:32hgbotStatusscheduled => resolved
2009-03-18 13:32hgbotResolutionopen => fixed
2009-03-18 13:32hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/43237535501763c8ecb9a4a3d75d92aad6f1dc32 [^] => http://code.openbravo.com/erp/stable/2.40/rev/51f14458fd50da0783ac37f3a3b94fee35bdf8e9 [^]
2009-03-24 11:00rafarodaRelationship addedrelated to 0008246
2009-03-24 11:01psarobeFixed in Version => 2.40MP4
2009-03-26 18:24hgbotCheckin
2009-03-26 18:24hgbotNote Added: 0015020
2009-03-26 18:24hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/stable/2.40/rev/51f14458fd50da0783ac37f3a3b94fee35bdf8e9 [^] => http://code.openbravo.com/erp/stable/2.40/rev/3d6a6294ac4099672f4d3ccaa02d0a65451acd61 [^]
2009-03-26 18:44psarobeStatusresolved => closed
2009-07-28 15:32shuehnerRelationship addedrelated to 0010089

Notes
(0014777)
hgbot   
2009-03-18 13:31   
Repository: erp/stable/2.40
Changeset: 3e6cc5386f261c48ead873dcf57ab8bcb438c2e2
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 17:13:03 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/3e6cc5386f261c48ead873dcf57ab8bcb438c2e2 [^]

Issue 0007957 Modified tables

---
M src-db/database/model/tables/AD_CLIENT.xml
M src-db/database/model/tables/M_INOUT.xml
---
(0014778)
hgbot   
2009-03-18 13:31   
Repository: erp/stable/2.40
Changeset: 8e7ce118506c8f3935eef5675d630a8e0ec898e4
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 17:26:34 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/8e7ce118506c8f3935eef5675d630a8e0ec898e4 [^]

Issue 0007957 Modified sample and source data of AD_CLIENT

---
M src-db/database/sampledata/AD_CLIENT.xml
M src-db/database/sourcedata/AD_CLIENT.xml
---
(0014779)
hgbot   
2009-03-18 13:31   
Repository: erp/stable/2.40
Changeset: fe9b886ea349d69ed3e823d8fc66baece5306f08
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 18:12:45 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/fe9b886ea349d69ed3e823d8fc66baece5306f08 [^]

Issue 0007957 Modified functions

---
M src-db/database/model/functions/C_CURRENCY_RATE.xml
M src-db/database/model/functions/GENERATE_AVERAGE_COSTS.xml
M src-db/database/model/functions/GET_PRODUCT_PRICE.xml
M src-db/database/model/functions/GET_PRODUCT_PRICE_DATE.xml
M src-db/database/model/functions/GET_PURCHASE_PRICE.xml
M src-db/database/model/functions/MA_STANDARD_COST.xml
M src-db/database/model/functions/MA_STANDARD_COST_SEQUENCE.xml
M src-db/database/model/functions/M_FREIGHT_CALCULATE.xml
A src-db/database/model/functions/C_CURRENCY_ISOSYM.xml
A src-db/database/model/functions/C_CURRENCY_SYMBOL.xml
---
(0014780)
hgbot   
2009-03-18 13:31   
Repository: erp/stable/2.40
Changeset: be1214e99281cf5dc2e02e299d934aa0cfa90f29
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Fri Mar 13 18:29:10 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/be1214e99281cf5dc2e02e299d934aa0cfa90f29 [^]

Issue 0007957 Modified Utility

---
M src/org/openbravo/erpCommon/utility/Utility.java
M src/org/openbravo/erpCommon/utility/Utility_data.xsql
---
(0014781)
hgbot   
2009-03-18 13:32   
Repository: erp/stable/2.40
Changeset: fee07653f206ccb8ad181365ab3e374e42f72a55
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Mon Mar 16 09:20:36 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/fee07653f206ccb8ad181365ab3e374e42f72a55 [^]

Issue 0007957 Modified AD_REFERENCE

---
M src-db/database/sourcedata/AD_REFERENCE.xml
---
(0014782)
hgbot   
2009-03-18 13:32   
Repository: erp/stable/2.40
Changeset: 43237535501763c8ecb9a4a3d75d92aad6f1dc32
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Mon Mar 16 09:21:14 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/43237535501763c8ecb9a4a3d75d92aad6f1dc32 [^]

Issue 0007957 Modified sourcedata

---
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_ELEMENT.xml
M src-db/database/sourcedata/AD_FIELD.xml
---
(0014783)
hgbot   
2009-03-18 13:32   
Repository: erp/stable/2.40
Changeset: 51f14458fd50da0783ac37f3a3b94fee35bdf8e9
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Mon Mar 16 09:24:09 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/51f14458fd50da0783ac37f3a3b94fee35bdf8e9 [^]

Fixes bug 0007957 Modified messaging

---
M src-db/database/sourcedata/AD_MESSAGE.xml
---
(0015020)
hgbot   
2009-03-26 18:24   
Repository: erp/stable/2.40
Changeset: 3d6a6294ac4099672f4d3ccaa02d0a65451acd61
Author: Rafa Roda Palacios <rafael.roda <at> openbravo.com>
Date: Thu Mar 26 18:22:35 2009 +0100
URL: http://code.openbravo.com/erp/stable/2.40/rev/3d6a6294ac4099672f4d3ccaa02d0a65451acd61 [^]

Issue 0007957 Modified sample data of M_INOUT

---
M src-db/database/sampledata/M_INOUT.xml
---